Hromadná normalizácia tabuliek predstavuje tri problémy spojené do jedného. Máte archív rôznych formátov: staršie .xls z éry BIFF, moderné .xlsx, niekoľko súborov .ods z nejakého experimentu s LibreOffice a zopár súborov, ktoré nikto nedokáže otvoriť, pretože heslo odišlo spolu s bývalým zamestnancom. Cieľom je previesť všetko do formátov XLSX a CSV. Riešenie tejto úlohy, ktoré väčšina ľudí napíše, je slučka, ktorá otvorí každý súbor a uloží ho pod novou príponou. To funguje až do momentu, kým sa niekto nespýta, ktoré súbory stratili svoje grafy, prišli o makrá alebo sa vôbec neotvorili. Slučka na to nemá odpoveď, pretože samotná konverzia neuchováva žiadne záznamy. Špecializovaný nástroj (workbench) to však dokáže: najprv urobí inventúru, potom vykoná konverziu a nakoniec všetko overí, pričom tieto tri fázy musia zdieľať informácie, aby bol výsledok dôveryhodný.
Zostavenie takéhoto pracovného nástroja v Delphi alebo C++Builderi znamená prepojenie štyroch funkcií HotXLS, z ktorých žiadna nevyžaduje inštaláciu Excelu na žiadnom mieste spracovania. K dispozícii sú dva natívne enginy: rozhranie BIFF8 pre .xls a rozhranie OOXML pre .xlsx a .ods. K dispozícii sú rýchle a nenáročné testovacie volania, ktoré čítajú metadáta bez analýzy celého súboru. K dispozícii sú aj audítorské počítadlá pre jednotlivé hárky, ktoré vám prezradia, čo zošit v skutočnosti obsahuje. A nakoniec je tu matica konverzie s dokumentovaným profilom vernosti pre každú cestu. Úlohou je vedieť, kde má každá z týchto funkcií svoje úskalia, pretože každá z nich nejaké má, a práve tieto úskalia sú tým, čo môže zmeniť bezproblémové nočné dávkové spracovanie na pondelňajšiu rannú haváriu.
Otestujte pred načítaním: názvy hárkov a detekcia šifrovania
Otvorenie 200 MB zošita len preto, aby ste zistili, že je zašifrovaný, stráca minúty na súbor, čo pri veľkom archíve znamená stratu celých dní. Obe rozhrania vystavujú metódu GetSheetNames, ktorá číta metadáta hárkov bez naplnenia samotného zošita. Implementácia BIFF skenuje iba záznamy BoundSheet na začiatku streamu; implementácia OOXML číta iba workbook.xml vo vnútri archívu zip. Popri tom metóda CanReadEncrypted deteguje šifrovaný kontajner bez toho, aby sa pokúšala o dešifrovanie:
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;Dva prevádzkové detaily robia túto slučku veľmi efektívnou. Metóda GetSheetNames neresetuje ani nenapĺňa inštanciu zošita, takže jediný testovací objekt môže klasifikovať tisíce súborov bez nutnosti opätovného vytvárania. Verzia toho istého volania pre rozhranie XLS navyše rozumie aj balíkom .xlsx, čo z nej robí pohodlný jednotný nástroj na testovanie, keď príponám súborov nemožno dôverovať (čo v takom starom archíve býva málokedy). Triedenie pred načítaním si zaslúži osobitnú pozornosť; mechanizmy ľahkej kontroly sú popísané v našom článku o zozname hárkov a ľahkej inšpekcii zošitov.
Počítanie toho, čo zošit skutočne obsahuje
Keď súbor prejde úvodným triedením, audítorský priechod rozhodne o spôsobe jeho konverzie. Rozhranie XLSX poskytuje počítadlo pre každú skupinu funkcií, ktorá ovplyvňuje vernosť prevodu: zlúčené bunky, grafy, obrázky, podmienené formáty, overenia údajov, tabuľky, hyperlinky a komentáre, plus príznaky na úrovni zošita pre makrá, ochranu a zdrojový formát. Cesta konverzie súboru závisí takmer výlučne od toho, ktoré z týchto hodnôt sú nenulové.
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;Pri čítaní Cells.Count majte na pamäti jedno upozornenie. Úložisko buniek je riedke (sparse), takže číslo udáva počet vytvorených buniek, nie obdĺžnikovú oblasť použitého rozsahu. Hárok s jednou hodnotou v A1 a druhou v ZZ9999 nahlási dve bunky, nie približne milión, ktoré ležia medzi nimi. Ekvivalentný sken na strane BIFF používa hranice UsedRange spolu s ForEachCell a obsahuje chybu o jeden index (off-by-one), na ktorej sa takmer každý prvýkrát popáli: UsedRange.FirstRow a jeho súrodenci začínajú od 0, zatiaľ čo Cells.Item[Row, Col] začína od 1. Prechádzanie, ktoré zabudne pripočítať jednotku ku každej hranici, vykoná audit nesprávneho obdĺžnika a nikdy na to neupozorní.
Dva nástroje znižujú náklady na čisto audítorský priechod veľkými staršími súbormi. Nastavenie _DisableGraphics na hodnotu true pred otvorením súboru .xls úplne preskočí analýzu kresliacej vrstvy OfficeArt, čo šetrí čas pri zošitoch s veľkým množstvom tvarov. Ide však o čisto read-only optimalizáciu: uloženie z inštancie otvorenej týmto spôsobom by odstránilo nákresy, ktoré sa nikdy neanalyzovali, takže tento príznak patrí len na cesty, ktoré nebudú súbor zapisovať späť. Keď audit vyžaduje obsah jednotlivých buniek a nie iba ich počet, callback ForEachCell prechádza naplnené bunky priamo a obchádza režijné náklady spojené s typom Variant pri každom prístupe k indexovaným vlastnostiam buniek, ktoré pri miliónoch buniek rýchlo narastajú.
Normalizujte nekonzistentné návratové kódy včas
Vstupné a výstupné volania HotXLS hlásia chyby prostredníctvom celočíselných hodnôt namiesto výnimiek a konvencie nie sú v celom API jednotné. Väčšina volaní na otvorenie a uloženie vracia 1 pri úspechu a -1 pri zlyhaní. GetSheetNames vracia počet hárkov alebo -1 s vyčisteným zoznamom. XLSX SaveAsHTML opäť mení vzor a vracia 0 pri úspechu, -1 pri indexe hárku mimo rozsahu. Nástroj, ktorý všade testuje = 1, ticho nesprávne klasifikuje volania signalizujúce úspech iným spôsobom, a ten, ktorý testuje <> -1, prehliadne tie, ktoré zlyhajú s iným kódom.
Pravidlo, ktoré funguje pre celé API, je jednoduchšie, než sa zdá: pri volaniach vracajúcich počty považujte hodnoty <= 0 za zlyhanie, skontrolujte zdokumentovanú hodnotu úspechu pre každú rutinu ukladania, ktorú reálne používate, a obe tieto kontroly umiestnite do jednej malej funkcie na overenie výsledku, aby táto konvencia existovala na jedinom mieste. Dávkové spracovania zlyhávajú oveľa častejšie na pomalom hromadení neskontrolovaných návratových kódov než na nejakej exotickej chybe parsera. Následky tohto pochybenia sa prejavia o štyridsaťtisíc súborov neskôr, keď si už nikto nebude pamätať, ktoré konverzie skutočne prebehli úspešne.
Konverzná matica a miesta, kde dochádza k strate údajov
Obe rozhrania si prácu s konverziou delia medzi sebou. TXLSXWorkbook otvára XLSX, ODS a CSV a ukladá XLSX, ODS, CSV, HTML, RTF a AES-zašifrované XLSX. TXLSWorkbook otvára a ukladá BIFF a exportuje HTML, RTF a CSV. Výhodou je, že každá cesta prichádza s dokumentovaným profilom vernosti, nie s vágnym prísľubom správnosti, takže sa môžete vopred rozhodnúť, ktoré trasy sú pre ktoré súbory bezpečné.
Export do CSV zapisuje kódovanie UTF-8 s BOM, koncami riadkov CRLF a úvodzovkami podľa RFC 4180. Čo však nerobí, je vyhodnocovanie vzorcov: bunka obsahujúca =SUM(...) sa exportuje ako doslovný text vzorca, takže hárok plný vzorcov sa zmení na hárok reťazcov, ak hodnoty najprv neprepočítate. Export do HTML vytvorí jedinú tabuľku, kde zlúčené bunky reprezentujú atribúty colspan a rowspan a základné štýly sú vložené inline. Export do RTF má prísnejšie obmedzenie: nedokáže rozdeliť zlúčené bunky medzi stĺpce, takže pokračujúce bunky zlúčenia ostanú prázdne. Import ODS je podľa vlastnej dokumentácie knižnice zámerne odľahčený. Skalárne hodnoty a vyrovnávacie výsledky vzorcov prejdú; štýly, živé vzorce ODF a kresby nie. To je dôležité vo chvíli, keď archív obsahuje skutočné OpenDocument súbory riadiace sa štandardom OASIS ODF 1.3, kde akákoľvek vizuálne verná konverzia vyžaduje viac, než na čo bola táto cesta importu navrhnutá. Audítorský priechod je to, čo vám prezradí existenciu týchto súborov predtým, ako ich dávka ticho znehodnotí.
SaveXLSWorkbookAsXLSX je dátový most, nie dizajnový most
Rozhranie BIFF nedokáže zapisovať OOXML priamo, takže prechod z .xls do .xlsx prebieha cez funkciu SaveXLSWorkbookAsXLSX v jednotke lxXlsxExport. Vernosť tohto prepojenia je dôležité popísať na rovinu, pretože názov naznačuje viac, než reálne robí. Kopíruje hodnoty, vzorce, číselné formáty, farby výplne, základné atribúty písma, šírky stĺpcov a nastavenia zobrazenia, ako sú mriežky. Nekopíruje však orámovania, zlúčené rozsahy, komentáre, grafy ani podmienené formáty. Pre normalizáciu na úrovni dát, kde výsledok analyzujú nadväzujúce systémy a nikto sa nepozerá na formátovanie, je to presne dosť a nestratí sa nič, čo by niekto potreboval. Pre formátovaný report určený pre manažment to však nestačí, a práve tu majú svoje opodstatnenie audítorské počítadlá: súbor, ktorý audit označil ako súbor obsahujúci grafy a podmienené formáty, by mal smerovať do frontu na manuálne spracovanie, a nie cez most, ktorý oboje bez slova zahodí.
var
Legacy: IXLSWorkbook; // interface reference: do not 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 sheet XML into the zip
if Modern.Open(FileName) = 1 then
Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
finally
Modern.Free;
end;
end;
end;Slučka vyššie tiež ukazuje nástroj na zvýšenie priepustnosti na strane OOXML. Nastavenie StreamingWrite na hodnotu true zapisuje XML hárku priamo do výstupného balíka namiesto toho, aby ho ukladalo ako jeden obrovský reťazec v pamäti. To predstavuje rozdiel medzi hladkým priebehom a pádom z dôvodu nedostatku pamäte, keď súbory dosiahnu stovky tisíc riadkov. Veľkosti a správanie pamäte pre tento režim sú podrobne opísané v našom článku o streamovanom zápise pre serverové dávkové úlohy. Ešte jedna vlastnosť je dôležitá pre dávkové spracovanie, ktoré chce využiť každé jadro: ani jedno rozhranie nie je vláknovo bezpečné, avšak nezdieľajú ani globálny stav, takže podporovaný vzor pre paralelnú konverziu je jedna inštancia zošita na jedno pracovné vlákno bez vzájomného zamykania.
Súbory chránené heslom a čo s nimi robiť
Uzamknuté súbory v archíve sa rozdeľujú podľa formátu a toto rozdelenie určuje ich ďalší postup. Staršie šifrovanie .xls, či už ide o RC4, RC4 cez CryptoAPI alebo staré XOR maskovanie, je čitateľné: odovzdajte heslo metóde Open a súbor sa skonvertuje ako každý iný. Šifrované balíky .xlsx sú iný prípad. HotXLS ich rozpozná pomocou CanReadEncrypted, ale nedokáže ich dešifrovať, takže jediným čestným riešením je nasmerovať ich do frontu, kde človek každý súbor otvorí a znova uloží v Exceli predtým, ako sa vráti do spracovania. S touto asymetriou sa oplatí počítať hneď na začiatku, pretože šifrované súbory XLSX sú často tie, na ktorých záznamoch niekomu skutočne záleží.
Uzavretie cyklu pomocou overenia
Tretia fáza je tá, ktorá sa často preskakuje, pričom jej vynechanie robí z hromadnej konverzie riziko. Žiadna ukladacia cesta v HotXLS nevyhodnocuje vzorce. Excel prepočítava hodnoty pri otvorení súboru, takže konverzia z XLSX do XLSX zostáva správna, no cieľový formát CSV prijme text vzorca doslovne, pokiaľ spracovanie najprv nespustí metódu Calculate na bunkách a nezapíše výsledky späť. Vedieť to vopred predstavuje rozdiel medzi CSV súborom plným čísel a CSV súborom plným reťazcov =SUM(...), ktoré si nikto nevšimne, až kým na nich nezlyhá následný import.
Samotné overenie je dostatočne lacné na to, aby neexistovala výhovorka na jeho vynechanie. Znova otvorte každý skonvertovaný súbor pomocou rovnakej knižnice, znova spustite audítorské počítadlá a porovnajte ich s číslami pred konverziou, ktoré už zaznamenal inventarizačný priechod. Pokles počtu hárkov, počet grafov, ktorý klesol na nulu, hoci zdroj mal tri, či počet buniek, ktorý prudko klesol: každá táto tichá strata sa zachytí za cenu jedného dodatočného otvorenia súboru. K tomu pridajte námatkovú vizuálnu kontrolu vzorky v Exceli alebo LibreOffice a táto kombinácia odhalí drvivú väčšinu poškodení pri konverzii ešte pred odoslaním. To je hlavný dôvod, prečo inventarizačná fáza zásobuje fázu overovania. Bez počiatočných čísiel tie konečné nič nedokazujú.
Pracovné prostredie zamerané na audit mení riskantnú hromadnú konverziu na merateľný proces s karanténnou vetvou pre súbory, ktoré nemôžu prejsť čisto. Všetky tu zobrazené testovacie, počítacie a konverzné volania sú súčasťou knižnice HotXLS Component, ktorá ich spúšťa natívne v rámci procesu bez automatizácie Excelu.