Una hoja de cálculo con un millón de filas y una docena de columnas es una exportación perfectamente común de un trabajo de informes de base de datos. Ábrala de la forma habitual, cargando todo el libro de trabajo en un TXLSWorkbook, y el proceso tiene que materializar cada una de esas doce millones de celdas como un objeto activo antes de que se ejecute su primera línea de lógica empresarial. El archivo en el disco puede tener sesenta megabytes de XML comprimido. El árbol de objetos en el que se expande es varias veces mayor, y todo tiene que estar residente 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, esa 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, usted escanea el XML de la hoja de trabajo solo hacia adelante, una celda a la vez, y deja pasar cada celda después de haberla examinado. Nada se acumula. La memoria se mantiene casi constante independientemente de si la hoja tiene mil filas o 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 de HotXLS, y el resto de este artículo trata sobre por qué se mantiene ligero y lo que le ofrece a cambio
Por qué el modelo en memoria no escala
Un archivo XLSX es un paquete ZIP de partes XML descritas por ECMA-376. Cada hoja de trabajo 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 modo que luego pueda solicitar Cells[12345, 7] y obtener una respuesta en tiempo constante. El acceso aleatorio es el objetivo principal de un modelo de libro de trabajo, y es exactamente lo que hace que la edición, la evaluación de fórmulas y la aplicación de estilos sean convenientes
El costo es que el acceso aleatorio requiere que todo esté presente simultáneamente. No puede indexar en una estructura que solo ha construido parcialmente. Por lo tanto, el uso máximo de memoria de una carga completa es una función del recuento de celdas, y en una hoja con millones de celdas pobladas, esa función aterriza en un lugar donde su servicio no quiere estar, especialmente si varios de esos 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 ningún árbol
El lector directo abre el paquete ZIP y recorre cada parte de la hoja de trabajo con un analizador de extracción estilo SAX. SAX aquí significa que el analizador informa los eventos de análisis a medida que los encuentra, un elemento de inicio, un tramo de texto, un elemento final, y luego continúa. No mantiene ningún árbol de nodos detrás de él. El lector rastrea la fila y columna actuales a partir de los atributos r, recopila el tipo de celda, el índice de estilo, el valor y el texto de la fórmula 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
Debido a que no se retiene nada entre las celdas, el consumo de memoria no crece con el número de celdas. Esa es la propiedad a la que vale la pena aferrarse. 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 cuánto tiempo se ejecuta el escaneo. Usted renuncia al acceso aleatorio, la característica principal del modelo, y a cambio obtiene un límite de memoria que el recuento de celdas no puede superar
Qué permanece residente y por qué esas dos partes
El escaneo no carece de estado por completo, y las excepciones son instructivas. Dos pequeñas tablas deben mantenerse en la memoria durante la ejecución, porque una celda por sí sola no contiene 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. Lleva t="s" y una carga útil numérica que es un índice en xl/sharedStrings.xml, una única lista sin duplicados de cada cadena distinta en el libro de trabajo. Este es un buen intercambio de espacio para archivos donde las mismas etiquetas se repiten en 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 en ella. El tamaño de la tabla está determinado por el número de cadenas distintas, no por el recuento de celdas, por lo que se mantiene modesto incluso en hojas enormes
La segunda es el mapeo de formato de número de la parte de estilos. Una celda numérica y una celda de fecha son byte por byte lo mismo en la transmisión: ambas son un número simple, porque una fecha en SpreadsheetML es solo un conteo 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 una fecha como 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 de celda reales que componen la mayor parte de los bytes, pasa en forma de flujo continuo sin almacenarse
Cada celda reporta un tipo y un valor
Cada celda emitida llega como un registro TXLSDirectCell. Lleva el índice y el nombre de la hoja, la fila y la columna con base 1, un Kind semántico, el Value como un Variant, el texto de Formula sin su signo igual inicial y el StyleIndex en bruto. El tipo es uno de xdkNumber, xdkString, xdkBoolean, xdkDate o xdkError, por lo que puede bifurcar sobre lo que significa la celda en lugar de derivarlo nuevamente de los atributos. Una celda de fórmula reporta el tipo de su resultado almacenado en caché, con el texto de la fórmula al lado, por lo que un total calculado llega como un número que también le indica 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 una mirada más cercana porque es donde la mayoría de los escáneres ingenuos se equivocan. No hay un tipo de fecha en una celda numérica. Una celda que contiene el valor de serie 46000 podría ser una cantidad, un precio o el 17 de febrero de 2025, y el archivo le indica cuál es solo a través del id de formato de número al que se llega mediante el estilo de la celda. ECMA-376 reserva un bloque de ids de formato integrados cuyo significado es fijo en todos los productores conformes, y los ids que contienen fechas se encuentran en dos rangos: 14 a 22 para los formatos de fecha y hora estándar, y 45 a 47 para los formatos de tiempo transcurrido como [h]:mm:ss. Cuando DetectDates está activado, lo cual es 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 verifican, inspeccionando el código de formato para detectar tokens de fecha y hora, pero los rangos reservados son la columna vertebral confiable. Si desactiva DetectDates y la tabla de estilos ni siquiera se carga, cada celda numérica llega como xdkNumber, y el escaneo es marginalmente más ligero
Omitir hojas y abortar temprano
El escaneo secuencial tiene una ventaja silenciosa que el acceso aleatorio no puede igualar: usted puede detenerse. El evento OnSheet se dispara antes de que se abra cada hoja de trabajo, y le da dos interruptores. Configure SkipSheet y toda esa parte nunca se analiza, que es como escanea solo las hojas que le interesan en un libro de trabajo de múltiples hojas sin pagar por leer el resto. Configure Abort y todo el escaneo finaliza de inmediato. El evento OnCell lleva su propio Abort, por lo que puede detenerse en el momento en que haya encontrado lo que estaba buscando, una fila particular, un valor centinela, el final de un bloque de encabezado, sin leer los millones de celdas restantes. En un escaneo solo hacia adelante, abortar es verdaderamente gratuito, porque el trabajo que omite es trabajo que aún no había sucedido
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 controlador
Vale la pena mencionar un refinamiento reciente porque convierte una pregunta común en una sola llamada de bajo costo. El lector cuenta cada celda poblada por la que pasa, y lo hace sin importar si hay un controlador OnCell adjunto o no. Anteriormente, sin un controlador configurado, el recuento de celdas pobladas volvía como cero, ya que contar era un efecto secundario de la emisión. Ahora el conteo es independiente de la emisión. Eso significa que puede hacer 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 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 pudo ser abierto 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, usted adjunta el controlador y 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 mantendría todo, el lector directo mantiene solo las cadenas compartidas y la tabla de estilo mientras los doce millones de celdas fluyen a través de su OnCell una a la vez. La aritmética que se ejecutó por celda no deja nada atrás, por lo que el uso máximo de memoria lo establece el recuento de cadenas distintas del libro de trabajo, no su recuento de 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 cambio necesita el acceso aleatorio del modelo completo pero desea que se comporte bien en archivos grandes, el ajuste en nuestras notas sobre el rendimiento de libros de trabajo grandes en Delphi cubre ese camino. Y cuando la dirección se invierte, produciendo grandes resultados en lugar de consumirlos, la guía paso a paso de escritura de transmisión para trabajos por lotes del servidor aplica la misma disciplina de memoria constante a la escritura. Los tres se incluyen como parte del Componente HotXLS para Delphi y C++Builder, junto con las API de lectura, escritura, fórmulas y formato que se tratan en otras partes de este blog