Työkirjojen auditointi- ja muuntotyöpöydän rakentaminen Delphissä HotXLS:llä

Laajamittainen laskentataulukoiden yhdenmukaistamistyö on kolme ongelmaa samassa paketissa. Arkistossa on sekalaisia muotoja: BIFF-ajan .xls-tiedostoja, nykyaikaisia .xlsx-tiedostoja, LibreOffice-kokeilusta jääneitä .ods-tiedostoja ja muutama tiedosto, jota kukaan ei voi avata, koska salasana lähti entisen työntekijän mukana. Tavoitteena on muuntaa kaikki XLSX- ja CSV-muotoon. Useimmat toteuttavat työn silmukkana, joka avaa jokaisen tiedoston ja tallentaa sen uudella tiedostopäätteellä, ja se toimii siihen asti, kun joku kysyy, mistä tiedostoista katosivat kaaviot, makrot tai jotka eivät koskaan avautuneet. Silmukalla ei ole vastausta, koska pelkkä muunnos ei pidä kirjaa. Työpöytä pitää: se inventoi ensin, muuntaa toiseksi ja tarkistaa kolmanneksi, ja kaikkien kolmen vaiheen on jaettava tietoa, jotta mikään niistä on luotettava

Tällaisen työpöydän kokoaminen Delphissä tai C++Builderissa tarkoittaa neljän HotXLS-ominaisuuden yhdistämistä, joista yksikään ei edellytä Excelin asennusta missään putken vaiheessa. Käytössä on kaksi natiivia moottoria: BIFF8-julkisivu .xls-muodolle sekä OOXML-julkisivu .xlsx- ja .ods-muodoille. Käytössä on edullisia luotauskutsuja, jotka lukevat metatietoja jäsentämättä koko tiedostoa. Käytössä on arkkikohtaisia auditointilaskureita, jotka kertovat, mitä työkirja todella sisältää. Lisäksi käytössä on muunnosmatriisi, jossa kunkin reitin säilyvyysprofiili on dokumentoitu. Työ on tietää, missä kussakin niistä on terävä reuna, sillä jokaisessa sellainen on, ja juuri nämä reunat muuttavat siistin yöajon maanantaiaamun häiriöksi

Luotaa ennen lataamista: arkkien nimet ja salauksen tunnistus

200 Mt:n työkirjan avaaminen vain salauksen havaitsemiseksi tuhlaa minuutteja tiedostoa kohti ja suuren arkiston mittakaavassa päiviä. Molemmat julkisivut tarjoavat GetSheetNames-kutsun, joka lukee arkkien metatiedot täyttämättä työkirjaa. BIFF-toteutus skannaa vain virran alussa olevat BoundSheet-tietueet; OOXML-toteutus lukee vain zip-paketin sisällä olevan workbook.xml-tiedoston. Sen rinnalla CanReadEncrypted tunnistaa salauskontin yrittämättä purkaa salausta:

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;

Kaksi toiminnallista yksityiskohtaa tekee tästä silmukasta edullisen. GetSheetNames ei nollaa eikä täytä työkirjaoliota, joten yksi luotausolio voi luokitella tuhansia tiedostoja ilman uudelleenluontia. Saman kutsun XLS-julkisivuversio ymmärtää myös .xlsx-paketteja, mikä tekee siitä kätevän yksittäisen luotaimen silloin, kun tiedostopäätteisiin ei voi luottaa, kuten näin vanhassa arkistossa harvoin voi. Lataamista edeltävä lajittelu ansaitsee oman käsittelynsä; kevyen tarkastuksen mekanismit esitetään arkkien luetteloa ja kevyttä työkirjatarkastusta käsittelevässä artikkelissamme

Sen laskeminen, mitä työkirja todella sisältää

