用PHPExcel数据导出封装

安装组件

composer require phpoffice/phpexcel

引用组件

use PHPExcel;
use PHPExcel_Cell;
use PHPExcel_IOFactory;

封装代码

    /**
     * @description excel数据导出方法封装 列数支持700+
     * @param array $title 列标题、宽度和数据字段 二维数组  必须 array(array('title'='name','width'=>30,'field'=>'user_name'),array('title'='sex','width'=>10,'field'=>'sex'))
     * @param array $data  二维数组数据
     * @param string $fileName  导出文件名
     * @param bool  $bold 列标题是否加粗
     * @param bool $returnFile 是否保存为文件
     */

    function exportDataToExcel($title = array(), $data = array(), $fileName = '', $bold = true,$returnFile = false){

        if (empty($title)) {
            return false;
        }
        $objPHPExcel = new PHPExcel();
        $count = count($title);

        for ($i = 0; $i < $count; $i++) {
            $i_name = PHPExcel_Cell::stringFromColumnIndex($i);//处理列大于26个的方法
            //设置列宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension($i_name)->setWidth($title[$i]['width']);
            //设置列名
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($i_name . '1', $title[$i]['title']);
            //列名是否加粗
            if ($bold) {
                $objPHPExcel->getActiveSheet()->getStyle($i_name . '1')->getFont()->setBold(true);
            }
            //数据处理
            if($data){
                foreach ($data as $key => $item) {
                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($i_name . ($key + 2), $item[$title[$i]['field']]);
                }
            }
        }

        $objPHPExcel->getActiveSheet()->setTitle('Simple');
        $objPHPExcel->setActiveSheetIndex(0);
        $fileName = $fileName ? $fileName : date("Ymd", time()) . '.xlsx';
        if($returnFile){
            $dirPath = '/';//注意要设置为绝对路径
        $newFileName = date('YmdHis').'_'.$fileName;
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($dirPath.'/'.$newFileName);
        return $newFileName;
    }else{
            ob_end_clean();
            header('Content-Type: application/vnd.ms-excel');
            header('Access-Control-Expose-Headers: Content-Disposition');
            header('Content-Disposition: attachment;filename=' . $fileName);
            header('Cache-Control: max-age=0');
            header('Cache-Control: max-age=1');
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
            header('Cache-Control: cache, must-revalidate');
            header('Pragma: public');

            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $objWriter->save('php://output');
            exit;
        }

    }

使用实例

<?php
    /**
     * @param array $title 列标题、宽度和数据字段 二维数组  必须 array(array('title'='name','width'=>30,'field'=>'user_name'),array('title'='sex','width'=>10,'field'=>'sex'))
     * @param array $data  二维数组数据
     */


$title = array(
                array('title'=>'Certificate name','width'=>20,'field'=>'cert_name'),
                array('title'=>'Name','width'=>30,'field'=>'user_name'),
                array('title'=>'Company','width'=>30,'field'=>'user_company'),
                array('title'=>'Certificate ID','width'=>30,'field'=>'cert_key'),
                array('title'=>'Effective date','width'=>15,'field'=>'effect_time'),
                array('title'=>'Expiration time','width'=>15,'field'=>'expire_time'),
                array('title'=>'Country','width'=>30,'field'=>'country'),
                array('title'=>'Region','width'=>15,'field'=>'region'),
                array('title'=>'SF_COMPANYCODE','width'=>20,'field'=>'SF_COMPANYCODE'),
                array('title'=>'EMAIL','width'=>30,'field'=>'EMAIL'),
                array('title'=>'COMPANYTYPE','width'=>30,'field'=>'COMPANYTYPE')
            );
            handleSheetData($title,$data,time().'.xlsx');

?>