php excel多个sheet导出

前段时间做公司的人力资源系统，其中用到很多表格导出，整理了一下

用的是PHPExcel插件

/**
 * 多个sheet
 * @author Red
 * @date
 * @param $list
 * @param $excelFieldsZHCN
 * @param $excelFileName
 * @param $sheetTitle
 */
function exportExcels($list, $excelFieldsZHCN, $excelFileName, $sheetTitle)
{
    $excelFileName = iconv('UTF-8', 'GBK', $excelFileName);

    $excelFileName = $excelFileName . date('YmdHi', time());
    include APP_PATH . '/Vendor/PHPExcel.php';
    $objPHPExcel = new PHPExcel();

    $objPHPExcel->getProperties()->setCreator("Red")->setLastModifiedBy("")->setTitle('i am superredman')->setDescription("create by red");
    //构造excel 列名
    $index = 0;
    $ret   = array();
    foreach ($excelFieldsZHCN as $key => $value) {
        $objPHPExcel->createSheet();
        $i = 0;
        foreach ($value as $fieldName => $ZHCN) {
            $pCoordinate = \PHPExcel_Cell::stringFromColumnIndex($i);
            $objPHPExcel->setActiveSheetIndex($index)->setCellValue($pCoordinate . '1', $value[$fieldName]);
            $ret[$i] = $fieldName;
            $i++;
        }
        $row = 2;//EXCEL 行索引 从第二行自增
        if ($list[$key]) {
            foreach ($list[$key] as $item) {
                $i = 0;
                foreach ($ret as $field) {

                    $pCoordinate = \PHPExcel_Cell::stringFromColumnIndex($i);
                    if(is_array($item[$field]) && $item[$field]['img']){
                        /*实例化插入图片类*/
                        $objDrawing = new PHPExcel_Worksheet_Drawing();
                        /*设置图片路径 切记：只能是本地图片*/
                        $objDrawing->setPath($item[$field]['path']);
                        /*设置图片高度*/
                        $objDrawing->setHeight($item[$field]['height']);
                        $objDrawing->setWidth($item[$field]['width']);
                        //图片位置
                        $objDrawing->setOffsetX(5);
                        $objDrawing->setOffsetY(5);
                        /*设置图片要插入的单元格*/
                        $objDrawing->setCoordinates($pCoordinate . $row);
                        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
                        //设置行高和行宽
                        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight($item[$field]['width']);
                    }else{
                        $objPHPExcel->setActiveSheetIndex($index)->setCellValue($pCoordinate . $row, ' ' . strip_tags($item[$field]));//过滤html标签
                    }

                    $i++;
                }
                $row++;
            }
        }
        $objPHPExcel->getActiveSheet()->setTitle($sheetTitle[$key]);
        $objPHPExcel->setActiveSheetIndex($index);
        $index++;
    }


    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $excelFileName . '.xls"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');
    // If you're serving to IE over SSL, then the following may be needed
    header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
    header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header('Pragma: public'); // HTTP/1.0
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;

}

　　导出的时候只需要按照固定格式调用即可

       $list = array(
            1=>$list1,
            2=>$list2,
            3=>$list3
        );
        //$list1 $list2 $list3为数据源
        $tableFiled = array(
            1 => array(
                'kid'           => '序号',
                'uName'         => '公司',
                'dName'         => '部门',
                'leave_count'   => '离职人数',
                'total_count'   => '原部门人数',
                'leave_rate'    => '离职率',

            ),
            2 => $table_list2,
            3 => $table_list3,

        );
        $sheetName  = array(
            1 => '离职人员分布',
            2 => '离职类别',
            3 => '离职原因',
        );
        exportExcels($list, $tableFiled, '离职统计', $sheetName);