Format the date column when exporting Excel files based on php-xlswriter
1. The format of the excel file exported now, the format of the operation time column is regular. as shown in Figure 1
Figure 1
2. You need to set the operation time column to the date time format. Reference: InsertDate method can only format a specific cell. Now plan to execute the insertDate method after every 10 rows of data are written, overriding the cells of the specific operation time column that writes 10 rows one by one. The date format of lines 11, 21, 31, etc. is found not set. as shown in Figure 2
Figure 2
3. Check the parameters of the insertDate method, which is in line with expectations. The order of the cells of the specific operation time columns 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 scheme is to overwrite all the cells written into the specific operation time column one by one after writing all the data to Excel. The format of one column of operation time is as expected. as shown in Figure 3
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 print 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. This is the conclusion that a colleague finally came to:
Export excel to set the cell date format. Before writing the complete Excel data, it is traversal to set the cell in Excel. Since the array of the format of the date format is too large, the variable memory will overflow.
The problem-solving process:
After inserting a row of data, set the date format of the row, and find that the setting date and the written data are always misplaced, and one row will be wrong
Try to set the date format first, and then write the row data, and the individual test finds that the date row and data will not be messed up,
When it is brought into the actual business, it is found that the date format is still a normal;
Summary: You need to set the date first, then insert the row, and when writing the row data, you need to leave the column of data in the date format that has been set, and you can solve the current problem.
6. Decide to try the summary: set the date first, then insert the row, and when writing the row data, set the column data of the set date format 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 is as expected. as shown in Figure 4
Figure 4
8. However, is it possible to set the date and time format of the cell in batches? It is best to process it together when inserting data, instead of inserting a datetime cell first, and then inserting other data. Or after generating a complete Excel file, directly set the format of the entire column to date and time format.
9. The final implementation is to directly set the format of the entire column to the date and time format after generating the complete Excel file. It was initially in line with expectations, but the title was on the right. as shown in Figure 5
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 realization 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 last generated Excel file is as expected. as shown in Figure 6
Figure 6
