HotXLS: munkafüzet-audit és formátumkonverzió Delphiben

Egy tömeges táblázat-normalizálási feladat valójában három probléma egyetlen kabátban. Van egy vegyes formátumú archívuma: BIFF-korszakbeli .xls fájlok, modern .xlsx fájlok, néhány .ods valamilyen LibreOffice-kísérlet maradékaként, és egy maroknyi olyan fájl, amelyet senki nem tud megnyitni, mert a jelszó egy volt munkatárssal együtt kisétált. A cél az, hogy minden XLSX és CSV formátumba kerüljön. Ennek a feladatnak azt a változatát írja meg a legtöbb ember, amely egy ciklusban megnyit minden fájlt, és új kiterjesztéssel elmenti, és ez egészen addig működik, amíg valaki meg nem kérdezi, mely fájlok veszítették el a diagramjaikat, ejtették el a makróikat, vagy nem nyíltak meg egyáltalán. A ciklusnak nincs válasza, mert önmagában a konverzió nem vezet nyilvántartást. Egy munkapad viszont igen: előbb leltározik, másodszor konvertál, harmadszor pedig ellenőriz, és ennek a három szakasznak információt kell megosztania egymással ahhoz, hogy az egész megbízható legyen

Ennek a munkapadnak az összeállítása Delphiben vagy C++Builderben négy HotXLS-képesség összedrótozását jelenti, és egyikhez sem kell telepített Excel a folyamat egyetlen pontján sem. Két natív motor van: egy BIFF8-homlokzat az .xls fájlokhoz, és egy OOXML-homlokzat az .xlsx és .ods fájlokhoz. Vannak olcsó szondázó hívások, amelyek metaadatot olvasnak a teljes fájl elemzése nélkül. Vannak munkalaponkénti auditszámlálók, amelyek megmondják, mit tartalmaz valójában egy munkafüzet. És van egy konverziós mátrix, minden útvonalhoz dokumentált hűségprofillal. A munka abban áll, hogy tudjuk, hol van ezeknek éles pereme, mert mindegyiknek van, és pontosan ezek a peremek fordítanak át egy tiszta éjszakai kötegfuttatást hétfő reggeli incidensbe

Szondázzon a betöltés előtt: munkalapnevek és titkosításészlelés

Ha egy 200 MB-os munkafüzetet csak azért nyit meg, hogy kiderüljön, titkosított, fájlonként perceket pazarol, egy nagy archívumra szorozva pedig napokat. Mindkét homlokzat közzéteszi a GetSheetNames hívást, amely a munkafüzet feltöltése nélkül olvassa be a munkalapok metaadatait. A BIFF-megvalósítás csak a stream elején álló BoundSheet rekordokat pásztázza; az OOXML-megvalósítás csak a zipen belüli workbook.xml fájlt olvassa. Mellette a CanReadEncrypted úgy észleli a titkosítási konténert, hogy meg sem kísérli a visszafejtést:

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;

Két üzemeltetési részlet teszi olcsóvá ezt a ciklust. A GetSheetNames nem állítja alaphelyzetbe és nem tölti fel a munkafüzet-példányt, így egyetlen szondázó objektum több ezer fájlt osztályozhat anélkül, hogy újra kellene hozni. Az XLS-homlokzat ugyanezen hívása ráadásul az .xlsx csomagokat is érti, ami kényelmes egyetlen szondává teszi ott, ahol a fájlkiterjesztésekben nem lehet megbízni – márpedig egy ilyen régi archívumban ritkán lehet. A betöltés előtti osztályozás önálló tárgyalást érdemel; a könnyűsúlyú vizsgálat mechanikája a munkalapok listázásáról és a könnyűsúlyú munkafüzet-vizsgálatról szóló cikkünkben szerepel

Annak megszámlálása, mit tartalmaz valójában egy munkafüzet