Kun tiedosto läpäisee lajittelun, auditointivaihe päättää sen muunnosreitin. XLSX-julkisivu tarjoaa laskurin jokaiselle ominaisuusperheelle, joka vaikuttaa säilyvyyspäätökseen: yhdistetyille soluille, kaavioille, kuville, ehdollisille muotoiluille, tietojen kelpoisuustarkistuksille, taulukoille, hyperlinkeille ja kommenteille sekä työkirjatason liput makroille, suojaukselle ja lähdemuodolle. Tiedoston muunnosreitti riippuu lähes kokonaan siitä, mitkä näistä palauttavat nollasta poikkeavan arvon

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;

Lue Cells.Count yksi varaus mielessäsi. Soluvarasto on harva, joten luku laskee instanssoidut solut, ei käytetyn alueen suorakulmaista pinta-alaa. Arkki, jossa on yksi arvo solussa A1 ja toinen solussa ZZ9999, raportoi kaksi solua, ei niiden välissä olevia yli miljoonaa solua. BIFF-puolen vastaava läpikäynti käyttää UsedRange-rajoja yhdessä ForEachCell-kutsun kanssa, ja siinä on lähes jokaisen ensimmäisellä kerralla kompastuttava yhden virhe: UsedRange.FirstRow ja sen sisarominaisuudet ovat nollapohjaisia, kun taas Cells.Item[Row, Col] on yksipohjainen. Läpikäynti, joka unohtaa lisätä yhden jokaiseen rajaan, auditoi väärän suorakulmion eikä koskaan kerro siitä

Kaksi vipua pienentää vain auditointiin tarkoitetun käsittelyn kustannusta suurissa vanhoissa tiedostoissa. Kun _DisableGraphics asetetaan todeksi ennen .xls-tiedoston avaamista, OfficeArt-piirtotason jäsentäminen ohitetaan kokonaan, mikä säästää todellista aikaa muotoja tiheästi sisältävissä työkirjoissa. Se on kuitenkin ehdottomasti vain luku -optimointi: tällaisesta instanssista tallentaminen pudottaisi piirtosisällön, jota se ei koskaan jäsentänyt, joten lippu kuuluu vain poluille, jotka eivät koskaan kirjoita tiedostoa takaisin. Kun auditointi tarvitsee laskureiden sijaan solukohtaista sisältöä, ForEachCell-takaisinkutsu käy suoraan läpi täytetyt solut ja välttää indeksoitujen soluominaisuuksien jokaisella luvulla maksaman Variant-kohtaisen käsittelykulun, joka kertyy nopeasti miljoonien solujen yli

Yhdenmukaista epäjohdonmukaiset paluukoodit varhain

HotXLS:n I/O-kutsut ilmoittavat virheistä kokonaislukutuloksilla poikkeusten sijaan, eivätkä käytännöt ole yhdenmukaisia kaikkialla API:ssa. Useimmat avaus- ja tallennuskutsut palauttavat onnistumisesta 1 ja epäonnistumisesta -1. GetSheetNames palauttaa arkkien lukumäärän tai -1 ja tyhjennetyn luettelon. XLSX:n SaveAsHTML rikkoo kaavan jälleen ja palauttaa onnistumisesta 0 sekä -1, kun arkin indeksi on alueen ulkopuolella. Työpöytä, joka testaa kaikkialla = 1, luokittelee hiljaisesti väärin kutsut, jotka ilmaisevat onnistumisen muulla tavalla, ja työpöytä, joka testaa <> -1, nielee kutsut, jotka epäonnistuvat toisella koodilla

Koko API:ssa toimiva sääntö on näennäistä kapeampi: käsittele <= 0 epäonnistumisena lukumäärän palauttaville kutsuille, tarkista jokaiselle todella käyttämällesi tallennusrutiinille dokumentoitu onnistumisarvo ja piilota molemmat yhden pienen tulostarkistusfunktion taakse, jotta käytäntö sijaitsee täsmälleen yhdessä paikassa. Eräputket epäonnistuvat paljon useammin tarkistamattomien paluukoodien hitaasti kasaantuvan joukon kuin eksoottisen jäsenninvirheen vuoksi, ja tämän virheen hinta näkyy neljäkymmentätuhatta tiedostoa myöhemmin, kun kukaan ei enää muista, mitkä muunnokset todella onnistuivat

