具有大数据的 PhpSpreadsheet

Question

我有一个包含 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 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 行。有没有更快的方法？还是我的代码存在瓶颈？

希望大家能帮帮我

最好的问候

Answer 1

非常感谢 kalyfe，我这样修改了我的代码:

$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 秒