PHP导出Excel表包含多个sheet

1.封装方法

/**
 * 导出数据
 * @param $list表格数据  二维数组
 * @param $excelFieldsZHCN 表头字段  二维数组
 * @param $excelFileName Excel文件名字 字符串
 * @param $sheetTitle    Excel文件sheet名字 一维数组
 */
function exportExcels($list, $excelFieldsZHCN, $excelFileName, $sheetTitle)
{
$excelFileName = iconv('UTF-8', 'GBK', $excelFileName);
$excelFileName = $excelFileName . date('YmdHi', time());
//需要注意下面引入的文件，微擎框架下不需要改动，其他按照实际文件目录引用。
include_once IA_ROOT . '/framework/library/phpexcel/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;
}
2.函数调用

//直接调用封装好的函数，传的参数需要自己去阻止。参数形式函数解释中有说明。
exportExcels($excledata, $tableFiled, $area['title'], $sheetName);