Millioner XLSX-rækker i Delphi med konstant hukommelse

Et rapporteringsjob kører fint i et år. Det bygger en arbejdsbog, udfylder et ark med det, forespørgslen returnerer, og gemmer den. Så beder en kunde med fem års historik om en fuld eksport, rækkeantallet krydser en million, og processen dør med en out-of-memory-fejl længe før filen når disken. Der var intet galt med koden. Den holdt hele arbejdsbogen i RAM, så den kunne serialisere den til sidst, og den hukommelse, den havde brug for, voksede i takt med antallet af rækker, den blev bedt om at skrive

Løsningen er ikke en større maskine. Det er en anden skrivemodel. Den streamende direkte skriver i HotXLS udsender OOXML-pakken trinvist, efterhånden som rækkerne ankommer, så den hukommelse, den bruger, ikke afhænger af, hvor mange rækker du skriver. Det er skrivesidens modstykke til den streamende læser: hvor læseren gennemløber et kæmpe ark uden at bygge et celletræ, producerer skriveren ét uden at bygge et celletræ heller

Hvorfor den normale gemmesti vokser med dataene

Den almindelige TXLSXWorkbook-sti bygger først en fuld objektmodel. Hver celle, med sin værdi, type og stilreference, lever som et objekt i hukommelsen, indtil du kalder gem, hvorefter hele træet serialiseres ind i pakken. Den model er den rigtige, når du vil læse et ark, redigere det, genberegne og skrive det tilbage, fordi vilkårlig adgang til enhver celle er præcis det, redigering har brug for. Den er den forkerte, når du hælder rækker i én retning og aldrig ser dig tilbage, fordi du betaler for at holde hver række resident uden nogen gevinst. En million rækker af objekter er en million rækker af objekter, uanset om du nogensinde besøger dem igen eller ej

Den streamende skriver fjerner træet. Så snart en celle er skrevet, bliver den til bytes i regnearksdelen, og de bytes afleveres til zip-outputtet. Regnearksstrømmen er den eneste buffer, der vokser, og den vokser på outputsiden, ikke som levende Delphi-objekter på heapen. Det, der forbliver resident, er en fast mængde bogholderi: arknavnene, nogle få flag, det aktuelle rækkenummer, en celletæller. Det sæt ændrer sig ikke mellem række ét og række ti millioner

Shared-string-tabellen er fælden, og inline-strenge er vejen ud

De fleste streamende XLSX-skrivere klarer sig godt, indtil de møder tekst. OOXML-formatet gemmer normalt strenge i en shared-string-tabel: hver distinkt streng skrives én gang i en separat del, og hver celle, der rummer den streng, bærer et indeks ind i tabellen i stedet for teksten. Det er en god pladsoptimering for filer fulde af gentagne etiketter, og det er den standard, den normale gemmesti bruger. Problemet for en streamende skriver er brutalt. For at deduplikere skal tabellen forblive resident under hele jobbet, fordi enhver række, der endnu ikke er kommet, kan gentage en streng fra en række, der allerede er skrevet, og kun et komplet kort i hukommelsen over sete strenge kan tildele det rigtige indeks. Så den ene struktur, en streamende skriver ikke kan streame, er netop den struktur, der skulle gøre filen lille. Teksttunge data slår den streaming, du kom efter, ud af kurs

Den direkte skriver går helt uden om tabellen. Strenge skrives inline, som t="inlineStr"-celler, hvis tekst sidder direkte inde i cellen med et <is><t>-element. Der er ingen tabel at akkumulere og intet kort over sete strenge at holde, så tekstkolonner koster ikke mere hukommelse end numeriske. Byttehandlen er eksplicit og værd at sige ligeud. Inline-strenge gentager den samme tekst, hvor end den forekommer, så en fil med mange identiske etiketter er større på disken end shared-string-modstykket. Du bruger filstørrelse for at købe konstant hukommelse. For en eksport i én omgang er det den rigtige side af byttehandlen, og zip-komprimeringen opsuger alligevel meget af gentagelsen på vej ud

Stiltabellen ankommer til sidst, med ét datoformat

Stile udgør den samme spænding som strenge. En arbejdsbog refererer til sin formatering gennem en styles-del, og en streamende skriver kan ikke holde en voksende palet af stile i takt med celler, den allerede har skyllet ud. Den direkte skriver besvarer dette ved at holde stiltabellen lille og fast og udsende den ved lukning frem for forud. Ét standardcelleformat dækker almindelige celler. Ét datotalformat dækker datoer, registreret med formatkoden yyyy-mm-dd på en kendt position i listen over celleformater

Det datoformat er grunden til, at WriteDateTime findes som sit eget kald. Excel har ingen native datotype; en dato er et tal iført et datoformat. WriteDateTime skriver værdien som et almindeligt serienummer og mærker cellen med den ene datostil, så regnearket gengiver den som en dato i stedet for et femcifret heltal. Det serienummer, den skriver, betyder noget for rundturen. Den gemmer TDateTime-værdien direkte under 1900-datosystemet, hvilket er den samme konvention, den almindelige TXLSXWorkbook-gemmesti bruger. Fordi begge stier er enige om serienummeret, læses en fil, den streamende skriver producerer, tilbage gennem HotXLS-læseren og åbner i Excel med datoer, der matcher det, du havde til hensigt, uden nogen off-by-one- eller epoke-overraskelse mellem skriveren og læseren

Rækkefølge er obligatorisk, fordi de bytes allerede er væk

