Technisch artikel

Excel 2.0 tot 4.0-bestanden lezen in Delphi met HotXLS

Een workbook opgeslagen door Excel 4.0 in 1992 begint met de bytes 09 04 06 00, terwijl elke .xls vanaf Excel 5.0 begint met de compound-file-signatuur D0 CF 11 E0. Een reader die op die signatuur controleert weigert het oudere bestand voordat er ook maar één cel is gelezen, en een reader die de controle overslaat en BIFF8 aanneemt parst het eerste record als iets dat het niet is. Allebei de falen komen uit dezelfde aanname: dat elke .xls een container heeft. Excel 2.0-, 3.0- en 4.0-bestanden hebben er geen, dus de recordstream zelf moet zeggen welke generatie hem schreef, en de celrecords moeten worden gedecodeerd met de indeling van die generatie

Deze bestanden duiken nog steeds op, en dat is de enige reden waarom dit er allemaal toe doet. Technische archieven, records-beheeropslagen, laboratoriumexports van instrumenten waarvan de besturingssoftware uit de vroege jaren negentig stamt, en langlopende boekhoudsystemen lieten allemaal BIFF2 tot BIFF4-werkmappen na. Huidige Excel-versies blokkeren verschillende van deze formaten by default via hun File Block-instellingen, dus de dataset zit vaak in een map die niemand met het gereedschap op zijn bureau kan openen

Hoe een pre-OLE-werkmap zich aankondigt

HotXLS herkent een pre-OLE-werkmap aan zijn eerste record, binnen dezelfde TXLSWorkbook.Open-aanroep die elke andere .xls laadt. Elke .xls vanaf Excel 5.0 is een OLE2-compound-bestand, een klein bestandssysteem binnen een bestand, met de workbook in een stream genaamd Workbook of Book; het parsen ervan begint met het parsen van die container, zoals beschreven in het compound file binary formaat in Pascal

BIFF2 tot en met BIFF4 slaan dat allemaal over. Het bestand begint direct met een BOF-record, en het recordnummer van die BOF codeert de generatie: $0009 voor BIFF2, $0209 voor BIFF3 en $0409 voor BIFF4. Het versiewoord in de body, waar latere formaten op leunen, wordt door deze generaties genegeerd, dus het record-id is het enige betrouwbare signaal. Voordat hij zich aan het ruwe pad commit, controleert de HotXLS-streamlaag nog twee dingen: de BOF-body moet vier tot zes bytes lang zijn, en het substream-typewoord moet $0010 zijn voor een worksheet, $0020 voor een chart of $0040 voor een macro sheet. Al het andere laat Open een errorcode teruggeven

Die controles tellen zwaarder dan ze lijken. Twee bytes $09 $00 op offset nul zijn niet zeldzaam in willekeurige binaire data, en een quarantainemap vol ongeïdentificeerde .xls-bestanden bevat doorgaans hernoemde tekstexports, afgekapte kopieën en af en toe een bestand van een heel ander programma. Drie onafhankelijke velden die het allebei met elkaar eens zijn, zijn een veel sterker signaal dan één

Diagram van hoe HotXLS pre-OLE Excel-bestanden detecteert vanuit Delphi: moderne OLE2-containerwerkboeken aan de ene kant, rauwe BIFF2 tot BIFF4-recordstreams aan de andere, en de BOF-recordvelden gecontroleerd voordat het legacy-pad wordt vastgelegd
Legacy-bestanden dragen geen storage wrapper, dus het voorafgaande BOF-record bewijst zelf de generatie, de lichaamslengte en het substream-type voordat er ook maar één cel wordt gelezen

Drie generaties, drie recordindelingen

De celrecords zijn waar de generaties het meest zichtbaar uiteenlopen. BIFF2 bezet een aaneengesloten blok lage recordnummers, $0001 tot $0005, voor lege, integer-, getal-, label- en boolean-of-error-cellen. Elk van die bodies draagt een drie-byte attribuutveld waar latere versies een twee-byte uitgebreide formaatindex zetten, dus elke waarde zit één byte dieper in het record. Het BIFF2 INTEGER-record is een eigenaardigheid op zich: een unsigned 16-bits waarde zonder breukdeel, een recordtype dat BIFF3 liet vallen ten gunste van RK

