Artículo técnico

Leer archivos de Excel 2.0 a 4.0 en Delphi con HotXLS

Un libro de trabajo guardado por Excel 4.0 en 1992 comienza con los bytes 09 04 06 00, mientras que todo .xls desde Excel 5.0 en adelante comienza con la firma de archivo compuesto D0 CF 11 E0. Un lector que verifica esa firma rechaza el archivo más viejo antes de leer una sola celda, y un lector que se salta la comprobación y asume BIFF8 parsea el primer record como algo que no es. Ambos fallos vienen de la misma suposición: que todo .xls tiene un contenedor. Los archivos de Excel 2.0, 3.0 y 4.0 no tienen ninguno, así que el stream de records mismo tiene que decir qué generación lo escribió, y los records de celda tienen que decodificarse con la disposición de esa generación

Estos archivos todavía aparecen, que es la única razón por la que todo esto importa. Archivos de ingeniería, bóvedas de retención de registros, exportaciones de laboratorio de instrumentos cuyo software de control data de comienzos de los 90, y sistemas de contabilidad de larga data dejaron todos atrás libros de trabajo BIFF2 a BIFF4. Los builds actuales de Excel bloquean varios de estos formatos por omisión mediante sus File Block settings, así que el conjunto de datos suele estar en una carpeta que nadie puede abrir con las herramientas que tiene a mano

Cómo se anuncia un libro de trabajo previo a OLE

HotXLS reconoce un libro de trabajo previo a OLE desde su primer record, dentro de la misma llamada TXLSWorkbook.Open que carga todo otro .xls. Todo .xls desde Excel 5.0 en adelante es un archivo compuesto OLE2, un pequeño sistema de archivos dentro de un archivo, con el libro de trabajo viviendo en un stream llamado Workbook o Book; parsear uno empieza por parsear ese contenedor, como se describe en el formato binario de archivo compuesto en Pascal

De BIFF2 a BIFF4 se saltan todo eso. El archivo comienza directamente con un record BOF, y el número de record de ese BOF codifica la generación: $0009 para BIFF2, $0209 para BIFF3 y $0409 para BIFF4. La palabra de versión dentro del cuerpo, en la que se apoyan los formatos posteriores, es ignorada por estas generaciones, así que el id del record es la única señal confiable. Antes de comprometerse con la ruta cruda, la capa de streams de HotXLS verifica dos cosas más: el cuerpo del BOF debe medir de cuatro a seis bytes, y la palabra de tipo de substream debe ser $0010 para una hoja de cálculo, $0020 para un chart o $0040 para una hoja de macros. Cualquier otra cosa hace que Open devuelva un código de error

Esas comprobaciones importan más de lo que parece. Dos bytes de $09 $00 en el offset cero no son raros en datos binarios arbitrarios, y una carpeta de cuarentena de archivos .xls sin identificar tiende a contener exportaciones de texto renombradas, copias truncadas y el archivo de vez en cuando de un programa completamente distinto. Tres campos independientes todos de acuerdo son una señal mucho más fuerte que uno

Diagrama de cómo HotXLS detecta archivos Excel pre-OLE desde Delphi: libros en contenedores OLE2 modernos por un lado, streams crudos de records BIFF2 a BIFF4 por el otro, y los campos del record BOF verificados antes de comprometer la ruta heredada
Los archivos heredados no traen envoltorio de almacenamiento, así que el propio registro BOF inicial prueba la generación, la longitud del cuerpo y el tipo de substream antes de leer cualquier celda

Tres generaciones, tres disposiciones de registro

Los records de celda son donde las generaciones divergen de forma más visible. BIFF2 ocupa un bloque contiguo de números de record bajos, $0001 a $0005, para celdas en blanco, entero, número, etiqueta y booleano o error. Cada uno de esos cuerpos lleva un campo de atributo de tres bytes donde las versiones posteriores ponen un índice de formato extendido de dos bytes, así que cada valor queda un byte más adentro del record. El record INTEGER de BIFF2 es su propia rareza: un valor de 16 bits sin signo sin parte fraccionaria, un tipo de record que BIFF3 descartó en favor de RK

BIFF3 y BIFF4 abandonan los números bajos y reutilizan los ids y disposiciones de record de BIFF5: $0201, $0203, $0204, $0205 y $027E, cada uno con un índice XF de dos bytes. Esa reutilización provoca un fallo específico y fácil de diagnosticar mal. Un record LABEL de BIFF3 o BIFF4 es estructuralmente idéntico a su contraparte de BIFF5: fila, columna, índice de formato, luego un conteo de caracteres de dos bytes en el offset 6. Aplique esa disposición a una etiqueta BIFF2 y el conteo se arma desde el último byte de atributo y el largo real de un byte, así que una etiqueta de cinco caracteres reclama por lo menos 1,280 caracteres y el lector se sale del final del record. Vaya para el otro lado, disposición BIFF2 sobre un record BIFF3, y el largo se lee del byte alto del conteo, que es cero para cualquier cadena corta. Nada lanza en ninguna de las dos direcciones. El libro de trabajo vuelve con etiquetas vacías o basura verosímil, y HotXLS evita ambas despachando por el id del record junto con la generación tomada del BOF

