php 导出excel表格

<?php

namespace app\common\services;

class OfficeExportService
{
    /**
     * 导出excel csv格式
     * 建议使用，导出数据量大的时候csv格式会快很多！！！！！
     * @param string $filename 文件名
     * @param array $tileArray 表头标题列表 格式一维数组 [标题1,标题2,标题3,标题n]
     * @param array $dataArray 数据列表数组 格式二维数组 [[1,2,3,n],[1,2,3,n]]
     */
    public function export_to_excel($filename='file', $tileArray=[], $dataArray=[]){
        header('Access-Control-Allow-Origin: *');
        ini_set('memory_limit','512M');
        ini_set('max_execution_time',0);
        ob_end_clean();
        ob_start();
        header("Content-Type: text/csv");
        header("Content-Disposition:filename=".$filename);
        $fp=fopen('php://output','w');
        fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));//转码 防止乱码(比如微信昵称(乱七八糟的))
        fputcsv($fp,$tileArray);
        $index = 0;
        foreach ($dataArray as $item) {
            if($index==1000){
                $index=0;
                ob_flush();
                flush();
            }
            $index++;
            fputcsv($fp,$item);
        }

        ob_flush();
        flush();
        ob_end_clean();
    }

    /**
     * 导出excel xls格式
     * $get_data 要导出的数据
     * $excel_name 文件名
     * $column_arr 头标题，excel第一行的文字标题
     * $auto_warp 自动换行的列
     */
    public function export_data($get_data,$excel_name,$column_arr,$auto_warp = []){
        header('Access-Control-Allow-Origin: *');
        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");

        $col = 0;

        foreach ($column_arr as $field){
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
            $col++;
        }
        // Fetching the table data
        $row = 2;
        foreach($get_data as $data){
            $col = 0;
            foreach ($data as $key => $field){
                //$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row,$field);
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,$field);
                $col++;
            }
            $row++;
        }
        //添加单元格内换行 $auto_warp数组里存允许自动换行的字母列
        if ($auto_warp) {
            for ($i = count($get_data) + 1; $i > 1; $i--) {
                foreach ($auto_warp as $value) {
                    $objPHPExcel->getActiveSheet()->getStyle($value . $i)->getAlignment()->setWrapText(true);
                }
            }
        }
        ob_end_clean();
        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //发送标题强制用户下载文件
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$excel_name.'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }


}

 

导出excel需要安装对应的扩展：

composer require phpoffice/phpexcel