Een bulk-normalisatietaak voor spreadsheets is drie problemen in één jas. Je hebt een archief met gemengde formaten: BIFF-tijdperk-.xls, modern .xlsx, een verspreiding aan .ods uit een of ander LibreOffice-experiment, en een handvol bestanden die niemand kan openen omdat het wachtwoord met een voormalige medewerker mee de deur uit ging. Het doel is alles om te zetten naar XLSX en CSV. De versie van die taak die de meeste mensen schrijven is een lus die elk bestand opent en onder een nieuwe extensie opslaat, en dat werkt prima totdat iemand vraagt welke bestanden hun grafieken verloren, hun macro's lieten vallen, of nooit openden. De lus heeft geen antwoord, want conversie alleen houdt geen verslag bij. Een werkbank wel: hij inventariseert eerst, converteert daarna, en verifieert als derde, en de drie fasen moeten informatie delen wil iets ervan betrouwbaar zijn
Zo'n werkbank bouwen in Delphi of C++Builder betekent vier HotXLS-mogelijkheden aan elkaar knopen, waarvan geen enkele Excel ergens in de pipeline geïnstalleerd hoeft te hebben. Er zijn twee native engines, een BIFF8-facade voor .xls en een OOXML-facade voor .xlsx en .ods. Er zijn goedkope onderzoeksaanroepen die metadata lezen zonder het hele bestand te parsen. Er zijn per-blad-audittellers die je vertellen wat een werkmap daadwerkelijk bevat. En er is een conversiematrix met een gedocumenteerd getrouwheidsprofiel per route. Het werk zit in weten waar elk daarvan een scherp randje heeft, want dat heeft elk ervan, en die randjes zijn precies de dingen die een schone nachtelijke batch in een maandagochtendincident veranderen
Onderzoeken vóór je laadt: bladnamen en versleutelingsdetectie
Een werkmap van 200 MB openen om er alleen achter te komen dat hij versleuteld is, verspilt minuten per bestand, en vermenigvuldigd over een groot archief verspilt het dagen. Beide facades bieden GetSheetNames, dat bladmetadata leest zonder de werkmap te vullen. De BIFF-implementatie scant alleen de BoundSheet-records aan het begin van de stream; de OOXML-implementatie leest alleen workbook.xml binnen de zip. Daarnaast detecteert CanReadEncrypted een versleutelingscontainer zonder ontsleuteling te proberen:
var
Probe: TXLSXWorkbook;
Names: TStringList;
begin
Names := TStringList.Create;
Probe := TXLSXWorkbook.Create;
try
if Probe.CanReadEncrypted(FileName) then
begin
Writeln(FileName + ': encrypted container - route to manual handling');
Exit;
end;
if Probe.GetSheetNames(FileName, Names) <= 0 then
Writeln(FileName + ': unreadable - quarantine')
else
Writeln(Format('%s: %d sheet(s), first "%s"',
[FileName, Names.Count, Names[0]]));
finally
Probe.Free;
Names.Free;
end;
end;Twee operationele details maken deze lus goedkoop. GetSheetNames reset noch vult de workbook-instantie, dus één probe-object kan duizenden bestanden classificeren zonder opnieuw te worden aangemaakt. En de XLS-facadeversie van dezelfde aanroep begrijpt ook .xlsx-pakketten, wat het een handige enkele probe maakt wanneer bestandsextensies niet te vertrouwen zijn, wat ze zelden zijn in een archief van die leeftijd. Triage vóór het laden is een eigen behandeling waard; de mechanica van lichtgewicht inspectie staat in ons artikel over bladlijsten en lichtgewicht workbook-inspectie
Tellen wat een werkmap echt bevat
Zodra een bestand de triage doorstaat, bepaalt de auditpas zijn conversieroute. De XLSX-facade biedt een teller voor elke functiefamilie die van invloed is op een getrouwheidsbeslissing: samengevoegde cellen, grafieken, afbeeldingen, voorwaardelijke opmaak, gegevensvalidaties, tabellen, hyperlinks en opmerkingen, plus vlaggen op werkmapniveau voor macro's, beveiliging en bronformaat. De conversieroute voor een bestand hangt bijna volledig af van welke hiervan niet-nul teruggeven
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) <> 1 then Exit;
for I := 0 to Book.Sheets.Count - 1 do
begin
Sheet := Book.Sheets[I];
Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
[Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
end;
if Book.HasVbaProject then
Writeln(' contains VBA project - macro policy applies');
if Book.ExternalLinks.Count > 0 then
Writeln(Format(' %d external link(s)', [Book.ExternalLinks.Count]));
finally
Book.Free;
end;
end;Lees Cells.Count met één kanttekening in gedachten. De celopslag is sparse, dus het getal telt geïnstantieerde cellen, niet het rechthoekige oppervlak van het gebruikte bereik. Een blad met één waarde in A1 en een andere in ZZ9999 meldt twee cellen, niet het miljoen-en-nog-wat dat ertussen ligt. De equivalente scan aan de BIFF-kant gebruikt UsedRange-grenzen samen met ForEachCell, en die draagt de off-by-one die bijna iedereen de eerste keer laat struikelen: UsedRange.FirstRow en zijn verwanten zijn 0-gebaseerd, terwijl Cells.Item[Row, Col] 1-gebaseerd is. Een doorloop die vergeet één op te tellen bij elke grens, audit de verkeerde rechthoek en zegt dat nooit
Twee hendels verlagen de kosten van een alleen-auditpas over grote legacy bestanden. _DisableGraphics op true zetten vóór het openen van een .xls slaat het parsen van de OfficeArt-tekenlaag helemaal over, wat echte tijd bespaart op werkmappen die vol staan met vormen. Het is echter strikt een alleen-lezen optimalisatie: opslaan vanuit een instantie die zo is geopend, zou de tekeningen laten vallen die nooit zijn geparst, dus de vlag hoort alleen thuis op paden die het bestand nooit zullen terugschrijven. Wanneer de audit per-cel-inhoud nodig heeft in plaats van tellingen, doorloopt de ForEachCell-callback de gevulde cellen rechtstreeks en omzeilt het de Variant-overhead per toegang die geïndexeerde celeigenschappen bij elke lezing betalen, wat snel oploopt over miljoenen cellen
De inconsistente returncodes vroeg normaliseren
HotXLS-I/O-aanroepen melden fouten via integer-resultaten in plaats van uitzonderingen, en de conventies zijn niet uniform over de API heen. De meeste open- en opslagaanroepen geven 1 terug bij succes en -1 bij falen. GetSheetNames geeft het aantal bladen terug, of -1 met de lijst geleegd. XLSX-SaveAsHTML breekt het patroon opnieuw en geeft 0 terug voor succes, -1 voor een bladindex buiten bereik. Een werkbank die overal test op = 1, zal stilletjes de aanroepen verkeerd classificeren die succes op een andere manier signaleren, en één die test op <> -1, zal de aanroepen slikken die met een andere code falen
De regel die het contact met de hele API overleeft, is nauwer dan hij lijkt: behandel <= 0 als mislukking voor aanroepen die een aantal teruggeven, controleer de gedocumenteerde succeswaarde voor elke opslagroutine die je daadwerkelijk gebruikt, en zet beide achter één kleine resultaatcontrolefunctie zodat de conventie op precies één plek leeft. Batchpipelines falen veel vaker door een langzame opeenstapeling van ongecontroleerde returncodes dan door een of andere exotische parserbug, en de kosten van dit fout doen zijn veertigduizend bestanden later, wanneer niemand meer weet welke conversies daadwerkelijk zijn geslaagd
De conversiematrix en waar elke weg data verliest
De twee facades verdelen het conversiewerk onderling. TXLSXWorkbook opent XLSX, ODS en CSV, en slaat XLSX, ODS, CSV, HTML, RTF en AES-versleutelde XLSX op. TXLSWorkbook opent en slaat BIFF op, en exporteert HTML, RTF en CSV. Het nuttige is dat elk pad met een gedocumenteerd getrouwheidsprofiel komt, niet met een vage belofte van correctheid, zodat je van tevoren kunt beslissen welke routes veilig zijn voor welke bestanden
CSV-export schrijft UTF-8 met een BOM, CRLF-regeleindes en RFC 4180-aanhalingstekens. Wat het niet doet is formules evalueren: een cel met =SUM(...) exporteert als de letterlijke formuletekst, dus een blad vol formules verandert in een blad vol strings tenzij je de waarden eerst berekent. HTML-export produceert één tabel, met colspan en rowspan als plaatsvervanger voor samengevoegde cellen en basisstijlen inline. RTF-export heeft een scherpere grens: het kan samengevoegde cellen niet over kolommen laten overspannen, dus de vervolgcellen van een samenvoeging komen leeg uit. ODS-import is met opzet lichtgewicht, volgens de eigen documentatie van de bibliotheek. Scalaire waarden en gecachte formuleresultaten komen door; stijlen, levende ODF-formule-expressies en tekeningen niet. Dat is van belang zodra het archief echte OpenDocument-bestanden bevat die vallen onder OASIS ODF 1.3, waar alles wat in de buurt komt van een visueel getrouwe conversie meer nodig heeft dan dit importpad is gebouwd om te dragen, en de auditpas is wat je vertelt dat die bestanden bestaan voordat de batch ze stilletjes platslaat
SaveXLSWorkbookAsXLSX is een databrug, geen lay-outbrug
De BIFF-facade kan geen OOXML rechtstreeks schrijven, dus de oversteek van .xls naar .xlsx loopt via de functie SaveXLSWorkbookAsXLSX in de unit lxXlsxExport. De getrouwheid van die brug is het waard om ronduit te vermelden, want de naam suggereert meer dan hij waarmaakt. Hij kopieert waarden, formules, getalnotaties, vulkleuren, kernlettertypekenmerken, kolombreedtes en weergave-instellingen zoals rasterlijnen. Hij kopieert geen randen, samengevoegde bereiken, opmerkingen, grafieken of voorwaardelijke opmaak. Voor datagerichte normalisatie, waar downstream systemen het resultaat zullen parsen en niemand naar de opmaak kijkt, is dat precies genoeg en gaat er niets verloren waar iemand om geeft. Voor een opgemaakt bestuursrapport bedoeld om door een mens te worden gelezen, is dat niet genoeg, en dit is precies waar de audittellers hun plek verdienen: een bestand dat de audit heeft gemarkeerd als drager van grafieken en voorwaardelijke opmaak, moet naar een handmatige wachtrij worden geroute, niet via een brug die beide zonder pardon laat vallen
var
Legacy: IXLSWorkbook; // interfaceverwijzing: geen Free
Modern: TXLSXWorkbook;
begin
if SameText(ExtractFileExt(FileName), '.xls') then
begin
Legacy := TXLSWorkbook.Create;
if Legacy.Open(FileName) <= 0 then Exit;
if SaveXLSWorkbookAsXLSX(Legacy,
ChangeFileExt(FileName, '.xlsx')) <= 0 then
Writeln('bridge failed: ' + FileName);
end
else
begin
Modern := TXLSXWorkbook.Create;
try
Modern.StreamingWrite := True; // stream werkblad-XML naar de zip
if Modern.Open(FileName) = 1 then
Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
finally
Modern.Free;
end;
end;
end;De lus hierboven toont ook de doorvoerhendel aan de OOXML-kant. StreamingWrite op true zetten streamt werkblad-XML rechtstreeks naar het uitvoerpakket in plaats van het als één gigantische string in het geheugen op te bouwen, wat het verschil is tussen een comfortabele run en een out-of-memory-crash zodra bestanden honderdduizenden rijen bereiken. Omvang en geheugengedrag voor die modus krijgen hun eigen behandeling in ons artikel over streaming writes voor server-batchtaken. Nog een eigenschap is van belang voor een batch die elke core wil gebruiken: geen van beide facades is thread-safe, maar geen van beide deelt ook globale status, dus het ondersteunde patroon voor parallelle conversie is één workbook-instantie per workerthread, zonder locking ertussen
De wachtwoordbestanden, en wat ermee te doen
De vergrendelde bestanden van het archief splitsen netjes per formaat, en die splitsing bepaalt waar ze naartoe gaan. Legacy .xls-versleuteling, of het nu RC4, RC4 over CryptoAPI, of de oude XOR-obfuscatie is, is leesbaar: geef het wachtwoord door aan Open en het bestand converteert als elk ander. Versleutelde .xlsx-pakketten zijn een ander verhaal. HotXLS detecteert ze met CanReadEncrypted maar kan ze niet ontsleutelen, dus de enige eerlijke zet is ze naar een wachtrij te routeren waar een mens elk exemplaar opent en opnieuw opslaat in Excel voordat het zich weer bij de pipeline voegt. Die asymmetrie is het waard om vooraf voor te ontwerpen, want de versleutelde XLSX-bestanden zijn juist degene die het meest waarschijnlijk de records zijn waar iemand daadwerkelijk om geeft
De lus sluiten met verificatie
De derde fase is degene die wordt overgeslagen, en dat overslaan is wat een bulkconversie in een aansprakelijkheid verandert. Geen enkel opslagpad in HotXLS evalueert formules. Excel herberekent wanneer het een bestand opent, dus een XLSX-naar-XLSX-conversie blijft correct, maar een CSV-doel ontvangt de formuletekst woordelijk tenzij de pipeline eerst Calculate op de cellen uitvoert en de resultaten terugschrijft. Dat van tevoren weten is het verschil tussen een CSV vol getallen en een CSV vol =SUM(...)-strings die niemand opmerkt totdat een downstream import erin stikt
Verificatie zelf is goedkoop genoeg dat er geen excuus is om het weg te laten. Heropen elk geconverteerd bestand met dezelfde bibliotheek, draai de audittellers opnieuw, en vergelijk ze met de cijfers van vóór de conversie die de inventarisatiepas al heeft vastgelegd. Een bladaantal dat is gedaald, een grafiekaantal dat naar nul ging waar de bron er drie had, een celaantal dat van een klif viel: elk daarvan is een stil verlies gevangen voor de prijs van een tweede open. Steekproefsgewijs een sample met het oog controleren in Excel of LibreOffice bovenop dat, en de combinatie vangt de overweldigende meerderheid van conversieschade voordat het wordt uitgeleverd. Dit is de hele reden waarom de inventarisatiefase de verificatiefase voedt. Zonder de cijfers van tevoren bewijzen de cijfers erna niets
Een audit-first-werkbank verandert een riskante bulkconversie in een meetbaar proces met een quarantainebaan voor de bestanden die niet schoon kunnen slagen. Alle onderzoeks-, tel- en conversieaanroepen die hier zijn getoond, maken deel uit van de HotXLS Delphi Component, die ze native in-process uitvoert zonder Excel-automatisering