问题描述
我有一个包含3070个值的多维数组
i have a multidimensional array with 3070 Values
$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.
I want to write the array tbl with 3070 arrays to a xlsx file.I use for this PhpSpreadsheet.
这是我的php代码:
<?php
//call the autoload
require($_SERVER['DOCUMENT_ROOT'].'/src/phpspreadsheet/vendor/autoload.php');
//load phpspreadsheet class using namespaces
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//call iofactory instead of xlsx writer
use 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')
->setAutoSize(true);
$spreadsheet->getActiveSheet()
->getColumnDimension('H')
->setAutoSize(true);
$spreadsheet->getActiveSheet()
->getColumnDimension('I')
->setAutoSize(true);
$spreadsheet->getActiveSheet()
->getColumnDimension('J')
->setAutoSize(true);
xdebug_break();
foreach($tbl as $item){
//insert a row after current row (before current row + 1)
$spreadsheet->getActiveSheet()->insertNewRowBefore($currentContenRow + 1,1);
//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++;
}
//remove last empty rows
//$spreadsheet->getActiveSheet()->removeRow($currentContenRow,2);
//set the header first, so the result will be treated as an xlsx file
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="result.xlsx"');
//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//save int php output
$writer->save('php://output');
当我执行代码时，创建xlsx文件需要49分钟，而该文件仅占用3070行.有没有更快的方法?还是我的代码存在瓶颈?
when i execute the code it takes 49 minutes to create the xlsx file, this it to long for only 3070 rows.Is there a faster way? Or have i a bottleneck in my code?
希望你们能帮助我
最诚挚的问候
推荐答案
最近我不得不做类似的工作，并认为这可能值得分享，可能会对某人有所帮助.
Recently I had to do a similar job, and thought it might be worth sharing, it might help someone.
该代码获取原始数组( $ tbl )并重新格式化(在该数组的开头插入列标题 record )，以便正确格式化数据供PhpSpreadsheet处理并写入 .xlsx 文件.
The code takes your original array ($tbl), and reformats it (injects a column header record at the start of the array) so the data is formatted properly for PhpSpreadsheet to process and write to a .xlsx file.
用于处理数据的函数: $ spreadsheet-> getActiveSheet()-> fromArray()(请参见下文).
The function used to process the data: $spreadsheet->getActiveSheet()->fromArray() (see below).
<?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()处理，如下所示:
The reshuffled $tbl array, ready to be processed by $spreadsheet->getActiveSheet()->fromArray(), looks as follows:
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
)
)
第一个记录将用于设置列标题，随后的记录是行数据.
The first record will be used to set column headers, the following records are the row data.
生成的xlsx文件:
The resulting xlsx file:
这篇关于带有大数据的PhpSpreadsheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！