Technischer Artikel

Excel-2.0- bis 4.0-Dateien in Delphi mit HotXLS lesen

Eine Arbeitsmappe, die Excel 4.0 1992 gespeichert hat, beginnt mit den Bytes 09 04 06 00, während jede .xls ab Excel 5.0 mit der Compound-File-Signatur D0 CF 11 E0 beginnt. Ein Reader, der auf diese Signatur prüft, weist die ältere Datei ab, bevor er eine einzige Zelle liest, und ein Reader, der die Prüfung überspringt und BIFF8 annimmt, parst den ersten Record als etwas, das er nicht ist. Beide Fehlschläge kommen aus derselben Annahme: dass jede .xls einen Container hat. Excel-2.0-, 3.0- und 4.0-Dateien haben keinen, der Record-Stream selbst muss also sagen, welche Generation ihn geschrieben hat, und die Zell-Records müssen mit dem Layout dieser Generation decodiert werden

Diese Dateien tauchen weiterhin auf, und das ist der einzige Grund, warum dies alles relevant ist. Technische Archive, Aufbewahrungsstellen, Labor-Exports von Messgeräten, deren Steuerungssoftware aus den frühen 1990ern stammt, und langlebige Buchhaltungssysteme haben allesamt BIFF2- bis BIFF4-Arbeitsmappen hinterlassen. Aktuelle Excel-Builds blockieren mehrere dieser Formate per Default über ihre File-Block-Einstellungen, der Datenbestand liegt also oft in einem Ordner, den niemand mit den Werkzeugen auf dem Schreibtisch öffnen kann

Wie eine vor-OLE-Arbeitsmappe sich zu erkennen gibt

HotXLS erkennt eine vor-OLE-Arbeitsmappe an ihrem ersten Record, innerhalb desselben TXLSWorkbook.Open-Aufrufs, der jede andere .xls lädt. Jede .xls ab Excel 5.0 ist eine OLE2-Compound-Datei, ein kleines Dateisystem in der Datei, mit der Arbeitsmappe in einem Stream namens Workbook oder Book; eine solche zu parsen beginnt mit dem Parsen dieses Containers, beschrieben im Artikel zum Compound-File-Binärformat in Pascal

BIFF2 bis BIFF4 überspringen all das. Die Datei beginnt direkt mit einem BOF-Record, und die Record-Nummer dieses BOF kodiert die Generation: $0009 für BIFF2, $0209 für BIFF3 und $0409 für BIFF4. Das Versionswort im Rumpf, auf das spätere Formate bauen, ignorieren diese Generationen, die Record-ID ist also das einzige vertrauenswürdige Signal. Bevor sich HotXLS auf den Roh-Pfad festlegt, prüft seine Stream-Layer zwei Dinge mehr: Der BOF-Rumpf muss vier bis sechs Bytes lang sein, und das Substream-Typ-Wort muss $0010 für ein Worksheet, $0020 für ein Chart oder $0040 für ein Makroblatt sein. Alles andere lässt Open einen Fehlercode zurückgeben

Diese Prüfungen zählen mehr, als sie aussehen. Zwei Bytes $09 $00 am Offset null sind in beliebigen Binärdaten nicht selten, und ein Quarantäne-Ordner voller identifizierungsunfähiger .xls-Dateien enthält umbenannte Text-Exports, abgeschnittene Kopien und gelegentlich eine Datei aus einem ganz anderen Programm. Drei unabhängige Felder, die alle übereinstimmen, sind ein weit stärkeres Signal als eines

Diagramm dazu, wie HotXLS vor-OLE-Excel-Dateien aus Delphi erkennt: moderne OLE2-Container-Arbeitsmappen auf der einen Seite, rohe BIFF2- bis BIFF4-Record-Streams auf der anderen, und die geprüften BOF-Record-Felder, bevor der Legacy-Pfad festgelegt wird
Legacy-Dateien tragen keinen Speicher-Wrapper, der führende BOF-Record beweist also selbst die Generation, die Rumpflänge und den Substream-Typ, bevor irgendeine Zelle gelesen wird

Drei Generationen, drei Record-Layouts

