Casi cualquier parte del formato binario heredado de Excel es un único registro con un tipo limpio de dos bytes y una longitud de dos bytes. Una celda es un LABELSST o un NUMBER. Una región combinada es un MERGEDCELLS. Puede leer la mayor parte de una hoja de trabajo recorriendo los registros uno a la vez y despachando según la palabra de tipo. Las tablas dinámicas rompen ese ritmo. Una sola tabla dinámica no es un registro, es un pequeño programa compuesto por decenas de registros que cooperan entre sí distribuidos en dos lugares diferentes del mismo flujo de documentos compuestos OLE, y las relaciones entre ellos son posicionales, empaquetadas en 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 se trata de dos artefactos soldados. Está la caché de la tabla dinámica, una instantánea independiente de los datos de origen con su propio subflujo, y está la vista de la tabla, el diseño que indica cuáles campos se sitúan en cuál eje. La caché y la vista se referencian mutuamente por índice. Si se equivoca en un índice, el archivo se abrirá con un error de actualización o con una cuadrícula silenciosamente vacía
La caché de la tabla dinámica es un subflujo propio
La caché reside en el flujo global del libro como un subflujo BIFF completo, enmarcado por un registro BOF cuyo tipo de documento es 0x0006 (el valor que marca una caché de tabla dinámica, a diferencia de 0x0005 para el libro o 0x0010 para una hoja de trabajo) y cerrado por el EOF correspondiente. Dentro de ese marco, la estructura es fija. Un registro SXDB es el encabezado de la caché. Lleva 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 utilidad. 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, qué elemento distinto tomó cada campo. Es por eso que construir una tabla dinámica mediante programación no es cuestión de copiar celdas. Debe escanear el rango de origen, inferir el tipo de cada campo a partir de los valores que contiene, eliminar los duplicados en una lista de elementos tipada y registrar cada fila como una tupla de índices de elementos. HotXLS hace 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 curiosa de toda la estructura, y reside en el registro SXDBB. La codificación ingenua almacenaría el índice de elementos 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 necesarios para direccionar los elementos de ese campo, y no más. El ancho es de ceil(log2(itemCount + 1)) bits. El + 1 importa: 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 elementos no aporta bits y se omite por completo durante el empaquetado
// 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 tablas dinámicas, debe encajar 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 de tipo y 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 de continuación como exija el recuento de filas. Cada continuación se reinicia limpiamente en un límite de registro, por lo que ninguna fila se corta a través de dos registros. Un lector que conozca el ancho de bits por registro puede recorrer cada SXDBB en secuencia como si fueran una única 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 la tabla es la segunda mitad. Su núcleo son los elementos de línea de eje, las filas del cuerpo de la tabla dinámica que enumeran cada combinación de valores de campo de fila y 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 un nuevo registro, y utiliza un pequeño truco de compresión: cada línea almacena solo en qué se diferencia de la línea anterior, expresado como un recuento de prefijo común, por lo que un eje profundamente anidado no repite los valores del campo externo en cada fila. La línea de gran total 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 la página, los menús desplegables de filtro que se sitúan sobre una tabla dinámica, es un registro separado. SXPI (tipo de registro 0x00B6, [MS-XLS] §2.4.276) lleva una entrada de diez bytes por campo de página: el índice del campo de la 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 hay que vigilar. Un campo de página que muestra "(All)", sin filtrar nada, almacena el centinela 0x7FFD en lugar de un índice de elemento real. Una tabla dinámica construida mediante programación se abre con cada campo de página establecido en "(All)" 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 los 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 de nuevo a la misma caché a la que hacen referencia las líneas del cuerpo
Dos escritores en uno: bloques de datos sin procesar y el modelo tipado
Hay una razón estructural por la que HotXLS mantiene dos rutas completamente separadas para escribir una tabla dinámica, y proviene directamente de las demandas de fidelidad. Cuando un libro se lee del disco, sus registros de tabla dinámica fueron escritos por Excel o por algún otro productor, y pueden usar variantes de registro, peculiaridades de ordenación o registros de extensión que ningún escritor externo del todo modela. Lo único seguro que se puede hacer con esos bytes es devolverlos sin cambios. De modo que una tabla dinámica que provino de un archivo se marca con FromRawBlobs = True, y al guardar, el escritor reproduce los bloques de registro preservados textualmente. Nada se regenera, nada se reinterpreta, y una operación de ida y vuelta a través de abrir y guardar es estable a nivel de bytes
Una tabla dinámica que construyó el programa es el caso contrario. 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 FromRawBlobs = False, y el escritor la serializa de la manera difícil, emitiendo un subflujo de caché BOF = 0x0006 nuevo, empaquetando la tabla de índices SXDBB a partir de los índices de elementos que contiene el modelo tipado y disponiendo los registros SXLI y SXPI a partir de la configuración del eje. La bandera es lo que permite que ambos tipos coexistan en un solo libro. 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 llevara una tabla leída se conserva como registro suplementario, accesible a través de la lista SupplementalRecords de la tabla, por lo que una tabla inspeccionada a través del modelo tipado no pierde las partes que el modelo no describe
Construcción de una tabla dinámica en código
Toda la maquinaria 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 escanéa para inferir los tipos de campo y construir la caché (reutilizando una caché existente si otra tabla ya se vincula al mismo rango), y devuelve una TXLSPivotTable tipada con un campo por columna de origen, 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 SXDBB y los registros de vista se producen para 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') coincide con 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: construye 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 puede mover o eliminar los datos subyacentes
La lectura y conciliación de los registros de tabla dinámica y caché de un archivo que usted no produjo, incluida la ruta de preservación de bloques de datos sin procesar, se cubre en la guía práctica del banco de trabajo de auditoría y conversión de libros. Cuando el rango de origen alcanza decenas de miles de filas y el flujo SXDBB abarca muchos registros continuos, las técnicas en las notas de rendimiento de libros de gran tamaño evitan que la construcción de la caché domine su tiempo de ejecución. Ambos se complementan con el escritor de tablas dinámicas que se incluye 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 secciones de este blog