El campo de atributo de BIFF2 esconde una trampa más. Su primer byte guarda el índice de formato en los seis bits bajos, y el valor $3F significa "el índice real es demasiado grande para seis bits, mire el record IXFE precedente". HotXLS rastrea el IXFE más reciente y resuelve el escape antes de asignarle a la celda su estilo, así que una celda cuyo índice de formato necesita más de seis bits igual recibe su propio formato

Comparación de las disposiciones de records de celda BIFF2 y BIFF3/BIFF4 leídas por HotXLS en Delphi, mostrando números de record por generación, la diferencia de disposición del campo LABEL, el modo de fallo por lectura errónea, y la numeración paralela de records de fórmula y cadena
Los cuerpos BIFF2 llevan un bloque de atributos de tres bytes, mientras que BIFF3 y BIFF4 reutilizan los números de BIFF5 con un índice XF de dos bytes, y mezclar los diseños desalinea silenciosamente todo lo que sigue al registro

El texto en las tres generaciones es una cadena de bytes plana sin Unicode por ningún lado. La codificación viene del record CODEPAGE del archivo, que en estos archivos vive dentro del único stream de hoja de cálculo porque no hay ningún stream a nivel de libro que lo sostenga. HotXLS decodifica etiquetas y resultados de cadena a través de esa página de códigos y recurre a Windows-1252 cuando el record está ausente, lo que cubre el valor por omisión occidental con el que se escribieron la mayoría de estos archivos. Un libro de trabajo japonés o de Europa central al que le falte un record CODEPAGE se decodificará como texto latino, y la única corrección es saber de dónde vino el archivo

Abrir uno desde Delphi

La detección ocurre dentro de Open, así que el código que llama es el mismo que para cualquier libro de trabajo. El bucle de abajo recorre el used range e imprime cada celda dos veces cuando sostiene una fórmula: la expresión traducida y el valor tal como Excel lo mostraría a través del formato de número de la celda

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 lanza cuando el archivo es realmente un paquete ZIP,
      // típicamente un .xlsx que alguien renombró a .xls
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

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

Observe la aritmética de índices. Los límites de UsedRange usan base cero mientras que tanto la colección de hojas como el acceso a celdas usan base uno, una inconsistencia que la API conserva por compatibilidad con código existente. Olvide el ajuste y el bucle audita el rectángulo equivocado sin reportar nada fuera de lo común. Los archivos que esta ruta acepta traen un único substream de hoja de cálculo, chart u hoja de macros, así que Sheets[1] es la única hoja que encontrará; un libro de trabajo enlazado de Excel 4.0 (BOF tipo $0100) queda fuera de ese conjunto y Open devuelve un código de error. Las comprobaciones previas económicas que evitan cargar un archivo por completo están cubiertas en inspección ligera de libros de trabajo

Los archivos protegidos por contraseña siguen la misma regla de stream único. El record FILEPASS aparece justo después del BOF en el stream de la hoja de cálculo, donde HotXLS lo recoge y activa la deofuscación XOR legada que usaron estas generaciones; pase la contraseña por la sobrecarga Open(FileName, APassWord)

¿Qué pasa con las fórmulas en un archivo BIFF2 a BIFF4?

HotXLS lee las dos mitades de una fórmula legada: el resultado en caché que Excel guardó la última vez que calculó, y el stream de tokens, que se traduce al modelo de cálculo actual durante la carga. Los records de fórmula comparten una numeración paralela a través de las tres generaciones, $0006, $0206 y $0406, pero los cuerpos difieren exactamente en los lugares donde un parser BIFF8 tropezaría

El resultado en caché comienza en el byte 7 en BIFF2, por culpa del campo de atributo, y en el byte 6 en BIFF3 y BIFF4. Los resultados no numéricos usan una palabra marcadora de $FFFF en los últimos dos bytes de ese espacio de ocho bytes, con un discriminador en el primer byte: 0 para cadena, 1 para booleano, 2 para código de error, 3 para vacío. Un resultado de cadena no cabe en el espacio, así que el texto llega en el record siguiente, $0007 en BIFF2 con un largo de un byte o $0207 en BIFF3 y BIFF4 con un largo de dos bytes

