Opbygning af en arbejdsbænk til projektmapperevision og konvertering i Delphi med HotXLS

Et bulk-normaliseringsjob for regneark er tre problemer i én frakke. Du har et arkiv af blandede formater: BIFF-æraens .xls, moderne .xlsx, et strøtal af .ods fra et LibreOffice-eksperiment, og en håndfuld filer, ingen kan åbne, fordi adgangskoden forsvandt sammen med en tidligere medarbejder. Målet er at konvertere alt til XLSX og CSV. Den version af det job, de fleste skriver, er en løkke, der åbner hver fil og gemmer den under en ny filendelse, og den virker lige indtil nogen spørger, hvilke filer der mistede deres diagrammer, tabte deres makroer, eller aldrig blev åbnet overhovedet. Løkken har intet svar, fordi konvertering alene ikke fører nogen journal. Det gør en workbench: den optager beholdning først, konverterer dernæst og verificerer til sidst, og de tre stadier skal dele information, for at noget af det kan være troværdigt

At samle den workbench i Delphi eller C++Builder betyder at koble fire HotXLS-evner sammen, hvoraf ingen kræver Excel installeret noget sted i pipelinen. Der er to indfødte motorer, en BIFF8-facade til .xls og en OOXML-facade til .xlsx og .ods. Der er billige sonderingskald, der læser metadata uden at parse hele filen. Der er pr.-ark-revisionstællere, der fortæller dig, hvad en arbejdsbog rent faktisk indeholder. Og der er en konverteringsmatrix med en dokumenteret troskabsprofil for hver rute. Arbejdet består i at vide, hvor hver af dem har en skarp kant, for det har de alle sammen, og kanterne er præcis det, der forvandler en ren natlig batch til en mandag morgen-hændelse

Sonder, før du indlæser: arknavne og krypteringsdetektion

At åbne en arbejdsbog på 200 MB blot for at opdage, at den er krypteret, spilder minutter pr. fil, og ganget op over et stort arkiv spilder det dage. Begge facader eksponerer GetSheetNames, som læser arkmetadata uden at udfylde arbejdsbogen. BIFF-implementeringen scanner kun BoundSheet-posterne forrest i streamen; OOXML-implementeringen læser kun workbook.xml inde i zip'en. Ved siden af den registrerer CanReadEncrypted en krypteringscontainer uden at forsøge 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 driftsmæssige detaljer gør denne løkke billig. GetSheetNames nulstiller eller udfylder ikke arbejdsbogsinstansen, så et enkelt sonderingsobjekt kan klassificere tusindvis af filer uden at blive genskabt. Og XLS-facadens version af det samme kald forstår også .xlsx-pakker, hvilket gør den til en bekvem enkelt sondering, når filendelser ikke kan stoles på, hvilket de sjældent kan i et arkiv så gammelt. Triage før indlæsning er værd sin egen behandling; mekanikken bag den lette inspektion findes i vores artikel om arklistning og let arbejdsbogsinspektion

Optælling af, hvad en arbejdsbog rent faktisk indeholder

Når en fil har klaret triagen, afgør revisionsgennemgangen dens konverteringsrute. XLSX-facaden eksponerer en tæller for hver funktionsfamilie, der har betydning for en troskabsbeslutning: sammenlagte celler, diagrammer, billeder, betinget formatering, datavalidering, tabeller, hyperlinks og kommentarer, plus flag på arbejdsbogsniveau for makroer, beskyttelse og kildeformat. Konverteringsruten for en fil afhænger næsten udelukkende af, hvilke af disse der kommer tilbage forskellige fra nul

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;

Læs Cells.Count med ét forbehold i baghovedet. Cellelageret er sparsomt, så tallet tæller instantierede celler, ikke det rektangulære område af det brugte område. Et ark med én værdi i A1 og en anden i ZZ9999 rapporterer to celler, ikke de omkring en million, der ligger imellem dem. Det tilsvarende scan på BIFF-siden bruger UsedRange-grænserne sammen med ForEachCell, og det bærer den off-by-one-fejl, der snubler næsten alle første gang: UsedRange.FirstRow og dens søskende er 0-baserede, mens Cells.Item[Row, Col] er 1-baseret. En gennemløbning, der glemmer at lægge én til hver grænse, reviderer det forkerte rektangel og siger det aldrig

