Een spreadsheet met een miljoen rijen en een twaalftal kolommen is een volstrekt normale export van een databaseraportage-taak. Open het op de gebruikelijke manier, door de hele werkmap in een TXLSWorkbook te laden, en het proces moet elk van die twaalf miljoen cellen materialiseren als een live object voordat uw eerste regel bedrijfslogica draait. Het bestand op schijf is misschien zestig megabyte aan gecomprimeerde XML. De objectenboom waarin het zich uitvouwt is een veelvoud daarvan, en het moet allemaal tegelijk resident zijn, omdat het model opzettelijk is ontworpen voor willekeurige toegang (random-access). Voor een rapport dat u van boven naar beneden wilt lezen en dan weggooien, is dat een grote hoeveelheid geheugen verspild aan een structuur die u nooit nodig had
Er is een tweede pad door hetzelfde bestand. In plaats van een model te bouwen, scant u de XML van het werkblad alleen voorwaarts, cel voor cel, en laat u elke cel voorbij stromen nadat u ernaar hebt gekeken. Er accumuleert niets. Het geheugengebruik blijft vrijwel constant, of het blad nu duizend of tien miljoen rijen heeft, omdat de reader nooit meer vasthoudt dan het deel dat op dat moment wordt geparseerd plus een paar kleine opzoektabelletjes. Dit is wat de directe reader van HotXLS doet, en de rest van dit artikel gaat in op de vraag waarom deze klein blijft en wat u daarvoor terugkrijgt
Waarom het in-memory model niet schaalt
Een XLSX-bestand is een ZIP-pakket van XML-delen die worden beschreven door ECMA-376. Elk werkblad is een eigen deel, xl/worksheets/sheetN.xml, en daarbinnen is elke rij een <row>-element met daarin <c>-celelementen. Het reguliere laadpad leest dat deel in en construeert een adresseerbaar object voor elke cel, zodat u later om Cells[12345, 7] kunt vragen en een antwoord krijgt in constante tijd. Willekeurige toegang is het hele bestaansrecht van een werkmapmodel, en het is precies wat bewerken, formule-evaluatie en opmaak zo handig maakt
De prijs daarvan is dat willekeurige toegang vereist dat alles tegelijkertijd aanwezig is. U kunt niet indexeren in een structuur die u slechts gedeeltelijk hebt gebouwd. Dus de pieklaadgeheugen is een functie van het aantal cellen, en op een blad met miljoenen ingevulde cellen komt die functie uit op een plek waar uw service niet wil zijn, vooral als er meerdere van zulke taken tegelijk draaien op een gedeelde machine. Wanneer het toegangspatroon dat u daadwerkelijk nodig hebt sequentieel is, betekent betalen voor willekeurige toegang betalen voor een mogelijkheid die u niet zult gebruiken
Een voorwaartse SAX-scan die geen boomstructuur bouwt
De directe reader opent het ZIP-pakket en doorloopt elk werkbladdeel met een SAX-achtige pull-parser. SAX betekent hier dat de parser parse-gebeurtenissen rapporteert zodra hij ze tegenkomt, een start-element, een tekst-run, een eind-element, en dan verder gaat. Hij houdt geen node-boom (tree) achter zich in stand. De reader houdt de huidige rij en kolom bij aan de hand van de r-attributen, verzamelt het type van de cel, de stijl-index, de waarde en de formule-tekst zodra de gebeurtenissen arriveren, en wanneer de afsluitende </c>-tag is gezien, zendt hij de cel uit (emits) en vergeet deze. De volgende cel hergebruikt hetzelfde handjevol lokale variabelen
Omdat er niets bewaard blijft tussen de cellen door, groeit de geheugenvoetafdruk niet mee met het aantal cellen. Dat is de eigenschap die de moeite waard is om vast te houden. Een blad met tweehonderd rijen en een blad met twintig miljoen rijen kosten de reader hetzelfde residente geheugen, en het verschil daartussen is alleen hoelang de scan duurt. U geeft willekeurige toegang, de hoofdattractie van het model, op, en in ruil daarvoor krijgt u een plafond op het geheugen waar de cel-aantallen niet doorheen kunnen drukken
Wat resident blijft, en waarom die twee delen
De scan is niet helemaal toestandsloos (stateless), en de uitzonderingen zijn leerzaam. Twee kleine tabellen moeten voor de duur in het geheugen worden gehouden, omdat een cel op zich niet genoeg informatie met zich meedraagt om hem zonder deze tabellen te interpreteren
De eerste is de gedeelde string-tabel (shared string table). In SpreadsheetML slaat een tekstcel zijn eigen tekst niet op. Hij draagt t="s" en een numerieke payload die fungeert als een index naar xl/sharedStrings.xml, een enkele ontdubbelde lijst van elke unieke string in de werkmap. Dit is een goede ruimtelijke afweging voor bestanden waarbij dezelfde labels zich over duizenden rijen herhalen, maar het betekent wel dat de reader die string-tabel van tevoren moet laden en resident moet houden, want elke cel overal in elk willekeurig blad kan verwijzen naar elk willekeurig item erin. De grootte van de tabel wordt bepaald door het aantal unieke strings, niet door de hoeveelheid cellen, waardoor deze zelfs bij enorme bladen bescheiden blijft
De tweede is de nummeropmaaktoewijzing (number-format mapping) uit het stijlen-deel. Een numerieke cel en een datumcel zijn op het draadje (byte-for-byte) identiek: beide zijn een gewoon getal, want een datum in SpreadsheetML is slechts een serieel dagentelling. Het enige dat hen onderscheidt is de stijl van de cel, die via cellXfs in xl/styles.xml naar een nummeropmaak-id (format id) wijst. Om een datum als datum te rapporteren in plaats van als het ruwe seriële getal, laadt de reader die style-to-format-tabel in en houdt hem resident. Al de rest in het bestand, de feitelijke celgegevens die het overgrote deel van de bytes uitmaken, stroomt voorbij zonder opgeslagen te worden
Elke cel rapporteert een soort en een waarde
Elke uitgezonden cel arriveert als een TXLSDirectCell record. Hij draagt de blad-index en naam met zich mee, de 1-gebaseerde rij en kolom, een semantische Kind, de Value als een Variant, de Formula-tekst zonder het inleidende isgelijkteken, en de ruwe StyleIndex. De soort is één van xdkNumber, xdkString, xdkBoolean, xdkDate, of xdkError, zodat u kunt vertakken (branch) op wat de cel betekent in plaats van dat u het opnieuw moet afleiden uit attributen. Een formule-cel rapporteert de soort van het in de cache opgeslagen resultaat, samen met de formule-tekst ernaast, dus een berekend totaal komt door als een getal dat u ook vertelt hoe het geproduceerd werd
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Onderscheid maken tussen een datum en een getal
Het datum-vraagstuk verdient een nadere blik, omdat hier de meeste naïeve scanners in de fout gaan. Er bestaat geen datumtype bij een numerieke cel. Een cel die de seriële waarde 46000 bevat, zou een hoeveelheid, een prijs, of de 17e februari 2025 kunnen zijn, en het bestand vertelt u welke het is uitsluitend via de format-id die wordt bereikt via de stijl van de cel. ECMA-376 reserveert een blok met ingebouwde format-id's waarvan de betekenis vastligt voor elke conforme producent, en de datumhoudende id's bevinden zich in twee reeksen: 14 t/m 22 voor de standaard datum- en tijdsopmaken, en 45 t/m 47 voor de verstreken-tijdopmaken (elapsed-time) zoals [h]:mm:ss. Wanneer DetectDates aan staat, wat de standaard is, lost de reader de stijl van elke numerieke cel op tot zijn format-id, en een cel waarvan de id in die gereserveerde reeksen valt, wordt gerapporteerd als xdkDate met zijn Value reeds geconverteerd naar een Delphi TDateTime. Aangepaste opmaken (custom formats) worden ook gecontroleerd, door de opmaakcode te inspecteren op datum- en tijd-tokens, maar de gereserveerde reeksen vormen de betrouwbare ruggengraat. Zet u DetectDates uit, dan wordt de stijlen-tabel niet eens geladen, elke numerieke cel komt door als xdkNumber, en de scan is fractioneel slanker
Bladen overslaan en voortijdig afbreken
Sequentieel scannen heeft een stil voordeel dat willekeurige toegang niet kan evenaren: u kunt stoppen. De OnSheet gebeurtenis vuurt af voordat elk werkblad geopend wordt, en geeft u twee schakelaars (switches). Stelt u SkipSheet in, dan wordt dat hele deel niet geparseerd, wat u in staat stelt om in een werkmap met meerdere bladen alleen diegene te scannen waarom u geeft, zonder te betalen voor het lezen van de rest. Stelt u Abort in, dan stopt de gehele scan onmiddellijk. De OnCell gebeurtenis draagt zijn eigen Abort met zich mee, zodat u kunt halt houden op het moment dat u gevonden heeft wat u zocht, een bepaalde rij, een sentinel-waarde, het einde van een header-blok, zonder de resterende miljoenen cellen te lezen. Bij een voorwaartse-scan (forward-only scan) is het daadwerkelijk gratis om af te breken, omdat het werk dat u overslaat werk was dat nog niet was gebeurd
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Cellen tellen zonder handler
Eén recente verfijning is het vermelden waard, omdat deze een veelgestelde vraag reduceert tot één enkele, goedkope aanroep. De reader telt elke ingevulde cel die hij passeert, en doet dit ongeacht of er nu een OnCell-handler gekoppeld is of niet. Eerder kwam het aantal ingevulde cellen, indien er geen handler ingesteld was, als nul terug, aangezien tellen een bijwerking van het uitzenden (emitting) was. Nu staat de telling los van emissie. Dat betekent dat u één vraag kunt stellen, hoeveel ingevulde cellen bevat deze werkmap eigenlijk, en u krijgt het antwoord voor de prijs van een scan, met helemaal geen callbacks (callbacks). Zowel ReadFile als ReadStream retourneren dat totaal als een Int64, en hetzelfde getal is naderhand beschikbaar via de eigenschap (property) CellCount. Als -1 wordt geretourneerd, is dit het teken dat het bestand niet geopend kon worden of dat het geen OOXML-pakket is
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Voor de volledige scan koppelt u de handler en roept u ReadFile op exact dezelfde wijze aan. Het contrast met een volledige lading (full load) is precies waar het om draait: daar waar het inladen van quarterly_export.xlsx in een werkmap iedere cel zou uitbreiden naar een resident object en de hele boel vast zou houden, houdt de directe reader alleen de shared strings en de style-table over, terwijl de twaalf miljoen cellen een-voor-een uw OnCell binnendruppelen (flow through). De rekenkundige operatie die per cel gedraaid is, laat niks achter, het maximale geheugen (peak memory) wordt daardoor bepaald door de hoeveelheid unieke strings uit de werkmap, niet door het rijen-aantal
De directe reader is het juiste gereedschap wanneer uw taak eruit bestaat om een grote werkmap één keer in te lezen en deze eruit te trekken (extract) of samen te vatten (summarise). Wanneer u juist de willekeurige toegang (random access) van het volledige model nodig heeft, maar wilt dat dit zich netjes gedraagt (behave) met grote bestanden, dan gaat de tuning in onze aantekeningen omtrent de prestaties van de grote werkmap in Delphi hierop in. En indien de richting omgedraaid is, dus er wordt grote uitvoer gegenereerd, veeleer dan opgeslorpt (consuming), dan leest u in de walkthrough streaming write voor server batch taken de manier waarop u diezelfde discipline qua constant-geheugen (constant-memory) op het schrijven kunt loslaten. Alle drie vormen zij onderdeel van de HotXLS Component voor Delphi en C++Builder, net als de API's met betrekking tot het inlezen (reading), schrijven (writing), verwerken van formules, en de formattering (formatting), welke op andere plekken in dit blog behandeld worden