使用phpExcel导出xls数据,将需要导出的数据格式成类似如下数组:
Array
(
[财务] => Array
(
[0] => Array
(
[0] => Array
(
[width] => 6
[value] => ID
[align] => center
)
[1] => Array
(
[width] => 10
[value] => 日期
[align] => center
)
[2] => Array
(
[width] => 8
[value] => 收入/支出
[align] => left
)
[3] => Array
(
[width] => 10
[value] => 金额
[align] => right
)
)
[1] => Array
(
[0] => 1
[1] => 2020-04-21
[2] => 支出
[3] => 20.00
)
[2] => Array
(
[0] => 2
[1] => 2020-04-21
[2] => 支出
[3] => 27.38
)
)
[计划] => Array
(
[0] => Array
(
[0] => Array
(
[width] => 6
[value] => ID
[align] => center
)
[1] => Array
(
[width] => 20
[value] => 标题
[align] => left
)
[2] => Array
(
[width] => 6
[value] => 重要程度
[align] => center
)
[3] => Array
(
[width] => 30
[value] => 内容
[align] => left
)
)
[1] => Array
(
[0] => 2
[1] => 测试计划
[2] => 正常
[3] => 内容
)
)
)
/**
* 使用 phpExcel 导出 Excel
* @param array $data 内容
* @param string $fileName 文件名
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
* @throws PHPExcel_Writer_Exception
*/
function exportExcel($data = [], $fileName = '账单导出数据.xls') {
if (empty($data)) die('内容不能为空!');
if (empty($fileName)) die('文件名不能为空!');
// 设置页面等待时间
set_time_limit(60);
// 不限制内存
ini_set('memory_limit', -1);
// 引入库
require APP_ROOT . 'phpExcel/PHPExcel.php';
$phpExcel = new PHPExcel();
// 所有单元格居中
//$phpExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$i = 0;
// 外循环产生每一项 Sheet
foreach ($data as $key => $lists) {
// 创建新的工作空间 sheet
if ($i > 0) {
$phpExcel->createSheet();
}
$phpExcel->setActiveSheetIndex($i);
// 给 Sheet 设置名字
$phpExcel->getActiveSheet()->setTitle($key);
// 内容
$line = 1;
// 外循环产生每一列
foreach ($lists as $item) {
// 内循环产生每一行
foreach ($item as $k => $value) {
$columnName = PHPExcel_Cell::stringFromColumnIndex($k);
if ($line == 1) {
if (isset($value['width'])) {
$phpExcel->getActiveSheet()->getColumnDimension($columnName)->setWidth($value['width']);
}
if (isset($value['align'])) {
$phpExcel->getActiveSheet()->getStyle($columnName)->getAlignment()->setHorizontal($value['align']);
}
$value = isset($value['value']) ? $value['value'] : (is_string($value) ? $value : '');
}
// 数字列转换为字母列 如:27变为AA
$phpExcel->getActiveSheet()->setCellValue($columnName . $line, $value);
}
$line++;
}
$i++;
}
$fileExt = substr($fileName, strrpos($fileName, '.'));
$fileName = substr($fileName, 0, strrpos($fileName, '.'));
$fileName = $fileName . '-' . date('Y-m-d H-i-s') . $fileExt;
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Content-Type: application/force-download');
header('Content-Type: application/vnd.ms-execl');
header('Content-Type: application/octet-stream');
header('Content-Type: application/download');
header('Content-Disposition: attachment; filename=' . iconv('UTF-8', 'UTF-8', $fileName));
header('Content-Transfer-Encoding: binary');
$objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}