BIFF3 en BIFF4 verlaten de lage nummers en hergebruiken de BIFF5-record-ids en -indelingen: $0201, $0203, $0204, $0205 en $027E, elk met een twee-byte XF-index. Dat hergebruik veroorzaakt een specifieke en makkelijk verkeerd gediagnosticeerde fout. Een BIFF3- of BIFF4-LABEL-record is structureel identiek aan zijn BIFF5-tegenhanger: rij, kolom, formaatindex, dan een twee-byte tekenaantal op offset 6. Pas die indeling toe op een BIFF2-label en het aantal wordt samengesteld uit de laatste attribuutbyte en de echte éénbyte-lengte, dus een label van vijf tekens beweert minstens 1.280 tekens te zijn en de reader loopt voorbij het einde van het record. Andersom, BIFF2-indeling op een BIFF3-record, wordt de lengte gelezen uit de hoge byte van het aantal, die voor elke korte string nul is. In geen van beide richtingen gooit iets een exception. De workbook komt terug met lege labels of plausibele troep, en HotXLS vermijdt allebei door te dispatchen op het record-id samen met de generatie uit de BOF

Het BIFF2-attribuutveld verbergt nog een valkuil. Zijn eerste byte houdt de formaatindex vast in de lage zes bits, en de waarde $3F betekent "de echte index is te groot voor zes bits, kijk naar het voorafgaande IXFE-record". HotXLS onthoudt de meest recente IXFE en lost de escape op voordat hij de cel zijn stijl toekent, dus een cel waarvan de formaatindex meer dan zes bits nodig heeft krijgt toch zijn eigen opmaak

Vergelijking van BIFF2- en BIFF3/BIFF4-celrecordlay-outs gelezen door HotXLS in Delphi, met per generatie recordnummers, het LABEL-veldlay-outverschil, de verkeerd-lees-faalmodus, en parallelle formule- en stringrecordnummering
BIFF2-lichamen dragen een drie-byte attribuutblok terwijl BIFF3 en BIFF4 de BIFF5-nummers hergebruiken met een twee-byte XF-index, en de layouts mengen verschuift stilletjes alles na het record

Tekst in alle drie de generaties is een kale bytestring, nergens Unicode. De encoding komt uit het CODEPAGE-record van het bestand, dat in deze bestanden binnen de ene worksheetstream zit omdat er geen workbook-level stream is om hem te huisvesten. HotXLS decodeert labels en stringresultaten via die codepagina en valt terug op Windows-1252 wanneer het record ontbreekt, wat de westerse default dekt waarin het merendeel van deze bestanden werd geschreven. Een Japanse of Centraal-Europese werkmap zonder CODEPAGE-record decodeert dus als Latijnse tekst, en de enige fix is weten waar het bestand vandaan kwam

Eén openen vanuit Delphi

De detectie gebeurt binnen Open, dus de aanroepende code is hetzelfde als voor elke andere workbook. De lus hieronder loopt de gebruikte range langs en print elke cel dubbel als hij een formule bevat: de vertaalde expressie en de waarde zoals Excel ze zou tonen via de getalnotatie van de cel

uses
  SysUtils, Variants, lxHandle;

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Cell: IXLSRange;
  R, C: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    try
      if Book.Open('archive\1992-inventory.xls') <> 1 then
      begin
        Writeln('not a readable workbook - quarantine for manual review');
        Exit;
      end;
    except
      // Open gooit een exception wanneer het bestand echt een ZIP-pakket is,
      // typisch een .xlsx die iemand naar .xls heeft hernoemd
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

    Sheet := Book.Sheets[1];          // Sheets[] begint bij 1
    for R := Sheet.UsedRange.FirstRow + 1 to Sheet.UsedRange.LastRow + 1 do
      for C := Sheet.UsedRange.FirstCol + 1 to Sheet.UsedRange.LastCol + 1 do
      begin
        Cell := Sheet.Cells[R, C];
        if VarIsEmpty(Cell.Value) or VarIsNull(Cell.Value) then
          Continue;
        if Cell.HasFormula then
          Writeln(Format('R%dC%d  %s  -> %s',
            [R, C, VarToStr(Cell.Formula), Cell.FormattedText]))
        else
          Writeln(Format('R%dC%d  %s', [R, C, Cell.FormattedText]));
      end;
  finally
    Book.Free;
  end;
