Bygge en arbeidsbenk for revisjon og konvertering av arbeidsbøker i Delphi med HotXLS

En massevis normaliseringsjobb for regneark er tre problemer i én frakk. Du har et arkiv med blandede formater: BIFF-æraens .xls, moderne .xlsx, et strøk med .ods fra et eller annet LibreOffice-eksperiment, og en håndfull filer ingen kan åpne fordi passordet gikk ut døren med en tidligere ansatt. Målet er å konvertere alt til XLSX og CSV. Versjonen av den jobben de fleste skriver, er en løkke som åpner hver fil og lagrer den under en ny filending, og den fungerer helt til noen spør hvilke filer som mistet diagrammene sine, mistet makroene sine, eller aldri åpnet i det hele tatt. Løkken har intet svar, fordi konvertering alene ikke fører noe register. En arbeidsbenk gjør det: den inventarfører først, konverterer så, og verifiserer til slutt, og de tre trinnene må dele informasjon for at noe av det skal være pålitelig

Å sette sammen den arbeidsbenken i Delphi eller C++Builder betyr å koble sammen fire HotXLS-egenskaper, hvorav ingen trenger Excel installert noe sted i pipelinen. Det finnes to native motorer, en BIFF8-fasade for .xls og en OOXML-fasade for .xlsx og .ods. Det finnes billige sonderingskall som leser metadata uten å parse hele filen. Det finnes revisjonstellere per ark som forteller deg hva en arbeidsbok faktisk inneholder. Og det finnes en konverteringsmatrise med en dokumentert nøyaktighetsprofil for hver rute. Arbeidet består i å vite hvor hver av disse har en skarp kant, for det har de alle sammen, og kantene er nettopp det som gjør en ren nattlig batch om til en mandagshendelse

Sonder før du laster: arknavn og krypteringsdeteksjon

Å åpne en arbeidsbok på 200 MB bare for å oppdage at den er kryptert, kaster bort minutter per fil, og multiplisert over et stort arkiv kaster det bort dager. Begge fasadene eksponerer GetSheetNames, som leser arkmetadata uten å fylle arbeidsboken. BIFF-implementasjonen skanner bare BoundSheet-postene helt fremst i strømmen; OOXML-implementasjonen leser bare workbook.xml inne i zipen. Ved siden av det oppdager CanReadEncrypted en krypteringsbeholder uten å forsøke dekryptering:

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;

To driftsmessige detaljer gjør denne løkken billig. GetSheetNames tilbakestiller eller fyller ikke arbeidsbokinstansen, så ett enkelt sonderingsobjekt kan klassifisere tusenvis av filer uten å bli gjenskapt. Og XLS-fasadens versjon av samme kall forstår også .xlsx-pakker, noe som gjør den til en praktisk enkelt sondering når filendelser ikke kan stoles på, noe de sjelden kan i et arkiv så gammelt. Triagering før lasting fortjener sin egen behandling; mekanikken bak lettvekts-inspeksjon finner du i vår artikkel om arklisting og lettvekts arbeidsbok-inspeksjon

Å telle hva en arbeidsbok egentlig inneholder

Når en fil består triageringen, avgjør revisjonspasset konverteringsruten dens. XLSX-fasaden eksponerer en teller for hver funksjonsfamilie som påvirker en nøyaktighetsbeslutning: sammenslåtte celler, diagrammer, bilder, betinget formatering, datavalidering, tabeller, hyperkoblinger og kommentarer, pluss flagg på arbeidsboknivå for makroer, beskyttelse og kildeformat. Konverteringsruten for en fil avhenger nesten utelukkende av hvilke av disse som kommer tilbake ulik null

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;

Les Cells.Count med ett forbehold i tankene. Cellelageret er glissent, så tallet teller instansierte celler, ikke det rektangulære arealet til det brukte området. Et ark med én verdi i A1 og en annen i ZZ9999 rapporterer to celler, ikke den millionen som ligger imellom. Den tilsvarende skanningen på BIFF-siden bruker UsedRange-grensene sammen med ForEachCell, og den bærer av-med-én-feilen som snubler nesten alle opp første gang: UsedRange.FirstRow og dens søsken er 0-basert, mens Cells.Item[Row, Col] er 1-basert. En gjennomgang som glemmer å legge til én på hver grense, reviderer feil rektangel og sier det aldri

