Articolo tecnico

Leggere file Excel da 2.0 a 4.0 in Delphi con HotXLS

Una cartella di lavoro salvata da Excel 4.0 nel 1992 comincia con i byte 09 04 06 00, mentre ogni .xls da Excel 5.0 in poi comincia con la firma compound-file D0 CF 11 E0. Un reader che controlla quella firma rifiuta il file più vecchio prima di leggere una singola cella, e un reader che salta il controllo e assume BIFF8 parsa il primo record come qualcosa che non è. Entrambi i fallimenti vengono dalla stessa supposizione: che ogni .xls abbia un contenitore. I file Excel 2.0, 3.0 e 4.0 non ce l'hanno, quindi deve essere lo stream di record stesso a dire quale generazione l'ha scritto, e i record di cella devono essere decodificati con il layout di quella generazione

Questi file ancora si presentano, ed è l'unica ragione per cui tutto questo conta. Archivi di ingegneria, store di conservazione dei registri, esportazioni di laboratorio da strumenti il cui software di controllo risale ai primi anni Novanta, e sistemi contabili di lunga durata hanno tutti lasciato dietro cartelle di lavoro BIFF2 a BIFF4. Le build attuali di Excel bloccano diversi di questi formati per default tramite le loro impostazioni File Block, quindi il set di dati spesso siede in una cartella che nessuno può aprire con gli strumenti sulla propria scrivania

Come si annuncia una cartella di lavoro pre-OLE

HotXLS riconosce una cartella di lavoro pre-OLE dal suo primo record, dentro la stessa chiamata TXLSWorkbook.Open che carica ogni altro .xls. Ogni .xls da Excel 5.0 in poi è un file composto OLE2, un piccolo file system dentro un file, con la cartella di lavoro che vive in uno stream nominato Workbook o Book; parsarne uno comincia col parsare quel contenitore, come descritto in il formato binario compound file in Pascal

BIFF2 fino a BIFF4 saltano tutto questo. Il file comincia direttamente con un record BOF, e il numero di record di quel BOF codifica la generazione: $0009 per BIFF2, $0209 per BIFF3 e $0409 per BIFF4. La word di versione dentro il body, su cui contano i formati successivi, viene ignorata da queste generazioni, quindi l'id del record è l'unico segnale fidabile. Prima di impegnarsi sul percorso grezzo, il livello stream di HotXLS controlla altre due cose: il body del BOF deve essere lungo da quattro a sei byte, e la word del tipo di substream deve essere $0010 per un foglio di lavoro, $0020 per un grafico o $0040 per un foglio macro. Qualunque altra cosa fa restituire a Open un codice di errore

Quei controlli contano più di quanto sembrino. Due byte di $09 $00 all'offset zero non sono rari in dati binari arbitrari, e una cartella di quarantena di .xls non identificati tende a contenere esportazioni di testo rinominati, copie troncate e il file occasionale di un programma del tutto diverso. Tre campi indipendenti tutti d'accordo sono un segnale molto più forte di uno

Diagramma di come HotXLS rileva i file Excel pre-OLE da Delphi: le cartelle di lavoro con contenitore OLE2 moderne da un lato, gli stream di record BIFF2 a BIFF4 grezzi dall'altro, e i campi del record BOF controllati prima che il percorso legacy venga impegnato
I file legacy non portano alcun wrapper di archiviazione, quindi il record BOF in testa dimostra da solo la generazione, la lunghezza del body e il tipo di substream prima che qualsiasi cella venga letta

Tre generazioni, tre layout di record

I record di cella sono dove le generazioni divergono più visibilmente. BIFF2 occupa un blocco contiguo di numeri di record bassi, da $0001 a $0005, per le celle vuote, intere, numeriche, etichetta e booleano-o-errore. Ognuno di quei body porta un campo attributo di tre byte dove le versioni successive mettono un indice di formato esteso a due byte, quindi ogni valore siede un byte più in fondo nel record. Il record INTEGER di BIFF2 è una sua stranezza: un valore a 16 bit senza segno senza parte frazionaria, un tipo di record che BIFF3 ha abbandonato a favore di RK

BIFF3 e BIFF4 abbandonano i numeri bassi e riusano gli id e i layout di record BIFF5: $0201, $0203, $0204, $0205 e $027E, ciascuno con un indice XF a due byte. Quel riuso causa un fallimento specifico e facile da diagnosticare male. Un record LABEL BIFF3 o BIFF4 è strutturalmente identico alla sua controparte BIFF5: riga, colonna, indice di formato, poi un conteggio di caratteri a due byte all'offset 6. Applicate quel layout a un'etichetta BIFF2 e il conteggio viene assemblato dall'ultimo byte di attributo e dalla vera lunghezza a un byte, così un'etichetta di cinque caratteri rivendica almeno 1.280 caratteri e il reader corre oltre la fine del record. Fate l'opposto, layout BIFF2 su un record BIFF3, e la lunghezza viene letta dal byte alto del conteggio, che è zero per qualunque stringa corta. Niente lancia in nessuna direzione. La cartella di lavoro torna con etichette vuote o spazzatura plausibile, e HotXLS evita entrambe dispatchando sull'id del record insieme alla generazione presa dal BOF

