HotXLS Streaming Writes til batchjob på Delphi-servere

Sig, at en natlig Delphi-service genererer én XLSX pr. kunde, nogle hundrede filer, hvoraf nogle er 400.000 rækker brede. Profiler den, og overraskelsen er sjældent celleudfyldningsløkken. Det er kaldet SaveAs. Med standardskriveren serialiseres hvert regneark til en enkelt XML-streng i hukommelsen, før strengen komprimeres ind i OOXML-zip'en, og for et bredt ark kan den midlertidige streng overgå cellemodellen, den blev bygget ud fra, langt i størrelse. Så et job, der behageligt bygger sine data og ligger på 800 MB, spidser forbi en 2 GB containergrænse under selve gemningen, og OOM-killeren indgiver fejlrapporten klokken 03:00, når ingen holder øje. HotXLS, losLabs indfødte regnearksbibliotek til Delphi og C++Builder, har en egenskab rettet direkte mod den spids: StreamingWrite. Omkring den sidder to yderligere håndtag, der afgør, om en batch-worker holder sig inden for sit hukommelses- og tidsbudget, nemlig rækkevise write-callbacks og den måde, stilpuljen opfører sig på inde i en tæt løkke

Hvad standardgemmestien bufrer, og hvad StreamingWrite ændrer

Standard-XLSX-skriveren favoriserer enkelhed. Den renderer regnearkets XML helt færdigt og overgiver derefter den færdige streng til zip-komprimeren. Det er den rigtige afvejning for langt de fleste arbejdsbøger, hvor hele arkets XML fylder nogle få megabyte. Det holder op med at være rigtigt, når ét arks serialiserede form løber op i hundredvis af megabyte. Regneark-XML er ordrig: hver numerisk celle koster snesevis af tegn markup, og strengen, der holder det hele, skal være sammenhængende. På en hukommelsesgraf er signaturen svær at overse. Et langt fladt plateau, mens rækkerne fyldes, derefter en skarp trekantet spids under SaveAs, og til sidst kollapset, når zip'en skylles ud

At sætte Book.StreamingWrite := True skifter SaveAs over til en regnearksskriver, der udsender arkets XML direkte ind i zip-streamen, efterhånden som den genereres. Den mellemliggende streng bliver aldrig allokeret, og den trekantede spids flader ud i støjen

Vær præcis om, hvad det rent faktisk giver dig, for at oversælge det fører til forkerte kapacitetsplaner. Flaget ændrer kun gemmestien. At bygge arbejdsbogen allokerer stadig hele cellemodellen i hukommelsen, så plateauet under udfyldningsfasen er lige så højt som før. Det, der forsvinder, er serialiseringsspidsen, der plejede at lægge sig oven på det plateau ved gemning, og for et job, der fylder 400.000 rækker, er den spids som regel hele forskellen på at holde sig inden for et hukommelsesbudget eller sprænge det. Egenskaben er som standard False for at bevare den historiske adfærd, så at slå den til er én eksplicit linje, du skriver med vilje

En bulkeksport med flaget slået til

Book := TXLSXWorkbook.Create;
try
  BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // puljeindeks, 0-baseret
  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-baseret på cellen
  end;
  Book.StreamingWrite := True;   // stream arkets XML direkte ind i zip'en
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Cells[R, C] opretter celler efter behov, hvilket holder løkkens krop ren. To gitterlofter er værd at huske: 1.048.576 rækker og 16.384 kolonner, eksponeret som XlsxMaxRow og XlsxMaxCol. Et datafeed, der overskrider rækkeloftet, skal splittes over flere ark i din egen kode. Intet nedstrøms bemærker overskridelsen eller retter den for dig, og filen ender simpelthen afkortet ved grænsen

Udfyld rækker uden Variant-overhead pr. celle

Hver Cells[R, C].Value-tildeling betaler for et celleopslag og en Variant-konvertering. Ved ti tusind rækker bemærker ingen det. Ved en million rækker med tyve kolonner hver bliver den overhead pr. kald den dominerende omkostning i udfyldningsfasen, og profileren vil pege lige på den. Batch-grænsefladerne lader dig i stedet give skriveren en hel række ad gangen. WriteRows driver et callback, der leverer én række pr. kald:

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

// udfyld rækker 2..100001, kolonner A..C, hentet fra readeren
Sheet.WriteRows(2, 1, 100001, 3, FillRow);

Cancel-flaget er det, der forvandler et fast rækkeinterval til "op til N rækker," hvilket er den naturlige form, når rækkeantallet kommer fra en forespørgsel, du ikke er færdig med at udføre. Skip er det lettere greb: det efterlader en enkelt række tom uden at stoppe kørslen. Ud over at udfylde celler viser callbacket sig at være et godt hjem for de driftsmæssige hensyn, der ellers bliver boltet på en udfyldningsløkke på akavede måder. En fremdriftstæller, der tikker for hver tusind rækker, et annulleringstoken, der polles fra jobplanlæggeren, en hastighedsbegrænser på læsninger fra kildedatabasen: alt sammen bor det ét sted i stedet for at blive flettet ind gennem cellskrivningskoden. På læsesiden spejler ForEachRow og ForEachCell det samme mønster, hvilket betyder noget, når et batch-job både forbruger og producerer store filer

