Un trabajo de informes funciona bien durante un año. Construye un libro de trabajo, llena una hoja con lo que devuelva la consulta y la guarda. Luego, un cliente con cinco años de historial solicita una exportación completa, el recuento de filas supera el millón y el proceso muere con un error de falta de memoria mucho antes de que el archivo llegue al disco. No había nada malo con el código. Estaba manteniendo todo el libro de trabajo en la RAM para poder serializarlo al final, y la memoria que necesitaba creció al mismo ritmo que la cantidad de filas que se le pidió que escribiera
La solución no es una máquina más grande. Es un modelo de escritura diferente. El escritor directo de transmisión en HotXLS emite el paquete OOXML de forma incremental a medida que llegan las filas, por lo que la memoria que usa no depende de cuántas filas escriba. Es la contraparte en el lado de la escritura del lector de transmisión: donde el lector recorre una hoja enorme sin construir un árbol de celdas, el escritor produce una sin construir un árbol de celdas tampoco
Por qué la ruta de guardado normal crece con los datos
La ruta normal de TXLSXWorkbook construye primero un modelo de objetos completo. Cada celda, con su valor, tipo y referencia de estilo, vive como un objeto en la memoria hasta que usted llama a guardar, momento en el cual todo el árbol se serializa en el paquete. Ese modelo es el correcto cuando desea leer una hoja, editarla, recalcular y volver a escribirla, porque el acceso aleatorio a cualquier celda es exactamente lo que necesita la edición. Es el incorrecto cuando está vertiendo filas en una dirección y nunca mira hacia atrás, porque paga para mantener cada fila residente sin ningún beneficio. Un millón de filas de objetos es un millón de filas de objetos, ya sea que vuelva a visitarlas o no
El escritor de transmisión elimina el árbol. Tan pronto como se escribe una celda, se convierte en bytes en la parte de la hoja de trabajo, y esos bytes se entregan a la salida zip. El flujo de la hoja de trabajo es el único búfer que crece, y crece en el lado de la salida, no como objetos vivos de Delphi en el montón. Lo que permanece residente es una cantidad fija de contabilidad: los nombres de las hojas, algunas banderas, el número de fila actual, un contador de celdas. Ese conjunto no cambia entre la fila uno y la fila diez millones
La tabla de cadenas compartidas es la trampa y las cadenas en línea son la salida
A la mayoría de los escritores de transmisión XLSX les va bien hasta que se encuentran con texto. El formato OOXML normalmente almacena cadenas en una tabla de cadenas compartidas: cada cadena distinta se escribe una vez en una parte separada, y cada celda que contiene esa cadena lleva un índice en la tabla en lugar del texto. Es una buena optimización de espacio para archivos llenos de etiquetas repetidas, y es la forma predeterminada que usa la ruta de guardado estándar. El problema para un escritor de transmisión es brutal. Para eliminar los duplicados, la tabla tiene que permanecer residente durante todo el trabajo, porque cualquier fila que esté por venir podría repetir una cadena de una fila ya escrita, y solo un mapa completo en memoria de las cadenas vistas puede asignar el índice correcto. Por lo tanto, la única estructura que un escritor de transmisión no puede transmitir es la misma estructura que se supone que hace que el archivo sea pequeño. Los datos con mucho texto anulan la transmisión por la que usted vino
El escritor directo elude la tabla por completo. Las cadenas se escriben en línea, como celdas t="inlineStr" cuyo texto se encuentra directamente dentro de la celda con un elemento <is><t>. No hay ninguna tabla para acumular ni ningún mapa de cadenas vistas para retener, por lo que las columnas de texto no cuestan más memoria que las numéricas. El intercambio es explícito y vale la pena decirlo claramente. Las cadenas en línea repiten el mismo texto dondequiera que ocurra, por lo que un archivo con muchas etiquetas idénticas es más grande en el disco que su equivalente de cadenas compartidas. Usted gasta tamaño de archivo para comprar memoria constante. Para una exportación de una sola pasada, ese es el lado correcto del intercambio, y la compresión zip absorbe gran parte de la repetición en la salida de todos modos
La tabla de estilos llega al final, con un formato de fecha
Los estilos presentan la misma tensión que las cadenas. Un libro de trabajo hace referencia a su formato a través de una parte de estilos, y un escritor de transmisión no puede mantener una paleta creciente de estilos a la par con las celdas que ya ha vaciado. El escritor directo responde a esto manteniendo la tabla de estilos pequeña y fija, y emitiéndola al cerrar en lugar de por adelantado. Un formato de celda predeterminado cubre las celdas ordinarias. Un formato de número de fecha cubre las fechas, registrado con un código de formato de yyyy-mm-dd en una posición conocida en la lista de formatos de celda
Ese formato de fecha es la razón por la que WriteDateTime existe como su propia llamada. Excel no tiene un tipo de fecha nativo; una fecha es un número que lleva un formato de fecha. WriteDateTime escribe el valor como un número de serie simple y etiqueta la celda con el único estilo de fecha, para que la hoja de cálculo lo represente como una fecha en lugar de un entero de cinco dígitos. El número de serie que escribe es importante para el viaje de ida y vuelta. Almacena el valor TDateTime directamente bajo el sistema de fechas de 1900, que es la misma convención que usa la ruta de guardado normal de TXLSXWorkbook. Debido a que ambas rutas coinciden en el número de serie, un archivo que produce el escritor de transmisión se lee a través del lector de HotXLS y se abre en Excel con fechas que coinciden con lo que usted pretendía, sin errores de uno en uno ni sorpresas de época entre el escritor y el lector
El orden es obligatorio porque los bytes ya se han ido
La transmisión compra su perfil de memoria con una regla que usted tiene que respetar. La salida se emite sobre la marcha y no se puede volver a visitar, por lo que todo debe escribirse en el orden en que aparece en el archivo. Dentro de una fila, las celdas van en orden ascendente de columna. Dentro de una hoja, las filas van en orden ascendente. No hay ningún búfer que permita al escritor ordenar sus celdas después del hecho, porque la fila que cerró hace un momento ya son bytes en el flujo zip y ya no es accesible. Entréguele la columna 5 y luego la columna 2 en la misma fila y la salida estará mal formada, ya que el escritor simplemente emite lo que usted le da en la secuencia que le da
La API de fila tiene una pequeña conveniencia para el caso común. AddRow toma un índice de fila basado en 1, pero pasar 0 significa tomar la siguiente fila después de la anterior, por lo que un llenado secuencial no tiene que rastrear y pasar un contador incremental. Cada AddRow cierra la fila que le precede, y cada AddSheet cierra la hoja que le precede, por lo que usted nunca finaliza explícitamente una fila o una hoja. Usted inicia la siguiente y el escritor finaliza la estructura abierta por usted
El escape se maneja donde el texto ingresa al XML
Cualquier texto que escriba se convierte en parte de un documento XML, por lo que las cinco entidades XML predefinidas deben escaparse o el paquete se vuelve inválido en el momento en que un valor contenga un ampersand o un paréntesis angular. El escritor escapa &, <, >, " y ' por usted tanto en el texto de cadena en línea como en el texto de fórmula, los dos lugares donde los caracteres suministrados por la persona que llama aterrizan dentro del marcado. Usted pasa un WideString en bruto y el escritor lo hace seguro. Un nombre de producto como Smith & Co <Ltd> o una fórmula que hace referencia a un nombre de hoja entre comillas sale como XML bien formado sin ningún escape por su parte
Ciclo de vida y por qué Destroy aún se cierra
Finalizar el paquete es lo que escribe la parte del libro de trabajo, la parte de estilos, las partes de tipos de contenido y relaciones, y finalmente el directorio central del zip. Ese trabajo ocurre en Close. Un paquete que nunca se cierra es un zip incompleto que ningún programa de hoja de cálculo abrirá, por lo que cerrar no es una limpieza opcional, es el paso que hace que el archivo sea válido. Para protegerse contra un Close olvidado en una ruta de error, Destroy realiza un cierre del mejor esfuerzo si el paquete aún está abierto, por lo que liberar el escritor no filtra el objeto zip subyacente incluso cuando una excepción omitió la llamada explícita. El patrón confiable sigue siendo el ordinario de Delphi: escriba dentro de un try, llame a Close y libere en el finally
Transmitir una hoja grande de un extremo a otro
La forma del trabajo es comenzar, agregar una hoja, verter filas, cerrar. El ejemplo a continuación escribe una fila de encabezado y luego una ejecución larga de filas de datos tipados, mezclando cadenas, números, una fórmula sin resultado almacenado en caché y una fecha. La memoria que utiliza para diez filas y para diez millones de filas es la misma, porque cada celda sale hacia el flujo zip tan pronto como se escribe
uses
lxDirectWrite;
procedure StreamReport(const Path: string; RowCount: Integer);
var
W: TXLSDirectWriter;
I: Integer;
begin
W := TXLSDirectWriter.Create;
try
W.BeginFile(Path);
W.AddSheet('Sales');
// Header row, written in ascending column order
W.AddRow(1);
W.WriteString(1, 'Item');
W.WriteString(2, 'Qty');
W.WriteString(3, 'Price');
W.WriteString(4, 'Total');
W.WriteString(5, 'Date');
// Data rows; pass 0 to AddRow to take the next row automatically
for I := 1 to RowCount do
begin
W.AddRow(0);
W.WriteString(1, 'Item ' + IntToStr(I));
W.WriteNumber(2, I);
W.WriteNumber(3, 1.5 + (I mod 10));
W.WriteFormula(4, Format('B%d*C%d', [I + 1, I + 1]));
W.WriteDateTime(5, EncodeDate(2026, 1, 1) + I);
end;
W.Close; // finalises the package
finally
W.Free;
end;
end;Una segunda hoja es simplemente otro AddSheet antes de continuar, y el escritor cierra la primera hoja a medida que abre la segunda. Las banderas booleanas usan WriteBoolean, que escribe una celda booleana tipada en lugar del texto "True". Si desea confirmar que el archivo es sólido y realiza viajes de ida y vuelta, la propiedad CellCount informa cuántas celdas se escribieron, y leer el resultado nuevamente con el lector de transmisión debería informar el mismo total
// A second sheet of typed flags after the data sheet above
W.AddSheet('Flags');
W.AddRow(1);
W.WriteString(1, 'Name');
W.WriteString(2, 'Active');
W.AddRow(0);
W.WriteString(1, 'alpha');
W.WriteBoolean(2, True);
WriteLn(Format('wrote %d cells', [W.CellCount]));Escribir en un flujo en lugar de en un archivo es el mismo código con BeginStream en lugar de BeginFile, lo que permite que un servidor envíe el libro de trabajo a una respuesta HTTP o a un flujo de memoria sin un archivo temporal en el disco. El escritor no es dueño del flujo que usted pasa, por lo que usted mantiene el control de su ciclo de vida
Cuando el trabajo es un punto final del servidor que construye libros de trabajo a pedido, los patrones en escrituras de transmisión para trabajos de servidor y por lotes muestran cómo conectar esto a un controlador de solicitudes y a una exportación programada. Cuando la pregunta es el costo más amplio de los libros de trabajo muy grandes, tanto de lectura como de escritura, rendimiento de libros de trabajo grandes en Delphi cubre a dónde van realmente el tiempo y la memoria. El escritor directo de transmisión se incluye como parte del Componente HotXLS para Delphi y C++Builder, junto con las API completas de lectura, edición y guardado que se tratan en otras partes de este blog