Muunnosmatriisi ja kohdat, joissa kukin reitti menettää tietoja

Kaksi julkisivua jakaa muunnostyön keskenään. TXLSXWorkbook avaa XLSX-, ODS- ja CSV-tiedostoja sekä tallentaa XLSX-, ODS-, CSV-, HTML-, RTF- ja AES-salattua XLSX-muotoa. TXLSWorkbook avaa ja tallentaa BIFF-muotoa sekä vie HTML-, RTF- ja CSV-muotoon. Hyödyllistä on, että jokaisella reitillä on dokumentoitu säilyvyysprofiili, ei epämääräistä lupausta oikeellisuudesta, joten voit päättää etukäteen, mitkä reitit ovat turvallisia millekin tiedostoille

CSV-vienti kirjoittaa UTF-8:aa BOM-merkinnällä, CRLF-rivinvaihdoilla ja RFC 4180 -lainauksella. Se ei kuitenkaan laske kaavoja: =SUM(...)-kaavan sisältävä solu viedään kirjaimellisena kaavatekstinä, joten kaavoja sisältävästä arkista tulee merkkijonoja sisältävä arkki, ellet laske arvoja ensin. HTML-vienti tuottaa yhden taulukon, jossa colspan ja rowspan edustavat yhdistettyjä soluja ja perustyylit on upotettu. RTF-viennillä on terävämpi rajoitus: se ei voi venyttää yhdistettyjä soluja sarakkeiden yli, joten yhdistämisen jatkosolut tulevat tyhjinä. ODS-tuonti on tarkoituksella kevyt kirjaston oman dokumentaation mukaan. Skalaariarvot ja välimuistissa olevat kaavatulokset tulevat läpi; tyylit, elävät ODF-kaavalausekkeet ja piirrokset eivät. Tämä merkitsee heti, kun arkistossa on todellisia OpenDocument-tiedostoja, joita hallitsee OASIS ODF 1.3 ja joiden lähelläkään visuaalisesti uskollinen muunnos tarvitsee enemmän kuin tämä tuontipolku on rakennettu kuljettamaan. Auditointivaihe kertoo, että tällaisia tiedostoja on olemassa, ennen kuin eräajo latistaa ne hiljaisesti

SaveXLSWorkbookAsXLSX on tietosilta, ei asettelusilta

BIFF-julkisivu ei voi kirjoittaa OOXML:ää suoraan, joten siirtymä .xls-muodosta .xlsx-muotoon kulkee lxXlsxExport-yksikön SaveXLSWorkbookAsXLSX-funktion kautta. Tämän sillan säilyvyys kannattaa ilmaista suoraan, sillä nimi antaa ymmärtää enemmän kuin se tekee. Se kopioi arvot, kaavat, lukumuotoilut, täyttövärit, fontin ydinuominaisuudet, sarakeleveydet ja näkymäasetukset, kuten ruudukkoviivat. Se ei kopioi reunuksia, yhdistettyjä alueita, kommentteja, kaavioita eikä ehdollisia muotoiluja. Datatason yhdenmukaistamiseen, jossa jatkojärjestelmät jäsentävät tuloksen eikä kukaan katso muotoilua, tämä on täsmälleen riittävä eikä mitään tarpeellista katoa. Muotoillulle hallituksen raportille, jonka ihmisen on tarkoitus lukea, se ei riitä, ja juuri tässä auditointilaskurit ansaitsevat paikkansa: tiedosto, jonka auditointi merkitsi kaavioita ja ehdollisia muotoiluja sisältäväksi, on ohjattava manuaaliseen jonoon, ei sillan läpi, joka pudottaa molemmat sanomatta sanaakaan

var
  Legacy: IXLSWorkbook;        // liittymäviittaus: älä kutsu 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;     // virtaa arkin XML zipiin
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