Die Zell-Records sind der Punkt, an dem sich die Generationen am sichtbarsten unterscheiden. BIFF2 belegt einen zusammenhängenden Block niedriger Record-Nummern, $0001 bis $0005, für leere, Integer-, Number-, Label- und Boolean-oder-Error-Zellen. Jeder dieser Rümpfe trägt ein Drei-Byte-Attributfeld, wo spätere Versionen einen Zwei-Byte-Extended-Format-Index setzen, jeder Wert sitzt also ein Byte weiter hinten im Record. Der BIFF2-INTEGER-Record ist eine eigene Marotte: ein vorzeichenloser 16-Bit-Wert ohne Bruchteil, ein Record-Typ, den BIFF3 zugunsten von RK fallen ließ

BIFF3 und BIFF4 geben die niedrigen Nummern auf und reutzen die BIFF5-Record-IDs und -Layouts: $0201, $0203, $0204, $0205 und $027E, jeweils mit Zwei-Byte-XF-Index. Diese Wiederverwendung verursacht einen spezifischen und leicht falsch diagnostizierten Fehlschlag. Ein BIFF3- oder BIFF4-LABEL-Record ist strukturell identisch mit seinem BIFF5-Gegenstück: Zeile, Spalte, Formatindex, dann eine Zwei-Byte-Zeichenzahl am Offset 6. Wendet man dieses Layout auf ein BIFF2-Label an, setzt sich die Zahl aus dem letzten Attribut-Byte und der echten Ein-Byte-Länge zusammen, ein Fünf-Zeichen-Label meldet also mindestens 1.280 Zeichen, und der Reader läuft über das Ende des Records hinaus. Andersherum, BIFF2-Layout auf einem BIFF3-Record, wird die Länge aus dem High-Byte der Zahl gelesen, das bei jedem kurzen String null ist. In beiden Richtungen wirft nichts. Die Arbeitsmappe kommt mit leeren Labels oder plausibel wirkendem Müll zurück, und HotXLS umgeht beides, indem es anhand der Record-ID zusammen mit der aus dem BOF genommenen Generation dispatcht

Das BIFF2-Attributfeld verbirgt eine weitere Falle. Sein erstes Byte hält den Formatindex in den unteren sechs Bits, und der Wert $3F bedeutet "der echte Index ist zu groß für sechs Bits, schau in den vorausgehenden IXFE-Record". HotXLS verfolgt den letzten IXFE und löst den Escape auf, bevor die Zelle ihren Stil bekommt, eine Zelle, deren Formatindex mehr als sechs Bits braucht, bekommt also ihre eigene Formatierung

Vergleich der Zellrecord-Layouts von BIFF2 und BIFF3/BIFF4, gelesen von HotXLS in Delphi: Record-Nummern je Generation, der LABEL-Feld-Layout-Unterschied, der Fehllesen-Modus und die parallele Nummerierung von Formel- und String-Records
BIFF2-Rümpfe tragen einen Drei-Byte-Attributblock, während BIFF3 und BIFF4 die BIFF5-Nummern mit Zwei-Byte-XF-Index wiederverwenden, und vermischte Layouts verreißen alles nach dem Record still

Text ist in allen drei Generationen ein schlichter Byte-String, nirgends Unicode. Die Kodierung kommt aus dem CODEPAGE-Record der Datei, der in diesen Dateien innerhalb des einen Worksheet-Streams sitzt, weil es keinen Workbook-Level-Stream gibt, der ihn halten könnte. HotXLS decodiert Labels und String-Ergebnisse durch diese Codepage und fällt zurück auf Windows-1252, wenn der Record fehlt, was den westlichen Default abdeckt, mit dem die meisten dieser Dateien geschrieben wurden. Eine japanische oder mitteleuropäische Arbeitsmappe ohne CODEPAGE-Record decodiert also als lateinischer Text, und der einzige Fix ist zu wissen, wo die Datei herkommt

Eine davon aus Delphi öffnen

Die Erkennung passiert innerhalb von Open, der aufrufende Code ist also derselbe wie für jede Arbeitsmappe. Die Schleife unten läuft über den Used Range und druckt jede Zelle doppelt, wenn sie eine Formel hält: den übersetzten Ausdruck und den Wert, wie Excel ihn über das Zahlenformat der Zelle anzeigen würde

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 wirft, wenn die Datei wirklich ein ZIP-Paket ist,
      // typischerweise eine .xlsx, die jemand zu .xls umubenannt hat
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

    Sheet := Book.Sheets[1];          // Sheets[] ist 1-basiert
    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;