end;

Let op de indexrekenkunde. De UsedRange-grenzen zijn nulgebaseerd terwijl zowel de sheetcollectie als celtoegang ééngebaseerd zijn, een inconsistentie die de API houdt om compatibel te blijven met bestaande code. Vergeet de aanpassing en de lus auditeert de verkeerde rechthoek zonder iets ongewoons te melden. De bestanden die dit pad accepteert dragen één worksheet-, chart- of macro-sheet-substream, dus Sheets[1] is het enige blad dat je vindt; een Excel 4.0 bound workbook (BOF-type $0100) valt buiten die verzameling en Open geeft een errorcode terug. Goedkope pre-checks die een bestand helemaal niet laden staan behandeld in lichtgewicht werkmapinspectie

Met wachtwoord beveiligde bestanden volgen dezelfde single-stream-regel. Het FILEPASS-record verschijnt vlak na de BOF in de worksheetstream, waar HotXLS hem oppikt en de legacy-XOR-ontcijfering aanzet die deze generaties gebruikten; geef het wachtwoord door via de Open(FileName, APassWord)-overload

Wat gebeurt er met formules in een BIFF2 tot BIFF4-bestand?

HotXLS leest beide helften van een legacy-formule: het gecachte resultaat dat Excel opslaat toen hij voor het laatst berekende, en de tokenstroom, die tijdens het laden wordt vertaald naar het huidige rekenmodel. Formulerecords delen een parallelle nummering over de drie generaties, $0006, $0206 en $0406, maar de bodies verschillen precies op de plekken waar een BIFF8-parser over zou struikelen

Het gecachte resultaat begint op byte 7 in BIFF2, vanwege het attribuutveld, en op byte 6 in BIFF3 en BIFF4. Niet-numerieke resultaten gebruiken een markerwoord van $FFFF in de laatste twee bytes van die acht-byte plek, met een discriminator in de eerste byte: 0 voor een string, 1 voor een boolean, 2 voor een errorcode, 3 voor leeg. Een stringresultaat past helemaal niet in de plek, dus de tekst komt aan in het volgende record, $0007 in BIFF2 met een éénbyte-lengte of $0207 in BIFF3 en BIFF4 met een twee-byte lengte

Anatomie van het gecachte formuleresultaat in BIFF2 tot BIFF4 FORMULA-records gelezen door HotXLS vanuit Delphi: de waarde begint op byte 7 in BIFF2 en byte 6 in BIFF3 en BIFF4, met een FFFF-markerwoord voor strings, booleans, fouten en lege cellen
HotXLS houdt de waarde die Excel het laatst berekende naast de vertaalde expressie, en niet-numerieke resultaten komen aan als een markerwoord plus een discriminator

De tokenstroom is waar een naïeve implementatie onzin produceert. BIFF2 prefixt de expressie met een éénbyte-lengte op offset 16 en begint de tokens op 17; BIFF3 en BIFF4 gebruiken een twee-byte lengte en beginnen op 18. Binnen de stroom dragen functietokens een éénbyte functieindex vóór BIFF5 en een twee-byte index vanaf BIFF5. BIFF4-tokens ongewijzigd aan een BIFF8-vertaler voeren laat elke functieaanroep één byte te veel verbruiken, dus het resultaat is niet een iets verkeerde formule maar een niet-gerelateerde. De HotXLS-vertaler neemt de BIFF-versie als parameter en leest elk token met de breedtes van die generatie

Beide helften hebben roeift een praktische vraag op: welk getal gaat er in de gemigreerde workbook? De gecachte waarde is wat de gebruikers van het bestand zagen, printten en aftekenden in 1992. Een herberekening op een moderne engine rekent uit wat de formule vandaag betekent, en functiegedrag is drie decennia niet stilgestaan. Voor een archiefmigratie is de gecachte waarde het record, dus houd hem vast en draai een herberekening alleen als kruiscontrole die zegt welke cellen een menselijk oog verdienen:

