Продуктивність при роботі з великими книгами Excel у Delphi з HotXLS

Коли експорт на 300 000 рядків проламує свій бюджет пам’яті, звинувачують зазвичай кількість рядків. Кількість рядків зазвичай невинна. Дорогі частини великої книги — це ті, що виникають побічним ефектом: пул стилів, який росте на один запис на клітинку, бо форматування додали всередині циклу; XML аркуша, зібраний в один величезний рядок під час збереження; мільйон однакових тіл формул, збережених по одному. HotXLS, нативна бібліотека losLab на Delphi для файлів XLS і XLSX, дає вам окремий важіль на кожну з цих витрат. Жоден із них не увімкнений усталено, бо кожен змінює якийсь компроміс, тож справжня майстерність продуктивності — знати, який важіль відповідає якому симптому

На що велика книга витрачає пам’ять

Розмірковувати варто про два окремі режими пам’яті. Під час генерації модель клітинок у пам’яті росте з кожною клітинкою, якої ви торкаєтеся: значення, формати й формули всі стають об’єктами або записами в пулі. Під час збереження усталений шлях XLSX додатково промальовує XML кожного аркуша в широкий рядок, перш ніж стиснути його в zip-контейнер, тож пікове споживання — це модель плюс серіалізована форма найбільшого аркуша. Завдання, що переживає цикл побудови, а тоді помирає всередині SaveAs, б’ється в другий режим, а не в перший, і ліки від одного нічим не зарадять другому

Розмір файлу дотримується суміжного правила: клітинки лише один із учасників, поряд зі стилями, спільними рядками, формулами, зображеннями й примітками. Прохід аудиту через ForEachCell і лічильники поаркушевих колекцій скаже вам, який ресурс насправді домінує в проблемному файлі, перш ніж ви оптимізуєте не той. Одна тонкість вимірювання: Sheet.Cells.Count з боку XLSX повідомляє кількість створених клітинок у розрідженому сховищі, а не площу використаного діапазону. Аркуш, чиї дані займають прямокутник 1000 на 50 із половиною порожніх клітинок, налічує близько 25 000, а не 50 000. Ця відмінність важить, коли ви порівнюєте «величезний» файл замовника зі своїми фікстурами, бо площа використаного діапазону й фактична населеність клітинок можуть різнитися на порядок у розріджених фінансових компонуваннях

StreamingWrite лагодить шлях збереження, а не шлях побудови

Задавання TXLSXWorkbook.StreamingWrite := True перемикає SaveAs на потоковий серіалізатор, що пише XML аркуша просто в zip-потік, усуваючи проміжний рядок на кожен аркуш. Усталено він має значення False заради сумісності поведінки, а ввімкнути його — це зміна в один рядок:

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // XML аркуша транслюється в zip-контейнер
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Будьте точні щодо того, що це дає: модель клітинок, побудована циклом, займає рівно стільки ж пам’яті, скільки й раніше. StreamingWrite згладжує стрибок під час збереження, а це різниця між пакетним завданням, що доходить до кінця, і тим, що падає на позначці 95%. Якщо пам’ять вичерпує сам цикл побудови, потрібні вам важелі — два наступні

Пули стилів: додайте раз, перевикористовуйте індекс

Форматування XLSX у HotXLS базоване на пулах: Book.Fonts.Add(...), Fills.AddSolid(...) і Borders.Add(...) повертають індекс у пулі від нуля, на який посилаються клітинки. Виклик Fonts.Add з однаковими параметрами всередині циклу дедуплікується, тож він марнує час, а не місце. Alignments.Add поводиться інакше: він повертає свіжий об’єкт на кожен виклик, тож створення вирівнювання на кожну клітинку нарощує пул лінійно з кількістю рядків. Одна звичка покриває обидва випадки. Розв’яжіть кожен індекс у пулі один раз, поза циклом, а всередині присвоюйте індекси

// винести пошуки в пулі за межі гарячого циклу
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // індекс у пулі, від нуля
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // клітинки зберігають від одиниці; 0 = усталений

+ 1 — не одрук, і забути про нього тут класична вада, що породжує симптоми: пули видають індекси від нуля, тоді як властивості з боку клітинки трактують 0 як «усталений», тож кожен індекс у пулі під час присвоєння треба зсунути на одиницю. Помиліться через пропуск — і ваші заголовки тихо промалюються усталеним шрифтом книги, а цього дефекту ніхто не помітить аж до перевірки фірмового стилю

Замініть поклітинковий трафік Variant на зворотні виклики по рядках