To håndtag skærer omkostningen ned på en ren revisionsgennemgang over store ældre filer. At sætte _DisableGraphics til true, før en .xls åbnes, springer OfficeArt-tegnelagsparsingen helt over, hvilket sparer reel tid på arbejdsbøger tætpakket med figurer. Det er dog udelukkende en skrivebeskyttet optimering: at gemme fra en instans, der er åbnet på den måde, ville droppe de tegninger, den aldrig parsede, så flaget hører kun hjemme på stier, der aldrig vil skrive filen tilbage. Når revisionen har brug for indhold pr. celle frem for tællinger, gennemløber ForEachCell-callbacket de udfyldte celler direkte og undgår den Variant-overhead pr. adgang, som indekserede celleegenskaber betaler ved hver læsning, hvilket løber hurtigt op over millioner af celler

Normaliser de inkonsistente returkoder tidligt

HotXLS' I/O-kald rapporterer fejl gennem heltalsresultater i stedet for undtagelser, og konventionerne er ikke ensartede på tværs af API'et. De fleste open- og save-kald returnerer 1 ved succes og -1 ved fejl. GetSheetNames returnerer arkantallet, eller -1 med listen ryddet. XLSX'ens SaveAsHTML bryder mønstret igen og returnerer 0 for succes, -1 for et arkindeks uden for området. En workbench, der tester = 1 overalt, vil stiltiende fejlklassificere de kald, der signalerer succes på en anden måde, og én, der tester <> -1, vil sluge dem, der fejler med en anden kode

Reglen, der overlever kontakten med hele API'et, er smallere, end den ser ud: behandl <= 0 som fejl for kald, der returnerer et antal, tjek den dokumenterede succesværdi for hver save-rutine, du rent faktisk bruger, og læg begge dele bag én lille resultat-tjekkende funktion, så konventionen bor præcis ét sted. Batch-pipelines fejler langt oftere på grund af en langsom ophobning af utjekkede returkoder end på grund af nogen eksotisk parser-bug, og prisen for at få det galt er fyrre tusind filer senere, når ingen længere husker, hvilke konverteringer der rent faktisk lykkedes

Konverteringsmatricen, og hvor hver vej mister data

De to facader deler konverteringsarbejdet mellem sig. TXLSXWorkbook åbner XLSX, ODS og CSV, og gemmer XLSX, ODS, CSV, HTML, RTF og AES-krypteret XLSX. TXLSWorkbook åbner og gemmer BIFF og eksporterer HTML, RTF og CSV. Det nyttige er, at hver sti kommer med en dokumenteret troskabsprofil, ikke et vagt løfte om korrekthed, så du kan beslutte på forhånd, hvilke ruter der er sikre for hvilke filer

CSV-eksport skriver UTF-8 med en BOM, CRLF-linjeskift og RFC 4180-citering. Det, den ikke gør, er at evaluere formler: en celle, der holder =SUM(...), eksporteres som den bogstavelige formeltekst, så et ark med formler bliver til et ark med strenge, medmindre du beregner værdierne først. HTML-eksport producerer en enkelt tabel, med colspan og rowspan som stedfortræder for sammenlagte celler og grundstile indlejret. RTF-eksport har en skarpere grænse: den kan ikke spænde sammenlagte celler over kolonner, så en sammenlægnings fortsættelsesceller kommer ud tomme. ODS-import er med vilje let, ifølge bibliotekets egen dokumentation. Skalarværdier og cachede formelresultater kommer med; stile, levende ODF-formeludtryk og tegninger gør ikke. Det betyder noget, i det øjeblik arkivet indeholder rigtige OpenDocument-filer styret af OASIS ODF 1.3, hvor alt, der bare kommer i nærheden af en visuelt tro konvertering, kræver mere, end denne importsti er bygget til at bære, og revisionsgennemgangen er det, der fortæller dig, at de filer findes, før batchen stiltiende flader dem ud

SaveXLSWorkbookAsXLSX er en databro, ikke en layoutbro

