导出方法使用的是Spreadsheet插件,但是原作者的方法对表头进行初始化形成数组,导致表头列数固定,当数据字段数量超出表头列数时,就会发生错误。为解决这个问题,将代码修改如下:
1.public static function export($tableNam, $moduleName)
2. {
3. ob_end_clean();
4. // 获取主键
5. $pk = \app\common\facade\MakeBuilder::getPrimarykey($tableNam);
6. // 获取列表数据
7. $columns = \app\common\facade\MakeBuilder::getListColumns($tableNam);
8. // 搜索
9. $where = \app\common\facade\MakeBuilder::getListWhere($tableNam);
10. $orderByColumn = \think\facade\Request::param('orderByColumn') ?? $pk;
11. $isAsc = \think\facade\Request::param('isAsc') ?? 'desc';
12. $model = '\app\common\model\\' . $moduleName;
13. // 获取要导出的数据
14. $list = $model::getList($where, 0, [$orderByColumn => $isAsc]);
15. // 初始化表头数组
16. //原代码:$str = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
17. $spreadsheet = new Spreadsheet();
18. $sheet = $spreadsheet->getActiveSheet();
19. $sheet->calculateColumnWidths();
20. $spreadsheet->getDefaultStyle()->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);//※默认垂直居中
21. $spreadsheet->getDefaultStyle()->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);//※默认水平居中
22. $spreadsheet->getDefaultStyle()->getFont()->setName('微软雅黑');//※默认字体
23. $spreadsheet->getDefaultStyle()->getFont()->setSize(10);//※默认字体大小
24. foreach ($columns as $k => $v) {
25.// ※原代码: $sheet->setCellValue($str[$k] . '1', $v['1']);
26.
27. $sheet->setCellValueByColumnAndRow($k+1, 2, $v['1']);//※修改为以坐标获取单元格的方式赋值
28. $column_max=$k+1;//※获取最大列标
29. $sheet->getColumnDimensionByColumn($column_max)->setAutoSize(true);//※把所有列宽设置为自动列宽
30. }
31. $list = isset($list['total']) && isset($list['per_page']) && isset($list['data']) ? $list['data'] : $list;
32. foreach ($list as $key => $value) {
33. foreach ($columns as $k => $v) {
34. // 修正字典数据
35. if (isset($v[4]) && is_array($v[4]) && !emptyempty($v[4])) {
36. $value[$v['0']]=$v[4][$value[$v['0']]];
37. }
38.// $sheet->setCellValue($str[$k] . ($key + 2), $value[$v['0']]);
39. $sheet->setCellValueByColumnAndRow($k+1, $key + 3, $value[$v['0']]);//※修改为以坐标获取单元格的方式赋值
40. $row_max=$key + 3;//※获取最大行标
41. }
42. //※设置主体单元格样式
43. }$styleArray = [
44. 'borders' => [
45. 'allBorders' => [
46. 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
47.
48. ],
49. ],
50. ];
51. $sheet->getStyleByColumnAndRow(1,2,$column_max,$row_max)->applyFromArray($styleArray);
52. //※设置主体单元格样式
53. $moduleName = \app\common\model\Module::where('table_name', $tableNam)->value('module_name');
54. $sheet->mergeCellsByColumnAndRow(1,1,$column_max,1);//※合并单元格作为大标题
55. $sheet->setCellValueByColumnAndRow(1,1,$moduleName);//※写入标题
56. $sheet->getStyleByColumnAndRow(1,1)->getFont()->setName('黑体');//※设置标题字体
57. $sheet->getStyleByColumnAndRow(1,1)->getFont()->setSize(18);//※设置标题字号
58. $spreadsheet->getActiveSheet()->setTitle($moduleName);//※设置工作表标签页标题
59. header('Content-Type: application/vnd.ms-excel');
60. header('Content-Disposition: attachment;filename="' . $moduleName . '导出' . '.xlsx"');
61. header('Cache-Control: max-age=0');
62. $writer = new Xlsx($spreadsheet);
63. $writer->save('php://output');
64. }