Anatomía del resultado de fórmula en caché en records FORMULA de BIFF2 a BIFF4 leídos por HotXLS desde Delphi: el valor empieza en el byte 7 en BIFF2 y en el byte 6 en BIFF3 y BIFF4, con una palabra marcadora FFFF para cadenas, booleanos, errores y vacíos
HotXLS conserva el último valor que Excel calculó junto a la expresión traducida, y los resultados no numéricos llegan como una palabra marcadora más un discriminador

El stream de tokens es donde una implementación ingenua produce disparates. BIFF2 antepone a la expresión un largo de un byte en el offset 16 y comienza los tokens en 17; BIFF3 y BIFF4 usan un largo de dos bytes y comienzan en 18. Dentro del stream, los tokens de función llevan un índice de función de un byte antes de BIFF5 y un índice de dos bytes desde BIFF5 en adelante. Alimente tokens BIFF4 a un traductor BIFF8 sin cambios y cada llamada de función consume un byte de más, así que el resultado no es una fórmula levemente equivocada sino una no relacionada. El traductor de HotXLS toma la versión BIFF como parámetro y lee cada token con los anchos de esa generación

Tener ambas mitades plantea una pregunta práctica: ¿qué número entra al libro de trabajo migrado? El valor en caché es lo que los usuarios del archivo vieron, imprimieron y firmaron en 1992. Un recálculo en un motor moderno computa lo que la fórmula significa hoy, y el comportamiento de las funciones no se ha quedado quieto en tres décadas. Para una migración de archivo el valor en caché es el registro, así que consérvelo y corra un recálculo solo como contrachequeo que le dice qué celdas merecen una mirada humana:

// Contrachequeo: compare los resultados guardados con un recálculo fresco.
// Este helper y su arreglo CachedText son código suyo
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;

Corra esto sobre una copia, o antes del guardado, ya que Recalculate reemplaza los resultados guardados en memoria. Un reporte vacío es el desenlace que usted quiere; uno corto por lo general apunta a funciones volátiles como NOW() o a referencias que el autor original dejó sueltas

Formato, fechas y la trampa del formato General

HotXLS convierte los records de formato de estas generaciones al mismo modelo de libro de trabajo que produce un .xls moderno. Los records FONT (con el record FONTCOLOR separado de BIFF2 integrado), las cadenas FORMAT, y los records XF de cada generación, con sus patrones de relleno, estilos de borde e índices de color legados, se vuelven fuentes, formatos de número y estilos de celda corrientes que usted puede leer y editar a través de Font, NumberFormat y las demás propiedades de rango

Las fechas se benefician directo. Cuando una celda numérica lleva un formato de número de fecha, el lector también aplica el flag DATEMODE del libro de trabajo, así que un libro del sistema 1904 aterriza en la misma escala TDateTime que uno del sistema 1900, y FormattedText muestra la fecha como la mostraba Excel. Las reglas detrás del offset de 1462 días están cubiertas en números de serie de fecha, el sistema 1904 y los formatos de número

La trampa es la columna que guarda fechas bajo un formato General. Montones de libros de los 90 fueron llenados por macros o por importaciones que escribieron números de serie sin molestarse en darles formato, y el archivo no da ninguna señal de que 33604 significa un día de enero de 1992 en lugar de un número de pieza. Esas celdas vuelven exactamente como están guardadas, sin corregir por el sistema de fechas. Decida por columna, a partir de lo que usted sabe de la fuente, y convierta esas columnas usted mismo:

// ColumnHoldsDates es su propia búsqueda, construida del conocimiento que
// tenga del sistema fuente; el archivo legado no puede responder esta pregunta por usted.
// Serial es un Double local; Cell y C vienen del primer listado
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // Serial con formato General: aún sin corrección por sistema de fechas
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

La prueba Pos('d', ...) es deliberadamente burda y solo busca saltarse celdas que ya llevan un token de día en su formato; una comprobación más estricta contra su propia lista de formatos de fecha conocidos vale las líneas extra si el archivo es grande

Escribir la copia migrada

Un libro de trabajo abierto desde un archivo BIFF2 a BIFF4 se guarda a través de los escritores BIFF5/BIFF8 o XLSX, ya que no hay razón para producir archivos nuevos en un formato que el Excel actual bloquea. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) escribe una copia BIFF8 que lleva los valores, las fórmulas traducidas y los estilos convertidos, y toda herramienta posterior de la canalización puede abrirla sin manejo especial. Conserve el original al lado: si una celda plantea una pregunta dentro de dos años, los bytes de 1992 son la única autoridad

Lectura legada, escritura moderna, recálculo y formato viven todos en una sola biblioteca para Delphi y C++Builder; la cobertura de formatos está listada en la página del componente de hojas de cálculo HotXLS para Delphi