Escritura de tablas dinámicas BIFF8 en Delphi: SXDB y SXLI

Casi todas las partes del formato binario heredado de Excel constan de un único registro con un tipo claro de dos bytes y una longitud de dos bytes. Una celda es un LABELSST o un NUMBER. Una región fusionada es un MERGEDCELLS. Puede leer la mayor parte de una hoja de trabajo recorriendo los registros uno a la vez y evaluando según la palabra del tipo. Las tablas dinámicas (PivotTables) rompen ese ritmo. Una sola tabla dinámica no es un registro, es un pequeño programa compuesto por decenas de registros cooperativos distribuidos en dos lugares diferentes en el mismo flujo de documentos compuestos OLE, y las relaciones entre ellos son posicionales, empaquetadas por bits e implacables. Esta es la estructura que la mayoría de los lectores de BIFF8 omiten por completo o conservan como bytes opacos, porque escribir una desde cero significa reproducir cada referencia cruzada que el propio Excel mantiene.

La razón por la que una tabla dinámica es difícil es que en realidad son dos artefactos soldados. Está la caché de tabla dinámica (pivot cache), una captura independiente de los datos de origen con su propio subflujo, y está la vista de tabla, el diseño que indica qué campos se sitúan en cuál eje. La caché y la vista se referencian mutuamente por índice. Si se equivoca en un solo índice, el archivo se abrirá con un error de actualización o con una cuadrícula silenciosamente vacía.

La caché de tabla dinámica es su propio subflujo

La caché reside en el flujo global del libro de trabajo como un subflujo BIFF completo, enmarcado por un registro BOF cuyo tipo de documento es 0x0006 (el valor que identifica a una caché de tabla dinámica, a diferencia de 0x0005 para el libro de trabajo o 0x0010 para una hoja de trabajo) y cerrado por el EOF correspondiente. Dentro de ese marco, la estructura es fija. Un registro SXDB representa el encabezado de la caché. Contiene el recuento de registros, el número de campos de la caché y el identificador de flujo que la vista de tabla citará para vincularse a esta caché. Cada columna de origen aporta luego un registro de definición de campo SXFDB seguido de un SXFDBType que lo clasifica, y luego los valores únicos que tomó esa columna, emitidos como un registro de elemento tipado por cada valor distinto.

Los registros de elementos son donde la caché demuestra su valor. Un valor de texto se convierte en un SXSTRING, un valor numérico en un SXNUM, un valor lógico en un SXBOOLEAN y un error de fórmula en un SXERR. La caché no almacena la cuadrícula de origen, almacena los valores distintos por campo más una tabla de índices que indica, para el registro n, cuál elemento distinto tomó cada campo. Es por ello que construir una tabla dinámica mediante programación no consiste en copiar celdas. Debe escanear el rango de origen, deducir el tipo de cada campo a partir de los valores que contiene, eliminar los duplicados en una lista de elementos tipados y registrar cada fila como una tupla de índices de elementos. HotXLS realiza exactamente esto: una columna completamente numérica se emite con elementos SXNUM, una columna de texto mixto se convierte en elementos SXSTRING y las fechas se transportan como valores seriales a través de la misma ruta numérica.

SXDBB y el empaquetado de bits que lo hace interesante

La tabla de índices por registro es la parte técnicamente más singular de toda la estructura, y reside en el registro SXDBB. La codificación simple almacenaría el índice de elemento de cada campo como una palabra de 16 bits. Excel no hace eso. Empaqueta el índice de cada campo exactamente en el número de bits requeridos para direccionar los elementos de ese campo, y nada más. El ancho es de ceil(log2(itemCount + 1)) bits. El + 1 es importante: el valor adicional es un centinela que significa "vacío, sin valor para este campo en este registro", por lo que un campo con tres elementos distintos necesita representar cuatro estados y, por lo tanto, ocupa dos bits, no el único bit que sugerirían tres elementos por sí solos. Un campo sin ningún elemento aporta cero bits y se omite por completo durante el empaquetado.