Auf die Index-Arithmetik achten. UsedRange-Grenzen sind nullbasiert, während sowohl die Sheet-Collection als auch der Zellzugriff einbasiert sind, eine Inkonsistenz, die die API aus Kompatibilität mit bestehendem Code beibehält. Vergisst man die Anpassung, auditiert die Schleife das falsche Rechteck, ohne irgendetwas Ungewöhnliches zu melden. Die Dateien, die dieser Pfad akzeptiert, tragen einen einzigen Worksheet-, Chart- oder Makroblatt-Substream, Sheets[1] ist also das einzige Blatt, das Sie finden; eine gebundene Excel-4.0-Arbeitsmappe (BOF-Typ $0100) fällt aus dieser Menge heraus, und Open gibt einen Fehlercode zurück. Günstige Vorprüfungen, die das Laden einer Datei ganz vermeiden, behandelt der Artikel zur leichtgewichtigen Arbeitsmappen-Inspektion

Passwortgeschützte Dateien folgen derselben Einzelstream-Regel. Der FILEPASS-Record erscheint direkt nach dem BOF im Worksheet-Stream, wo ihn HotXLS aufnimmt und die Legacy-XOR-Deobfuscation einschaltet, die diese Generationen nutzten; das Passwort geht über den Open(FileName, APassWord)-Overload hinein

Was mit Formeln in einer BIFF2- bis BIFF4-Datei passiert?

HotXLS liest beide Hälften einer Legacy-Formel: das gecachte Ergebnis, das Excel bei seiner letzten Berechnung gespeichert hat, und den Token-Stream, der beim Laden in das aktuelle Berechnungsmodell übersetzt wird. Formel-Records teilen sich eine parallele Nummerierung über die drei Generationen, $0006, $0206 und $0406, aber die Rümpfe unterscheiden sich genau an den Stellen, an denen ein BIFF8-Parser stolpert

Das gecachte Ergebnis beginnt bei Byte 7 in BIFF2, wegen des Attributfelds, und bei Byte 6 in BIFF3 und BIFF4. Nichtnumerische Ergebnisse nutzen ein Markerwort $FFFF in den letzten beiden Bytes dieses Acht-Byte-Slots, mit einem Diskriminator im ersten Byte: 0 für einen String, 1 für einen Boolean, 2 für einen Fehlercode, 3 für leer. Ein String-Ergebnis passt gar nicht in den Slot, der Text kommt also im nächsten Record an, $0007 in BIFF2 mit Ein-Byte-Länge oder $0207 in BIFF3 und BIFF4 mit Zwei-Byte-Länge

Anatomie des zwischengespeicherten Formelergebnisses in BIFF2- bis BIFF4-FORMULA-Records, gelesen von HotXLS aus Delphi: der Wert beginnt bei Byte 7 in BIFF2 und Byte 6 in BIFF3 und BIFF4, mit einem FFFF-Markerwort für Strings, Booleans, Fehler und Leere
HotXLS liefert den Wert zurück, den Excel zuletzt berechnet hat, statt Ausdruckstokens zu übersetzen, und nichtnumerische Ergebnisse kommen als Marker-Wort plus Diskriminator an

Der Token-Stream ist der Ort, an dem eine naive Implementierung Unsinn produziert. BIFF2 präfixiert den Ausdruck mit einer Ein-Byte-Länge am Offset 16 und startet die Tokens bei 17; BIFF3 und BIFF4 nutzen eine Zwei-Byte-Länge und starten bei 18. Im Stream tragen Funktions-Tokens vor BIFF5 einen Ein-Byte-Funktionsindex und ab BIFF5 einen Zwei-Byte-Index. BIFF4-Tokens unverändert durch einen BIFF8-Übersetzer zu füttern lässt jeden Funktionsaufruf ein Byte zu viel konsumieren, das Ergebnis ist also nicht eine leicht falsche Formel, sondern eine ganz andere. Der HotXLS-Übersetzer nimmt die BIFF-Version als Parameter und liest jeden Token mit den Breiten dieser Generation

