Casi todas las partes del formato binario .xls heredado son un solo 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. Se puede leer la mayor parte de una hoja de cálculo recorriendo los registros de a uno por vez y procesándolos según su palabra de 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 docenas de registros que cooperan entre sí, distribuidos en dos lugares diferentes del mismo flujo de documento compuesto 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 entre sí. Existe la memoria caché de la tabla dinámica, una instantánea independiente de los datos de origen con su propio subflujo, y existe la vista de tabla, el diseño que indica qué campos se ubican en qué eje. La caché y la vista se hacen referencia mutuamente por índice. Equivóquese en un índice y el archivo se abrirá con un error de actualización o con una cuadrícula silenciosamente vacía
La memoria caché de la tabla dinámica es un subflujo propio
El caché reside en el flujo global del libro de trabajo (workbook) como un subflujo BIFF completo, enmarcado por un registro BOF cuyo tipo de documento es 0x0006 (el valor que marca un caché dinámico, a diferencia de 0x0005 para el libro de trabajo o 0x0010 para una hoja de cálculo) y cerrado por el EOF correspondiente. Dentro de ese marco, la estructura es fija. Un registro SXDB es el encabezado de la caché. Contiene el conteo 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é. Luego, cada columna de origen aporta 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 (typed item) por cada valor distinto
Los registros de elementos son donde la caché justifica su existencia. 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. El caché no almacena la cuadrícula de origen, almacena los valores distintos por campo más una tabla de índice que indica, para el registro n, qué elemento distinto tomó cada campo. Es por eso que construir una tabla dinámica programáticamente no es una cuestión de copiar celdas. Usted tiene que escanear el rango de origen, inferir el tipo de cada campo a partir de los valores que contiene, deduplicarlos en una lista de elementos tipados y registrar cada fila como una tupla (tuple) 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 de serie (serial values) 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 vive en el registro SXDBB. La codificación ingenua almacenaría el índice del elemento de cada campo como una palabra de 16 bits. Excel no hace eso. Empaqueta el índice de cada campo en exactamente la cantidad de bits necesarios para direccionar los elementos de ese campo, y ni uno más. El ancho es de ceil(log2(itemCount + 1)) bits. El + 1 es importante: el valor adicional es un centinela que significa "en blanco, 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, toma 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 (fresh byte boundary). 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 manejable a costa de unos pocos bits de relleno (padding) por fila. El empaquetado dentro de un byte es primero el bit menos significativo (least-significant-bit first). Una vez que usted acepta esas dos reglas, el codificador es una simple bomba de bits, y el decodificador es su espejo
// Ancho del índice de un campo en el flujo SXDBB.
// citmTotal elementos distintos necesitan ceil(log2(citmTotal + 1)) bits,
// el +1 reservando un valor centinela "en blanco" (blank).
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // un campo vacío aporta cero 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 puede ser ignorado es el límite máximo de 8224 bytes en un solo registro BIFF. Cada registro en el formato, incluidos los registros de tablas dinámicas, debe ajustar su carga útil (payload) en un máximo de 8224 bytes, y un caché dinámico ocupado con miles de filas de origen superará ese límite mucho antes de haber emitido todas las filas. Así que la tabla de índices se divide. HotXLS limita el cuerpo de un solo SXDBB a 8220 bytes, que es el límite de 8224 del registro menos el encabezado de registro de cuatro bytes (tipo y longitud), divide eso por el ancho en bytes de un registro empaquetado para saber cuántas filas completas caben, y luego emite tantos registros SXDBB continuos como el recuento de filas requiera. Cada continuación se reinicia limpiamente en un límite de registro, por lo que ninguna fila se corta entre dos registros. Un lector que conoce 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 (axis line items), 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 los 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 fuerza un nuevo registro, y utiliza un pequeño truco de compresión: cada línea almacena solo en qué difiere de la línea que está arriba de ella, expresado como un recuento de prefijos comunes, para que un eje profundamente anidado no repita 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 prefijos a cero para que el 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 ubican encima de una tabla dinámica, es un registro separado. SXPI (tipo de registro 0x00B6, [MS-XLS] §2.4.276) transporta una entrada de diez bytes por campo de página: el índice del campo dinámico isxvd, el elemento de caché seleccionado iCache, una palabra de posición ipos y una id de objeto heredado objId. El valor iCache es el que hay que observar. Un campo de página que muestra "(Todas)", es decir, que no filtra nada, almacena el centinela 0x7FFD en lugar de un índice de elemento real. Una tabla dinámica construida programáticamente se abre con cada campo de página establecido en "(Todas)" 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 ubican 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 campo que ordenan cada eje, y SXFormat para el formato numérico, cada uno apuntando de vuelta a la misma caché a la que hacen referencia las líneas del cuerpo
Dos escritores en uno: blobs en crudo (raw blobs) y el modelo tipado
Existe una razón estructural por la que HotXLS mantiene dos caminos completamente separados para escribir una tabla dinámica, y surge directamente de las exigencias de fidelidad. Cuando un libro de trabajo se lee desde el disco, sus registros dinámicos fueron escritos por Excel o por algún otro productor, y pueden usar variantes de registros, peculiaridades de ordenamiento o registros de extensión que ningún escritor de terceros 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 FromRawBlobs = True, y al guardar, el escritor reproduce los blobs de registro preservados literalmente. Nada se regenera, nada se reinterpreta, y un viaje de ida y vuelta a través de la apertura y el guardado mantiene los bytes estables
Una tabla dinámica que el programa construyó es el caso opuesto. No hay bytes originales que preservar, solo el modelo de objetos tipados: 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 por el camino 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 diseñando los registros SXLI y SXPI a partir de la configuración de ejes. La bandera (flag) es lo que permite que ambos tipos coexistan en un solo libro de trabajo. Sin ella, un escritor único 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 mantiene como registros complementarios (supplemental records), accesibles a través de la lista SupplementalRecords de la tabla, de modo que una tabla inspeccionada a través del modelo tipado no pierda las partes que el modelo no describe
Construyendo una tabla dinámica en código
Toda la maquinaria descrita anteriormente 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 inferir los tipos de campo y construir el caché (reutilizando un caché existente si otra tabla ya se vincula al mismo rango), y devuelve un TXLSPivotTable tipado con un campo por columna de origen, cada campo inicialmente fuera del eje (off-axis). Usted 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 la vista se producen para usted en el 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];
// Origen A1:E500 en 'Data'; anclar la tabla dinámica en la fila 3, columna 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') hace coincidir una columna por el texto de su encabezado en lugar de por su posición. Debido a que la tabla devuelta es un modelo tipado con FromRawBlobs = False, el escritor toma el camino desde cero: construye una caché independiente que no depende de que el rango de origen todavía esté presente en el momento de la actualización, lo cual es exactamente la propiedad que usted desea cuando la tabla dinámica se envíe 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 blobs en crudo (raw blobs), se cubre en el tutorial del banco de trabajo de auditoría y conversión de libros. Cuando el rango de origen llega a decenas de miles de filas y el flujo SXDBB abarca muchos registros continuos, las técnicas en las notas de rendimiento de libros de trabajo grandes evitan que la construcción del caché domine su tiempo de ejecución. Ambos se combinan 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 formato cubiertas en otras partes de este blog