php导出多张表关联数据,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);

本文来自投稿，不代表微擎百科立场，如若转载，请注明出处：https://www.w7.wiki/develop/2695.html