To spaker kutter kostnaden ved et revisjons-bare pass over store gamle filer. Å sette _DisableGraphics til sann før en .xls åpnes, hopper helt over parsingen av OfficeArt-tegnelaget, noe som sparer reell tid på arbeidsbøker tette av former. Det er derimot strengt en skrivebeskyttet optimalisering: å lagre fra en instans åpnet på den måten ville droppe tegningene den aldri parset, så flagget hører bare hjemme på stier som aldri vil skrive filen tilbake. Når revisjonen trenger celleinnhold per celle fremfor tellinger, går ForEachCell-tilbakekallet gjennom de fylte cellene direkte og unngår Variant-overheaden per tilgang som indekserte celleegenskaper betaler for hver lesing, noe som summerer seg raskt over millioner av celler

Normaliser de inkonsekvente returkodene tidlig

HotXLS' IO-kall rapporterer feil gjennom heltallsresultater fremfor unntak, og konvensjonene er ikke ensartede på tvers av API-et. De fleste åpne- og lagringskall returnerer 1 ved suksess og -1 ved feil. GetSheetNames returnerer arkantallet, eller -1 med listen tømt. XLSX-SaveAsHTML bryter mønsteret igjen og returnerer 0 for suksess, -1 for en arkindeks utenfor gyldig område. En arbeidsbenk som tester = 1 overalt, vil stille feilklassifisere kallene som signaliserer suksess på en annen måte, og en som tester <> -1 vil svelge dem som feiler med en annen kode

Regelen som overlever kontakt med hele API-et, er smalere enn den ser ut: behandle <= 0 som feil for tellingsreturnerende kall, sjekk den dokumenterte suksessverdien for hver lagringsrutine du faktisk bruker, og plasser begge bak én liten resultatsjekkende funksjon slik at konvensjonen bor på nøyaktig ett sted. Batch-pipelines feiler langt oftere av en langsom opphopning av usjekkede returkoder enn av noen eksotisk parser-feil, og kostnaden for å gjøre dette feil kommer førti tusen filer senere, når ingen lenger husker hvilke konverteringer som faktisk lyktes

Konverteringsmatrisen og hvor hver vei mister data

De to fasadene deler konverteringsarbeidet mellom seg. TXLSXWorkbook åpner XLSX, ODS og CSV, og lagrer XLSX, ODS, CSV, HTML, RTF og AES-kryptert XLSX. TXLSWorkbook åpner og lagrer BIFF, og eksporterer HTML, RTF og CSV. Det nyttige er at hver sti kommer med en dokumentert nøyaktighetsprofil, ikke et vagt løfte om korrekthet, slik at du kan bestemme på forhånd hvilke ruter som er trygge for hvilke filer

CSV-eksport skriver UTF-8 med BOM, CRLF-linjeslutt og RFC 4180-sitering. Det den ikke gjør, er å evaluere formler: en celle som holder =SUM(...) eksporteres som den bokstavelige formelteksten, så et ark med formler blir til et ark med strenger med mindre du beregner verdiene først. HTML-eksport produserer én enkelt tabell, med colspan og rowspan som stedfortreder for sammenslåtte celler og basisstiler satt inline. RTF-eksport har en skarpere begrensning: den kan ikke strekke sammenslåtte celler over kolonner, så videreføringscellene i en sammenslåing kommer ut tomme. ODS-import er lettvektig med hensikt, ifølge bibliotekets egen dokumentasjon. Skalarverdier og bufrede formelresultater kommer gjennom; stiler, levende ODF-formeluttrykk og tegninger gjør det ikke. Det spiller en rolle i det øyeblikket arkivet inneholder ekte OpenDocument-filer styrt av OASIS ODF 1.3, hvor alt i nærheten av en visuelt tro konvertering trenger mer enn denne importstien ble bygget for å bære, og revisjonspasset er det som forteller deg at de filene finnes før batchen stille flater dem ut

SaveXLSWorkbookAsXLSX er en databro, ikke en layoutbro