Yllä oleva silmukka näyttää myös OOXML-puolen läpimenon vivun. StreamingWrite-asetuksen asettaminen todeksi suoratoistaa laskentataulukon XML:n suoraan tulospakettiin sen sijaan, että se valmisteltaisiin yhtenä valtavana merkkijonona muistissa. Tämä ratkaisee, onko ajo mukava vai päättyykö se muistin loppumiseen, kun tiedostoissa on satojatuhansia rivejä. Tämän tilan kokoa ja muistinkäyttöä käsitellään erikseen palvelineräajojen suoratoistokirjoitusta käsittelevässä artikkelissamme. Vielä yksi ominaisuus merkitsee eräajolle, joka haluaa käyttää kaikki ytimet: kumpikaan julkisivu ei ole säieturvallinen, mutta kumpikaan ei jaa globaalia tilaa, joten tuettu malli rinnakkaiseen muunnokseen on yksi työkirjainstanssi työntekijäsäiettä kohti ilman lukitusta niiden välillä

Salasanatiedostot ja niiden käsittely

Arkiston lukitut tiedostot jakautuvat selvästi muodon mukaan, ja jako määrää niiden reitin. Vanhan .xls-muodon salaus, olipa se RC4, CryptoAPI:n päällä käytettävä RC4 tai vanha XOR-hämäys, on luettavissa: anna salasana Open-kutsulle ja tiedosto muuntuu kuten mikä tahansa muu. Salatut .xlsx-paketit ovat eri asia. HotXLS tunnistaa ne CanReadEncrypted-kutsulla mutta ei voi purkaa niiden salausta, joten ainoa rehellinen ratkaisu on ohjata ne jonoon, jossa ihminen avaa ja tallentaa jokaisen uudelleen Excelissä ennen kuin se palaa putkeen. Tämä epäsymmetria kannattaa suunnitella etukäteen, sillä salatut XLSX-tiedostot ovat todennäköisimmin juuri niitä tietueita, joista joku todella välittää

Sulje silmukka varmennuksella

Kolmas vaihe jätetään usein pois, ja sen pois jättäminen muuttaa massamuunnoksen vastuuksi. Mikään HotXLS:n tallennuspolku ei laske kaavoja. Excel laskee ne uudelleen, kun se avaa tiedoston, joten XLSX:stä XLSX:ään -muunnos säilyy oikeana, mutta CSV-kohde vastaanottaa kaavatekstin sellaisenaan, ellei putki ensin suorita Calculate-kutsua soluille ja kirjoita tuloksia takaisin. Tämän tietäminen etukäteen ratkaisee, saatko lukuja täynnä olevan CSV:n vai =SUM(...)-merkkijonoja täynnä olevan CSV:n, joita kukaan ei huomaa ennen kuin jatkojärjestelmän tuonti tukehtuu niihin

Varmennus on itsessään niin edullista, ettei sen pois jättämiseen ole tekosyytä. Avaa jokainen muunnettu tiedosto uudelleen samalla kirjastolla, suorita auditointilaskurit uudelleen ja vertaa niitä inventointivaiheen jo tallentamiin muunnosta edeltäviin lukuihin. Pudonnut arkkien lukumäärä, kaavioiden lukumäärä, joka putosi nollaan lähteen sisältäessä kolme, tai solumäärä, joka romahti: jokainen on hiljainen menetys, joka havaitaan toisen avauksen hinnalla. Tee lisäksi silmämääräinen pistokoe Excelissä tai LibreOfficessa, ja yhdistelmä havaitsee valtaosan muunnosvaurioista ennen toimitusta. Tästä syystä inventointivaihe syöttää varmennusvaihetta. Ilman ennen-lukuja jälkeen-luvut eivät todista mitään

Auditointi ensin -työpöytä muuttaa riskialttiin massamuunnoksen mitattavaksi prosessiksi, jossa puhtaasti läpäisemättömille tiedostoille on karanteenikaista. Kaikki tässä esitetyt luotaus-, laskenta- ja muunnoskutsut ovat osa HotXLS Delphi Component -komponenttia, joka suorittaa ne natiivisti samassa prosessissa ilman Excel-automatisointia