Un libro de finanzas mantenido a mano acumula cachés de tablas dinámicas, segmentaciones de datos, formatos condicionales y años de estilos de celda, y sin embargo el trabajo nocturno solo tiene que cambiar los números de una pestaña de datos. Una carga y guardado completos parsean, reconstruyen y desinflan cada parte otra vez. Reescribir solo esa parte de hoja de cálculo y llevar cada otra entrada zip intacta se salta casi todo ese trabajo
Lo que hace esto posible es el propio formato de paquete. Un archivo .xlsx es un archivo zip de partes XML dispuestas según las Open Packaging Conventions (ECMA-376 Parte 2), y cada entrada zip registra su método de compresión, su CRC-32 y su tamaño comprimido en la cabecera local y otra vez en el directorio central. Una entrada puede por tanto pasar de un archivo a otro sin desinflarse nunca, siempre que quien copia escriba esos tres valores junto a los bytes crudos. La caché de tabla dinámica no necesita entenderse para conservarse. Solo necesita copiarse
¿Por qué copiar bytes comprimidos en vez de volver a guardar el libro?
Copiar a nivel comprimido convierte la mayor parte del parche en una copia de flujo llana. En HotXLS, TXLSDirectWriter.BeginPatch abre el paquete de origen en solo lectura y le entrega a TZipArchive.CopyCompressedFrom cada entrada que el parche deja quieta, la cual escribe los bytes comprimidos, el CRC y los tamaños de la entrada de origen directamente en el archivo de destino. Una vía de escritura convencional paga una descompresión a la entrada y una compresión a la salida, y la compresión es la mitad cara. En un libro cuyo volumen es una caché de tabla dinámica grande más unas cuantas docenas de imágenes incrustadas, el tiempo se va a la hoja que de verdad estás escribiendo
La copia cruda cubre las entradas guardadas sin comprimir (método zip 0) y las entradas desinfladas (método 8). Una entrada que use algún otro método de compresión también se lleva: el escritor la lee a través de un stream descompresor y la vuelve a escribir, así que la parte llega al destino de una forma u otra. Las entradas marcadoras de directorio no llevan contenido y se omiten, porque el escritor produce las suyas propias. Un puñado de entradas nunca se copia, porque se regeneran o se editan: la hoja objetivo y su parte _rels, xl/calcChain.xml, xl/_rels/workbook.xml.rels y [Content_Types].xml, más xl/workbook.xml cuando se inserta una hoja
Reemplazar una hoja in situ con BeginPatch
La sobrecarga de dos argumentos BeginPatch(FileName, SheetName) reemplaza una hoja de cálculo existente y escribe el resultado de vuelta en la misma ruta. Resuelve el nombre a través del registro de hojas en xl/workbook.xml y del mapa de relaciones que ata cada hoja a su parte, compara nombres sin distinguir mayúsculas y lanza una excepción que contiene "worksheet not found" cuando nada coincide. Desde ahí la hoja se redacta con las mismas llamadas AddSheet, AddRow y Write* que el escritor en flujo usa para archivos nuevos
var
W: TXLSDirectWriter;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch('monthly-dashboard.xlsx', 'Data'); // reemplazo in situ
W.AddSheet('Data'); // el nombre es informativo en modo parche
W.AddRow(1);
W.WriteString(1, 'Region');
W.WriteString(2, 'Revenue');
W.AddRow(2);
W.WriteString(1, 'North');
W.WriteNumber(2, 184320.55);
W.AddRow(3);
W.WriteString(1, 'South');
W.WriteNumber(2, 97410.20);
W.AddRow(4);
W.WriteString(1, 'Total');
W.WriteFormula(2, 'SUM(B2:B3)'); // sin '=' inicial
W.Close;
finally
W.Free;
end;
end;
Dos detalles de ese listado se leen mal con facilidad. El nombre que se pasa a AddSheet no renombra nada: la pestaña conserva el nombre, el sheetId y la posición que ya tiene en xl/workbook.xml, así que AddSheet('Totals') seguiría escribiendo en Data. Y un segundo AddSheet lanza, porque un parche escribe exactamente una hoja. Refrescar tres pestañas significa tres parches, cada uno de los cuales copia el paquete una vez más
La fórmula entra sin signo igual. WriteFormula coloca su texto directamente en el elemento <f> de la celda, y la ECMA-376 Parte 1 §18.3.1.40 guarda ahí las fórmulas sin el "=" que Excel muestra en la barra de fórmulas; escribir '=SUM(B2:B3)' mete un signo igual literal en el XML. WriteFormula tampoco emite resultado en caché. Si algo aguas abajo lee valores en caché en lugar de recalcular, WriteFormulaNumber escribe la fórmula junto con el número que ya sabes que evalúa
La sobrecarga in situ nunca escribe sobre el original mientras trabaja. El paquete parcheado se construye en un archivo hermano nombrado como el origen con .hxlpatch.tmp añadido. En Close se libera el manejador de origen y el archivo preparado reemplaza al original mediante un único rename. Si ese rename falla, Close lanza una excepción cuyo mensaje nombra ambas rutas, conserva la copia preparada y deja el original donde estaba. Un proceso muerto antes de Close deja el original intacto y un .hxlpatch.tmp huérfano al lado, que el siguiente BeginPatch sobre ese archivo borra antes de empezar
Añadir una hoja nueva a una copia de la plantilla
La sobrecarga de cuatro argumentos BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) deja el archivo de origen quieto y escribe un paquete nuevo. Con InsertSheet = False reemplaza la hoja nombrada en la copia; con True añade una hoja de cálculo nueva con ese nombre y lanza "worksheet already exists" si la plantilla ya tiene una pestaña llamada así
// template.xlsx sigue intacto; output.xlsx recibe una pestaña extra
W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
W.AddSheet('Extra');
W.AddRow(1);
W.WriteString(1, 'Appended by the nightly job');
W.Close;
La inserción es donde se edita la contabilidad del paquete. El escritor rastrea las entradas xl/worksheets/sheetN.xml existentes buscando el número de parte más alto, los elementos <sheet> buscando el sheetId más alto, y las relaciones del libro buscando el rId más alto, y luego toma el valor siguiente de cada uno. El nuevo elemento <sheet> entra justo antes de </sheets>, así que la pestaña nueva aparece la última. El atributo de relación reutiliza el prefijo con que el libro de origen ata las hojas, que suele ser r:id pero no tiene por qué
Los tipos de relación siguen también al paquete de origen. Cuando las relaciones del libro ya usan los espacios de nombres Strict de ISO/IEC 29500 bajo purl.oclc.org, la nueva relación de hoja de cálculo se escribe con la URI de tipo Strict; un libro transicional recibe la transicional. Un libro Strict parcheado no acaba por tanto con una relación transicional entre las Strict
Estilos, cadenas y las partes que un parche deja quietas
Dentro de un parche, todo lo que la hoja nueva menciona debe existir ya en el paquete de origen. Las cadenas se escriben por tanto en línea dentro de la hoja en lugar de en xl/sharedStrings.xml, porque la tabla de cadenas compartidas pasa byte a byte y no puede crecer. Poner UseSharedStrings a True hace que AddSheet lance en modo parche
La misma regla cambia lo que significa StyleIndex. En un parche es una posición en la lista cellXfs del libro de origen (ECMA-376 Parte 1 §18.8.10), y el escritor solo comprueba que esté entre 0 y 65534. No puede saber si el índice 14 existe en ese archivo concreto, así que un número equivocado va directo a la salida. La forma más fiable de dar con el correcto es descomprimir la versión actual de la hoja y leer el atributo s de una celda que ya lleve el formato que quieres; ese número es la posición cellXfs. Excel puede añadir entradas a esa lista cada vez que alguien formatea celdas en la plantilla, así que vuelve a mirar el índice después de cada edición de la plantilla en lugar de fiarte de una constante escrita hace un año
const
// posición cellXfs de un formato de fecha en ESTA plantilla, tomado del
// atributo s="..." de una celda de fecha existente; re-mirar tras editar la plantilla
TemplateDateXf = 5;
begin
...
W.AddRow(5);
W.WriteString(1, 'Refreshed');
W.WriteDateTime(2, Now, TemplateDateXf); // índice explícito: aceptado
// W.WriteDateTime(2, Now); lanza en modo parche: su estilo de fecha implícito
// vive en una parte de estilos que el parche nunca escribe
end;
Todo lo que añadiría partes nuevas al paquete se rechaza. AddTable, AddChart, la familia AddImage, AddComment, AddDefinedName y AddCellStyle lanzan en cuanto se llaman, y Close vuelve a comprobarlo antes de confirmar, lo que también caza los formatos condicionales que fijan su propio relleno, color de fuente, negrita o cursiva, porque esos necesitan entradas nuevas en styles.xml. Una llamada rechazada es mejor que un paquete donde la hoja referencia una parte de tabla que nadie registró
El modo de reemplazo además arranca la hoja desde cero. La parte de relaciones de la hoja vieja no se copia, así que un dibujo, comentarios o hipervínculos externos de la versión anterior dejan de estar atados a la nueva, y los anchos de columna, paneles congelados, rangos combinados y validación de datos guardados en el XML de la hoja vieja también desaparecen. Los que viven en el XML de la hoja pueden escribirse de nuevo con SetColumnWidth, FreezePanes, MergeCells, AddDataValidation y AddHyperlink. Un gráfico o una imagen anclados en la pestaña de datos no pueden volver mediante un parche, así que deja los gráficos en su propia hoja y apunta sus series a la pestaña de datos
La cadena de cálculo se suelta en ambos modos. xl/calcChain.xml lista las celdas con fórmula en el orden en que Excel las evaluó la última vez; tras un reemplazo sus entradas describen celdas que ya no existen en esa forma, y tras una inserción los índices de hoja a los que se refiere se han desplazado. La parte se queda fuera de la copia, y su relación y su Override en [Content_Types].xml se eliminan, de modo que el paquete no referencia una parte ausente. Excel reconstruye la cadena la próxima vez que recalcule
Cuando el bucle de filas lanza a mitad de camino
El destructor de TXLSDirectWriter termina el paquete cuando nunca se llamó a Close, así que un Close olvidado no deja un zip a medio escribir tirado por ahí. Como termina lo que sea que el escritor tenga, una excepción que se desenrolla a través de Free bajo la sobrecarga in situ igualmente confirma las filas escritas hasta ese momento y las intercambia sobre el original. Un bucle que trae y escribe filas, lanza a mitad y libera el escritor en un bloque finally acaba exactamente así
Para cualquier cosa que corre desatendida, escribe a un destino explícito, abre el resultado, compruébalo y solo entonces reemplaza tú el archivo en vivo. Una excepción del bucle de filas todavía deja que Free termine el archivo preparado, pero también se salta el intercambio, así que el libro en vivo conserva las cifras de ayer en lugar de la mitad de las de hoy
uses
Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;
procedure RefreshDashboard;
const
Live = 'monthly-dashboard.xlsx';
Staged = 'monthly-dashboard.next.xlsx';
var
W: TXLSDirectWriter;
Check: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch(Live, Staged, 'Data', False);
W.AddSheet('Data');
WriteRefreshedRows(W); // tu propio bucle de filas, no forma parte de HotXLS
W.Close;
finally
W.Free;
end;
Check := TXLSXWorkbook.Create;
try
if Check.Open(Staged) <> 1 then
raise Exception.Create('Staged workbook does not open');
Sheet := Check.Sheets.SheetByName('Data');
if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
raise Exception.Create('Data sheet did not come back as written');
finally
Check.Free;
end;
// API de Windows, no HotXLS: reemplaza el archivo en vivo solo tras las comprobaciones
if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
RaiseLastOSError;
end;
TXLSXWorkbook.Open devuelve 1 si va bien, y tanto Sheets como Cells son base 1: Check.Sheets[1] es la primera pestaña y Cells[2, 1] es A2. Más allá de un valor puntual, dos comprobaciones estructurales cazan la mayoría de los errores. Check.Sheets.Count debería igualar el recuento de la plantilla tras un reemplazo y superarlo en uno tras una inserción, y un listado del archivo con cualquier herramienta zip no debería mostrar ningún xl/calcChain.xml ni ningún nombre de entrada que aparezca dos veces. Los nombres duplicados importan porque algunos lectores se quedan con la última entrada de un nombre dado, lo que hace que dos hojas parezcan tener los contenidos intercambiados
¿Parchear, escribir en flujo o cargar el libro?
El parcheo encaja cuando el libro es grande, el cambio se limita a una hoja y el resto del archivo tiene que sobrevivir bit a bit. Es la herramienta equivocada cuando el cambio abarca varias hojas, cuando los datos nuevos necesitan formatos que la plantilla aún no define, cuando hay que crear gráficos o tablas, o cuando el archivo es lo bastante pequeño como para que una carga y guardado normales no cuesten nada digno de medir. Para generación masiva desde cero, el escritor directo en flujo encaja mejor, y como comparte las mismas llamadas AddRow y Write*, mover código entre ambos es sobre todo cuestión de cambiar BeginFile por BeginPatch
Cuando el trabajo de verdad necesita el modelo de objetos completo, el trabajo a nivel de hoja dentro de un libro cargado está cubierto en duplicar hojas de cálculo en paquetes XLSX. Y si el parcheo está sobre la mesa solo porque procesar el libro entero se ha vuelto lento, el rendimiento con libros grandes mira a dónde se va de verdad el tiempo y la memoria de carga y guardado antes de que te comprometas con un enfoque
El escritor de parches, el escritor en flujo y el modelo de objetos completo de TXLSXWorkbook vienen todos en una única biblioteca para Delphi y C++Builder; las ediciones y la lista completa de funciones están en la página del componente de hojas de cálculo para Delphi de HotXLS