具有大数据的 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 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 瓶颈？

Answer 1

我修改了我的代码：

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

Answer 2

最近我不得不做一个类似的工作，并认为它可能值得分享，它可能会帮助某人。

代码获取您的原始数组 ( $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 文件：

Answer 3

我会用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';