Artículo técnico

Leer archivos de Excel 2.0 a 4.0 en Delphi con HotXLS

Un libro guardado por Excel 4.0 en 1992 empieza con los bytes 09 04 06 00, mientras que cada .xls de Excel 5.0 en adelante empieza con la firma de archivo compuesto D0 CF 11 E0. Un lector que comprueba 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 registro como algo que no es. Ambos fallos vienen de la misma suposición: que cada .xls tiene un contenedor. Los archivos de Excel 2.0, 3.0 y 4.0 no lo tienen, así que el propio stream de registros tiene que decir qué generación lo escribió, y los registros 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, almacenes de retención documental, exportaciones de laboratorio de instrumentos cuyo software de control data de principios de los noventa, y sistemas de contabilidad de larga ejecución dejaron todos libros BIFF2 a BIFF4 atrás. Las versiones actuales de Excel bloquean varios de estos formatos por defecto mediante sus ajustes de File Block, 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 pre-OLE

HotXLS reconoce un libro pre-OLE por su primer registro, dentro de la misma llamada TXLSWorkbook.Open que carga cualquier otro .xls. Cada .xls de Excel 5.0 en adelante es un archivo compuesto OLE2, un pequeño sistema de archivos dentro de un archivo, con el libro 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

BIFF2 a BIFF4 se saltan todo eso. El archivo empieza directamente con un registro BOF, y el número de registro 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, de la que dependen los formatos posteriores, es ignorada por estas generaciones, así que el id de registro es la única señal fiable. Antes de comprometerse con la ruta en crudo, la capa de streams de HotXLS comprueba 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 gráfico 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 parecen. Dos bytes $09 $00 en el offset cero no son raros en datos binarios arbitrarios, y una carpeta de cuarentena de .xls sin identificar tiende a contener exportaciones de texto renombradas, copias truncadas y algún archivo de un programa completamente distinto. Tres campos independientes acordando todos es una señal mucho más fuerte que uno

Diagrama de cómo HotXLS detecta ficheros Excel pre-OLE desde Delphi: libros de trabajo en contenedores OLE2 modernos por un lado, streams de registros BIFF2 a BIFF4 en bruto por otro, y los campos del registro BOF comprobados antes de comprometer la ruta antigua
Los archivos heredados no llevan envoltorio de almacenamiento, de modo que el propio registro BOF inicial prueba la generación, la longitud del cuerpo y el tipo de subflujo antes de leer cualquier celda

Tres generaciones, tres disposiciones de registro

Los registros de celda son donde las generaciones divergen de forma más visible. BIFF2 ocupa un bloque contiguo de números de registro bajos, $0001 a $0005, para celdas blank, integer, number, label y boolean-o-error. Cada uno de esos cuerpos lleva un campo de atributos de tres bytes donde las versiones posteriores ponen un índice de formato extendido de dos bytes, así que cada valor se sienta un byte más adentro del registro. El registro INTEGER de BIFF2 es su propia rareza: un valor unsigned de 16 bits sin parte fraccional, un tipo de registro que BIFF3 abandonó en favor de RK

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

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

Comparación de las disposiciones de registros de celda BIFF2 y BIFF3/BIFF4 leídas por HotXLS en Delphi, mostrando números de registro por generación, la diferencia de disposición del campo LABEL, el modo de fallo de lectura errónea, y la numeración paralela de registros 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 va tras el registro

El texto en las tres generaciones es una cadena de bytes plana sin Unicode por ningún lado. La codificación viene del registro CODEPAGE del archivo, que en estos archivos se sienta dentro del único stream de hoja de cálculo porque no hay stream de nivel libro que lo sostenga. HotXLS decodifica etiquetas y resultados de cadena a través de esa página de código y recurre a Windows-1252 cuando el registro está ausente, lo que cubre el occidental por defecto con el que se escribieron la mayoría de estos archivos. Un libro japonés o de Europa Central al que le falte un registro CODEPAGE se decodificará por tanto como texto latino, y el único arreglo 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. 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 como lo mostraría Excel a través del formato numérico 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[] es 1-based
    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;

Vigila la aritmética de índices. Los límites de UsedRange son 0-based mientras que tanto la colección de hojas como el acceso a celdas son 1-based, una inconsistencia que la API mantiene por compatibilidad con código existente. Olvida el ajuste y el bucle audita el rectángulo equivocado sin reportar nada inusual. Los archivos que esta ruta acepta llevan un único substream de hoja de cálculo, gráfico o hoja de macros, así que Sheets[1] es la única hoja que encontrarás; un libro bound de Excel 4.0 (BOF tipo $0100) queda fuera de ese conjunto y Open devuelve un código de error. Las comprobaciones previas baratas que evitan cargar un archivo están cubiertas en inspección ligera de libros