Amint egy fájl túljutott az osztályozáson, az auditlépés dönt a konverziós útvonaláról. Az XLSX-homlokzat minden olyan funkciócsaládhoz kínál számlálót, amely befolyásol egy hűségdöntést: egyesített cellák, diagramok, képek, feltételes formátumok, adatérvényesítések, táblázatok, hiperhivatkozások és megjegyzések, ezenfelül munkafüzetszintű jelzők a makrókhoz, a védelemhez és a forrásformátumhoz. Egy fájl konverziós útvonala szinte teljes egészében attól függ, ezek közül melyik tér vissza nem nulla értékkel

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;

A Cells.Count értéket egyetlen kikötéssel olvassa. A cellatár ritka, tehát a szám a példányosított cellákat számolja, nem a használt tartomány téglalap alakú területét. Az a munkalap, amelyen egy érték van az A1 cellában és egy másik a ZZ9999 cellában, két cellát jelent, nem a köztük fekvő nagyjából egymilliót. A BIFF oldalán ugyanez a pásztázás az UsedRange határait használja a ForEachCell hívással együtt, és hordozza azt az eggyel való eltérést, amelybe elsőre szinte mindenki belebotlik: az UsedRange.FirstRow és testvérei nulla alapúak, míg a Cells.Item[Row, Col] egy alapú. Az a bejárás, amely elfelejt egyet hozzáadni minden határhoz, rossz téglalapot auditál, és soha nem szól róla

Két emelő csökkenti egy csak auditáló futtatás költségét nagy, régi fájlokon. Ha az .xls megnyitása előtt igazra állítja a _DisableGraphics értéket, teljesen kihagyja az OfficeArt rajzréteg elemzését, ami alakzatokkal sűrűn teleírt munkafüzeteknél valódi időt takarít meg. Ez azonban szigorúan csak olvasási optimalizálás: az így megnyitott példányból való mentés elejtené azokat a rajzokat, amelyeket sosem elemzett, ezért a jelző csak olyan útvonalakra való, amelyek soha nem írják vissza a fájlt. Amikor az auditnak számlálók helyett cellánkénti tartalomra van szüksége, a ForEachCell visszahívása közvetlenül járja be a feltöltött cellákat, és megkerüli azt a hozzáférésenkénti Variant többletköltséget, amelyet az indexelt cellatulajdonságok minden olvasáskor megfizetnek, és amely millió cellán át gyorsan összeadódik

Az egyenetlen visszatérési kódokat korán egységesítse

A HotXLS I/O-hívásai kivételek helyett egész visszatérési értékekkel jeleznek hibát, és a konvenciók nem egységesek az API egészében. A legtöbb megnyitó és mentő hívás sikernél 1-et, hibánál -1-et ad vissza. A GetSheetNames a munkalapok számát adja vissza, hiba esetén pedig -1-et, a listát kiürítve. Az XLSX SaveAsHTML ismét megtöri a mintát: sikernél 0-t ad vissza, tartományon kívüli munkalapindexnél -1-et. Az a munkapad, amely mindenütt a = 1 feltételt vizsgálja, csendben félreosztályozza azokat a hívásokat, amelyek másképp jelzik a sikert, az pedig, amely a <> -1 feltételt nézi, elnyeli azokat, amelyek más kóddal buknak el

Az a szabály, amely az egész API-val való találkozást túléli, szűkebb, mint amilyennek látszik: a darabszámot visszaadó hívásoknál a <= 0 értéket kezelje hibaként, minden ténylegesen használt mentő rutinnál ellenőrizze a dokumentált sikerértéket, és mindkettőt tegye egyetlen kis eredményellenőrző függvény mögé, hogy a konvenció pontosan egy helyen éljen. A kötegelt folyamatok sokkal gyakrabban buknak el az ellenőrizetlen visszatérési kódok lassú feltorlódásától, mint bármilyen egzotikus elemzőhibától, és ennek elrontása negyvenezer fájllal később kerül elő, amikor már senki nem emlékszik, mely konverziók sikerültek valójában

