El método AddCopy de HotXLS copia una hoja de un libro de Excel a otro descompilando cada fórmula de esa hoja a texto estilo A1 y recompilando el texto dentro del libro de destino, en lugar de copiar directamente el árbol de fórmula compilado, porque las referencias de serie de gráficos, los índices de fuente de texto enriquecido y la numeración de enlaces externos se asignan todos de forma independiente dentro de cada archivo de libro
El fallo aparece exactamente en el libro que cabría esperar: un trabajo de fin de mes que extrae una hoja del informe de cada oficina de sucursal y la añade a un archivo resumen. Abrid el resultado y un gráfico de subtotales representa las cifras de una sucursal completamente distinta, una nota que era negrita y roja en el origen vuelve a ser texto plano negro, y una fórmula que antes extraía un tipo impositivo de un libro de búsqueda complementario ahora muestra un número congelado que nadie puede explicar. Nada lanza aquí una excepción, el archivo se abre, los números parecen plausibles, y el daño permanece ahí hasta que alguien nota un gráfico con el título equivocado junto a él
¿Por qué no puede AddCopy simplemente copiar el árbol de fórmula compilado?
AddCopy no puede mover el árbol de fórmula compilado sin cambios, porque una fórmula BIFF compilada no es texto independiente, es una secuencia de tokens, y varios de esos tokens son enteros pequeños que solo se resuelven correctamente dentro del libro que los produjo. Una referencia 3D como Sheet2!A1:A10 no lleva el nombre literal Sheet2 una vez compilada; lleva un campo que la especificación BIFF llama ixti (HotXLS conserva el mismo valor en su propio árbol compilado bajo el nombre de campo FExternID), un índice en la tabla EXTERNSHEET privada de ese libro, numerado de la forma en que ese libro concreto haya registrado sus hojas y libros externos. Mover el token sin cambios a un libro cuya tabla EXTERNSHEET se construyó en un orden distinto y el índice 3 ya no significa Sheet2, significa cualquiera que sea la hoja que ocupe la ranura 3 allí, y Excel no tiene forma de señalar el error, porque en lo que respecta al formato de archivo la fórmula está perfectamente bien formada. Este es exactamente el fallo que TXLSWorksheets.AddCopy existe para evitar: llamado desde la propia colección de hojas de cualquiera de los dos libros en código Delphi o C++Builder, copia una hoja de cálculo, valores de celda, formatos, fórmulas, gráficos, comentarios, combinaciones, configuración de página y más, desde un libro de origen que puede o no ser aquel sobre el que lo estáis llamando, y añade el resultado al destino con un nombre que elijáis o una copia del original con el nombre desambiguado
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;La solución: descompilar a texto, recompilar en el destino
HotXLS resuelve el problema de indexación no dejando nunca que el propio árbol compilado cruce la frontera del libro. Para cada celda de fórmula en una copia entre libros, AddCopy descompila la fórmula de origen al mismo texto estilo A1 que un usuario vería en la barra de fórmulas de Excel, y luego entrega ese texto al libro de destino, que lo analiza de vuelta a un árbol usando sus propias tablas desde cero, una referencia calificada por hoja como Data!D2:D100 es solo una cadena en ese momento, y una cadena significa lo mismo en cualquier libro, así que si el destino ya tiene una hoja llamada Data la referencia se resuelve correctamente sin ninguna traducción de índice en absoluto, porque nunca hubo un índice en bruto en tránsito que traducir. HotXLS solo paga por este viaje de ida y vuelta cuando tiene que hacerlo: copiar una hoja dentro del mismo libro toma una vía más barata donde el árbol compilado simplemente se duplica en memoria, ya que cada índice dentro de él ya es válido donde permanece, y el desvío por texto solo se ejecuta en cuanto AddCopy detecta que el origen y el destino son de verdad instancias de libro distintas. También merece la pena precisar qué no es esta reescritura. No tiene nada que ver con el desplazamiento de filas y columnas que se ejecuta al insertar o eliminar filas dentro de una única hoja, que un artículo complementario cubre en detalle: ese motor reescribe el texto A1 in situ para rastrear celdas que se movieron unas cuantas filas arriba o abajo dentro de un mismo libro, mientras que este se ejecuta cuando una fórmula abandona por completo el libro que la compiló, donde las filas movidas no son el problema y la numeración privada del libro sí lo es
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);¿Y si el destino todavía no tiene esa hoja, o ese nombre?
La recompilación de AddCopy solo tiene éxito cuando el libro de destino ya tiene todo aquello a lo que hace referencia el texto de la fórmula, y las dos lagunas que aparecen en la práctica son una hoja con el mismo nombre que todavía no se ha copiado en este lote, y un nombre definido con ámbito de libro que nunca ha existido en el destino en absoluto. HotXLS no lanza una excepción cuando la recompilación falla a mitad de la copia de una hoja, la asignación Value de la celda simplemente almacena el texto de la fórmula como una cadena plana en su lugar, un modo de fallo deliberado e inspeccionable en lugar de silencioso, ya que una celda de fórmula que inesperadamente muestra texto literal como =SUM(Q1!B2:B12) en lugar de un número calculado es la señal de que algo en la copia, aguas arriba, no se resolvió. Antes de rendirse, AddCopy intenta una reparación: recorre el árbol de sintaxis de la fórmula fallida recopilando cada identificador de nombre definido que toca la fórmula, y para cada nombre con ámbito de libro que exista en el origen pero todavía no en el destino, copia el nombre y recompila el mismo texto una segunda vez. Los nombres con ámbito de hoja quedan fuera de lo que esta reparación puede arreglar, ya que un nombre visible solo para las fórmulas de una hoja del libro de origen no tiene una ranura equivalente a la que migrar, y un destino que ya posee un nombre con la misma grafía se deja intacto en lugar de sobrescribirse, bajo la suposición de que un nombre que quien llama precreó deliberadamente es el que quiere que se respete. Dentro de un único libro, la búsqueda de nombre de una fórmula entre hojas recorre desde el ámbito de hoja hasta el ámbito de libro automáticamente, que es el mecanismo que cubre el artículo de HotXLS sobre nombres definidos y fórmulas entre hojas; cruzar una frontera de libro real elimina por completo esa red de seguridad, y un nombre tiene que trasladarse deliberadamente o la fórmula que depende de él se degrada a texto
Las referencias de serie de gráfico necesitan la misma solución, pero una vía de código distinta
Una serie de gráfico de HotXLS que representa un rango de celdas topa exactamente con el mismo problema de numeración que una fórmula de celda ordinaria, porque la referencia de rango de datos de un gráfico también es un flujo de tokens de fórmula compilada, la especificación BIFF llama al registro que la lleva BRAI ([MS-XLS] sección 2.4.51), pero AddCopy no puede arreglarlo reutilizando la vía normal de carga de gráficos, porque esa vía es exactamente lo que crea el fallo. Cuando un registro de gráfico se analiza desde disco en el curso ordinario de abrir un archivo, su árbol de fórmula se construye traduciendo los bytes en bruto a través de cualquiera que sea la instancia de calculadora que esté haciendo el análisis; alimentad los bytes BRAI en bruto de un gráfico de origen a través del cargador de registros ordinario propio del libro de destino en su lugar, y el ixti incrustado en esos bytes se resuelve contra la tabla EXTERNSHEET del destino, así que la serie apunta silenciosamente a cualquiera que sea la hoja que ocupe esa ranura allí, la misma clase de error que copiar el árbol compilado de una celda sin cambios, solo que más difícil de notar porque nadie lee las fórmulas de serie de gráfico del modo en que lee las fórmulas de celda. HotXLS evita la trampa con una vía de clonación dedicada en su lugar: TXLSCustomChart.AssignFrom copia los bytes de cabecera propios sin fórmula de cada registro de gráfico literalmente, y luego reconstruye el rango adjunto mediante la misma primitiva de descompilar y recompilar usada para las celdas ordinarias, así que el árbol nuevo se construye contra la tabla EXTERNSHEET del destino desde cero en lugar de reinterpretarse contra ella después del hecho
El mismo problema de numeración, un índice de fuente cada vez
No todo número local de un libro dentro de un gráfico o de una celda de texto enriquecido es una fórmula, y un índice de fuente es la misma clase de problema en miniatura. Las tiradas de texto enriquecido, junto con otros dos tipos de registro de gráfico que llevan una fuente de título o de eje, almacenan una referencia de fuente como un índice entero en bruto en la propia tabla de fuentes del libro propietario, y ese índice no significa nada en la tabla de un libro distinto, podría igual de fácilmente apuntar allí a una tipografía, tamaño o color completamente diferentes. HotXLS resuelve esto por valor en lugar de por número: busca los atributos de fuente reales en ese índice en la tabla de origen, encuentra o crea una entrada coincidente en la tabla de fuentes del destino, y reescribe el índice almacenado para que apunte a esa nueva ranura. Una peculiaridad del formato hace que la propia búsqueda sea delicada: el índice numerado en archivo salta la ranura 4, un hueco de numeración que documenta [MS-XLS] sección 2.5.339, así que el código tiene que desplazar el índice una unidad hacia abajo antes de comparar fuentes y una unidad hacia arriba de nuevo antes de escribir el resultado
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);¿Qué ocurre con una fórmula que ya apunta fuera del libro?
Una fórmula que alcanza un tercer libro incluso antes de que llaméis jamás a AddCopy es el único caso que el viaje de ida y vuelta por texto no puede llevar, porque el propio descompilador de fórmula a texto de HotXLS deliberadamente no sintetiza el texto entre corchetes [Book]Sheet! para una referencia externa, y el compilador del otro extremo tampoco acepta esa sintaxis como entrada, así que este único caso pasa por un segundo mecanismo que nunca toca texto en absoluto. Cuando la reparación de migración de nombres descrita arriba aun así deja una celda como cadena, y el libro de origen tiene un nombre de archivo real, AddCopy cambia de estrategia: copia en profundidad el propio árbol de fórmula compilado en lugar de su texto, y luego entrega la copia a una pasada de reenlazado dedicada, RebindExternRefsInTree, que la recorre nodo a nodo. Para cada referencia de rango que encuentra, esa pasada resuelve la entrada EXTERNSHEET del origen de vuelta a un par de nombres de hoja, y registra, o reutiliza, una entrada equivalente en las propias tablas de referencia externa del destino, creando un enlace de libro externo completamente nuevo si el destino nunca antes había referenciado ese archivo de origen
Aquí es donde el problema de numeración local del libro es más literal, porque un token de referencia externa agrupa tres coordenadas independientes en un único campo y cada una de ellas es privada del libro que la escribió: qué libro externo, una ranura en la propia lista del destino de libros externos asignada en cualquiera que sea el orden en que ese libro haya registrado los suyos; qué hoja dentro de la propia lista de hojas de ese libro externo, almacenada como un índice basado en 1 con ámbito específico del libro externo, un dominio de numeración completamente distinto de los propios identificadores internos de hoja del destino; y el propio rango de celdas, simples coordenadas de fila y columna que no necesitan traducción porque nunca fueron relativas al libro en primer lugar. Equivocaos en cualquiera de las dos primeras y Excel sigue abriendo el archivo, sigue mostrando una fórmula, y la evalúa contra las celdas externas equivocadas sin protestar. Un tipo de nodo derrota incluso este reenlazado a nivel de árbol: una referencia a un nombre definido, un índice en la propia tabla de nombres privada de su libro exactamente del mismo modo en que un índice de hoja es privado de su propio EXTERNSHEET, sin ninguna reparación equivalente a nivel de árbol disponible, en el momento en que el recorrido de reenlazado se topa con una referencia de nombre en cualquier punto del árbol, abandona la fórmula entera en lugar de escribir una parcialmente correcta. Incluso cuando el reenlazado sí tiene éxito, la celda de destino no muestra un número recién recalculado; muestra el valor que la celda de origen ya tenía en el momento de la copia, conservado en una ranura en caché del mismo modo en que el propio Excel almacena en caché el último valor conocido de cualquier referencia externa hasta que actualizáis los enlaces explícitamente, que es el comportamiento por defecto correcto, ya que recalcular a través de un enlace vivo a otro archivo es exactamente el tipo de operación que queréis disparar una vez, deliberadamente, no en cada apertura
Qué os cuesta este diseño
La maquinaria de descompilar y recompilar de AddCopy no es gratis, y el coste merece planificarse antes de programar un gran trabajo de consolidación, no después. Copiar una hoja dentro del mismo libro toma la vía barata, una duplicación directa en memoria del árbol compilado, porque cada índice dentro de él ya es válido en el libro donde permanece; una copia entre libros paga en cambio por un análisis genuino en cada celda de fórmula, descompilar a texto y luego compilar ese texto de nuevo desde nada, y aunque la diferencia no merece la pena medirla en una hoja con unas pocas docenas de fórmulas, un libro de origen con decenas de miles de celdas de fórmula, copiado como una hoja entre docenas en un trabajo por lotes, debería esperar que la recompilación domine el tiempo de ejecución en lugar de la E/S de archivo que lo rodea. El orden de copia importa por una segunda razón más allá de la velocidad: una fórmula que hace referencia a una hoja a la que AddCopy todavía no ha llegado en este lote falla su recompilación por la misma razón por la que lo hace una fórmula que hace referencia a una hoja genuinamente inexistente, así que un trabajo que copia la hoja B antes que la fórmula de la hoja A que depende de ella verá que esa fórmula se degrada exactamente como se describió arriba, texto de cadena o un respaldo de enlace externo que apunta directamente de vuelta al archivo de origen del que acaba de venir. Y como cada libro de origen en un lote de consolidación suele estar creado de forma independiente, merece la pena probar explícitamente el único modo de fallo del que ningún archivo de origen individual podría haberos advertido jamás: cinco libros de sucursal que cada uno totaliza las cifras de una sucursal homóloga pueden combinarse en una referencia circular genuina dentro del libro resumen sin que ningún archivo de origen individual contenga jamás una, un ciclo que solo existe una vez que cada hoja ha aterrizado en el mismo sitio y el recálculo se ejecuta sobre el conjunto combinado
La copia de hojas de cálculo entre libros se distribuye como comportamiento estándar de AddCopy en el componente Excel HotXLS para Delphi para Delphi y C++Builder; la página del producto recoge la referencia completa de la API de hojas de cálculo y libros, incluido el comportamiento de gráficos, texto enriquecido y referencias externas descrito aquí