php导出文件Execl格式

方法一：（支持各种Execl格式）

/**
 * PHP封装导出Execl
 * @param string $expTitle 文件名（不含扩展名）
 * @param array $expCellName 导航头部数据，二维数组，$expCellName = [ ['id','序号'],['mobile','手机'],['createat','时间'] ]
 * @param array $expTableData 数据，二维数组
 */
function exportExcel($expTitle,$expCellName,$expTableData){
    $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
    $fileName = $expTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
    $cellNum = count($expCellName);
    $dataNum = count($expTableData);

    vendor("PHPExcel.PHPExcel");

    $objPHPExcel = new \PHPExcel();
    $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');

    // $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
    // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
    for($i=0;$i<$cellNum;$i++){
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);
    }

    for($i=0;$i<$dataNum;$i++){
        for($j=0;$j<$cellNum;$j++){
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
        }
    }

    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls");
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}

方法二：（比较旧的格式）

/**
 * 导出csv文件
 * @param string $filename 文件名（不含扩展名）
 * @param string $Fields 导航头部列标题，例如：$Fields = ['序号', '订单编号/充值流水号', '设备编号', '角色', '姓名', '手机号', '总金额', '分成比例', '收益金额', '渠道', '时间'];
 * @param string $fields 导航头部对应的数据库参数，例如：$fields = ['id', 'order_no', 'macno', 'user_type', 'nickname', 'mobile', 'total_money', 'discount', 'money', 'log', 'createat'];
 * @param array $data 数据，二维数组
 */
function export_to_csv($filename, $Fields, $fields, $data)
{
    //清除缓冲区
    ob_end_clean();
    header("Content-Type: text/html;charset=utf-8");
    header("Content-type:application/vnd.ms-excel");
    header("Content-Disposition:filename=$filename.xls");

    //$Fields = ['序号', '订单编号/充值流水号', '设备编号', '角色', '姓名', '手机号', '总金额', '分成比例', '收益金额', '渠道', '时间'];
    //$fields = ['id', 'order_no', 'macno', 'user_type', 'nickname', 'mobile', 'total_money', 'discount', 'money', 'log', 'createat'];

    echo "<table bordercolor='gray' border='1' cellspacing='0' style='border-collapse:collapse;font-family:微软雅黑'>";
    echo "<tr>";

    //echo "<pre>";
    //print_r($Fields);die;

    foreach($Fields as $v){
        echo "<th>".$v."</th>";
    }
    echo "</tr>";
    /***********头部结束********************/
    if($data){
        foreach($data as $k => $v){
            echo "<tr>";
            foreach($fields as $v1){
                if($v1 == 'id'){
                    $v[$v1] = $k+1;
                }
                echo "<td style='vnd.ms-excel.numberformat:@'>".$v[$v1]."</td>";
            }
            echo "</tr>";
        }
        echo "</table>";
    }else{
        echo "<tr><td>暂无数据</td></tr>";
        echo "</table>";
    }
    echo exit;
}

方法三：（自测通过）

/**
 * 数据导出(自测)
 */
public function actionExport() {

    $objPHPExcel = new \PHPExcel();

    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);

    $objPHPExcel->setActiveSheetIndex(0);

    $objPHPExcel->getActiveSheet()
        ->setCellValue('A1', '用户id')
        ->setCellValue('B1', '用户昵称')
        ->setCellValue('C1', '注册账号')
        ->setCellValue('D1', 'MT5账号')
        ->setCellValue('E1', '模拟账号')
        ->setCellValue('F1', '注册时间')
        ->setCellValue("G1", '登陆次数')
        ->setCellValue("H1", '最后登陆时间')
        ->setCellValue("I1", '状态');

    $key = 1;
    $data = [
        ['id'=>10000,'nickname'=>'李白','email'=>'10000@qq.com','mt5'=>10001,'demo_mt5_id'=>10000,'bu_login_num'=>10,'status'=>0,]
    ];
    /* 以下就是对处理Excel里的数据，横着取数据 */
    foreach ($data as $v) {
        //设置循环从第二行开始
        $key++;
        $reg_time = date("Y-m-d H:i:s", time());
        $last_login = date("Y-m-d H:i:s", time());
        $status = $v["status"] == 0 ? "正常":"禁用";
        $objPHPExcel->getActiveSheet()
            //Excel的第A列，name是你查出数组的键值字段，下面以此类推
            ->setCellValue('A' . $key, $v['id'])
            ->setCellValue('B' . $key, $v['nickname'])
            ->setCellValue('C' . $key, $v['email'])
            ->setCellValue('D' . $key, $v['mt5'])
            ->setCellValue('E' . $key, $v['demo_mt5_id'])
            ->setCellValue('F' . $key, $reg_time)
            ->setCellValue('G' . $key, $v['bu_login_num'])
            ->setCellValue('H' . $key, $last_login)
            ->setCellValue('I' . $key, $status);

    }
    ob_end_clean();
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header('Content-Disposition:attachment;filename=数据导出（自测）.xls');
    header("Content-Transfer-Encoding:binary");
    $objWriter->save('php://output');
    exit;
}