1. In the currently exported Excel file, the format of the operation time column is General. See Figure 1.
2. I need to set the operation time column to a date-time format. Reference: Insert Date. The insertDate method can only set the format of a specific cell. I now plan to execute the insertDate method after every 10 rows of data are written, sequentially overwriting the individual cells in the operation time column for those 10 rows. However, I found that the date format was not set for rows 11, 21, 31, and so on. See Figure 2.
3. I checked the parameters of the insertDate method, and they match expectations. The order of the individual cells in the operation time column is correct.
[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. This approach involves writing all the data into Excel first, and then sequentially overwriting the individual cells in the operation time column. The format of the operation time column now matches expectations. See Figure 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;
}
The printed result of $dateData is as follows:
[
{
"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. A colleague reached the following final conclusion:
When exporting Excel and setting cell date formats, we previously wrote all the Excel data first and then iterated through the Excel file to set the cells. Because the array storing the date format settings was too large, it caused a variable memory overflow.
The troubleshooting process for this issue was as follows:
After inserting a row of data, we set the date format for that row. We found that the set dates and the written data were always misaligned by one row.
We tried setting the date format first and then writing the row data. Standalone testing showed that the date rows and data did not get mixed up.
However, when integrated into the actual business logic, the date format was still General. Upon investigation, we found that this was caused by being overwritten when the row data was written. We adjusted the data written to Excel by setting the data in the column that already had the date format set to empty. After testing, the data was correct.
Summary: You must set the date first and then insert the row. When writing the row data, set the data in the column that already has the date format applied to empty to resolve the current issue.
6. I decided to try the summarized approach: set the date first, then insert the row. When writing the row data, set the data in the column that already has the date format applied to 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. The exported Excel file meets expectations. See Figure 4.
8. However, is it possible to batch set the date-time format for cells? Ideally, this would be handled at the same time as data insertion, rather than inserting the date-time cells first and then inserting the rest of the data. Alternatively, after generating the complete Excel file, the format of the entire column could be set directly to a date-time format.
9. The final implementation was to set the format of the entire column directly to a date-time format after generating the complete Excel file. This initially met expectations, except that the title was right-aligned. See Figure 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. The adjusted implementation is as follows:
/**
* 当 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. The final generated Excel file meets expectations. See Figure 6.
