Поточно записване с HotXLS при пакетни задания на Delphi сървъри

Да предположим, че нощна Delphi услуга генерира по един XLSX файл за всеки клиент - общо няколкостотин файла, някои от които с ширина 400 000 реда. Профилирайте я и изненадата рядко ще бъде цикълът за попълване на клетките. Тя се крие в извикването на SaveAs. При писателя по подразбиране всеки работен лист се сериализира в един-единствен XML низ в паметта, преди този низ да бъде компресиран в OOXML zip архива, като при широк лист преходният низ може да направи модела на клетките, от който е изграден, да изглежда незначителен. Така задача, която комфортно изгражда своите данни и заема 800 MB, ще скочи над лимита на контейнера от 2 GB по време на записа, и OOM killer (процесът за управление на паметта при недостиг) ще отчете срива в 03:00 часа през нощта, когато никой не гледа. HotXLS, оригиналната библиотека за електронни таблици на losLab за Delphi и C++Builder, има свойство, насочено директно към този пик: StreamingWrite. Около него стоят още два лоста, които решават дали изпълнителят на пакетни задачи ще се вмести в бюджета си за памет и време, а именно обратните извиквания на ниво ред при запис и начинът, по който се държи пулът от стилове вътре в тесен цикъл.

Какво буферира пътят за запис по подразбиране и какво променя StreamingWrite

Писателят на XLSX по подразбиране залага на простотата. Той рендира XML на работния лист напълно, след което предава готовия низ на zip компресора. Това е правилен компромис за по-голямата част от работните книги, където XML на целия лист се побира в няколко мегабайта. Това спира да бъде правилно, когато сериализираната форма на един лист достигне стотици мегабайти. XML на електронните таблици е подробен: всяка числова клетка струва десетки символи маркиране, а низът, съдържащ всичко това, трябва да бъде непрекъснат. На графиката на паметта този почерк е труден за пропускане. Дълго плоско плато, докато редовете се попълват, след това остър триъгълен пик по време на SaveAs и срив след изчистване на zip архива.

Задаването на Book.StreamingWrite := True превключва SaveAs към писател на работни листове, който излъчва XML на листа директно в zip потока, докато се генерира. Междинният низ никогда не се заделя, а триъгълният пик се свива до фонов шум.

Бъдете точни относно това какво всъщност ви носи това, тъй като преувеличаването му води до грешни планове за капацитет. Флагът променя само пътя за запис. Изграждането на работната книга все още заделя пълния модел на клетките в паметта, така че платото по време на фазата на попълване е точно толкова високо, колкото и преди. Това, което изчезва, е пикът на сериализацията, който се натрупваше върху това плато при записване, а при задача, попълваща 400 000 реда, този пик обикновено е цялата разлика между това да се вместите в бюджета за памет или да го превишите. Свойството е False по подразбиране, за да запази историческото поведение, така че включването му е един изричен ред, който пишете умишлено.

Масов експорт с активиран флаг

Book := TXLSXWorkbook.Create;
try
  BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // pool index, 0-based
  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;        // 1-based at the cell
  end;
  Book.StreamingWrite := True;   // stream sheet XML straight into the 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;              // data source drained: stop cleanly
    Exit;
  end;
  Values := VarArrayCreate([FirstCol, LastCol], varVariant);
  Values[FirstCol]     := FReader.RecordId;
  Values[FirstCol + 1] := FReader.CustomerName;
  Values[FirstCol + 2] := FReader.Amount;
end;

// fill rows 2..100001, columns A..C, pulling from the reader
Sheet.WriteRows(2, 1, 100001, 3, FillRow);

Флагът Cancel е това, което превръща фиксирания диапазон от редове в "до N реда", което е естествената форма, когато броят на редовете идва от заявка, която не сте приключили да изпълнявате. Skip е по-лекият вариант: той оставя отделен ред празен без прекъсване на изпълнението. Освен попълването на клетките, обратната функция се оказва подходяща за оперативните въпроси, които иначе се добавят към цикъла за попълване по неудобен начин. Брояч на напредъка, който отчита на всеки хиляда реда, токен за отмяна от планировчика на задачи, ограничител на скоростта на четене от базата данни-източник: всичко това живее на едно място, вместо да бъде вплетено в кода за записване на клетки. От страната на четенето, ForEachRow и ForEachCell отразяват същия модел, което е важно, когато пакетната задача както консумира, така и произвежда големи файлове.

