Потоковий запис HotXLS для пакетних завдань на сервері Delphi

Уявімо, що нічна служба на Delphi генерує один XLSX на клієнта — кілька сотень файлів, деякі з них завширшки 400 000 рядків. Профілюйте це — і сюрприз рідко ховається в циклі заповнення клітинок. Це виклик SaveAs. У типового записувача кожен аркуш серіалізується в один рядок XML у пам’яті, перш ніж цей рядок стискається в zip OOXML, і для широкого аркуша тимчасовий рядок може затьмарити модель клітинок, з якої його побудовано. Тож завдання, що спокійно будує свої дані й тримається на позначці 800 МБ, під час збереження злітає за межу контейнера в 2 ГБ, і о третій ночі, коли ніхто не дивиться, вбивця нестачі пам’яті подає звіт про помилку. HotXLS, нативна бібліотека електронних таблиць losLab для Delphi та C++Builder, має властивість, спрямовану точно проти цього сплеску: StreamingWrite. Навколо неї є ще два важелі, що визначають, чи лишиться пакетний робочий процес у межах свого бюджету пам’яті й часу, а саме зворотні виклики запису на рівні рядків і те, як поводиться пул стилів усередині щільного циклу

Що буферизує типовий шлях збереження, і що змінює StreamingWrite

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

Встановлення Book.StreamingWrite := True перемикає SaveAs на записувач аркуша, який видає XML аркуша прямо в потік zip у міру генерації. Проміжний рядок ніколи не виділяється, і трикутний сплеск розчиняється в шумі

Будьте точні щодо того, що це насправді дає, бо перебільшення веде до неправильних планів потужності. Прапорець змінює лише шлях збереження. Побудова книги все одно виділяє повну модель клітинок у пам’яті, тож плато на фазі заповнення таке саме високе, як і раніше. Зникає сплеск серіалізації, що раніше нашаровувався на це плато під час збереження, і для завдання, що заповнює 400 тисяч рядків, цей сплеск зазвичай і є всією різницею між вкладанням у бюджет пам’яті та його перевищенням. Властивість за замовчуванням має значення False, щоб зберегти історичну поведінку, тож увімкнення — це один явний рядок, який ви пишете навмисно

Масовий експорт з увімкненим прапорцем

Book := TXLSXWorkbook.Create;
try
  BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // індекс пулу, з нуля
  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;
    if (R mod 1000) = 0 then
      Sheet.Cells[R, 2].FontIndex := BoldIdx + 1;        // з одиниці на рівні клітинки
  end;
  Book.StreamingWrite := True;   // передавати XML аркуша потоком прямо в zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Cells[R, C] створює клітинки на вимогу, що тримає тіло циклу чистим. Варто запам’ятати дві межі сітки: 1 048 576 рядків і 16 384 стовпці, доступні як XlsxMaxRow і XlsxMaxCol. Потік даних, що перевищує межу рядків, має бути розбитий на кілька аркушів у вашому власному коді. Нижче за течією ніщо не помічає перевищення й не виправляє його за вас, і файл просто виявляється обрізаним на межі

Заповнення рядків без накладних витрат Variant на клітинку

Кожне присвоєння Cells[R, C].Value платить за пошук клітинки й перетворення Variant. На десяти тисячах рядків цього ніхто не помічає. На мільйоні рядків по двадцять стовпців кожен ці накладні витрати на виклик стають домінантною вартістю фази заповнення, і профілювальник вкаже прямо на них. Пакетні інтерфейси дозволяють передавати записувачу цілий рядок за раз замість цього. WriteRows керує зворотним викликом, що постачає один рядок на виклик:

procedure TBulkExporter.FillRow(Sender: TObject; SheetIndex, Row, FirstCol,
  LastCol: Integer; var Values: Variant; var Skip: Boolean;
  var Cancel: Boolean);
begin
  if not FReader.Next then
  begin
    Cancel := True;              // джерело даних вичерпано: чисто зупинитися
    Exit;
  end;
  Values := VarArrayCreate([FirstCol, LastCol], varVariant);
  Values[FirstCol]     := FReader.RecordId;
  Values[FirstCol + 1] := FReader.CustomerName;
  Values[FirstCol + 2] := FReader.Amount;
end;

// заповнити рядки 2..100001, стовпці A..C, беручи дані з читача
Sheet.WriteRows(2, 1, 100001, 3, FillRow);

Прапорець Cancel — це те, що перетворює фіксований діапазон рядків на «до N рядків», що є природною формою, коли кількість рядків береться із запиту, виконання якого ще не завершено. Skip — м’якший дотик: він лишає окремий рядок порожнім, не зупиняючи виконання. Окрім заповнення клітинок, зворотний виклик виявляється хорошим місцем для операційних турбот, які інакше незграбно прикручуються до циклу заповнення. Лічильник прогресу, що тікає щотисячу рядків, токен скасування, опитуваний з планувальника завдань, обмежувач швидкості читання з джерельної бази даних: усе це живе в одному місці замість того, щоб бути розкиданим по коду запису клітинок. З боку читання ForEachRow і ForEachCell віддзеркалюють той самий шаблон, що важливо, коли пакетне завдання одночасно споживає й видає великі файли