BIFF-facaden kan ikke skrive OOXML direkte, så overgangen fra .xls til .xlsx løber gennem funktionen SaveXLSWorkbookAsXLSX i enheden lxXlsxExport. Den bros troskab er værd at sige ligeud, fordi navnet antyder mere, end den gør. Den kopierer værdier, formler, talformater, fyldfarver, grundlæggende skrifttypeattributter, kolonnebredder og visningsindstillinger som gitterlinjer. Den kopierer ikke kanter, sammenlagte områder, kommentarer, diagrammer eller betinget formatering. Til datagrad normalisering, hvor downstream-systemer vil parse resultatet, og ingen kigger på formateringen, er det præcis nok, og intet går tabt, som nogen har brug for. Til en formateret bestyrelsesrapport, der er tænkt til at blive læst af et menneske, er det ikke nok, og det er præcis her, revisionstællerne tjener deres plads: en fil, revisionen har markeret som bærende diagrammer og betinget formatering, bør routes til en manuel kø, ikke gennem en bro, der dropper begge dele uden et ord

var
  Legacy: IXLSWorkbook;        // grænsefladereference: kald ikke 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 arkets XML ind i zip'en
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

Løkken ovenfor viser også gennemstrømningshåndtaget på OOXML-siden. At sætte StreamingWrite til true streamer regnearkets XML direkte ind i outputpakken i stedet for at iscenesætte den som én kæmpe streng i hukommelsen, hvilket er forskellen mellem en behagelig kørsel og et out-of-memory-nedbrud, når filer når hundredtusindvis af rækker. Størrelse og hukommelsesadfærd for den tilstand får deres egen behandling i vores artikel om streaming-skrivninger til server-batchjob. Én egenskab mere betyder noget for en batch, der vil bruge hver eneste kerne: ingen af facaderne er trådsikker, men ingen af dem deler heller global tilstand, så det understøttede mønster for parallel konvertering er én arbejdsbogsinstans pr. worker-tråd, uden nogen låsning mellem dem

Adgangskodefilerne, og hvad man skal gøre med dem

Arkivets låste filer deler sig rent efter format, og den deling afgør, hvor de skal hen. Ældre .xls-kryptering, hvad enten det er RC4, RC4 over CryptoAPI eller den gamle XOR-tilsløring, kan læses: giv adgangskoden til Open, og filen konverteres som enhver anden. Krypterede .xlsx-pakker er en anden historie. HotXLS registrerer dem med CanReadEncrypted, men kan ikke dekryptere dem, så det eneste ærlige træk er at route dem til en kø, hvor et menneske åbner og gemmer hver enkelt igen i Excel, før den slutter sig til pipelinen igen. Den asymmetri er værd at designe for på forhånd, for de krypterede XLSX-filer er dem, der med størst sandsynlighed er de journaler, nogen rent faktisk bekymrer sig om

Luk løkken med verifikation

Det tredje stadie er det, der bliver sprunget over, og at springe det over er det, der forvandler en bulk-konvertering til en hæftelse. Ingen gemmesti i HotXLS evaluerer formler. Excel genberegner, når det åbner en fil, så en XLSX-til-XLSX-konvertering forbliver korrekt, men et CSV-mål modtager formelteksten ordret, medmindre pipelinen først kører Calculate på cellerne og skriver resultaterne tilbage. At vide det på forhånd er forskellen på en CSV fuld af tal og en CSV fuld af =SUM(...)-strenge, som ingen bemærker, før en downstream-import gør sig fejl på dem

Selve verifikationen er billig nok til, at der ikke er nogen undskyldning for at udelade den. Genåbn hver konverteret fil med det samme bibliotek, kør revisionstællerne igen, og sammenlign dem med tallene fra før konverteringen, som beholdningsgennemgangen allerede har registreret. Et arkantal, der er faldet, et diagramantal, der gik til nul, hvor kilden havde tre, et celleantal, der styrtdykkede: hvert eneste er et stille tab fanget for prisen af én ekstra åbning. Stikprøvekontroller derudover et udvalg med øjet i Excel eller LibreOffice, og kombinationen fanger langt størstedelen af konverteringsskaden, før den sendes ud. Det er hele grunden til, at beholdningsstadiet fodrer verifikationsstadiet. Uden før-tallene beviser efter-tallene ingenting

En revision-først-workbench forvandler en risikabel bulk-konvertering til en målbar proces med en karantænebane til de filer, der ikke kan bestå rent. Alle de sonderings-, tællings- og konverteringskald, der er vist her, er en del af HotXLS Delphi Component, som kører dem indfødt in-process uden Excel-automatisering