Stilpuljer belønner hoisting

XLSX-stilmodellen er et sæt delte puljer. Fonts.Add, Fills.AddSolid og Borders.Add returnerer alle et 0-baseret puljeindeks, og en celle refererer til en skrifttype ved at gemme det indeks plus én i FontIndex, hvor nul er reserveret til arbejdsbogens standard. Det +1 sidder lige der i bulk-eksemplet ovenfor. Glem det, og cellen tager stiltiende den forkerte stil, fordi en off-by-one i et stilpuljeindeks stadig er et gyldigt indeks, og intet rejser en fejl

Den disciplin, der følger af det, er at oprette hvert stilobjekt før rækkeløkken og referere dets indeks inde i løkken. Fonts.Add dedupler identiske definitioner, så at kalde den én gang pr. række spilder kun CPU. Alignments.Add er fælden, fordi den returnerer en frisk post ved hvert kald. Inde i en 100.000-rækkers løkke begraver det styles.xml under hundrede tusind duplikerede justeringsposter, hvilket oppuster filen på disk og gør enhver senere åbning i Excel langsommere, efterhånden som duplikaterne bliver genparset. Byg hver stil én gang uden for løkken, og referer derefter dens indeks lige så mange gange, du har brug for

Streams, temp-mapper og batchløkken omkring det hele

Intet af dette kræver et filsystem. Begge facader bærer TStream-overloads på tværs af deres IO-flade, blandt andet Open, SaveAs, SaveAsCSV, SaveAsHTML og SaveAsODS, så en batch-worker kan rendere direkte ind i en TMemoryStream bestemt til blob-lagring eller et HTTP-svar uden nogensinde at røre disken. Der er én skarp kant at huske. SaveAs(Stream) skriver fra streamens aktuelle position og spoler ikke tilbage bagefter, så sæt selv Position := 0, før du overgiver streamen til det, der leverer den, ellers læser modtageren nul bytes. XLS-facaden tilføjer to knapper af sin egen. SetTempDir peger BIFF-skriverens midlertidige filer mod et drev, der har pladsen og IO-luften til at absorbere dem, hvilket betyder noget på servere, hvor standard-temp-stien ligger på en trang systemdisk. UseSharedFormulas folder gentagne formeludtryk sammen til delte grupper, en reel størrelsesreduktion for den klassiske rapportform, hvor én formel er kopieret ned gennem en hel kolonne

Selve batchløkken forbliver kedelig med vilje:

for FileName in SourceFiles do
begin
  Book := TXLSXWorkbook.Create;        // frisk instans: ingen tilstand siver over
  try
    Book.StreamingWrite := True;
    if Book.Open(FileName) <> 1 then
      Continue;                        // ét dårligt input må ikke slå batchen ihjel
    Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
  finally
    Book.Free;
  end;
end;

En frisk arbejdsbogsinstans pr. fil koster mikrosekunder og fjerner en hel kategori af bugs med kryds-fil-kontaminering: stile, definerede navne og dokumentegenskaber fra fil 17 har ingen vej til at lække ind i fil 18. Spring-over-og-fortsæt ved et mislykket Open tjener sin plads lige så meget, fordi én afkortet upload i en batch på 600 filer bør koste dig én enkelt loglinje frem for resten af kørslen. Værd at fremhæve er også, hvad CSV-benet bevidst ikke gør. SaveAsCSV skriver formler ud som bogstavelig tekst og evaluerer dem aldrig, så en konverteringsbatch, hvis forbrugere forventer beregnede tal, skal køre Calculate på de relevante celler først, eller starte fra arbejdsbøger, der allerede bærer cachede resultater fra en tidligere beregning

Samtidighedsmodel: én arbejdsbog pr. tråd

Ingen af facadernes objekter er trådsikre, og designet har aldrig foregivet andet. Fordi der ikke er nogen delt global tilstand mellem instanser, er skaleringsreglen simpelthen én arbejdsbog pr. worker-tråd, uden nogen deling af en arbejdsbog på tværs af tråde. En pool af N workere, hver med sin egen TXLSXWorkbook, skalerer tæt på lineært, indtil hukommelsen bliver loftet, og det loft er noget, du kan sætte et tal på: den største samtidige cellemodel ganget med antallet af workere, plus det spidsforbrug ved gemning, som StreamingWrite har fladet ud. Når køen bliver dyb, skal du lægge modtryk på jobkøen i stedet for inde i skriveren. En udsultet tråd, der har halvskrevet en arbejdsbog, har ikke produceret noget brugbart, mens et job, der ventede et par sekunder på en ledig worker, fuldføres intakt

For det bredere tuningbillede, inklusive delte formler, springer over grafik på læsesiden og de XLS-specifikke håndtag, se guiden til ydelse i store arbejdsbøger. Batch-job, hvis rækker kommer direkte fra en forespørgsel, dækkes separat i mønstrene for databaseeksport til Delphi-rapporter

HotXLS kompileres ind i din Delphi- eller C++Builder-service som indfødt Object Pascal uden eksterne afhængigheder; udgaver og licensiering findes på HotXLS Delphi Component-produktsiden