Пуловете от стилове възнаграждават изнасянето нагоре

Моделът за стилизиране на XLSX е набор от споделени пулове. Fonts.Add, Fills.AddSolid, и Borders.Add връщат 0-базиран индекс на пул, а клетката реферира към шрифт, като съхранява този индекс плюс едно в FontIndex, където нулата е запазена за шрифта по подразбиране. Увеличението с +1 е показано в примера за масово генериране по-горе. Забравете го и клетката тихо ще приеме грешния стил, тъй като отместването с едно в индекса на пула от стилове все още е валиден индекс и нищо не предизвиква грешка.

Дисциплината, която следва, е да създадете всеки обект за стил преди цикъла за редове и да реферирате към неговия индекс вътре в цикъла. Fonts.Add дедуплицира идентични дефиниции, така че извикването му веднъж на ред само пилее процесорно време. Alignments.Add е капанът, защото връща нов запис при всяко извикване. Вътре в цикъл от 100 000 реда това затрупва styles.xml под сто хиляди дублирани записа за подравняване, което раздува файла на диска и забавя всяко следващо отваряне в Excel, тъй като дубликатите се парсват отново. Изграждайте всеки стил веднъж извън цикъла, след което реферирайте към неговия индекс толкова пъти, колкото ви е необходимо.

Потоци, временни директории и пакетният цикъл около всичко това

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

Самият пакетен цикъл остава умишлено прост:

for FileName in SourceFiles do
begin
  Book := TXLSXWorkbook.Create;        // fresh instance: no state bleed
  try
    Book.StreamingWrite := True;
    if Book.Open(FileName) <> 1 then
      Continue;                        // one bad input must not kill the batch
    Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
  finally
    Book.Free;
  end;
end;

Нова инстанция на работна книга за всеки файл струва микросекунди и премахва цяла категория бъгове с междуфайлово замърсяване: стилове, дефинирани имена и свойства на документи от файл 17 нямат начин да изтекат във файл 18. Прескачането и продължаването при неуспешно Open е също толкова полезно, тъй като едно прекъснато качване в пакет от 600 файла трябва да ви струва само един лог ред, а не спиране на целия процес. Заслужава да се отбележи и това, което CSV етапът умишлено не прави. SaveAsCSV записва формулите като буквален текст и никога не ги изчислява, така че процесът за преобразуване, чиито консуматори очакват изчислени числа, трябва първо да изпълни Calculate за съответните клетки или да започне от работни книги, които вече съдържат кеширани резултати от предишно изчисление.

Модел за паралелност: по една работна книга на нишка

Обектите на нито една от фасадите не са нишково безопасни (thread-safe) и дизайнът никога не е твърдял обратното. Тъй като няма споделено глобално състояние между инстанциите, правилото за мащабиране е просто по една работна книга на работен поток (worker thread), без споделяне на работна книга между нишки. Пул от N работника, всеки от които притежава свой собствен TXLSXWorkbook, се мащабира почти линейно, докато паметта се превърне в таван, и този таван е нещо, което можете да изчислите числово: най-големият паралелен модел на клетки, умножен по броя на работниците, плюс допълнителните разходи по време на запис, които StreamingWrite е изравнил. Когато опашката стане твърде дълга, приложете обратен натиск (back-pressure) върху опашката от задачи, вместо вътре в писателя. Нишка без ресурси, която е записала наполовина работна книга, не е произвела нищо полезно, докато задача, изчакала няколко секунди за свободен работник, завършва успешно.

За по-широката картина на оптимизацията, включително споделени формули, пропускане на графики от страната на четенето и специфичните за XLS контроли, вижте ръководството за производителност на големи работни книги. Пакетните задачи, чиито редове идват директно от заявка, са разгледани отделно в моделите за експортиране на бази данни за Delphi отчети.

HotXLS се компилира във вашата Delphi или C++Builder услуга като оригинален Object Pascal код без външни зависимости; изданията и лицензирането са на страницата на продукта HotXLS Component.