Il campo attributo di BIFF2 nasconde un'altra trappola. Il suo primo byte tiene l'indice di formato nei sei bit bassi, e il valore $3F significa "l'indice reale è troppo grande per sei bit, guardate il record IXFE precedente". HotXLS tiene traccia dell'IXFE più recente e risolve l'escape prima di assegnare alla cella il suo stile, così una cella il cui indice di formato ha bisogno di più di sei bit riceve comunque la sua formattazione

Confronto dei layout dei record di cella BIFF2 e BIFF3/BIFF4 letti da HotXLS in Delphi, che mostra i numeri di record per generazione, la differenza di layout del campo LABEL, la modalità di fallimento da lettura errata, e la numerazione parallela dei record formula e stringa
I body BIFF2 portano un blocco attributo di tre byte mentre BIFF3 e BIFF4 riusano i numeri BIFF5 con un indice XF a due byte, e mischiare i layout disallinea silenziosamente tutto ciò che sta dopo il record

Il testo in tutte e tre le generazioni è una stringa di byte pura senza Unicode da nessuna parte. La codifica viene dal record CODEPAGE del file, che in questi file siede dentro l'unico stream del foglio di lavoro perché non c'è uno stream a livello cartella che lo tenga. HotXLS decodifica etichette e risultati stringa attraverso quella code page e ripiega su Windows-1252 quando il record è assente, il che copre il default occidentale con cui la maggior parte di questi file è stata scritta. Una cartella di lavoro giapponese o dell'Europa centrale priva di record CODEPAGE si decodificherà quindi come testo latino, e l'unica correzione è sapere da dove viene il file

Aprirne uno da Delphi

Il rilevamento avviene dentro Open, quindi il codice chiamante è lo stesso di qualunque cartella di lavoro. Il ciclo qui sotto percorre l'used range e stampa ogni cella due volte quando contiene una formula: l'espressione tradotta e il valore così Excel lo mostrerebbe attraverso il formato numerico della cella

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 solleva quando il file è davvero un pacchetto ZIP,
      // tipicamente un .xlsx che qualcuno ha rinominato in .xls
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

    Sheet := Book.Sheets[1];          // Sheets[] è a base uno
    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;

Guardate l'aritmetica degli indici. I limiti di UsedRange sono a base zero mentre sia la collezione dei fogli sia l'accesso alle celle sono a base uno, un'incoerenza che l'API mantiene per compatibilità con il codice esistente. Dimenticate la correzione e il ciclo auditorà il rettangolo sbagliato senza riportare niente di insolito. I file che questo percorso accetta portano un singolo substream di foglio di lavoro, grafico o foglio macro, quindi Sheets[1] è l'unico foglio che troverete; una cartella di lavoro vincolata Excel 4.0 (tipo BOF $0100) cade fuori da quel set e Open restituisce un codice di errore. I pre-controlli economici che evitano del tutto di caricare un file sono coperti in ispezione leggera delle cartelle di lavoro

I file protetti da password seguono la stessa regola del singolo stream. Il record FILEPASS appare subito dopo il BOF nello stream del foglio di lavoro, dove HotXLS lo raccoglie e attiva la de-offuscazione XOR legacy che queste generazioni usavano; passate la password tramite l'overload Open(FileName, APassWord)

Cosa succede alle formule in un file BIFF2 a BIFF4?

HotXLS legge entrambe le metà di una formula legacy: il risultato in cache che Excel conservò quando calcolò l'ultima volta, e lo stream di token, che viene tradotto nel modello di calcolo corrente durante il caricamento. I record formula condividono una numerazione parallela attraverso le tre generazioni, $0006, $0206 e $0406, ma i body differiscono esattamente nei punti dove un parser BIFF8 inciamperebbe

Il risultato in cache comincia al byte 7 in BIFF2, per via del campo attributo, e al byte 6 in BIFF3 e BIFF4. I risultati non numerici usano una word marcante di $FFFF negli ultimi due byte di quello slot a otto byte, con un discriminante nel primo byte: 0 per una stringa, 1 per un booleano, 2 per un codice di errore, 3 per un vuoto. Un risultato stringa non sta nello slot per niente, quindi il testo arriva nel record successivo, $0007 in BIFF2 con una lunghezza a un byte o $0207 in BIFF3 e BIFF4 con una lunghezza a due byte

