HotXLS-streaming-schrijven voor Delphi-serverbatchtaken

Stel dat een nachtelijke Delphi-service één XLSX per klant genereert, een paar honderd bestanden, sommige daarvan 400.000 rijen breed. Profileer het en de verrassing is zelden de lus die de cellen vult. Het is de aanroep van SaveAs. Met de standaardschrijver wordt elk werkblad geserialiseerd naar één XML-tekenreeks in het geheugen voordat die tekenreeks in de OOXML-zip wordt gecomprimeerd, en bij een breed blad kan die tijdelijke tekenreeks het celmodel waaruit hij is opgebouwd volledig overvleugelen. Een taak die zijn gegevens comfortabel opbouwt en op 800 MB blijft steken, schiet tijdens het opslaan dus voorbij een containerlimiet van 2 GB, en de OOM-killer dient het bugrapport om 03:00 in wanneer niemand kijkt. HotXLS, de native spreadsheetbibliotheek van losLab voor Delphi en C++Builder, heeft een eigenschap die recht op die piek is gericht: StreamingWrite. Daaromheen liggen twee verdere hefbomen die bepalen of een batchworker binnen zijn geheugen- en tijdbudget blijft, namelijk schrijfcallbacks op rijniveau en de manier waarop de stijlpool zich in een strakke lus gedraagt

Wat het standaard opslagpad buffert, en wat StreamingWrite verandert

De standaard XLSX-schrijver kiest voor eenvoud. Hij rendert de werkblad-XML volledig en geeft de afgeronde tekenreeks vervolgens aan de zip-compressor. Dat is de juiste afweging voor de overgrote meerderheid van werkmappen, waar de XML van het hele blad in enkele megabytes past. Het houdt op de juiste afweging te zijn wanneer de geserialiseerde vorm van één blad tot honderden megabytes oploopt. Spreadsheet-XML is breedsprakig: elke numerieke cel kost tientallen tekens aan opmaak, en de tekenreeks die dat allemaal vasthoudt, moet aaneengesloten zijn. Op een geheugengrafiek is het patroon moeilijk te missen. Een lang vlak plateau terwijl de rijen zich vullen, dan een scherpe driehoekige piek tijdens SaveAs, dan de val zodra de zip is weggeschreven

Met Book.StreamingWrite := True schakelt SaveAs over op een werkbladschrijver die de blad-XML rechtstreeks in de zip-stroom uitstoot terwijl die wordt gegenereerd. De tussenliggende tekenreeks wordt nooit toegewezen, en de driehoekige piek vlakt af tot ruis

Wees precies over wat u daar werkelijk mee koopt, want overdrijven leidt tot verkeerde capaciteitsplannen. De vlag verandert alleen het opslagpad. Het opbouwen van de werkmap wijst nog steeds het volledige celmodel in het geheugen toe, dus het plateau tijdens de vulfase is precies even hoog als voorheen. Wat verdwijnt, is de serialisatiepiek die daar bij het opslaan bovenop kwam, en voor een taak die 400.000 rijen vult, is die piek stelselmatig het hele verschil tussen binnen een geheugenbudget passen en het opblazen. De eigenschap staat standaard op False om het historische gedrag te bewaren, dus meedoen is één expliciete regel die u met opzet schrijft

Een bulkexport met de vlag aan

Book := TXLSXWorkbook.Create;
try
  BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // poolindex, 0-gebaseerd
  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-gebaseerd bij de cel
  end;
  Book.StreamingWrite := True;   // stroom blad-XML rechtstreeks de zip in
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Cells[R, C] maakt cellen op aanvraag aan, wat de body van de lus schoon houdt. Twee rasterlimieten zijn het onthouden waard: 1.048.576 rijen en 16.384 kolommen, blootgesteld als XlsxMaxRow en XlsxMaxCol. Een gegevensstroom die de rijlimiet overschrijdt, moet u in uw eigen code over meerdere bladen verdelen. Verderop in de keten merkt niets de overschrijding op en niets herstelt die voor u, en het bestand eindigt eenvoudigweg afgekapt op de limiet

Rijen vullen zonder Variant-overhead per cel

Elke toewijzing van Cells[R, C].Value betaalt voor een celopzoeking en een Variant-conversie. Bij tienduizend rijen merkt niemand het. Bij een miljoen rijen van twintig kolommen elk wordt die overhead per aanroep de dominante kostenpost van de vulfase, en de profiler wijst er recht naartoe. Met de batchinterfaces kunt u de schrijver in plaats daarvan een hele rij tegelijk aanreiken. WriteRows stuurt een callback aan die per aanroep één rij levert:

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;              // gegevensbron leeg: stop netjes
    Exit;
  end;
  Values := VarArrayCreate([FirstCol, LastCol], varVariant);
  Values[FirstCol]     := FReader.RecordId;
  Values[FirstCol + 1] := FReader.CustomerName;
  Values[FirstCol + 2] := FReader.Amount;
end;

// vul rijen 2..100001, kolommen A..C, met gegevens uit de reader
Sheet.WriteRows(2, 1, 100001, 3, FillRow);

De vlag Cancel is wat een vast rijbereik verandert in "tot en met N rijen", de natuurlijke vorm wanneer het aantal rijen komt uit een query die u nog niet klaar hebt uitgevoerd. Skip is de lichtere ingreep: hij laat een enkele rij leeg zonder de run te stoppen. Behalve cellen vullen blijkt de callback een goed onderdak voor de operationele zorgen die anders op onhandige manieren aan een vullus worden geschroefd. Een voortgangsteller die elke duizend rijen tikt, een annuleringstoken dat bij de taakplanner wordt gepolld, een snelheidsbegrenzer op het lezen uit de brondatabase: het woont allemaal op één plek in plaats van door celschrijvende code te worden geregen. Aan de leeskant spiegelen ForEachRow en ForEachCell hetzelfde patroon, wat telt wanneer een batchtaak grote bestanden zowel verbruikt als produceert

