先composer安装PhpSpreadsheet扩展
composer require "phpoffice/phpspreadsheet": "^1.11"
直接贴代码
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
public function exportExcel($title, $header, $sheet_title, $data)
{
$count = count($data);
$spreadsheet = new Spreadsheet();
for($i=0;$i<$count;$i++){
$spreadsheet->createSheet();//创建sheet
$objActSheet = $spreadsheet->setActiveSheetIndex($i);//设置当前的活动sheet
$objActSheet->setTitle($sheet_title[$i]);
foreach ($header[$i] as $index => $item) {
$cellnum = Coordinate::stringFromColumnIndex($index + 1) . "1";
$objActSheet->setCellValue($cellnum, $item);
$objActSheet->getStyle($cellnum)->getFont()->setBold(true); //标题栏加粗
$objActSheet->getStyle($cellnum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); //居中
}
$loop = 2;
foreach ($data[$i] as $values) {
foreach ($values as $index => $val) {
$cellnum = Coordinate::stringFromColumnIndex($index + 1);
$objActSheet->setCellValue($cellnum . $loop, $val);
$objActSheet->getStyle($cellnum. $loop)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); //居中
}
$loop++;
}
}
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=' . $title . '.xlsx');
header('Cache-Control: max-age=0');
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
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空:
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
数据格式说明:
$title = '班级1快速问答报告';
//每个sheet的表头
$header = Array
(
[0] => Array
(
[0] => 题号
[1] => 题型
[2] => A
[3] => B
[4] => C
[5] => D
[6] => 正确
[7] => 错误
[8] => 正确答案
[9] => 正确率
)
[1] => Array
(
[0] => 姓名
[1] => 1
[2] => 2
[3] => 3
[4] => 正确率
)
[2] => Array
(
[0] => 姓名
[1] => 1
[2] => 2
[3] => 3
)
)
//每个sheet的名称
$sheet_title = Array
(
[0] => 题目统计
[1] => 学生统计
[2] => 原始数据
)
//数据,对应每个表格的表头
$data = Array
(
[0] => Array
(
[0] => Array
(
[0] => 1
[1] => 多选题
[2] => 39
[3] => 37
[4] => 3
[5] => 1
[6] => --
[7] => --
[8] => AB
[9] => 87.5%
)
)
[1] => Array
(
[0] => Array
(
[0] => 诸葛大师
[1] => ✔
[2] => ○
[3] => ✔
[4] => 66.67%
)
)
[2] => Array
(
[0] => Array
(
[0] => 诸葛大师
[1] => AB
[2] => 错误
[3] => A
)
)
)
好啦,就这样吧,自己测试成功了