Anatomia del risultato di formula in cache nei record FORMULA BIFF2 a BIFF4 letti da HotXLS da Delphi: il valore comincia al byte 7 in BIFF2 e al byte 6 in BIFF3 e BIFF4, con una word marcante FFFF per stringhe, booleani, errori e vuoti
HotXLS conserva il valore che Excel calcolò l'ultima volta accanto all'espressione tradotta, e i risultati non numerici arrivano come word marcante più un discriminante

Lo stream di token è dove un'implementazione ingenua produce assurdità. BIFF2 prefissa l'espressione con una lunghezza a un byte all'offset 16 e comincia i token al 17; BIFF3 e BIFF4 usano una lunghezza a due byte e cominciano al 18. Dentro lo stream, i token funzione portano un indice di funzione a un byte prima di BIFF5 e un indice a due byte da BIFF5 in poi. Date i token BIFF4 a un traduttore BIFF8 invariati e ogni chiamata di funzione consuma un byte in più, quindi il risultato non è una formula leggermente sbagliata ma una senza relazione. Il traduttore di HotXLS prende la versione BIFF come parametro e legge ogni token con le larghezze di quella generazione

Avere entrambe le metà solleva una domanda pratica: quale numero va nella cartella di lavoro migrata? Il valore in cache è ciò che gli utenti del file videro, stamparono e firmarono nel 1992. Un ricalcolo su un motore moderno calcola ciò che la formula significa oggi, e il comportamento delle funzioni non è rimasto fermo per tre decenni. Per una migrazione di archivio il valore in cache è il registro, quindi conservatelo ed eseguite un ricalcolo solo come controprova che vi dice quali celle meritano un'occhiata umana:

// Controprova: confrontate i risultati conservati con un ricalcolo fresco.
// Questo helper e il suo array CachedText sono codice vostro
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;

Eseguite questo su una copia, o prima del salvataggio, dato che Recalculate sostituisce i risultati conservati in memoria. Un report vuoto è l'esito che volete; uno corto di solito punta alle funzioni volatili come NOW() o ai riferimenti che l'autore originale lasciò appesi

Formattazione, date e la trappola del formato Generale

HotXLS converte i record di formattazione di queste generazioni nello stesso modello di cartella di lavoro che un .xls moderno produce. I record FONT (con il separato record FONTCOLOR di BIFF2 ripiegato dentro), le stringhe FORMAT, e i record XF di ciascuna generazione, con i loro pattern di riempimento, stili di bordo e indici di colore legacy, diventano font ordinari, formati numerici e stili di cella che potete leggere e modificare tramite Font, NumberFormat e le altre proprietà degli intervalli

Le date beneficiano direttamente. Quando una cella numerica porta un formato numerico di data, il reader applica anche il flag DATEMODE della cartella di lavoro, così una cartella di lavoro sul sistema 1904 atterra sulla stessa scala TDateTime di una sul sistema 1900, e FormattedText mostra la data così come la mostrava Excel. Le regole dietro l'offset di 1462 giorni sono coperte in numeri seriali di data, il sistema 1904 e i formati numerici

La trappola è la colonna che tiene le date sotto un formato Generale. Tante cartelle di lavoro degli anni Novanta furono riempite da macro o da import che scrissero numeri seriali senza preoccuparsi di formattarli, e il file non dà alcun segnale che 33604 significa un giorno di gennaio 1992 anziché un numero di parte. Quelle celle tornano esattamente come conservate, senza correzione per il sistema di date. Decidete per colonna, da ciò che sapete della sorgente, e convertite quelle colonne voi stessi:

// ColumnHoldsDates è una ricerca vostra, costruita dalla conoscenza del
// sistema sorgente; il file legacy non può rispondervi questa domanda.
// Serial è un Double locale; Cell e C vengono dal primo listato
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // seriale con formato Generale: nessuna correzione di sistema di date ancora applicata
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

Il test Pos('d', ...) è deliberatamente grezzo e serve solo a saltare le celle che portano già un token giorno nel loro formato; un controllo più severo contro la vostra lista di formati data noti vale le righe in più se l'archivio è grande

Scrivere la copia migrata

Una cartella di lavoro aperta da un file BIFF2 a BIFF4 viene salvata tramite i writer BIFF5/BIFF8 o XLSX, dato che non c'è motivo di produrre nuovi file in un formato che l'Excel attuale blocca. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) scrive una copia BIFF8 che porta i valori, le formule tradotte e gli stili convertiti, e ogni strumento successivo nella pipeline può aprirlo senza trattamento speciale. Conservate l'originale accanto: se tra due anni una cella solleva una domanda, i byte del 1992 sono l'unica autorità

Lettura legacy, scrittura moderna, ricalcolo e formattazione vivono tutti in una sola libreria per Delphi e C++Builder; la copertura dei formati è elencata nella pagina del componente foglio di calcolo HotXLS Delphi