Beide Hälften zu haben wirft eine praktische Frage auf: Welche Zahl wandert in die migrierte Arbeitsmappe? Der gecachte Wert ist das, was die Nutzer der Datei sahen, 1992 gedruckt und abgezeichnet. Eine Neuberechnung auf moderner Engine berechnet, was die Formel heute bedeutet, und das Funktionsverhalten ist drei Jahrzehnte nicht stehengeblieben. Für eine Archivmigration ist der gecachte Wert der Record, ihn also behalten und eine Neuberechnung nur als Gegenprobe fahren, die verrät, welche Zellen einen menschlichen Blick verdienen:

// Gegenprobe: gespeicherte Ergebnisse mit einer frischen Neuberechnung vergleichen.
// Dieser Helfer und sein CachedText-Array sind Ihr eigener 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;

Das auf einer Kopie fahren, oder vor dem Speichern, denn Recalculate ersetzt die gespeicherten Ergebnisse im Speicher. Ein leerer Report ist das gewünschte Ergebnis; ein kurzer deutet meist auf volatile Funktionen wie NOW() oder auf Referenzen, die der ursprüngliche Autor hängen ließ

Formatierung, Daten und die General-Format-Falle

HotXLS überführt die Formatierungs-Records dieser Generationen in dasselbe Arbeitsmappen-Modell, das eine moderne .xls erzeugt. FONT-Records (mit dem separaten BIFF2-FONTCOLOR-Record eingefaltet), die FORMAT-Strings und die XF-Records jeder Generation, mit ihren Füllmustern, Rahmenstilen und Legacy-Farbindexen, werden zu gewöhnlichen Fonts, Zahlenformaten und Zellstilen, die sich über Font, NumberFormat und die anderen Range-Eigenschaften lesen und bearbeiten lassen

Daten profitieren direkt. Trägt eine numerische Zelle ein Datums-Zahlenformat, wendet der Reader auch das DATEMODE-Flag der Arbeitsmappe an, eine 1904er-Arbeitsmappe landet also auf derselben TDateTime-Skala wie eine 1900er, und FormattedText zeigt das Datum so, wie Excel es zeigte. Die Regeln hinter dem 1462-Tage-Offset behandelt der Artikel zu Datumsseriennummern, das 1904-System und Zahlenformate

Die Falle ist die Spalte, die Daten unter einem General-Format hält. Viele 90er-Arbeitsmappen wurden von Makros oder Imports gefüllt, die Seriennummern schrieben, ohne sich um Formatierung zu scheren, und die Datei gibt kein Signal, dass 33604 einen Tag im Januar 1992 bedeutet statt eine Teilenummer. Diese Zellen kommen exakt wie gespeichert zurück, unkorrigiert für das Datumssystem. Pro Spalte entscheiden, aus dem, was Sie über die Quelle wissen, und diese Spalten selbst konvertieren:

// ColumnHoldsDates ist Ihr eigener Nachschlag, gebaut aus Wissen über das
// Quellsystem; die Legacy-Datei kann diese Frage für Sie nicht beantworten.
// Serial ist ein lokales Double; Cell und C kommen aus dem ersten Listing
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // General-formatierte Seriennummer: noch keine Datumssystem-Korrektur angewendet
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

Der Pos('d', ...)-Test ist absichtlich plump und soll nur Zellen überspringen, die bereits ein Tag-Token in ihrem Format tragen; eine strengere Prüfung gegen die eigene Liste bekannter Datenformate ist die Extra-Zeilen wert, wenn das Archiv groß ist

Die migrierte Kopie schreiben

Eine Arbeitsmappe, die aus einer BIFF2- bis BIFF4-Datei geöffnet wurde, wird über die BIFF5/BIFF8- oder XLSX-Writer gespeichert, denn es gibt keinen Grund, neue Dateien in einem Format zu erzeugen, das aktuelles Excel blockiert. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) schreibt eine BIFF8-Kopie, die die Werte, die übersetzten Formeln und die konvertierten Stile trägt, und jedes spätere Werkzeug in der Pipeline öffnet sie ohne Sonderbehandlung. Das Original daneben behalten: Taucht in zwei Jahren eine Frage zu einer Zelle auf, sind die 1992er Bytes die einzige Autorität

Legacy-Lesen, modernes Schreiben, Neuberechnung und Formatierung leben alle in einer Bibliothek für Delphi und C++Builder; die Formatabdeckung listet die HotXLS-Delphi-Tabellenkalkulationskomponentenseite auf