Кожне Sheet.Cells[R, C].Value := X тягне за собою пошук-або-створення клітинки плюс присвоєння Variant. На кількох сотнях тисяч клітинок ці накладні витрати на доступ стають помітними в профілях. HotXLS надає на обох фасадах API масових зворотних викликів (ForEachCell і ForEachRow для читання, WriteCells і WriteRows для запису), які переносять ітерування всередину рушія й подають вашому коду цілі рядки за раз:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // спинити весь запис
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// один виклик рушія замість сотень тисяч звернень до властивостей
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Прапорець Skip зворотного виклику лишає рядок незайманим, не перериваючи роботи, а Cancel завершує операцію достроково, що стає в пригоді, коли джерелом є читач, довжину якого ви дізнаєтеся по ходу. Поєднайте WriteRows для побудови зі StreamingWrite для збереження — і на шляху генерації не лишиться жодної поклітинкової гарячої точки

Важелі з боку читання на фасаді XLS

Великі спадкові файли .xls мають власний набір інструментів. _DisableGraphics := True перед Open цілком пропускає розбір шару малюнків, що пришвидшує завантаження книг, які несуть роками накопичені фігури та вбудовані зображення. Обмеження тут жорстке: шар малюнків тоді відсутній у моделі, тож збереження такої книги запише файл без її малюнків. Тримайте цей прапорець для завдань аналізу лише на читання. SetTempDir перенаправляє тимчасові файли записувача BIFF, а це важить на серверах, де усталене тимчасове розташування має квоту чи лежить на повільному сховищі. UseSharedFormulas групує повторювані тіла формул у записи спільних формул, зменшуючи файли, де стовпець формули повторюється на шістдесят тисяч рядків углиб

Цикли читання даних XLS мають пастку індексації, про яку варто сказати, бо в разі обережного поводження вона подвоює роботу, а в разі недогляду псує результати: UsedRange повідомляє свої межі FirstRow, LastRow, FirstCol і LastCol від нуля, тоді як Cells.Item[Row, Col] нумерується з одиниці. Сканування, що обходить використаний діапазон, мусить додавати одиницю до кожної координати під час доступу до клітинки, як у Cells.Item[Row + 1, Col + 1], інакше воно читає сітку, зсунуту по діагоналі на одну клітинку, тихо гублячи останній рядок і стовпець та включаючи фантомний перший. Зворотний виклик ForEachCell оминає цю неузгодженість цілком, і це ще одна причина віддавати йому перевагу для сканування цілих аркушів

Зондуйте файли, перш ніж їх завантажувати

Найдешевша операція з великою книгою — та, якої ви уникли. GetSheetNames на обох фасадах перелічує аркуші файлу, не завантажуючи даних клітинок. Реалізація для XLSX читає лише маніфест книги всередині zip і явно лишає екземпляр книги ненаповненим, а фасад XLS припиняє сканування на першій межі підпотоку. Це робить його правильною передпольотною перевіркою на питання «на який аркуш має цілити це завдання імпорту», а CanReadEncrypted відповідає на «чи це зашифрований контейнер» ще до приреченої спроби Open

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // невдача очищає список
  // оберіть цільовий аркуш, а тоді вирішіть, чи вартий повний Open
finally
  Book.Free;
  Names.Free;
end;

Зверніть увагу на домовленість про коди повернення: ці зондувальні функції сигналізують про невдачу значеннями, що дорівнюють нулю або менші за нього, і спорожняють вихідний список, тож перевіряйте <= 0, а не порівнюйте з одним конкретним значенням успіху

Як припасувати підхід до завдання

Для конвеєрів без нагляду, що генерують багато великих файлів поспіль, картину доповнюють ще дві звички. Об’єкти книги не є потокобезпечними для спільного використання, але ніщо не заважає мати по одній незалежній книзі на робочий потік, і це чисто розпаралелює пакетну конвертацію. А коли вивід іде в HTTP, а не на диск, перевантаження збереження з TStream поєднуються зі StreamingWrite, тож велика відповідь ніколи не матеріалізується як тимчасовий файл. Тут діє одна експлуатаційна примітка: збереження в потік пише з поточної позиції, не перемотуючи, тож задайте Position := 0, перш ніж передавати потік каркасу відповіді. Стаття про потоковий запис і пакетні завдання розгортає цей серверний підхід, а стаття про експорт із бази даних показує, куди ці важелі вбудовуються у звіт, керований набором даних

Нарешті, тримайте по одній найгіршій фікстурі на кожну родину звітів і заміряйте її час у CI. Регресії продуктивності в генерації документів рідко оголошують про себе. Стиль, доданий усередині циклу, або зондування, замінене на повний Open, функційно нічого не змінюють, і нічний пакет просто триває на сорок хвилин довше. Тест із заміром часу на репрезентативній фікстурі в пів мільйона клітинок обертає це сповзання на червону збірку замість інциденту в експлуатації

Ознайомчі збірки, демонстраційні проєкти з прикладом масової генерації та повний довідник API доступні на сторінці HotXLS Delphi Component