Een spreadsheet met een miljoen rijen en een dozijn kolommen is een volkomen normale export van een database-rapportagetaak. Open deze op de gebruikelijke manier, door de hele werkmap in een TXLSWorkbook te laden, en het proces moet elk van die twaalf miljoen cellen als een live-object materialiseren voordat uw eerste regel bedrijfslogica wordt uitgevoerd. Het bestand op schijf is misschien zestig megabyte aan gecomprimeerde XML. De objectboom waarin dit uitvouwt is een veelvoud daarvan, en het moet allemaal tegelijkertijd aanwezig zijn omdat het model inherent is ontworpen voor willekeurige toegang (random access). Voor een rapport dat u van boven naar beneden wilt lezen en weggooien, is dat erg veel geheugen besteed 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 tabblad uitsluitend voorwaarts, cel voor cel, en laat u elke cel voorbijstromen nadat u deze hebt bekeken. Er hoopt zich niets op. Het geheugengebruik blijft nagenoeg constant, of het tabblad nu duizend rijen heeft of tien miljoen, omdat de lezer nooit meer vasthoudt dan het deel dat momenteel wordt geparseerd plus een paar kleine opzoektabellen (lookup tables). Dit is wat de HotXLS direct reader does, en de rest van dit artikel gaat over waarom deze zo klein blijft en wat u er in ruil voor terugkrijgt
Waarom het in-memory model niet schaalt
Een XLSX-bestand is een ZIP-pakket van XML-onderdelen beschreven door ECMA-376. Elk tabblad is een eigen onderdeel, xl/worksheets/sheetN.xml, en daarbinnen is elke rij een element <row> dat <c>-cel-elementen bevat. Het reguliere laadpad leest dat onderdeel en bouwt een adresseerbaar object voor elke cel, zodat u later kunt vragen om Cells[12345, 7] en in constante tijd antwoord krijgt. Willekeurige toegang is het hele doel van een werkmapmodel, en dat is precies wat bewerken, formule-evaluatie en opmaak handig maakt
De keerzijde is dat willekeurige toegang vereist dat alles tegelijkertijd aanwezig is. U kunt niet indexeren in een structuur die u slechts gedeeltelijk hebt opgebouwd. De piek van het geheugengebruik bij een volledige belasting is dus afhankelijk van het aantal cellen. Op een tabblad met miljoenen gevulde cellen komt dat geheugengebruik op een niveau dat u liever wilt vermijden voor uw importservice, vooral als er meerdere van dergelijke taken tegelijkertijd op een gedeelde machine draaien. Wanneer het toegangspatroon dat u daadwerkelijk nodig hebt sequentieel is, betaalt u voor willekeurige toegang voor een functie die u niet zult gebruiken
Een forward-only SAX-scan die geen boom bouwt
De direct reader opent het ZIP-pakket en doorloopt elk tabbladonderdeel met een parser in SAX-stijl (pull parser). SAX betekent hier dat de parser parseer-events rapporteert zodra hij ze tegenkomt: een start-element, een tekstblok, een eind-element, en dan weer verder gaat. Hij houdt geen boomstructuur van knooppunten achter zich. De lezer houdt de huidige rij en kolom bij op basis van de r-attributen, verzamelt het type van de cel, de stijl-index, waarde en formuletekst zodra de events binnenkomen, en wanneer de sluitende tag </c> wordt gezien, zendt hij één cel uitzendt en vergeet deze. De volgende cel hergebruikt dezelfde handvol lokale variabelen
Omdat er tussen cellen niets wordt onthouden, groeit het geheugenbeslag niet met het aantal cellen. Dat is de eigenschap die we willen vasthouden. Een tabblad met tweehonderd rijen en een tabblad met twintig miljoen rijen kosten de lezer evenveel geheugen, en het enige verschil is hoe lang de scan duurt. U geeft willekeurige toegang — de belangrijkste functie van het model — op en in ruil daarvoor krijgt u een plafond voor het geheugengebruik waar het aantal cellen niet doorheen kan breken
Wat in het geheugen blijft, en waarom die twee onderdelen
De scan is niet helemaal stateloos, en the uitzonderingen zijn leerzaam. Twee kleine tabellen moeten gedurende het proces in het geheugen worden gehouden, omdat een cel op zichzelf niet genoeg informatie bevat om deze te kunnen interpreteren
De eerste is de gedeelde stringtabel (shared string table). In SpreadsheetML slaat een tekstcel zijn eigen tekst niet op. Deze draagt t="s" en een numerieke waarde die een index is in xl/sharedStrings.xml, een enkele ontdubbelde lijst van elke unieke string in de werkmap. Dit is een efficiënte oplossing voor bestanden waarin dezelfde labels in duizenden rijen worden herhaald, maar het betekent wel dat de lezer die stringtabel vooraf moet laden en in het geheugen moet houden, omdat elke cel in elk tabblad naar elke vermelding daarin kan verwijzen. De grootte van de tabel hangt af van het aantal unieke strings, niet van het aantal cellen, waardoor deze zelfs bij enorme tabbladen bescheiden blijft
De tweede is de getalopmaak-koppeling (number-format mapping) uit het stijlen-onderdeel. Een numerieke cel en een datumcel zijn binair identiek in het bestand: beide zijn een gewoon getal, omdat een datum in SpreadsheetML simpelweg een opeenvolgende dagtelling (serial day count) is. Het enige dat hen onderscheidt is de stijl van de cel, die via cellXfs in xl/styles.xml naar een getalopmaak-id (number-format id) verwijst. Om een datum als een datum te rapporteren in plaats van als het onbewerkte serienummer, laadt de lezer die stijl-naar-opmaaktabel en houdt deze in het geheugen. Al het andere in het bestand, de eigenlijke celgegevens die het grootste deel van de bytes uitmaken, stroomt voorbij zonder te worden opgeslagen
Elke cel rapporteert een soort en een waarde
Elke verzonden cel komt binnen als een record van het type TXLSDirectCell. Het bevat de tabblad-index en -naam, de 1-gebaseerde rij en kolom, een semantische Kind, de Value als een Variant, the Formula-tekst zonder het leidende is-gelijkteken, en de onbewerkte StyleIndex. De soort is een van xdkNumber, xdkString, xdkBoolean, xdkDate of xdkError, zodat u kunt vertakken op basis van wat de cel betekent in plaats van dit opnieuw te moeten afleiden uit attributen. Een formulecel rapporteert de soort van zijn gecachte resultaat, met de formuletekst ernaast, zodat een berekend totaal binnenkomt als een getal dat u ook vertelt hoe het tot stand is gekomen
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;Een datum onderscheiden van een getal
De datum-kwestie verdient nadere beschouwing, omdat dit is waar de meeste eenvoudige scanners de fout in gaan. Er is geen datumtype op een numerieke cel. Een cel met de seriële waarde 46000 kan een aantal, een prijs of 17 februari 2025 zijn, en het bestand vertelt u dit alleen via de getalopmaak-id die via de stijl van de cel wordt bereikt. ECMA-376 reserveert een blok met ingebouwde opmaak-id's waarvan de betekenis vaststaat bij elke conformerende maker. De id's die betrekking hebben op een datum bevinden zich in twee bereiken: 14 tot en met 22 voor de standaard datum- en tijdnotaties, en 45 tot en met 47 voor tijdsduur-notaties zoals [h]:mm:ss. Wanneer DetectDates is ingeschakeld, wat standaard het geval is, herleidt de lezer de stijl van elke numerieke cel naar de bijbehorende opmaak-id. Een cel waarvan de id binnen die gereserveerde bereiken valt, wordt gerapporteerd als xdkDate met de Value al geconverteerd naar een Delphi TDateTime. Aangepaste notaties worden ook gecontroleerd door de opmaakcode te inspecteren op datum- en tijdtokens, maar de gereserveerde bereiken vormen de betrouwbare ruggengraat. Schakel DetectDates uit en de stijlentabel wordt niet eens geladen, elke numerieke cel komt binnen als xdkNumber, en de scan is marginaal sneller
Tabbladen overslaan en vroegtijdig afbreken
Sequentiële scanning heeft een discreet voordeel dat willekeurige toegang niet kan evenaren: u kunt stoppen. Het event OnSheet vuurt af voordat elk tabblad wordt geopend, en geeft u twee schakelaars. Stel SkipSheet in en dat hele onderdeel wordt nooit geparseerd. Dit is hoe u alleen de tabbladen scant die u belangrijk vindt in een werkmap met meerdere tabbladen, zonder te betalen voor het lezen van de rest. Stel Abort in en de hele scan eindigt onmiddellijk. Het event OnCell draagt zijn eigen Abort, zodat u kunt stoppen op het moment dat u hebt gevonden wat u zocht — een bepaalde rij, een grenswaarde, het einde van een kopblok — zonder de resterende miljoenen cellen te lezen. Bij een forward-only scan is afbreken echt gratis, omdat het werk dat u overslaat werk is dat nog niet was uitgevoerd
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
De lezer telt elke gevulde cel die hij passeert, en hij doet dit ongeacht of er een OnCell-handler is gekoppeld. Voorheen kwam de telling van gevulde cellen bij een niet-gekoppelde handler terug als nul, omdat tellen een bijwerking was van het uitzenden. Nu is de telling onafhankelijk van het uitzenden. Dit betekent dat u één vraag kunt stellen: hoeveel gevulde cellen bevat deze werkmap eigenlijk? En u krijgt het antwoord voor de prijs van een scan zonder callbacks. ReadFile en ReadStream retourneren beide dat totaal als een Int64, en hetzelfde getal is achteraf beschikbaar als de property CellCount. Een retourwaarde van -1 geeft aan dat het bestand niet kon worden geopend of 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 manier aan. Het contrast met een volledige belading is de kern van de zaak: waar het laden van quarterly_export.xlsx in een werkmap elke cel zou uitvouwen in een in het geheugen aanwezig object en de boel zou vasthouden, de direct reader bewaart alleen de gedeelde strings en de stijlentabel, terwijl de twaalf miljoen cellen een voor een door uw OnCell stromen. De berekening die per cel werd uitgevoerd laat niets achter, dus het piekgeheugen wordt bepaald door het aantal unieke strings van de werkmap, niet door het aantal rijen
De direct reader is het juiste gereedschap wanneer de taak is om een grote werkmap eenmalig te lezen en te extraheren of samen te vatten. Als u in plaats daarvan de willekeurige toegang van het volledige model nodig hebt, maar wilt dat dit goed presteert bij grote bestanden, behandelt de afstemming in onze aantekeningen over de prestaties van grote werkmappen in Delphi dat pad. En wanneer de richting wordt omgekeerd — het produceren van grote uitvoer in plaats van het consumeren ervan — past de handleiding voor streaming-schrijven voor server-batchtaken dezelfde discipline van constant geheugen toe op het schrijven. Alle drie worden geleverd als onderdeel van het HotXLS Component voor Delphi en C++Builder, samen met de lees-, schrijf-, formule- en opmaak-API's die elders op deze blog worden behandeld