定义一个通用的数据导出方法，这样在需要的地方定义一下数据就可以快速调用了。
public static function export_data($data, $info, $file_name = '')
{
$spreadsheet = new Spreadsheet();
// 设置文档的基本属性
$spreadsheet->getProperties()->setCreator(APPLICATION_NAME)//创作者
->setLastModifiedBy(APPLICATION_NAME)//最后更新者
->setTitle(APPLICATION_NAME . '-数据统计')//属性标题
->setSubject(APPLICATION_NAME . '-文档整理')//主题
->setDescription('小V平台一个是集成微信系统、智能公众号、微官网、微商城、支付中台等功能的平台')
->setKeywords('小V平台，' . APPLICATION_NAME)
->setManager('www.wei1.top')
->setCompany('唯依科技')
->setCategory('免费资源');
try {
//表头数据
$head = $info;
$columnCount = count($head);//获取总列数
//设置居中
$spreadsheet->getActiveSheet()->getStyle($head[0]['columnName'] . ':' . $head[$columnCount - 1]['columnName'])
->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
//设置加粗
$spreadsheet->getActiveSheet()
->getStyle($head[0]['columnName'] . $head[0]['dataStartCell'] . ':' . $head[$columnCount - 1]['columnName'] . $head[$columnCount - 1]['dataStartCell'])
->getFont()->setBold(true)->setName('Arial');
//设置表头数据
foreach ($head as $value) {
$spreadsheet->setActiveSheetIndex(0)->setCellValue($value['columnName'] . $value['dataStartCell'], $value['value']);
//设置列宽度
$spreadsheet->getActiveSheet()->getColumnDimension($value['columnName'])->setWidth($value['width']);
//设置颜色
/*$spreadsheet->getActiveSheet()->getStyle($value['cellName'])->getFont()->getColor()
->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_BLACK);*/
}
//这种方式有点消耗性能，但是通用一点，其他地方定义一个基本info就行了
if (!empty($data['data'])) {
foreach ($data['data'] as $k => $item) {
foreach ($head as $key => $hd) {
$spreadsheet->setActiveSheetIndex(0)
->setCellValue($hd['columnName'] . ($hd['dataStartCell'] + $k + 1), $item[$hd['dataColName']]);
}
unset($data['data'][$k]);
}
}
$file_name = $file_name ? $file_name . '.xlsx' : '数据统计' . date('Ymd') . '.xlsx';//设置文件名称
// 重命名表单
$spreadsheet->getActiveSheet()->setTitle($file_name);
//设置一个激活状态（打开时选中状态）,支持范围,默认0
$spreadsheet->setActiveSheetIndex(0);
//把数据从PHP的缓冲（buffer）中释放出来，这样才可以支持大数据量导出。
if (ob_get_level()>0){
ob_flush();
}
// 设置浏览器输出文件头header类型，这里是xlsx格式
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename=' . $file_name);
header('Cache-Control: max-age=0');
//如果浏览器是IE9，则这里添加下面这句header
header('Cache-Control: max-age=1');
//如果您是通过SSL服务于IE，那么可能需要以下内容
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
exit;
} catch (\PhpOffice\PhpSpreadsheet\Exception $e) {
exit($e->getMessage());
}
}定义数据后直接调用
$info = [ ['columnName' => 'A', 'dataStartCell' => 1, 'dataColName' => 'adminName', 'value' => '管理员', 'width' => 20, 'height' => 20], ['columnName' => 'B', 'dataStartCell' => 1, 'dataColName' => 'orderId', 'value' => '平台单号', 'width' => 25, 'height' => 20], ['columnName' => 'C', 'dataStartCell' => 1, 'dataColName' => 'outOrder', 'value' => '外部单号', 'width' => 25, 'height' => 20], ['columnName' => 'D', 'dataStartCell' => 1, 'dataColName' => 'price', 'value' => '应付金额', 'width' => 10, 'height' => 10], ['columnName' => 'E', 'dataStartCell' => 1, 'dataColName' => 'payPrice', 'value' => '支付金额', 'width' => 10, 'height' => 10], ['columnName' => 'F', 'dataStartCell' => 1, 'dataColName' => 'payChannel', 'value' => '支付方式', 'width' => 10, 'height' => 20], ['columnName' => 'G', 'dataStartCell' => 1, 'dataColName' => 'payTime', 'value' => '支付时间', 'width' => 20, 'height' => 20], ['columnName' => 'H', 'dataStartCell' => 1, 'dataColName' => 'addTime', 'value' => '新增时间', 'width' => 20, 'height' => 20], ['columnName' => 'I', 'dataStartCell' => 1, 'dataColName' => 'refundMoney', 'value' => '退款金额', 'width' => 10, 'height' => 20], ]; $data = self::index(true, 10000); ExportServer::export_data($data, $info, '中台支付订单');
在使用过程自己添加其他功能：在info里面设置高度、单元格类型（数字、字符串等）
提示：这里嵌套了循环，会消耗内存资源，但是比较方便，简单的导出可以很通用