Los bits de un registro se concatenan a lo largo de todos los campos, luego el siguiente registro comienza en un límite de byte nuevo. Los registros están alineados por bytes, no empaquetados por bits de extremo a extremo, lo que hace que el acceso aleatorio a la tabla sea viable a costa de unos pocos bits de relleno por fila. El empaquetado dentro de un byte se realiza comenzando por el bit menos significativo. Una vez que acepta esas dos reglas, el codificador es una bomba de bits directa y el decodificador es su espejo.

// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
  capacity: Integer;
begin
  Result := 0;
  if itemCount <= 0 then
    Exit;            // empty field contributes zero bits
  Result := 1;
  capacity := 2;
  while capacity < itemCount + 1 do
  begin
    Inc(Result);
    capacity := capacity * 2;
  end;
end;

La razón por la que este detalle no se puede ignorar es el límite de 8224 bytes en un solo registro BIFF. Cada registro en el formato, incluidos los registros de tabla dinámica, debe ajustar su carga útil en un máximo de 8224 bytes, y una caché de tabla dinámica activa con miles de filas de origen superará ese límite mucho antes de haber emitido cada fila. Por lo tanto, la tabla de índices se divide. HotXLS limita el cuerpo de un único SXDBB a 8220 bytes, que es el límite de registro de 8224 menos el encabezado de registro de cuatro bytes para el tipo y la longitud, divide eso por el ancho de bytes de un registro empaquetado para saber cuántas filas completas caben, y luego emite tantos registros SXDBB continuos como lo exija el recuento de filas. Cada continuación se reinicia limpiamente en un límite de registro, de modo que ninguna fila se divide entre dos registros. Un lector que conozca el ancho de bits por registro puede avanzar a través de cada SXDBB en secuencia como si fueran una sola matriz de bits contigua.

El diseño de la vista: SXLI para el cuerpo, SXPI para la página

Con la caché construida, la vista de tabla es la segunda mitad. Su núcleo son los elementos de línea del eje, las filas del cuerpo de la tabla dinámica que enumeran cada combinación de valores de campo de fila y campo de columna que dibuja la tabla. Estos se transportan en registros SXLI (tipo de registro 0x00B5, descrito en [MS-XLS] §2.4.275). Un SXLI contiene muchas líneas, nuevamente hasta que el límite de 8224 bytes obliga a crear un nuevo registro, y utiliza un pequeño truco de compresión: cada línea almacena únicamente cómo difiere de la línea anterior, expresado como un recuento de prefijo común, de modo que un eje profundamente anidado no repite los valores de campo externos en cada fila. La línea de total general y la primera línea de cualquier registro siempre restablecen ese recuento de prefijo a cero para que un lector nunca tenga que mirar hacia atrás a través de un límite de registro para reconstruir una línea.

El eje de página (los menús desplegables de filtro que se sitúan encima de una tabla dinámica) es un registro independiente. SXPI (tipo de registro 0x00B6, [MS-XLS] §2.4.276) transporta una entrada de diez bytes por cada campo de página: el índice del campo de tabla dinámica isxvd, el elemento de caché seleccionado iCache, una palabra de posición ipos y un identificador de objeto heredado objId. El valor iCache es el que se debe observar. Un campo de página que muestra "(Todo)", sin filtrar nada, almacena el centinela 0x7FFD en lugar de un índice de elemento real. Una tabla dinámica creada mediante programación se abre con cada campo de página establecido en "(Todo)" hasta que el llamador preselecciona un elemento, momento en el cual el índice de caché de ese elemento reemplaza al centinela y Excel se abre con el filtro ya aplicado. Junto a estos se encuentran los registros de soporte que describen campos individuales y su formato, SXVD y SXVDEx para definiciones de vista de campo, SXIVD para las listas de índices de campos que ordenan cada eje y SXFormat para el formato de números, cada uno de ellos indexando hacia la misma caché que referencian las líneas del cuerpo.

Dos escritores en uno: bloques directos y el modelo tipado

