Closed JrDwarf closed 3 months ago
I have laptop with i7-10510U CPU & 16 Gb RAM. I create Excel file 50 cols x 40k rows
elapsed time: 68.447 sec memory peak usage: 367001600 total rows: 40000 speed: 584.394 rows/sec
I took your code and skip report_header (I don't think it affects performance). And this code runs on my laptop in 68 seconds
<?php
require_once __DIR__ . '/vendor/autoload.php';
require_once __DIR__ . '/src/autoload.php';
function randStr($maxLen)
{
static $chars = 'abcdefghijklmnopqrstuvwxyz0123456789 ';
$result = '';
$len = random_int(1, $maxLen);
for ($j = 0; $j < $len; $j++) {
$result .= $chars[mt_rand() % 36];
}
return $result;
}
// fill $excel_data_array - 50 cols & 40k rows
$excel_data_array = [
'table_header' => [],
'table_data' => [],
];
$maxCol = 50;
$maxRow = 40000;
for ($col = 0; $col < $maxCol; $col++) {
if ($col === 0) {
$excel_data_array['table_header'][] = 'row';
}
else {
$excel_data_array['table_header'][] = randStr(10);
}
}
for ($row = 0; $row < $maxRow; $row++) {
for ($col = 0; $col < $maxCol; $col++) {
if ($col === 0) {
$excel_data_array['table_data'][$row][] = $row + 1;
}
else {
$excel_data_array['table_data'][$row][] = randStr(10);
}
}
}
$outFileName = __DIR__ . '/50-40k.xlsx';
// ++++++++++
// start point here
$timer = microtime(true);
$excel = \avadim\FastExcelWriter\Excel::create(['Sheet1']);
$sheet = $excel->sheet();
$sheet->setTopLeftCell('B2'); //start from B2
$headStyle = [
'font' => [
'style' => 'bold',
],
];
//write the table header
$table_header = $excel_data_array['table_header'];
$sheet->writeRow($table_header, $headStyle)->applyBorder('thin')->applyTextAlign('center');
// Assuming $table_header is an array containing headers up to max column
// Set column width for columns C to the end column
$end_column = count($table_header) + 2; // Add 2 to skip columns A and B
for ($col = 3; $col <= $end_column; $col++) {
$sheet->setColWidthAuto($col);
}
//write all td
$table_data = $excel_data_array['table_data'];
foreach($table_data as $row_data) {
$sheet->writeRow($row_data)->applyBorder('thin')->applyTextAlign('center');
}
$excel->save($outFileName);
$time = round(microtime(true) - $timer, 3);
echo '<b>', basename(__FILE__, '.php'), "</b><br>\n<br>\n";
echo 'out filename: ', $outFileName, "<br>\n";
echo 'elapsed time: ', $time, ' sec', "<br>\n";
echo 'memory peak usage: ', memory_get_peak_usage(true), "<br>\n";
echo 'total rows: ', $maxRow, "<br>\n";
echo 'speed: ', round($maxRow / $time, 3), " rows/sec<br>\n";
You can take just my code and check how long it will take for you to execute
I just tested with your library and phpspreadsheet to compare the speed of the writing of the excel with the 40k+ lines of row and 50 column of data.
Both takes about 23min, so i don't see any improvement for the library? Unless I'm doing it wrongly. Kindly provide any extra advice that I should know about. Thanks