Stijlpools belonen het naar buiten halen

Het opmaakmodel van XLSX is een verzameling gedeelde pools. Fonts.Add, Fills.AddSolid en Borders.Add geven alle een 0-gebaseerde poolindex terug, en een cel verwijst naar een lettertype door die index plus één op te slaan in FontIndex, waarbij nul is gereserveerd voor de standaard van de werkmap. De +1 staat er in het bulkvoorbeeld hierboven gewoon. Vergeet hem en de cel pakt stilletjes de verkeerde stijl op, want een verschil van één in een stijlpoolindex is nog steeds een geldige index en er wordt niets opgeworpen

De discipline die daaruit volgt, is elk stijlobject vóór de rijlus aan te maken en binnen de lus alleen de index ervan te gebruiken. Fonts.Add ontdubbelt identieke definities, dus het per rij aanroepen verspilt alleen CPU. Alignments.Add is de valstrik, want die geeft bij elke aanroep een nieuwe vermelding terug. Binnen een lus van 100.000 rijen begraaft dat styles.xml onder honderdduizend dubbele uitlijningsrecords, wat het bestand op schijf opblaast en elke latere opening in Excel vertraagt doordat de duplicaten opnieuw worden geparseerd. Bouw elke stijl één keer buiten de lus en verwijs daarna naar de index zo vaak als u nodig hebt

Streams, tijdelijke mappen en de batchlus eromheen

Niets hiervan vereist een bestandssysteem. Beide facades dragen over hun hele IO-oppervlak TStream-overloads, waaronder Open en SaveAs en SaveAsCSV en SaveAsHTML en SaveAsODS, zodat een batchworker rechtstreeks kan renderen naar een TMemoryStream op weg naar blobopslag of een HTTP-antwoord, zonder ooit de schijf aan te raken. Er is één scherpe rand om te onthouden. SaveAs(Stream) schrijft vanaf de huidige positie van de stroom en spoelt daarna niet terug, dus zet zelf Position := 0 voordat u de stroom overdraagt aan wat hem aflevert, anders leest de afnemer nul bytes. De XLS-facade voegt twee eigen knoppen toe. SetTempDir wijst de tijdelijke bestanden van de BIFF-schrijver naar een volume met de ruimte en de IO-marge om ze op te vangen, wat telt op servers waar het standaard tijdelijke pad op een krappe systeemschijf staat. UseSharedFormulas vouwt herhaalde formulelichamen samen tot gedeelde groepen, een echte grootteverkleining voor de klassieke rapportvorm waarin één formule een hele kolom omlaag is gekopieerd

De batchlus zelf blijft met opzet saai:

for FileName in SourceFiles do
begin
  Book := TXLSXWorkbook.Create;        // nieuwe instantie: geen doorlekkende toestand
  try
    Book.StreamingWrite := True;
    if Book.Open(FileName) <> 1 then
      Continue;                        // een slechte invoer mag de batch niet doden
    Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
  finally
    Book.Free;
  end;
end;

Een verse werkmapinstantie per bestand kost microseconden en verwijdert een hele categorie besmettingsbugs tussen bestanden: stijlen, gedefinieerde namen en documenteigenschappen uit bestand 17 hebben geen route om naar bestand 18 te lekken. Het overslaan en doorgaan bij een mislukte Open verdient net zo goed zijn plaats, want één afgekapte upload in een batch van 600 bestanden hoort u één logregel te kosten en niet de rest van de run. Het benoemen waard is ook wat de CSV-tak bewust niet doet. SaveAsCSV schrijft formules als letterlijke tekst weg en evalueert ze nooit, dus een conversiebatch waarvan de afnemers berekende getallen verwachten, moet eerst Calculate op de betreffende cellen draaien, of vertrekken vanuit werkmappen die al gecachte resultaten van een eerdere berekening bevatten

Gelijktijdigheidsmodel: één werkmap per thread

De objecten van geen van beide facades zijn thread-safe, en het ontwerp heeft nooit anders voorgewend. Omdat er geen gedeelde globale toestand tussen instanties bestaat, is de schaalregel simpelweg één werkmap per workerthread, zonder een werkmap over threads te delen. Een pool van N workers, elk met een eigen TXLSXWorkbook, schaalt bijna lineair totdat geheugen het plafond wordt, en op dat plafond kunt u een getal plakken: het grootste gelijktijdige celmodel vermenigvuldigd met het aantal workers, plus wat er aan overhead bij het opslaan overblijft nadat StreamingWrite die heeft afgevlakt. Wordt de wachtrij diep, pas dan tegendruk toe bij de takenwachtrij en niet binnen de schrijver. Een uitgehongerde thread die een werkmap half heeft weggeschreven, heeft niets bruikbaars opgeleverd, terwijl een taak die een paar seconden op een vrije worker wachtte, intact afrondt

Voor het bredere afstembeeld, inclusief gedeelde formules, het overslaan van afbeeldingen aan de leeskant en de XLS-specifieke hefbomen, zie de prestatiegids voor grote werkmappen. Batchtaken waarvan de rijen rechtstreeks uit een query komen, worden apart behandeld in de patronen voor database-export in Delphi-rapporten

HotXLS compileert als native Object Pascal zonder externe afhankelijkheden mee in uw Delphi- of C++Builder-service; edities en licenties staan op de productpagina van de HotXLS Delphi-component