Los archivos protegidos por contraseña siguen la misma regla de stream único. El registro FILEPASS aparece justo después del BOF en el stream de la hoja, donde HotXLS lo recoge y activa la desofuscación XOR heredada que usaban estas generaciones; pasa 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 heredada: el resultado en caché que Excel almacenó cuando calculó por última vez, y el stream de tokens, que se traduce al modelo de cálculo actual durante la carga. Los registros 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 sitios donde un parser BIFF8 tropezaría

El resultado en caché empieza en el byte 7 en BIFF2, por el campo de atributos, y en el byte 6 en BIFF3 y BIFF4. Los resultados no numéricos usan una palabra marcadora de $FFFF en los dos últimos bytes de esa ranura de ocho bytes, con un discriminante en el primer byte: 0 para cadena, 1 para booleano, 2 para código de error, 3 para blank. Un resultado de cadena no cabe en la ranura, así que el texto llega en el registro siguiente, $0007 en BIFF2 con longitud de un byte o $0207 en BIFF3 y BIFF4 con longitud de dos bytes

Anatomía del resultado de fórmula en caché en los registros 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 blancos
HotXLS conserva el valor que Excel calculó por última vez junto a la expresión traducida, y los resultados no numéricos llegan como una palabra marcadora más un discriminante

El stream de tokens es donde una implementación ingenua produce tonterías. BIFF2 antepone a la expresión una longitud de un byte en el offset 16 y empieza los tokens en 17; BIFF3 y BIFF4 usan una longitud de dos bytes y empiezan 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. Alimenta tokens BIFF4 a un traductor BIFF8 sin cambios y cada llamada a función consume un byte de más, así que el resultado no es una fórmula ligeramente equivocada sino una sin relación. El traductor de HotXLS recibe la versión de BIFF como parámetro y lee cada token con las anchuras de esa generación

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

// Contraste: compara los resultados almacenados con un recálculo fresco.
// Este helper y su array CachedText son código tuyo
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;

Ejecuta esto sobre una copia, o antes del guardado, ya que Recalculate sustituye los resultados almacenados en memoria. Un informe vacío es el desenlace que quieres; uno corto suele apuntar a funciones volátiles como NOW() o a referencias que el autor original dejó colgando

Formato, fechas y la trampa del formato General

HotXLS convierte los registros de formato de estas generaciones al mismo modelo de libro que produce un .xls moderno. Los registros FONT (con el registro FONTCOLOR separado de BIFF2 plegado dentro), las cadenas FORMAT, y los registros XF de cada generación, con sus patrones de relleno, estilos de borde e índices de color heredados, se convierten en fuentes, formatos numéricos y estilos de celda corrientes que puedes leer y editar mediante Font, NumberFormat y las demás propiedades de rango

Las fechas salen beneficiadas directamente. Cuando una celda numérica lleva un formato de fecha, el lector aplica además el flag DATEMODE del libro, así que un libro de sistema 1904 aterriza en la misma escala TDateTime que uno de sistema 1900, y FormattedText muestra la fecha como lo hacía Excel. Las reglas detrás del desfase de 1462 días están cubiertas en números de serie de fecha, el sistema 1904 y formatos numéricos

La trampa es la columna que sostiene fechas bajo un formato General. Muchos libros de los noventa se llenaron con macros o con importaciones que escribieron números de serie sin molestarse en darles formato, y el archivo no da señal de que 33604 significa un día de enero de 1992 en lugar de un número de pieza. Esas celdas regresan exactamente como están almacenadas, sin corregir por el sistema de fechas. Decide por columnas, a partir de lo que sabes de la fuente, y convierte esas columnas tú:

// ColumnHoldsDates es tu propia búsqueda, construida con conocimiento del
// sistema fuente; el archivo heredado no puede responder esta pregunta por ti.
// 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 del sistema de fechas
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

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

Escribir la copia migrada

Un libro abierto desde un archivo BIFF2 a BIFF4 se guarda por 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 cada herramienta posterior del pipeline puede abrirla sin manejo especial. Conserva el original al lado: si dentro de dos años una celda levanta una pregunta, los bytes de 1992 son la única autoridad

La lectura heredada, la escritura moderna, el recálculo y el formato viven todos en una sola biblioteca para Delphi y C++Builder; la cobertura de formatos está enumerada en la página del componente de hoja de cálculo HotXLS Delphi