php excel 多sheet导出函数
安装 phpoffice/phpspreadsheet 包
composer require phpoffice/phpspreadsheet
实现代码如下
<?php
/**
* Created by PhpStorm.
* User: xinu x-php@outlook.com
* Coding Standard: PSR2
* DateTime: 2020-07-11 21:24
*/
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$sheet = [
'sheetTitle' => [
'header' => [
[
'title' => '列1', // 表头名
'key' => 'id', // data中的键名
'width' => 20, // 列宽度 可不设置
],
[
'title' => '列2', // 表头名
'key' => 'name', // data中的键名
'width' => 20, // 列宽度 可不设置
]
],
'data' => [
[
'id' => 1,
'name' => 'test1',
]
]
]
];
function exportXlsx($sheets, $file)
{
$AZ = [
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N'
];
ob_end_clean();
$spreadsheet = new Spreadsheet();
$n = 0;
foreach ($sheets as $sheetTitle => $sheet) {
$data = array_values($sheet['data']);
$header = array_values($sheet['header']);
if (empty($header)) {
throw new \Exception('excel头不可为空');
}
if ($n) {
$spreadsheet->addSheet(new Worksheet($spreadsheet, (string)$sheetTitle), $n);
}
$sheet = $spreadsheet->getSheet($n);
$sheet->setTitle((string)$sheetTitle);
$n++;
foreach ($header as $kIndex => $item) {
$k = $AZ[$kIndex];
if (isset($item['width'])) {
// 设置列宽
$sheet->getColumnDimension($k)->setWidth(floatval($item['width']));
}
$sheet->setCellValue("{$k}1", $item['title']);
// 设置标题加粗
$sheet->getStyle("{$k}1")->getFont()->setBold(true);
}
foreach ($data as $k => $row) {
$rowNum = $k + 2;
foreach ($header as $xIndex => $v) {
$x = $AZ[$xIndex];
$sheet->setCellValue("{$x}{$rowNum}", $row[$v['key']]);
}
}
}
$spreadsheet->setActiveSheetIndex(0);
$writer = new Xlsx($spreadsheet);
header("Content-Type: application/octet-stream");
header("Content-Disposition: attachment;filename=\"{$file}\"");
header("Cache-Control: max-age=0");
$writer->save('php://output');
exit();
}