Streaming køber sin hukommelsesprofil med én regel, du skal overholde. Output udsendes undervejs og kan ikke besøges igen, så alt skal skrives i den rækkefølge, det optræder i filen. Inden for en række går celler i stigende kolonnerækkefølge. Inden for et ark går rækker i stigende rækkefølge. Der er ingen buffer, der lader skriveren sortere dine celler bagefter, fordi den række, du lukkede for et øjeblik siden, allerede er bytes i zip-strømmen og ikke længere kan nås. Giv den kolonne 5 og derefter kolonne 2 i samme række, og outputtet er misdannet, da skriveren simpelthen udsender det, du giver den, i den sekvens, du giver det

Række-API'en har en lille bekvemmelighed til det almindelige tilfælde. AddRow tager et 1-baseret rækkeindeks, men at sende 0 betyder tag den næste række efter den forrige, så en sekventiel udfyldning ikke behøver at holde styr på og sende en stigende tæller. Hvert AddRow lukker rækken før det, og hvert AddSheet lukker arket før det, så du afslutter aldrig eksplicit en række eller et ark. Du starter det næste, og skriveren færdiggør den åbne struktur for dig

Escaping håndteres dér, hvor tekst går ind i XML'en

Enhver tekst, du skriver, bliver en del af et XML-dokument, så de fem foruddefinerede XML-entiteter skal escapes, ellers er pakken ugyldig i det øjeblik, en værdi indeholder et og-tegn eller en vinkelparentes. Skriveren escaper &, <, >, " og ' for dig på både inline-strengtekst og formeltekst, de to steder, hvor kalderleverede tegn lander inde i markup. Du sender en rå WideString, og skriveren gør den sikker. Et produktnavn som Smith & Co <Ltd> eller en formel, der refererer til et arknavn i anførselstegn, kommer ud som velformet XML uden nogen escaping fra din side

Livscyklus, og hvorfor Destroy stadig lukker

At færdiggøre pakken er det, der skriver arbejdsbogsdelen, styles-delen, content-types- og relationsdelene og til sidst zip-centralkataloget. Det arbejde sker i Close. En pakke, der aldrig lukkes, er en ufuldstændig zip, som intet regnearksprogram vil åbne, så lukning er ikke valgfri oprydning, det er det trin, der gør filen gyldig. For at sikre mod et glemt Close i en fejlsti udfører Destroy en best-effort-lukning, hvis pakken stadig er åben, så frigivelse af skriveren ikke lækker det underliggende zip-objekt, selv når en undtagelse sprang det eksplicitte kald over. Det pålidelige mønster er stadig det almindelige Delphi-mønster: skriv inde i en try, kald Close, og frigiv i finally

Streaming af et stort ark fra ende til anden

Jobbets form er begynd, tilføj et ark, hæld rækker i, luk. Eksemplet nedenfor skriver en overskriftsrække og derefter en lang række typede datarækker, der blander strenge, tal, en formel uden cachet resultat og en dato. Den hukommelse, det bruger til ti rækker og til ti millioner rækker, er den samme, fordi hver celle forlader mod zip-strømmen, så snart den er skrevet

uses
  lxDirectWrite;

procedure StreamReport(const Path: string; RowCount: Integer);
var
  W: TXLSDirectWriter;
  I: Integer;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginFile(Path);
    W.AddSheet('Sales');

    // Overskriftsrække, skrevet i stigende kolonnerækkefølge
    W.AddRow(1);
    W.WriteString(1, 'Item');
    W.WriteString(2, 'Qty');
    W.WriteString(3, 'Price');
    W.WriteString(4, 'Total');
    W.WriteString(5, 'Date');

    // Datarækker; send 0 til AddRow for automatisk at tage den næste række
    for I := 1 to RowCount do
    begin
      W.AddRow(0);
      W.WriteString(1, 'Item ' + IntToStr(I));
      W.WriteNumber(2, I);
      W.WriteNumber(3, 1.5 + (I mod 10));
      W.WriteFormula(4, Format('B%d*C%d', [I + 1, I + 1]));
      W.WriteDateTime(5, EncodeDate(2026, 1, 1) + I);
    end;

    W.Close;                       // færdiggør pakken
  finally
    W.Free;
  end;
end;

Et andet ark er simpelthen endnu et AddSheet, før du fortsætter, og skriveren lukker det første ark, når den åbner det andet. Boolske flag bruger WriteBoolean, som skriver en typet boolsk celle frem for teksten "True". Hvis du vil bekræfte, at filen er sund og kan rundtures, rapporterer egenskaben CellCount, hvor mange celler der blev skrevet, og at læse resultatet tilbage med den streamende læser bør rapportere den samme total

  // Et andet ark med typede flag efter dataarket ovenfor
  W.AddSheet('Flags');
  W.AddRow(1);
  W.WriteString(1, 'Name');
  W.WriteString(2, 'Active');
  W.AddRow(0);
  W.WriteString(1, 'alpha');
  W.WriteBoolean(2, True);

  WriteLn(Format('wrote %d cells', [W.CellCount]));

At skrive til en strøm i stedet for en fil er den samme kode med BeginStream i stedet for BeginFile, hvilket lader en server sende arbejdsbogen til et HTTP-svar eller en hukommelsesstrøm uden en midlertidig fil på disken. Skriveren ejer ikke den strøm, du sender, så du beholder kontrollen over dens levetid

Når arbejdet er et serverendepunkt, der bygger arbejdsbøger efter behov, viser mønstrene i streamende skrivninger til server- og batchjob, hvordan dette kobles ind i en request-handler og en planlagt eksport. Når spørgsmålet er den bredere omkostning ved meget store arbejdsbøger, både læsning og skrivning, dækker ydeevne for store arbejdsbøger i Delphi, hvor tiden og hukommelsen faktisk går hen. Den streamende direkte skriver leveres som en del af HotXLS Delphi Component til Delphi og C++Builder, sammen med de fulde læse-, redigerings- og gemme-API'er, der er dækket andetsteds på denne blog