Долгая вставка большого объема данных и строк

[Palamaryuk]

Привет. Помогите пожалуйста решить проблему.

Создаем экспорт Excel файл на 18 колонок, в каждой из них, в среднем, по 50-100 символов. Версия либы последняя. Каждая такая строка вставляется за +- 0.002-0.005 миллисекунд. При экспорте больших файлов, примерно на 50 тыс строк наблюдаются следующие проблемы.

1) Файл в 10 тыс строк формируется около минуты (0.002-0.005) * 10 000 2) Каждые следующие 10 тысяч строк вставляются сильно дольше, например 10-20 тыс вставляются 3 минуты, 20-30 6 минут.

Может можно как то это обойти кодировками, версией Excel файла, батч или другие способы?

Для вставки используем \avadim\FastExcelWriter\Sheet::writeRow

[aVadim483]

Привет! Думаю, тут проблема не в библиотеке. Провел сейчас синтетические тесты, 18 столбцов и строки по 100 символов, результат:

10К строк пишутся менее, чем за 6 секунд elapsed time: 5.652 sec memory peak usage: 16Mb total rows: 10000 speed: 1769.285 rows/sec

100К строк - скорость упала на 15%, генерация чуть более минуты elapsed time: 66.165 sec memory peak usage: 18Mb total rows: 100000 speed: 1511.373 rows/sec

Вот код теста:

<?php

require_once __DIR__ . '/vendor/autoload.php';
require_once __DIR__ . '/src/autoload.php';

$maxCol = 18;
$maxRow = 100000;

$rowData = [];
for ($col = 0; $col < $maxCol; $col++) {
    $rowData[] = str_repeat(chr(65 + $col), 100);
}

$outFileName = __DIR__ . '/' . $maxCol . '-' . (int)($maxRow / 1000) . 'k.xlsx';

$timer = microtime(true);

$excel = \avadim\FastExcelWriter\Excel::create(['Sheet1']);
$sheet = $excel->sheet();

for($rowNum = 1; $rowNum <= $maxRow; $rowNum++) {
    $sheet->writeRow($rowData);
}
$excel->save($outFileName);

$time = round(microtime(true) - $timer, 3);
$memory = round(memory_get_peak_usage(true) / (1024 * 1024), 2);

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, "Mb<br>\n";
echo 'total rows: ', $maxRow, "<br>\n";
echo 'speed: ', round($maxRow / $time, 3), " rows/sec<br>\n";

[aVadim483]

Но это голые данные, если используется оформление, скорость может быть ниже, но вряд ли она может упасть так сильно

[Palamaryuk]

Спасибо. Буду разбираться дальше, отпишусь когда найду решение.

[Palamaryuk]

Привет. Проблема оказалась в методе \avadim\FastExcelWriter\Sheet::mergeCells. У нас на входе для 50 тысяч записей передается 30 тысяч мерж элементов. Каждая следующая проверка медленней предыдущей, и когда остается около 9 тысяч время выполнения с 0.001 возрастает до 0.03.

Пробема в цикле который проверяет пересечения, если его убрать - выполняется моментально. Пробовали решить другими проверками - не смогли пока что. Для себя выбрали решение убрать цикл с проверками, добавив параметр в вышеуказанный метод.

[aVadim483]

О, спасибо за фидбэк, надо будет подумать, как это ускорить. А мержатся ячейки внутри одной строки?

[Palamaryuk]

Большая часть мерж между строками

[aVadim483]

Немного оптимизировал объединение ячеек, если объединение идет не вразброс (например, A1:E10, потом Z120:AA300, потом B5:C6), а более менее последовательно (скажем, B1:B2, B3:B4, B5:B6 и т.д.), то работать будет довольно быстро.

Но если уверены, что объединяемые ячейки точно не пересекутся, то можно отключать проверку на пересечение, если передавать вторым параметром -1:

$sheet->mergeCells('B1:B2', -1);
$sheet->mergeCells('B3:B4', -1);
$sheet->mergeCells('B5:B6', -1);

Но имейте ввиду, что если по факту будет пересечение объединяемых ячеек, то Excel будет ругаться при открытии созданного файла

[Palamaryuk]

@aVadim483 привет. Снова добрался до задачи, вижу твой фикс. Хотел обновить пакет, чтобы не городить свои костыли. Но если я передам -1, то буду ловить исключения, вместо скипа вот тут

https://github.com/aVadim483/fast-excel-writer/blob/7bb81b99ab952c50bd3e3b4b43813a74dae8def6/src/FastExcelWriter/Sheet.php#L2049

[aVadim483]

Не понял, почему? Исключение там возникает, если адрес сам по себе кривой, т.е., например, не 'A1:C3', а, скажем A1:3'

Пример кода:

$sheet->writeRow([1, 2, 3, 4]);
$sheet->writeRow([1, 2, 3, 4]);
$sheet->writeRow([1, 2, 3, 4]);

$sheet->mergeCells('A2:B2', -1);
$sheet->mergeCells('A3:C3', -1);

Но, как выше писал, с параметром -1 надо самому следить, чтобы не было пересечений, потому что такой код тоже сработает:

$sheet->mergeCells('A2:B2', -1);
$sheet->mergeCells('A1:C3', -1);

Но получишь, в итоге, поломанный эксель-файл, который будет давать ошибку при открытии, т.к. диапазоны пересекаются