// Kruiscontrole: vergelijk opgeslagen resultaten met een verse herberekening.
// Deze helper en zijn CachedText-array zijn je eigen code
procedure CompareWithRecalc(Book: TXLSWorkbook; Sheet: TXLSWorksheet);
var
  CachedText: array of array of string;
  Rows, Cols, R, C: Integer;
begin
  Rows := Sheet.UsedRange.LastRow + 1;
  Cols := Sheet.UsedRange.LastCol + 1;
  SetLength(CachedText, Rows + 1, Cols + 1);
  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula then
        CachedText[R, C] := VarToStr(Sheet.Cells[R, C].Value);

  Book.Recalculate;

  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula and
         (VarToStr(Sheet.Cells[R, C].Value) <> CachedText[R, C]) then
        Writeln(Format('R%dC%d  stored=%s  now=%s  formula=%s',
          [R, C, CachedText[R, C], VarToStr(Sheet.Cells[R, C].Value),
           VarToStr(Sheet.Cells[R, C].Formula)]));
end;

Draai dit op een kopie, of vóór de save, want Recalculate vervangt de opgeslagen resultaten in het geheugen. Een leeg rapport is de uitkomst waar je op hoopt; een kort rapport wijst meestal op volatile functies als NOW() of op verwijzingen die de oorspronkelijke auteur heeft laten bungelen

Opmaak, datums en de General-notatie-valkuil

HotXLS zet de opmaakrecords van deze generaties om in hetzelfde werkmapmodel dat een moderne .xls oplevert. FONT-records (met het aparte BIFF2 FONTCOLOR-record ingevouwen), de FORMAT-strings, en de XF-records van elke generatie, met hun vulpatronen, randstijlen en legacy-kleurindexes, worden gewone fonts, getalnotaties en celstijlen die je kunt lezen en bewerken via Font, NumberFormat en de andere range-properties

Datums plukken daar direct de vruchten van. Draagt een numerieke cel een datumgetalnotatie, dan past de reader ook de DATEMODE-vlag van de werkmap toe, dus een werkmap op het 1904-systeem landt op dezelfde TDateTime-schaal als een op het 1900-systeem, en FormattedText toont de datum zoals Excel dat deed. De regels achter de offset van 1462 dagen staan behandeld in datumseriële getallen, het 1904-systeem en getalnotaties

De valkuil is de kolom die datums bevat onder een General-notatie. Heel wat werkmappen uit de jaren negentig werden gevuld door macro's of imports die seriële getallen schreven zonder ze te formatteren, en het bestand geeft geen signaal dat 33604 een dag in januari 1992 betekent in plaats van een onderdeelnummer. Die cellen komen exact zoals opgeslagen terug, ongecorrigeerd voor het datumsysteem. Beslis per kolom, op basis van wat je over de bron weet, en converteer die kolommen zelf:

// ColumnHoldsDates is je eigen opzoeking, gebouwd op kennis van het
// bronsysteem; het legacy-bestand kan deze vraag niet voor je beantwoorden.
// Serial is een lokale Double; Cell en C komen uit de eerste listing
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // Standaard geformatteerd serial: nog geen datumsysteemcorrectie toegepast
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

De Pos('d', ...)-test is met opzet kaal en bedoeld om alleen cellen over te slaan die al een dag-token in hun notatie dragen; een strengere controle tegen je eigen lijst bekende datumnotaties is de extra regels waard als het archief groot is

De gemigreerde kopie schrijven

Een workbook geopend uit een BIFF2 tot BIFF4-bestand wordt opgeslagen via de BIFF5/BIFF8- of XLSX-writers, want er is geen reden om nieuwe bestanden in een formaat te produceren dat huidige Excel blokkeert. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) schrijft een BIFF8-kopie die de waarden, de vertaalde formules en de omgezette stijlen draagt, en elk later gereedschap in de pijplijn kan hem zonder bijzondere behandeling openen. Houd het origineel ernaast: komt er over twee jaar een vraag over een cel op, dan zijn de bytes van 1992 de enige autoriteit

Legacy-lezen, modern schrijven, herberekening en opmaak leven allemaal in één library voor Delphi en C++Builder; de formaatdekking staat opgesomd op de HotXLS Delphi spreadsheetcomponent-pagina