我有一個包含 3070 個值的多維數組$tbl= array( array( "KDNR" => 1, "GESCHL" => "test", "TITEL" => "test", "VORNAME" => "test", "FAMNAME" => "test", "PLZ" => "test", "ORT" => "test", "STRASSE" => "test", "EMAIL" => "test", "PRIVTEL" => "test" ), "KDNR" => 2, "GESCHL" => "test2", "TITEL" => "test2", "VORNAME" => "test2", "FAMNAME" => "test2", "PLZ" => "test2", "ORT" => "test2", "STRASSE" => "test2", "EMAIL" => "test2", "PRIVTEL" => "test2" ), etc...);我想將具有 3070 個數組的數組 tbl 寫入 xlsx 文件。我用于這個 PhpSpreadsheet。這是我的 php 代碼：<?php//call the autoloadrequire($_SERVER['DOCUMENT_ROOT'].'/src/phpspreadsheet/vendor/autoload.php');//load phpspreadsheet class using namespacesuse PhpOffice\PhpSpreadsheet\Spreadsheet;//call iofactory instead of xlsx writeruse PhpOffice\PhpSpreadsheet\Aligment;use PhpOffice\PhpSpreadsheet\Fill;use PhpOffice\PhpSpreadsheet\IOFactory;//load from xlsx template$reader = IOFactory::createReader('Xlsx');$spreadsheet = $reader->load($_SERVER['DOCUMENT_ROOT']. '/src/ExcelVorlagen/polbezirk_template.xlsx');//loop the data$contentStartRow = 3;$currentContenRow = 3;//set coulm dimension to auto size$spreadsheet->getActiveSheet() ->getColumnDimension('A') ->setAutoSize(true);$spreadsheet->getActiveSheet() ->getColumnDimension('B') ->setAutoSize(true);$spreadsheet->getActiveSheet() ->getColumnDimension('C') ->setAutoSize(true);$spreadsheet->getActiveSheet() ->getColumnDimension('D') ->setAutoSize(true);$spreadsheet->getActiveSheet() ->getColumnDimension('E') ->setAutoSize(true);$spreadsheet->getActiveSheet() ->getColumnDimension('F') ->setAutoSize(true);$spreadsheet->getActiveSheet() ->getColumnDimension('G')}當我執行代碼時，創建 xlsx 文件需要 49 分鐘，它只需要 3070 行。有更快的方法嗎？或者我的代碼中有 ia 瓶頸？
3 回答
人到中年有點甜
TA貢獻1895條經驗 獲得超7個贊
我修改了我的代碼：
$row = count($tbl);
$spreadsheet->getActiveSheet()->insertNewRowBefore($currentContenRow + 1, $row);
foreach($tbl as $item){
//fill the cell with Data
$spreadsheet->getActiveSheet()
->setCellValue('A'.$currentContenRow, $item['KDNR'])
->setCellValue('B'.$currentContenRow, $item['GESCHL'])
->setCellValue('C'.$currentContenRow, $item['TITEL'])
->setCellValue('D'.$currentContenRow, $item['VORNAME'])
->setCellValue('E'.$currentContenRow, $item['FAMNAME'])
->setCellValue('F'.$currentContenRow, $item['PLZ'])
->setCellValue('G'.$currentContenRow, $item['ORT'])
->setCellValue('H'.$currentContenRow, $item['STRASSE'])
->setCellValue('I'.$currentContenRow, $item['EMAIL'])
->setCellValue('J'.$currentContenRow, $item['PRIVTEL']);
//increment the current row number
$currentContenRow++;
}
現在創建 xlsx 文件需要 15 秒
HUX布斯
TA貢獻1876條經驗 獲得超6個贊
最近我不得不做一個類似的工作，并認為它可能值得分享，它可能會幫助某人。
代碼獲取您的原始數組 ( $tbl)，并重新格式化它（在數組的開頭注入列標題record），以便正確格式化數據，以便 PhpSpreadsheet 處理和寫入.xlsx文件。
用于處理數據的函數：（$spreadsheet->getActiveSheet()->fromArray()見下文）。
<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
require dirname(__DIR__, 1) . "/vendor/autoload.php";
// the original array
$tbl = [
[
"KDNR" => 1,
"GESCHL" => "test",
"TITEL" => "test",
"VORNAME" => "test",
"FAMNAME" => "test",
"PLZ" => "test",
"ORT" => "test",
"STRASSE" => "test",
"EMAIL" => "test",
"PRIVTEL" => "test"
],
[
"KDNR" => 2,
"GESCHL" => "test2",
"TITEL" => "test2",
"VORNAME" => "test2",
"FAMNAME" => "test2",
"PLZ" => "test2",
"ORT" => "test2",
"STRASSE" => "test2",
"EMAIL" => "test2",
"PRIVTEL" => "test2"
],
];
/*
* inject header 'record'.
*/
$headers = array_keys($tbl[0]); // get headers from source array
array_unshift($tbl, $headers); // insert headers as first record
/*
* write data to xlsx file
*/
$spreadsheet = new Spreadsheet();
// build spreadsheet from array
$spreadsheet->getActiveSheet()->fromArray($tbl,
NULL, // array values with this value will not be set
'A1');
// write array data to xlsx file
$writer = new Xlsx($spreadsheet);
$writer->save('yourfile.xlsx');
$tbl準備好由 處理的重新洗牌的數組$spreadsheet->getActiveSheet()->fromArray()如下所示：
Array
(
[0] => Array
(
[0] => KDNR
[1] => GESCHL
[2] => TITEL
[3] => VORNAME
[4] => FAMNAME
[5] => PLZ
[6] => ORT
[7] => STRASSE
[8] => EMAIL
[9] => PRIVTEL
)
[1] => Array
(
[KDNR] => 1
[GESCHL] => test
[TITEL] => test
[VORNAME] => test
[FAMNAME] => test
[PLZ] => test
[ORT] => test
[STRASSE] => test
[EMAIL] => test
[PRIVTEL] => test
)
[2] => Array
(
[KDNR] => 2
[GESCHL] => test2
[TITEL] => test2
[VORNAME] => test2
[FAMNAME] => test2
[PLZ] => test2
[ORT] => test2
[STRASSE] => test2
[EMAIL] => test2
[PRIVTEL] => test2
)
)
第一條記錄將用于設置列標題，以下記錄為行數據。
生成的 xlsx 文件：
慕雪6442864
TA貢獻1812條經驗 獲得超5個贊
我會用https://github.com/aVadim483/fast-excel-writer對此進行測試，在我的筆記本上創建 10K 行的 xlsx 需要 1.12 秒
require 'src/autoload.php';
$row = [
"KDNR" => 1,
"GESCHL" => "test",
"TITEL" => "test",
"VORNAME" => "test",
"FAMNAME" => "test",
"PLZ" => "test",
"ORT" => "test",
"STRASSE" => "test",
"EMAIL" => "test",
"PRIVTEL" => "test"
];
$tbl = [];
// fill $tpl
for ($i = 0; $i < 10000; $i++) {
$tbl[] = $row;
}
$excel = \avadim\FastExcelWriter\Excel::create();
$sheet = $excel->getSheet();
$timer = microtime(true);
$sheet->writeRow(array_keys($row));
foreach($tbl as $row) {
$sheet->writeRow($row);
}
$excel->save('simple.xlsx');
echo 'elapsed time: ', round(microtime(true) - $timer, 3), ' sec';
- 3 回答
- 0 關注
- 219 瀏覽
添加回答
舉報
0/150
提交
取消