Una hoja de cálculo con un millón de filas y una docena de columnas es una exportación perfectamente normal de un trabajo de informes de base de datos. Ábralo de la forma habitual, cargando todo el libro de trabajo en un TXLSWorkbook, y el proceso tendrá que materializar cada una de esos doce millones de celdas como un objeto vivo antes de que se ejecute su primera línea de lógica de negocio. El archivo en disco puede ocupar sesenta megabytes de XML comprimido. El árbol de objetos en el que se expande es varias veces mayor, y todo tiene que residir a la vez porque el modelo es de acceso aleatorio por diseño. Para un informe que pretende leer de arriba a abajo y desechar, eso es una gran cantidad de memoria gastada en una estructura que nunca necesitó
Hay un segundo camino a través del mismo archivo. En lugar de construir un modelo, se escanea el XML de la hoja de cálculo solo hacia adelante, una celda a la vez, y se deja que cada celda fluya una vez que se ha examinado. No se acumula nada. La memoria se mantiene casi constante tanto si la hoja tiene mil filas como diez millones, porque el lector nunca retiene más que la parte que está analizando actualmente más un par de pequeñas tablas de búsqueda. Esto es lo que hace el lector directo HotXLS, y el resto de este artículo trata sobre por qué se mantiene pequeño y qué le da a cambio
Por qué el modelo en memoria no escala
Un archivo XLSX es un paquete ZIP de partes XML descrito por ECMA-376. Cada hoja de cálculo es su propia parte, xl/worksheets/sheetN.xml, y dentro de ella cada fila es un elemento <row> que contiene elementos de celda <c>. La ruta de carga normal lee esa parte y construye un objeto direccionable para cada celda de forma que más tarde pueda pedir Cells[12345, 7] y obtener una respuesta en un tiempo constante. El acceso aleatorio es la razón de ser de un modelo de libro de trabajo, y es exactamente lo que hace cómoda la edición, la evaluación de fórmulas y el estilo
El costo es que el acceso aleatorio requiere que todo esté presente simultáneamente. No se puede indexar en una estructura que solo se ha construido parcialmente. Por tanto, el pico de memoria de una carga completa es una función del número de celdas, y en una hoja con millones de celdas pobladas esa función aterriza en algún lugar en el que su servicio no quiere estar, especialmente si varios de estos trabajos se ejecutan a la vez en una máquina compartida. Cuando el patrón de acceso que realmente necesita es secuencial, pagar por el acceso aleatorio es pagar por una capacidad que no utilizará
Un escaneo SAX solo hacia adelante que no construye árbol
El lector directo abre el paquete ZIP y recorre cada parte de la hoja de cálculo con un analizador de extracción estilo SAX. SAX aquí significa que el analizador sintáctico informa de los eventos de análisis a medida que los encuentra, un elemento de inicio, una ejecución de texto, un elemento final, y luego sigue adelante. No guarda ningún árbol de nodos tras de sí. El lector rastrea la fila y columna actuales a partir de los atributos r, recopila el tipo, índice de estilo, valor y texto de la fórmula de la celda a medida que llegan los eventos, y cuando se ve la etiqueta de cierre </c> emite una celda y la olvida. La siguiente celda reutiliza el mismo puñado de variables locales
Dado que no se retiene nada entre celdas, la huella de memoria no crece con el número de celdas. Esa es la propiedad que merece la pena conservar. Una hoja de doscientas filas y una hoja de veinte millones de filas le cuestan al lector la misma memoria residente, y la diferencia entre ellas es solo el tiempo que dura el escaneo. Se renuncia al acceso aleatorio, la característica principal del modelo, y a cambio se obtiene un techo de memoria que el recuento de celdas no puede superar
Qué se mantiene residente, y por qué esas dos partes
El escaneo no carece por completo de estado, y las excepciones son instructivas. Hay que mantener dos pequeñas tablas en memoria mientras dure, porque una celda por sí sola no conlleva suficiente información para interpretarse sin ellas
La primera es la tabla de cadenas compartidas. En SpreadsheetML, una celda de texto no almacena su propio texto. Conlleva t="s" y una carga útil numérica que es un índice a xl/sharedStrings.xml, una única lista deduplicada de cada cadena distinta en el libro de trabajo. Se trata de un buen intercambio de espacio para los archivos en los que las mismas etiquetas se repiten a lo largo de miles de filas, pero significa que el lector tiene que cargar esa tabla de cadenas por adelantado y mantenerla residente, porque cualquier celda en cualquier lugar de cualquier hoja puede hacer referencia a cualquier entrada de ella. El tamaño de la tabla viene dado por el número de cadenas distintas, no por el número de celdas, de modo que se mantiene modesto incluso en hojas enormes
La segunda es el mapeo de formato de número desde la parte de estilos. Una celda numérica y una celda de fecha son byte por byte lo mismo en el cable: ambas son un número simple, porque una fecha en SpreadsheetML es solo un recuento de días en serie. Lo único que las distingue es el estilo de la celda, que apunta a través de cellXfs en xl/styles.xml a un id de formato de número. Para informar de una fecha como una fecha en lugar de como el número de serie en bruto, el lector carga esa tabla de estilo a formato y la mantiene residente. Todo lo demás en el archivo, los datos reales de la celda que conforman la mayor parte de los bytes, fluye sin almacenarse
Cada celda informa de un tipo y un valor
Cada celda emitida llega como un registro TXLSDirectCell. Conlleva el índice y el nombre de la hoja, la fila y la columna base 1, un Kind semántico, el Value como un Variant, el texto Formula sin su signo igual inicial, y el StyleIndex en bruto. El tipo es uno de xdkNumber, xdkString, xdkBoolean, xdkDate, o xdkError, de forma que puede bifurcar sobre lo que significa la celda en lugar de volver a derivarlo de los atributos. Una celda de fórmula informa del tipo de su resultado almacenado en caché, junto con el texto de la fórmula, de modo que un total calculado llega como un número que también le dice cómo se produjo
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Distinguir una fecha de un número
La cuestión de la fecha merece un vistazo más de cerca porque es donde la mayoría de los escáneres ingenuos se equivocan. No hay tipo de fecha en una celda numérica. Una celda que contenga el valor de serie 46000 podría ser una cantidad, un precio, o el 17 de febrero de 2025, y el archivo le dice cuál solo a través del id del formato de número alcanzado mediante el estilo de la celda. ECMA-376 reserva un bloque de ids de formato integrados cuyo significado es fijo en todos los productores que lo cumplen, y los ids portadores de fecha se sitúan en dos rangos: del 14 al 22 para los formatos de fecha y hora estándar, y del 45 al 47 para los formatos de tiempo transcurrido como [h]:mm:ss. Cuando DetectDates está activado, que lo está por defecto, el lector resuelve el estilo de cada celda numérica a su id de formato, y una celda cuyo id cae en esos rangos reservados se reporta como xdkDate con su Value ya convertido a un TDateTime de Delphi. Los formatos personalizados también se comprueban, inspeccionando el código de formato en busca de tokens de fecha y hora, pero los rangos reservados son la columna vertebral fiable. Si apaga DetectDates y la tabla de estilos ni siquiera se carga, cada celda numérica llega como xdkNumber, y el escaneo es fraccionalmente más ligero
Omitir hojas y abortar pronto
El escaneo secuencial tiene una ventaja silenciosa que el acceso aleatorio no puede igualar: se puede detener. El evento OnSheet se dispara antes de abrir cada hoja de cálculo, y le ofrece dos interruptores. Ajuste SkipSheet y toda esa parte nunca se analizará, que es como se escanean solo las hojas que le importan en un libro de trabajo de múltiples hojas sin pagar por leer el resto. Ajuste Abort y todo el escaneo terminará inmediatamente. El evento OnCell conlleva su propio Abort, por lo que puede detenerse en el momento en que haya encontrado lo que buscaba, una fila en particular, un valor centinela, el final de un bloque de cabecera, sin leer los millones de celdas restantes. En un escaneo de solo avance, abortar es genuinamente gratis, porque el trabajo que se salta es trabajo que aún no había ocurrido
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Scan only the "Data" sheet; leave the rest unread
SkipSheet := SheetName <> 'Data';
end;Contar celdas sin un manejador
Vale la pena destacar un refinamiento reciente porque convierte una pregunta común en una única llamada barata. El lector cuenta cada celda poblada por la que pasa, y lo hace tanto si hay adjunto un manejador OnCell como si no. Anteriormente, sin un manejador establecido, el recuento de celdas pobladas volvía como cero, ya que el recuento era un efecto secundario de la emisión. Ahora el recuento es independiente de la emisión. Eso significa que puede formular una pregunta, cuántas celdas pobladas contiene realmente este libro de trabajo, y obtener la respuesta por el precio de un escaneo sin devoluciones de llamada en absoluto. ReadFile y ReadStream devuelven ambos ese total como un Int64, y el mismo número está disponible después como la propiedad CellCount. Un retorno de -1 señala que el archivo no se pudo abrir o no es un paquete OOXML
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// No OnCell handler: a pure populated-cell census, still near-constant memory
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Para el escaneo completo, se adjunta el manejador y se llama a ReadFile exactamente de la misma manera. El contraste con una carga completa es todo el punto: donde cargar quarterly_export.xlsx en un libro de trabajo expandiría cada celda en un objeto residente y lo retendría todo, el lector directo mantiene solo las cadenas compartidas y la tabla de estilos mientras los doce millones de celdas fluyen a través de su OnCell una a una. La aritmética que se ejecutó por celda no deja nada atrás, por lo que el pico de memoria está establecido por el recuento de cadenas distintas del libro de trabajo, no por el recuento de sus filas
El lector directo es la herramienta adecuada cuando el trabajo consiste en leer un libro de trabajo grande una vez y extraerlo o resumirlo. Cuando en su lugar necesita el acceso aleatorio del modelo completo pero quiere que se comporte en archivos grandes, la optimización en nuestras notas sobre el rendimiento de libros de trabajo grandes en Delphi cubre ese camino. Y cuando se invierte la dirección, produciendo una gran salida en lugar de consumirla, el tutorial de escritura en streaming para trabajos por lotes de servidor aplica la misma disciplina de memoria constante a la escritura. Los tres se distribuyen como parte del HotXLS Component para Delphi y C++Builder, junto a las API de lectura, escritura, fórmulas y formato cubiertas en otras partes de este blog