【PHP】PHPExcel数据导出方法封装

1、方法封装

/**
 * @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;
    }

}

 

2、调用示例 data为二维数组数据

<?php

$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,'Certificate_list_'.date("Ymd", time()).'.xlsx');

?>