Un libro de trabajo 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 formato de paquete mismo. 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 propio método de compresión, CRC-32 y tamaño comprimido en el encabezado local y otra vez en el directorio central. Una entrada puede entonces moverse de un archivo a otro sin jamás desinflarse, siempre que quien copia escriba esos tres valores junto con los bytes crudos. La caché de tabla dinámica no necesita entenderse para preservarse. Solo necesita copiarse
¿Por qué copiar bytes comprimidos en lugar de volver a guardar el libro de trabajo?
Copiar a nivel comprimido convierte la mayor parte del parche en una simple copia de stream. En HotXLS, TXLSDirectWriter.BeginPatch abre el paquete fuente en solo lectura y le entrega a TZipArchive.CopyCompressedFrom cada entrada que el parche deja tranquila, la cual escribe los bytes comprimidos, el CRC y los tamaños de la entrada fuente directo en el archivo destino. Una ruta de escritura convencional paga un inflate a la entrada y un deflate a la salida, y el deflate es la mitad cara. En un libro de trabajo cuyo bulto es una caché de tabla dinámica grande más unas cuantas docenas de imágenes incrustadas, el tiempo se va en la hoja que usted de verdad está 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 otro método de compresión igual se lleva: el escritor la lee a través de un stream descompresor y la escribe de vuelta, así que la parte aterriza en el destino de todas formas. Las entradas marcadoras de directorio no cargan contenido y se saltan, ya que el escritor produce las suyas propias. Un puñado de entradas jamás se copia, porque se regeneran o se editan: la hoja de cálculo destino 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 en el lugar con BeginPatch
La sobrecarga de dos argumentos BeginPatch(FileName, SheetName) reemplaza una hoja de cálculo existente y escribe el resultado de vuelta a la misma ruta. Resuelve el nombre a través del registro de hojas en xl/workbook.xml y el mapa de relaciones que ata cada hoja con su parte, compara nombres sin distinguir mayúsculas, y lanza una excepción que contiene "worksheet not found" cuando nada coincide. De ahí en adelante la hoja se escribe 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 en el lugar
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 son fáciles de malinterpretar. El nombre pasado 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') igual escribiría en Data. Y un segundo AddSheet lanza excepción, porque un parche escribe exactamente una hoja de cálculo. 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 de igualdad. WriteFormula coloca su texto directo en el elemento <f> de la celda, y ECMA-376 Parte 1 §18.3.1.40 guarda las fórmulas ahí sin el "=" que Excel muestra en la barra de fórmulas; escribir '=SUM(B2:B3)' mete un signo de igualdad literal en el XML. WriteFormula tampoco emite ningún 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 usted ya sabe a lo que evalúa
La sobrecarga en el lugar jamás escribe en el original mientras trabaja. El paquete parchado se construye en un archivo hermano nombrado a partir del fuente con .hxlpatch.tmp agregado. En Close el handle del fuente se libera y el archivo preparado reemplaza al original mediante un solo 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 matado antes de Close deja el original intacto y un .hxlpatch.tmp suelto a su lado, que el próximo BeginPatch sobre ese archivo borra antes de arrancar
Agregar una hoja de cálculo nueva a una copia de la plantilla
La sobrecarga de cuatro argumentos BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) deja el archivo fuente tranquilo y escribe un paquete nuevo. Con InsertSheet = False reemplaza la hoja nombrada en la copia; con True agrega una hoja de cálculo nueva bajo ese nombre y lanza "worksheet already exists" si la plantilla ya tiene una pestaña llamada así
// template.xlsx queda 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 la contabilidad del paquete se edita. El escritor escanea 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 de trabajo buscando el rId más alto, y luego toma el siguiente valor de cada uno. El elemento <sheet> nuevo entra justo antes de </sheets>, así que la pestaña nueva aparece al final. El atributo de relación reutiliza el prefijo con que el libro de trabajo fuente ata sus hojas, que usualmente es r:id pero no está obligado a serlo
Los tipos de relación también siguen al paquete fuente. Cuando las relaciones del libro de trabajo ya usan los namespaces ISO/IEC 29500 Strict bajo purl.oclc.org, la relación de la hoja de cálculo nueva se escribe con la URI de tipo Strict; un libro de trabajo Transitional recibe la Transitional. Un libro de trabajo Strict parchado por lo tanto no termina con una relación Transitional entre Strict
Estilos, cadenas y las partes que un parche deja tranquilas
Dentro de un parche, todo lo que la hoja nueva apunta debe existir ya en el paquete fuente. Las cadenas por lo tanto se escriben inline en la hoja en lugar de en xl/sharedStrings.xml, porque la tabla de cadenas compartidas cruza byte por byte y no puede crecer. Poner UseSharedStrings en True hace que AddSheet lance excepción en modo parche
La misma regla cambia lo que StyleIndex significa. En un parche es una posición en la lista cellXfs del libro de trabajo fuente (ECMA-376 Parte 1 §18.8.10), y el escritor solo verifica que esté entre 0 y 65534. No puede saber si el índice 14 existe en ese archivo en particular, así que un número equivocado va directo a la salida. La forma más confiable de encontrar el correcto es descomprimir la versión actual de la hoja y leer el atributo s de una celda que ya cargue el formato que usted quiere; ese número es la posición en cellXfs. Excel puede agregar entradas a esa lista cada vez que alguien formatea celdas en la plantilla, así que busque el índice otra vez después de cada edición de la plantilla en lugar de confiar en una constante escrita hace un año
const
// posición cellXfs de un formato de fecha en ESTA plantilla, tomada del
// atributo s="..." de una celda de fecha existente; revise 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); genera excepción en modo parche: su estilo de fecha implícito
// vive en una parte de estilos que el parche jamás escribe
end;
Todo lo que agregaría partes nuevas al paquete se rechaza. AddTable, AddChart, la familia AddImage, AddComment, AddDefinedName y AddCellStyle lanzan en cuanto se les llama, y Close verifica otra vez antes de confirmar, lo que también atrapa los formatos condicionales que fijan su propio relleno, color de fuente, negrita o itálica, ya que 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 también arranca la hoja desde cero. La parte de relaciones de la hoja vieja no se copia, así que un drawing, comentarios o hipervínculos externos adjuntos a la versión anterior quedan desadjuntados de 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 se van. Los que viven en el XML de la hoja se pueden escribir otra vez con SetColumnWidth, FreezePanes, MergeCells, AddDataValidation y AddHyperlink. Un gráfico o imagen anclado en la pestaña de datos no puede volver a través de un parche, así que mantenga los gráficos en su propia hoja y apunte 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; después de un reemplazo sus entradas describen celdas que ya no existen en esa forma, y después de una inserción los índices de hoja a los que se refiere se han corrido. La parte se deja fuera de la copia, y su relación y su Override en [Content_Types].xml se eliminan, así que el paquete no referencia una parte faltante. Excel reconstruye la cadena la próxima vez que recalcule
Cuando el ciclo de filas lanza a mitad de camino
El destructor de TXLSDirectWriter termina el paquete cuando Close jamás fue llamado, así que un Close olvidado no deja un zip a medio escribir tirado por ahí. Como termina lo que sea que el escritor cargue, una excepción que se desenrolla a través de Free bajo la sobrecarga en el lugar igual confirma las filas escritas hasta ese momento y las cambia sobre el original. Un ciclo que busca y escribe filas, lanza a mitad de camino y libera el escritor en un bloque finally termina exactamente así
Para cualquier cosa que corre desatendida, escriba a un destino explícito, abra el resultado, verifíquelo, y solo entonces reemplace usted mismo el archivo en vivo. Una excepción del ciclo de filas igual deja que Free termine el archivo preparado, pero también se salta el intercambio, así que el libro de trabajo en vivo conserva los números de ayer en lugar de la mitad de los 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); // su propio ciclo de filas, no es 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: reemplace el archivo en vivo solo tras las verificaciones
if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
RaiseLastOSError;
end;
TXLSXWorkbook.Open devuelve 1 en caso de éxito, y tanto Sheets como Cells son con base 1: Check.Sheets[1] es la primera pestaña y Cells[2, 1] es A2. Más allá de un valor puntual, dos verificaciones estructurales atrapan la mayoría de los errores. Check.Sheets.Count debe igualar el conteo de la plantilla después de un reemplazo y superarlo en uno después de una inserción, y un listado del archivo desde 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 toman la última entrada con un nombre dado, lo que hace que dos hojas parezcan como si sus contenidos estuvieran intercambiados
¿Parche, stream o cargar el libro de trabajo?
El parche encaja cuando el libro de trabajo es grande, el cambio se limita a una hoja, y el resto del archivo tiene que sobrevivir bit por 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 medirse. Para la generación masiva desde cero, el escritor directo en flujo es el mejor ajuste, y como comparte las mismas llamadas AddRow y Write*, mover código entre los dos es mayormente 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 de trabajo cargado está cubierto en duplicación de hojas de cálculo en paquetes XLSX. Y si el parche está sobre la mesa solo porque procesar el libro de trabajo completo se volvió lento, rendimiento de libros de trabajo grandes mira a dónde se van de verdad el tiempo y la memoria de carga y guardado antes de que usted se comprometa con un enfoque
El escritor de parches, el escritor en flujo y el modelo de objetos completo de TXLSXWorkbook vienen en una sola librería 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 HotXLS para Delphi