基于 PHP-Xlswriter 导出 Excel 文件时，设置日期列的格式
1、现在导出的 Excel 文件，操作时间一列的格式为常规。如图1
图1
2、需要将操作时间一列设置为日期时间格式。参考：插入日期，insertDate 方法只能够设置某一个具体的单元格的格式。现在计划在每写入 10 行数据后，就执行 insertDate 方法，依次覆盖写入 10 行的一个个的具体的操作时间列的单元格。发现第 11 、21、31 等行的日期格式未设置。如图2
图2
3、查看 insertDate 方法 的参数，符合预期。一个个的具体的操作时间列的单元格的顺序是正确的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | [2024-03-14 07:42:24] local.INFO: row [1,12] [2024-03-14 07:42:24] local.INFO: row [1,17] [2024-03-14 07:42:24] local.INFO: row [2,12] [2024-03-14 07:42:24] local.INFO: row [2,17] [2024-03-14 07:42:24] local.INFO: row [3,12] [2024-03-14 07:42:24] local.INFO: row [3,17] [2024-03-14 07:42:24] local.INFO: row [4,12] [2024-03-14 07:42:24] local.INFO: row [4,17] [2024-03-14 07:42:24] local.INFO: row [5,12] [2024-03-14 07:42:24] local.INFO: row [5,17] [2024-03-14 07:42:24] local.INFO: row [6,12] [2024-03-14 07:42:24] local.INFO: row [6,17] [2024-03-14 07:42:24] local.INFO: row [7,12] [2024-03-14 07:42:24] local.INFO: row [7,17] [2024-03-14 07:42:24] local.INFO: row [8,12] [2024-03-14 07:42:24] local.INFO: row [8,17] [2024-03-14 07:42:24] local.INFO: row [9,12] [2024-03-14 07:42:24] local.INFO: row [9,17] [2024-03-14 07:42:24] local.INFO: row [10,12] [2024-03-14 07:42:24] local.INFO: row [10,17] [2024-03-14 07:42:24] local.INFO: row [11,12] [2024-03-14 07:42:24] local.INFO: row [11,17] [2024-03-14 07:42:24] local.INFO: row [12,12] [2024-03-14 07:42:24] local.INFO: row [12,17] [2024-03-14 07:42:24] local.INFO: row [13,12] [2024-03-14 07:42:24] local.INFO: row [13,17] [2024-03-14 07:42:24] local.INFO: row [14,12] [2024-03-14 07:42:24] local.INFO: row [14,17] [2024-03-14 07:42:24] local.INFO: row [15,12] [2024-03-14 07:42:24] local.INFO: row [15,17] [2024-03-14 07:42:24] local.INFO: row [16,12] [2024-03-14 07:42:24] local.INFO: row [16,17] [2024-03-14 07:42:24] local.INFO: row [17,12] [2024-03-14 07:42:24] local.INFO: row [17,17] [2024-03-14 07:42:24] local.INFO: row [18,12] [2024-03-14 07:42:24] local.INFO: row [18,17] [2024-03-14 07:42:24] local.INFO: row [19,12] [2024-03-14 07:42:24] local.INFO: row [19,17] [2024-03-14 07:42:24] local.INFO: row [20,12] [2024-03-14 07:42:24] local.INFO: row [20,17] |
4、此方案是在将所有数据全部写入 Excel 后，再逐一覆盖写入一个个的具体的操作时间列的单元格。操作时间一列的格式符合预期。如图3
图3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Log::info( '$dateData', $dateData);foreach ($dateData as $rowDateData) { $excel->setDate($rowDateData['row'], $rowDateData['column'], $rowDateData['timestamp']);}public function setDate(int $row, int $column, $timestamp, $formatData = 'yyyy/m/d hh:mm:ss'){ $this->excel->insertDate($row, $column, (int)$timestamp, $formatData); return $this;} |
$dateData 的打印结果如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | [ { "row": 1, "column": 12, "timestamp": 1710243567 }, { "row": 1, "column": 17, "timestamp": 1710243389 }, { "row": 2, "column": 12, "timestamp": 1710235546 }, { "row": 2, "column": 17, "timestamp": 1710235540 }, { "row": 3, "column": 12, "timestamp": 1710186421 }, { "row": 3, "column": 17, "timestamp": 1710186418 }, { "row": 4, "column": 12, "timestamp": 1710186130 }, { "row": 4, "column": 17, "timestamp": 1710186126 }, { "row": 5, "column": 12, "timestamp": 1710186131 }, { "row": 5, "column": 17, "timestamp": 1710186126 }, { "row": 6, "column": 12, "timestamp": 1710186131 }, { "row": 6, "column": 17, "timestamp": 1710186126 }] |
5、这是一位同事最终得出的结论如下：
导出excel 设置单元格日期格式，之前采用写完整个excel数据之后，再遍历excel设置单元格，由于保存设置日期格式的数组过大，会导致变量内存溢出
这个问题解决流程：
插入一行数据之后，再设置该行的日期格式，发现设置日期和写入的数据总是错位，会错一行
尝试先设置日期格式，再写入行数据，单独测试发现，日期行和数据不会错乱，
带入实际业务中，发现日期格式还是常规；经过排查发现，是被写入行数据覆盖导致，调整写入excel的数据，把已经设置日期格式的数据置空，经过测试数据正确；
总结：要先设置日期 再插入行，写入行数据的时候，要把已经设置的日期格式的那一列数据置空，即可解决当前的问题
6、决定尝试一下总结出的方案：要先设置日期 再插入行，写入行数据的时候，要把已经设置的日期格式的那一列数据设置为 null
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | $columns = $params['readShop'] ? [12, 17] : [10, 15];foreach ($orderShippingLogs as $rowKey => $orderShippingLog) { $data = static::toResource($orderShippingLog, $params['timezone']); foreach ($columns as $column) { Log::info( 'row', [$rowKey + $total, $column, $data[$column]] ); $excel->setDate($rowKey + $total, $column, strtotime($data[$column])); $data[$column] = null; } $excel->writeRows([$data]); // $data[] = static::toResource($orderShippingLog, $params['timezone']);}// $excel->writeRows($data); |
7、导出的 Excel 文件符合预期。如图4
图4
8、但是，是否可以批量的设置单元格的日期时间格式呢。最好是在插入数据时，一并处理，而不是先插入日期时间单元格，再插入其他的数据。或者是在生成完整的 Excel 文件后，直接设置整列的格式为日期时间格式。
9、最终实现为在生成完整的 Excel 文件后，直接设置整列的格式为日期时间格式。初步符合预期，只不过标题居右了。如图5
图5
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | /* * [['range' => 'A1:A1', 'width' => 7], ['range' => 'B1:B1', 'width' => 7], ...] */$this->columns = $columns;/** * 当 Excel 文件生成完毕后，设置一些列的整列的样式 * * @param array $columns 列序号，其必须存在于 $this->columns 的键中 * [12, 17] * @param string $formatData * @return void */public function setSomeColumnStyle(array $columns, string $formatData = 'yyyy/m/d hh:mm:ss'){ $fileHandle = $this->excel->getHandle(); $format = new \Vtiful\Kernel\Format($fileHandle); $boldStyle = $format->number($formatData)->align(Format::FORMAT_ALIGN_RIGHT)->toResource(); foreach ($columns as $column) { $this->excel->setColumn($this->columns[$column]['range'], $this->columns[$column]['width'], $boldStyle); } return $this;}$excel->setSomeColumnStyle($columns); |
10、调整后的实现如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | /** * 当 Excel 文件数据完全写入后，设置一些列的整列的样式 * * @param array $columns 列序号，其必须存在于 $this->columns 的键中 * [12, 17] * @param string $formatData * @return void */public function setSomeColumnStyle(array $columns, string $formatData = 'yyyy/m/d hh:mm:ss'){ $fileHandle = $this->excel->getHandle(); $format = new \Vtiful\Kernel\Format($fileHandle); $boldStyle = $format->number($formatData)->align(Format::FORMAT_ALIGN_RIGHT)->toResource(); foreach ($columns as $column) { $this->excel->setColumn($this->columns[$column]['range'], $this->columns[$column]['width'], $boldStyle); } // 由于时间格式列需要居右，进而导致了整列居右，需要将第一行重新设置为居左 $headerFormat = new \Vtiful\Kernel\Format($fileHandle); $headerStyle = $headerFormat->align(Format::FORMAT_ALIGN_LEFT)->toResource(); $this->excel->setRow('A1', 15, $headerStyle); return $this;} |
11、最后的生成的 Excel 文件符合预期。如图6
图6
近期评论