A konverziós mátrix, és hol veszít adatot az egyes utak

A két homlokzat megosztja egymás közt a konverziós munkát. A TXLSXWorkbook XLSX, ODS és CSV fájlokat nyit meg, és XLSX, ODS, CSV, HTML, RTF, valamint AES-titkosítású XLSX formátumban ment. A TXLSWorkbook BIFF-et nyit és ment, továbbá HTML, RTF és CSV formátumba exportál. A hasznos az, hogy minden útvonalhoz dokumentált hűségprofil tartozik, nem a helyesség homályos ígérete, így előre eldöntheti, mely útvonalak biztonságosak mely fájlokhoz

A CSV-export UTF-8 kódolást ír BOM-mal, CRLF sorvégekkel és RFC 4180 szerinti idézőjelezéssel. Amit nem tesz meg: nem értékel ki képleteket. Egy =SUM(...) tartalmú cella a képlet szó szerinti szövegeként exportálódik, tehát a képletek munkalapjából sztringek munkalapja lesz, hacsak előbb ki nem számítja az értékeket. A HTML-export egyetlen táblázatot állít elő, amelyben a colspan és a rowspan helyettesíti az egyesített cellákat, az alapstílusok pedig soron belül szerepelnek. Az RTF-exportnak élesebb a korlátja: nem tud egyesített cellákat oszlopokon átívelően megjeleníteni, így az egyesítés folytatócellái üresen jönnek ki. Az ODS-import a könyvtár saját dokumentációja szerint szándékosan könnyűsúlyú. A skalárértékek és a gyorsítótárazott képleteredmények átjönnek; a stílusok, az élő ODF-képletkifejezések és a rajzok nem. Ez abban a pillanatban számít, amint az archívum valódi, az OASIS ODF 1.3 hatálya alá tartozó OpenDocument fájlokat tartalmaz, ahol bármi, ami vizuálisan hű konverzióhoz közelít, többet kíván, mint amennyit ez az importútvonal hordozni hivatott, és éppen az auditlépés az, ami megmondja, hogy ilyen fájlok léteznek, mielőtt a köteg csendben ellaposítaná őket

A SaveXLSWorkbookAsXLSX adathíd, nem elrendezéshíd

A BIFF-homlokzat nem tud közvetlenül OOXML-t írni, ezért az .xls fájlból az .xlsx felé vezető átkelés az lxXlsxExport unit SaveXLSWorkbookAsXLSX függvényén keresztül fut. Ennek a hídnak a hűségét érdemes nyíltan kimondani, mert a neve többet sugall annál, amit tesz. Átmásolja az értékeket, a képleteket, a számformátumokat, a kitöltőszíneket, az alapvető betűtulajdonságokat, az oszlopszélességeket és a nézetbeállításokat, például a rácsvonalakat. Nem másolja át a szegélyeket, az egyesített tartományokat, a megjegyzéseket, a diagramokat és a feltételes formátumokat. Adatszintű normalizáláshoz, ahol a downstream rendszerek elemzik az eredményt, és a formázást senki nem nézi, ez pontosan elegendő, és semmi nem vész el abból, amire bárkinek szüksége van. Egy formázott, emberi olvasásra szánt vezetői jelentéshez viszont nem elég, és pontosan itt nyerik el a helyüket az auditszámlálók: az a fájl, amelyet az audit diagramokat és feltételes formátumokat hordozóként jelölt meg, kézi sorba tartozik, nem egy olyan hídra, amely mindkettőt szó nélkül elejti

var
  Legacy: IXLSWorkbook;        // interfészhivatkozás: ne hívjon Free-t
  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;     // a munkalap XML-jét a zipbe streameli
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