Existe una razón estructural por la que HotXLS mantiene dos rutas completamente independientes para escribir una tabla dinámica, y proviene directamente de las demandas de fidelidad. Cuando un libro de trabajo se lee desde el disco, sus registros de tabla dinámica fueron escritos por Excel o por algún otro productor, y pueden usar variantes de registro, particularidades de ordenamiento o registros de extensión que ningún escritor externo modela por completo. Lo único seguro que se puede hacer con esos bytes es devolverlos sin cambios. Por lo tanto, una tabla dinámica que provino de un archivo se marca con la bandera FromRawBlobs = True, y al guardar, el escritor reproduce los bloques de registro preservados textualmente. Nada se regenera, nada se reinterpreta y el proceso de ida y vuelta a través de abrir y guardar es estable a nivel de bytes.

Una tabla dinámica construida por el programa representa el caso opuesto. No hay bytes originales que preservar, solo el modelo de objetos tipado: un TXLSPivotCache con sus campos y listas de elementos, y un TXLSPivotTable con sus asignaciones de ejes. Esa tabla se marca con la bandera FromRawBlobs = False, y el escritor la serializa por el camino difícil: emite un subflujo de caché BOF = 0x0006 nuevo, empaqueta la tabla de índices SXDBB a partir de los índices de elementos que contiene el modelo tipado y distribuye los registros SXLI y SXPI a partir de la configuración de los ejes. La bandera es lo que permite que ambos tipos coexistan en un solo libro de trabajo. Sin ella, un único escritor tendría que descartar la fidelidad de las tablas leídas o negarse a generar otras nuevas. Cualquier registro de extensión específico del productor que contuviera una tabla leída se conserva como registro suplementario, accesible a través de la lista SupplementalRecords de la tabla, de modo que una tabla inspeccionada a través del modelo tipado no pierde las partes que el modelo no describe.

Cómo construir una tabla dinámica en código

Todo el mecanismo anterior se encuentra detrás de una sola llamada. AddPivotTable toma el rango de origen en notación A1, la celda de destino donde se ancla la esquina superior izquierda de la tabla y un nombre. Analiza el rango, lo escanea para deducir los tipos de campo y compila la caché (reutilizando una caché existente si otra tabla ya está vinculada al mismo rango) y devuelve un TXLSPivotTable tipado con un campo por columna de origen, con cada campo inicialmente fuera del eje. Luego, coloca los campos en los ejes y elige una agregación. La firma es exactamente esta, y la caché, el empaquetado de SXDBB y los registros de vista se producen por usted al momento de guardar.

uses
  lxHandle, lxPivot;

var
  Book : TXLSWorkbook;
  Sheet: IXLSWorkSheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('Sales.xls');
    Sheet := Book.Sheets[1];

    // Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
    end;

    Book.SaveAs('Sales-Pivot.xls');
  finally
    Book.Free;
  end;
end;

La primera fila del rango de origen se lee como el encabezado que nombra los campos de la caché, por lo que AddRowField('Region') busca una columna por su texto de encabezado en lugar de por su posición. Debido a que la tabla devuelta es un modelo tipado con FromRawBlobs = False, el escritor toma la ruta desde cero: compila una caché independiente que no depende de que el rango de origen siga estando presente al momento de la actualización, que es exactamente la propiedad que desea cuando la tabla dinámica se enviará a un destinatario que podría mover o eliminar los datos subyacente.

La lectura y conciliación de los registros de caché y de tabla dinámica de un archivo que usted no produjo, incluida la ruta de preservación de bloques directos, se cubre en la guía del banco de trabajo de conversión y auditoría de libros de trabajo. Cuando el rango de origen se extiende a decenas de miles de filas y el flujo SXDBB abarca muchos registros continuados, las técnicas en las notas de rendimiento de libros de trabajo grandes evitan que la compilación de la caché domine su tiempo de ejecución. Ambos se complementan con el escritor de tablas dinámicas incluido en el componente de hoja de cálculo HotXLS para Delphi y C++Builder, junto con las API de celdas, fórmulas, gráficos y formatos cubiertas en otras partes de este blog.