BIFF-fasaden kan ikke skrive OOXML direkte, så overgangen fra .xls til .xlsx går gjennom funksjonen SaveXLSWorkbookAsXLSX i enheten lxXlsxExport. Nøyaktigheten til den broen er verdt å si rett ut, fordi navnet antyder mer enn det leverer. Den kopierer verdier, formler, tallformater, fyllfarger, kjerneskriftattributter, kolonnebredder og visningsinnstillinger som rutenettlinjer. Den kopierer ikke kantlinjer, sammenslåtte områder, kommentarer, diagrammer eller betinget formatering. For datagradsnormalisering, hvor nedstrøms systemer skal parse resultatet og ingen ser på formateringen, er det akkurat nok, og ingenting som noen trenger, går tapt. For en formatert styrerapport ment å bli lest av et menneske, er det ikke nok, og det er nettopp her revisjonstellerne fortjener plassen sin: en fil revisjonen flagget som bærende diagrammer og betinget formatering, bør rutes til en manuell kø, ikke gjennom en bro som vil droppe begge uten et ord

var
  Legacy: IXLSWorkbook;        // interface-referanse: ikke kall 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;     // strøm arkets XML inn i zipen
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

Løkken over viser også gjennomstrømningsspaken på OOXML-siden. Å sette StreamingWrite til sann strømmer arkets XML direkte inn i utdatapakken fremfor å legge den midlertidig som én kjempestreng i minnet, noe som er forskjellen mellom en behagelig kjøring og en tomt-for-minne-krasj når filer når hundretusenvis av rader. Størrelses- og minneatferd for den modusen får sin egen behandling i vår artikkel om strømmende skriving for serverbatchjobber. Enda en egenskap betyr noe for en batch som vil bruke hver kjerne: ingen av fasadene er trådsikre, men ingen av dem deler global tilstand heller, så det støttede mønsteret for parallell konvertering er én arbeidsbokinstans per arbeidertråd, uten låsing mellom dem

Passordfilene, og hva du skal gjøre med dem

Arkivets låste filer deler seg rent etter format, og delingen bestemmer hvor de går. Gammel .xls-kryptering, enten RC4, RC4 over CryptoAPI, eller den gamle XOR-fordunklingen, er leselig: send passordet til Open, og filen konverteres som enhver annen. Krypterte .xlsx-pakker er en annen historie. HotXLS oppdager dem med CanReadEncrypted, men kan ikke dekryptere dem, så det eneste ærlige trekket er å rute dem til en kø hvor et menneske åpner og lagrer hver av dem på nytt i Excel før de går tilbake inn i pipelinen. Den asymmetrien er verdt å designe for på forhånd, fordi de krypterte XLSX-filene er de som mest sannsynlig er journalene noen faktisk bryr seg om

Å lukke løkken med verifisering

Det tredje trinnet er det som blir hoppet over, og å hoppe over det er det som gjør en massekonvertering til en risiko. Ingen lagringssti i HotXLS evaluerer formler. Excel regner om når det åpner en fil, så en XLSX-til-XLSX-konvertering forblir korrekt, men et CSV-mål mottar formelteksten ordrett med mindre pipelinen først kjører Calculate på cellene og skriver resultatene tilbake. Å vite det på forhånd er forskjellen mellom en CSV full av tall og en CSV full av =SUM(...)-strenger som ingen legger merke til før en nedstrøms import kveler på dem

Selve verifiseringen er billig nok til at det ikke finnes noen unnskyldning for å utelate den. Åpne hver konvertert fil på nytt med det samme biblioteket, kjør revisjonstellerne på nytt, og sammenlign dem mot tallene fra før konverteringen som inventarpasset allerede registrerte. Et arkantall som falt, et diagramantall som gikk til null der kilden hadde tre, et celleantall som stupte: hver av dem er et stille tap fanget for kostnaden av en ny åpning. Stikkprøvekontroller et utvalg for øyet i Excel eller LibreOffice i tillegg, og kombinasjonen fanger det overveldende flertallet av konverteringsskader før det sendes ut. Dette er hele grunnen til at inventartrinnet mater verifiseringstrinnet. Uten før-tallene beviser etter-tallene ingenting

En arbeidsbenk med revisjon først gjør en risikabel massekonvertering om til en målbar prosess med en karantenebane for filene som ikke kan bestå rent. Alle sonderings-, tellings- og konverteringskallene vist her er en del av HotXLS Delphi Component, som kjører dem nativt in-process uten Excel-automatisering