基于 PHP-Xlswriter 导出 Excel 文件时，设置日期列的格式
1、现在导出的 Excel 文件，操作时间一列的格式为常规。如图1
图1
2、需要将操作时间一列设置为日期时间格式。参考：插入日期，insertDate 方法只能够设置某一个具体的单元格的格式。现在计划在每写入 10 行数据后，就执行 insertDate 方法，依次覆盖写入 10 行的一个个的具体的操作时间列的单元格。发现第 11 、21、31 等行的日期格式未设置。如图2
图2
3、查看 insertDate 方法 的参数，符合预期。一个个的具体的操作时间列的单元格的顺序是正确的。
[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
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 的打印结果如下所示：
[
{
"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
$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
/*
* [['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、调整后的实现如下
/**
* 当 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
近期评论