php最新导出excel适用于tp5框架,其他框架暂未测试
<?php
class exportExcel{
public function test()
{
$info = [
['name'=>'小明','age'=>'18'],
['name'=>'小红','age'=>'16'],
];
$title = ['名称','年龄'];
$this->excel('测试表',$info,$title);
}
/**
* @param string $name 表名
* @param array $data 数据内容
* @param array $head 表头
*
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public function excel($name = '表名', $data=[], $head=[])
{
$title = $this->getTitle($head, array_keys($data[0]));
$count = count($head); //计算表头数量
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
foreach ($data as $key => $item) { //循环设置单元格:
for ($i = 65; $i < $count + 65; $i++) { //数字转字母从65开始:
$sheet->setCellValue(strtoupper(chr($i)) . '1', $title[$i - 65]['title']);//数字转字母从65开始,循环设置表头
$sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$title[$i - 65]['id']]);//$key+2,因为第一行是表头,所以写到表格时 从第二行开始写
$spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
}
}
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空:
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
/**
* 数据格式处理
*
* @param array $arr 导出数据
* @param array $column 表头信息
*
* @return array
*/
public function getTitle($arr, $column)
{
$info = [];
if (!empty($arr)) {
foreach ($arr as $k=>$v){
$info[$k]['title'] = $v;
$info[$k]['id'] = $column[$k];
}
}
return $info;
}
}