A fenti ciklus az OOXML oldal átbocsátóképességi emelőjét is megmutatja. Ha a StreamingWrite értéket igazra állítja, a munkalap XML-je közvetlenül a kimeneti csomagba áramlik, ahelyett hogy egyetlen óriási sztringként állomásozna a memóriában – és ez a különbség egy kényelmes futtatás és egy memóriahiányos összeomlás között, amint a fájlok több százezer soros méretet érnek el. Ennek a módnak a méretezése és memóriaviselkedése a kiszolgálói kötegfeladatokhoz való streamelt írásról szóló cikkünkben kap önálló tárgyalást. Még egy tulajdonság számít annak a kötegnek, amely minden magot ki akar használni: egyik homlokzat sem szálbiztos, de globális állapotot sem osztanak meg, így a párhuzamos konverzió támogatott mintája munkaszálanként egy munkafüzet-példány, közöttük mindenféle zárolás nélkül

A jelszavas fájlok, és mihez kezdjünk velük

Az archívum zárolt fájljai tisztán szétválnak formátum szerint, és ez a szétválás dönti el, hová kerülnek. A régi .xls titkosítás – legyen az RC4, CryptoAPI feletti RC4 vagy a régi XOR-elhomályosítás – olvasható: adja át a jelszót az Open hívásnak, és a fájl ugyanúgy konvertálódik, mint bármelyik másik. A titkosított .xlsx csomagok más történet. A HotXLS a CanReadEncrypted hívással észleli őket, de nem tudja visszafejteni, tehát az egyetlen becsületes lépés az, hogy olyan sorba irányítja őket, ahol ember nyitja meg és menti újra mindegyiket Excelben, mielőtt visszatérnének a folyamatba. Ezt az aszimmetriát érdemes előre betervezni, mert éppen a titkosított XLSX-fájlok azok, amelyek a legnagyobb eséllyel olyan iratok, amelyekre valakinek ténylegesen szüksége van

A kör bezárása ellenőrzéssel

A harmadik szakasz az, amelyet ki szoktak hagyni, és épp a kihagyása fordítja át a tömeges konverziót kockázati tétellé. A HotXLS egyetlen mentési útvonala sem értékel ki képleteket. Az Excel újraszámol, amikor megnyit egy fájlt, tehát egy XLSX–XLSX konverzió helyes marad, de egy CSV-cél szó szerint kapja meg a képlet szövegét, hacsak a folyamat előbb le nem futtatja a Calculate hívást a cellákon, és vissza nem írja az eredményeket. Ha ezt előre tudja, az a különbség egy számokkal teli CSV és egy =SUM(...) sztringekkel teli CSV között, amelyet senki nem vesz észre, amíg egy downstream import bele nem fullad

Maga az ellenőrzés elég olcsó ahhoz, hogy ne legyen mentség a kihagyására. Nyisson meg újra minden konvertált fájlt ugyanazzal a könyvtárral, futtassa le ismét az auditszámlálókat, és vesse össze őket a konverzió előtti számokkal, amelyeket a leltározási lépés már rögzített. Egy lecsökkent munkalapszám, egy nullára esett diagramszám ott, ahol a forrásban három volt, egy szakadékba zuhant cellaszám: mindegyik néma veszteség, amelyet egyetlen megnyitás áráért kap el. Tegyen emellé szemrevételezéses mintaellenőrzést Excelben vagy LibreOffice-ban, és a kettő együtt a konverziós kár túlnyomó többségét elkapja még kiszállítás előtt. Ez az egész oka annak, hogy a leltározási szakasz táplálja az ellenőrzési szakaszt. A kiindulási számok nélkül az utólagos számok semmit nem bizonyítanak

Egy audit-elsőségű munkapad a kockázatos tömeges konverziót mérhető folyamattá alakítja, karanténsávval azoknak a fájloknak, amelyek nem jutnak át tisztán. Az itt bemutatott szondázó, számláló és konverziós hívások mind a HotXLS Delphi Component részei, amely natívan, folyamaton belül futtatja őket, Excel-automatizálás nélkül