具有大数据的 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')

}

当我执行代码时，创建 xlsx 文件需要 49 分钟，它只需要 3070 行。有更快的方法吗？或者我的代码中有 ia 瓶颈？

Answer 1

我修改了我的代码：$row = count($tbl);$spreadsheet->getActiveSheet()->insertNewRowBefore($currentContenRow + 1, $row);foreach($tbl as $item){&nbsp; &nbsp; //fill the cell with Data&nbsp; &nbsp; $spreadsheet->getActiveSheet()&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('A'.$currentContenRow, $item['KDNR'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('B'.$currentContenRow, $item['GESCHL'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('C'.$currentContenRow, $item['TITEL'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('D'.$currentContenRow, $item['VORNAME'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('E'.$currentContenRow, $item['FAMNAME'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('F'.$currentContenRow, $item['PLZ'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('G'.$currentContenRow, $item['ORT'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('H'.$currentContenRow, $item['STRASSE'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('I'.$currentContenRow, $item['EMAIL'])&nbsp; &nbsp; &nbsp; &nbsp; ->setCellValue('J'.$currentContenRow, $item['PRIVTEL']);&nbsp; &nbsp; //increment the current row number&nbsp; &nbsp; $currentContenRow++;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;}现在创建 xlsx 文件需要 15 秒

Answer 2

最近我不得不做一个类似的工作，并认为它可能值得分享，它可能会帮助某人。代码获取您的原始数组 ( $tbl)，并重新格式化它（在数组的开头注入列标题record），以便正确格式化数据，以便 PhpSpreadsheet 处理和写入.xlsx文件。用于处理数据的函数：（$spreadsheet->getActiveSheet()->fromArray()见下文）。<?phpuse PhpOffice\PhpSpreadsheet\Spreadsheet;use PhpOffice\PhpSpreadsheet\Writer\Xlsx;require dirname(__DIR__, 1) . "/vendor/autoload.php";// the original array$tbl = [&nbsp; &nbsp; [&nbsp; &nbsp; "KDNR" => 1,&nbsp; &nbsp; "GESCHL" => "test",&nbsp; &nbsp; "TITEL" => "test",&nbsp; &nbsp; "VORNAME" => "test",&nbsp; &nbsp; "FAMNAME" => "test",&nbsp; &nbsp; "PLZ" => "test",&nbsp; &nbsp; "ORT" => "test",&nbsp; &nbsp; "STRASSE" => "test",&nbsp; &nbsp; "EMAIL" => "test",&nbsp; &nbsp; "PRIVTEL" => "test"&nbsp; &nbsp; ],&nbsp; &nbsp; [&nbsp; &nbsp; "KDNR" => 2,&nbsp; &nbsp; "GESCHL" => "test2",&nbsp; &nbsp; "TITEL" => "test2",&nbsp; &nbsp; "VORNAME" => "test2",&nbsp; &nbsp; "FAMNAME" => "test2",&nbsp; &nbsp; "PLZ" => "test2",&nbsp; &nbsp; "ORT" => "test2",&nbsp; &nbsp; "STRASSE" => "test2",&nbsp; &nbsp; "EMAIL" => "test2",&nbsp; &nbsp; "PRIVTEL" => "test2"&nbsp; &nbsp; ],];/*&nbsp;* inject header 'record'.&nbsp;*/$headers = array_keys($tbl[0]); // get headers from source arrayarray_unshift($tbl, $headers); // insert headers as first record/*&nbsp;* write data to xlsx file&nbsp;*/$spreadsheet = new Spreadsheet();// build spreadsheet from array$spreadsheet->getActiveSheet()->fromArray($tbl,&nbsp; &nbsp; NULL, // array values with this value will not be set&nbsp; &nbsp; 'A1');// write array data to xlsx file$writer = new Xlsx($spreadsheet);$writer->save('yourfile.xlsx');$tbl准备好由 处理的重新洗牌的数组$spreadsheet->getActiveSheet()->fromArray()如下所示：Array(&nbsp; &nbsp; [0] => Array&nbsp; &nbsp; &nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [0] => KDNR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [1] => GESCHL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [2] => TITEL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [3] => VORNAME&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [4] => FAMNAME&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [5] => PLZ&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [6] => ORT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [7] => STRASSE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [8] => EMAIL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [9] => PRIVTEL&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; [1] => Array&nbsp; &nbsp; &nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [KDNR] => 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [GESCHL] => test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [TITEL] => test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [VORNAME] => test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [FAMNAME] => test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [PLZ] => test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ORT] => test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [STRASSE] => test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [EMAIL] => test&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [PRIVTEL] => test&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; [2] => Array&nbsp; &nbsp; &nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [KDNR] => 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [GESCHL] => test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [TITEL] => test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [VORNAME] => test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [FAMNAME] => test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [PLZ] => test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [ORT] => test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [STRASSE] => test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [EMAIL] => test2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [PRIVTEL] => test2&nbsp; &nbsp; &nbsp; &nbsp; ))第一条记录将用于设置列标题，以下记录为行数据。生成的 xlsx 文件：

Answer 3

我会用https://github.com/aVadim483/fast-excel-writer对此进行测试，在我的笔记本上创建 10K 行的 xlsx 需要 1.12 秒require 'src/autoload.php';$row = [&nbsp; &nbsp; "KDNR" => 1,&nbsp; &nbsp; "GESCHL" => "test",&nbsp; &nbsp; "TITEL" => "test",&nbsp; &nbsp; "VORNAME" => "test",&nbsp; &nbsp; "FAMNAME" => "test",&nbsp; &nbsp; "PLZ" => "test",&nbsp; &nbsp; "ORT" => "test",&nbsp; &nbsp; "STRASSE" => "test",&nbsp; &nbsp; "EMAIL" => "test",&nbsp; &nbsp; "PRIVTEL" => "test"];$tbl = [];// fill $tplfor ($i = 0; $i < 10000; $i++) {&nbsp; &nbsp; $tbl[] = $row;}$excel = \avadim\FastExcelWriter\Excel::create();$sheet = $excel->getSheet();$timer = microtime(true);$sheet->writeRow(array_keys($row));foreach($tbl as $row) {&nbsp; &nbsp; $sheet->writeRow($row);}$excel->save('simple.xlsx');echo 'elapsed time: ', round(microtime(true) - $timer, 3), ' sec';