Пули стилів винагороджують винесення за межі циклу

Модель стилізації XLSX — це набір спільних пулів. Fonts.Add, Fills.AddSolid і Borders.Add усі повертають індекс пулу з нуля, і клітинка посилається на шрифт, зберігаючи цей індекс плюс один у FontIndex, де нуль зарезервовано для типового значення книги. Це «+1» прямо там, у прикладі масового заповнення вище. Забудьте про нього — і клітинка мовчки підхопить неправильний стиль, бо помилка на одиницю в індексі пулу стилів усе одно лишається валідним індексом, і нічого не викликає винятку

Дисципліна, що з цього випливає, — створювати кожен об’єкт стилю до циклу по рядках і посилатися на його індекс усередині циклу. Fonts.Add усуває дублікати ідентичних визначень, тож виклик його раз на рядок лише марнує процесорний час. Alignments.Add — це пастка, бо він повертає новий запис при кожному виклику. Усередині циклу на 100 тисяч рядків це ховає styles.xml під сто тисячами дубльованих записів вирівнювання, що роздуває файл на диску й уповільнює кожне наступне відкриття в Excel, поки дублікати повторно розбираються. Побудуйте кожен стиль один раз поза циклом, а потім посилайтеся на його індекс стільки разів, скільки потрібно

Потоки, тимчасові каталоги й пакетний цикл навколо всього цього

Ніщо з цього не потребує файлової системи. Обидва фасади несуть перевантаження TStream по всій поверхні вводу-виводу, серед них Open, SaveAs, SaveAsCSV, SaveAsHTML і SaveAsODS, тож пакетний робочий процес може рендерити прямо в TMemoryStream, призначений для сховища blob чи відповіді HTTP, жодного разу не торкаючись диска. Є одна гостра деталь, яку варто пам’ятати. SaveAs(Stream) пише від поточної позиції потоку і не перемотує його назад після цього, тож встановіть Position := 0 самостійно, перш ніж передавати потік тому, що його доставляє, інакше споживач прочитає нуль байтів. Фасад XLS додає ще два власні перемикачі. SetTempDir спрямовує тимчасові файли записувача BIFF на том, що має простір і запас пропускної здатності вводу-виводу, щоб їх поглинути, що важливо на серверах, де типовий шлях тимчасових файлів сидить на тісному системному диску. UseSharedFormulas згортає повторювані тіла формул у спільні групи — реальне зменшення розміру для класичної форми звіту, де одна формула скопійована вниз по всьому стовпцю

Сам пакетний цикл навмисно лишається нудним:

for FileName in SourceFiles do
begin
  Book := TXLSXWorkbook.Create;        // свіжий екземпляр: без витоку стану
  try
    Book.StreamingWrite := True;
    if Book.Open(FileName) <> 1 then
      Continue;                        // один поганий вхід не має вбивати весь пакет
    Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
  finally
    Book.Free;
  end;
end;

Свіжий екземпляр книги на файл коштує мікросекунди й усуває цілу категорію помилок міжфайлового забруднення: стилі, визначені імена та властивості документа з файлу 17 не мають шляху витекти у файл 18. Пропуск-і-продовження при невдалому Open виправдовує себе так само сильно, бо один обрізаний завантажений файл серед 600 у пакеті має коштувати вам одного рядка логу, а не решти запуску. Варто також відзначити, чого гілка CSV навмисно не робить. SaveAsCSV записує формули як буквальний текст і ніколи їх не обчислює, тож пакет конвертації, чиї споживачі очікують обчислених чисел, має спершу виконати Calculate для відповідних клітинок або починати з книг, що вже несуть кешовані результати попереднього обчислення

Модель конкурентності: одна книга на потік

Жоден з об’єктів жодного фасаду не є потокобезпечним, і дизайн ніколи не вдавав інакшого. Оскільки між екземплярами немає спільного глобального стану, правило масштабування просто таке: одна книга на робочий потік, без обміну книгою між потоками. Пул із N робочих потоків, кожен з яких володіє власним TXLSXWorkbook, масштабується майже лінійно, поки стелею не стає пам’ять, і цю стелю можна виразити числом: найбільша одночасна модель клітинок, помножена на кількість робочих потоків, плюс будь-які накладні витрати часу збереження, які згладив StreamingWrite. Коли черга стає глибокою, застосовуйте зворотний тиск на рівні черги завдань, а не всередині записувача. Голодуючий потік, що наполовину записав книгу, не виробив нічого корисного, тоді як завдання, що чекало кілька секунд на вільний робочий потік, завершується цілим

Ширшу картину налаштувань, включно зі спільними формулами, пропуском графіки на боці читання та специфічними для XLS важелями, дивіться в посібнику з продуктивності великих книг. Пакетні завдання, чиї рядки надходять прямо із запиту, розглянуто окремо в шаблонах експорту з бази даних для звітів Delphi

HotXLS компілюється у ваш сервіс на Delphi чи C++Builder як нативний Object Pascal без зовнішніх залежностей; редакції та ліцензування — на сторінці продукту HotXLS Delphi Component