El método AddCopy de HotXLS copia una hoja de cálculo de un libro de Excel a otro descompilando cada fórmula de esa hoja en texto de 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áfico, 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 uno esperaría: un trabajo de fin de mes que extrae una hoja del informe de cada sucursal y la agrega a un archivo resumen. Abra el resultado y un gráfico de subtotal traza números de una sucursal completamente distinta, una nota que era negrita y roja en el origen vuelve a ser texto negro plano, y una fórmula que antes extraía una tasa de impuesto de un libro de búsqueda complementario ahora muestra un número congelado que nadie puede explicar. Nada lanza una excepción aquí: el archivo abre, los números se ven plausibles, y el daño permanece ahí hasta que alguien nota un gráfico con el título equivocado sentado junto a él
¿Por qué AddCopy no puede 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 mantiene 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 manera en que ese libro particular haya registrado sus hojas y libros externos. Mueva 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 cualquier 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. Esto es exactamente el fallo que TXLSWorksheets.AddCopy existe para evitar: llamado desde la propia colección de hojas de cualquiera de los 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— de un libro de origen que puede o no ser aquel sobre el que se lo está llamando, y agrega el resultado al destino bajo un nombre que usted elija o una copia con nombre desambiguado del original
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 árbol compilado mismo cruce el límite del libro. Para cada celda de fórmula en una copia entre libros, AddCopy descompila la fórmula de origen al mismo texto de 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 punto, 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 crudo 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 ruta más barata donde el árbol compilado simplemente se duplica en memoria, ya que cada índice dentro de él ya es válido donde se está quedando, y el desvío por texto solo se ejecuta una vez que AddCopy detecta que el origen y el destino son genuinamente instancias de libro distintas. Vale la pena ser preciso sobre lo que esta reescritura no es, también. No tiene nada que ver con el desplazamiento de filas y columnas que se ejecuta cuando se insertan o eliminan filas dentro de una sola hoja, que un artículo complementario cubre en detalle —ese motor reescribe el texto A1 en el mismo lugar para rastrear celdas que se movieron algunas filas arriba o abajo dentro de un libro, mientras que este se ejecuta cuando una fórmula sale por completo del 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);¿Qué pasa 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 a lo que se refiere el texto de la fórmula, y las dos brechas 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 alcance 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 una copia de hoja —la asignación Value de la celda silenciosamente almacena el texto de la fórmula como una cadena simple en su lugar, un modo de fallo deliberado e inspeccionable en lugar de uno 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 aguas arriba en la copia no se resolvió. Antes de rendirse, AddCopy intenta una reparación: recorre el árbol de sintaxis de la fórmula fallida recolectando cada ID de nombre definido que la fórmula toca, y para cada nombre con alcance de libro que existe en el origen pero todavía no en el destino, copia el nombre y recompila el mismo texto una segunda vez. Los nombres con alcance de hoja quedan fuera de lo que esta reparación puede arreglar, ya que un nombre visible solo para fórmulas en una hoja del libro de origen no tiene una ranura equivalente a la cual migrar, y un destino que ya posee un nombre con la misma ortografía se deja intacto en lugar de sobrescribirse, bajo el supuesto de que un nombre que quien llama precreó deliberadamente es el que quiere que se respete. Dentro de un solo libro, la búsqueda de nombre de una fórmula entre hojas recorre desde el alcance de hoja hasta el alcance de libro automáticamente, que es el mecanismo que cubre el artículo de HotXLS sobre nombres definidos y fórmulas entre hojas; cruzar un límite de libro real elimina esa red de seguridad por completo, y un nombre tiene que llevarse deliberadamente de un lado a otro o la fórmula que depende de él se degrada a texto
Las referencias de serie de gráfico necesitan la misma corrección, pero una ruta de código distinta
Una serie de gráfico de HotXLS que traza un rango de celdas se topa precisamente 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 compilado —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 ruta normal de carga de gráfico, porque esa ruta es exactamente lo que crea el bug. 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 crudos a través de cualquier instancia de calculadora que esté haciendo el análisis; alimente los bytes BRAI crudos de un gráfico de origen a través del propio cargador de registros ordinario 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 silenciosamente apunta a cualquier 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 de la forma en que lee las fórmulas de celda. HotXLS evita la trampa con una ruta de clonación dedicada en su lugar: TXLSCustomChart.AssignFrom copia los bytes de encabezado no relacionados con fórmula de cada registro de gráfico textualmente, y luego reconstruye el rango adjunto mediante la misma primitiva de descompilar-y-recompilar usada para celdas ordinarias, así que el nuevo árbol 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 a la vez
No todo número local del libro dentro de un gráfico o una celda de texto enriquecido es una fórmula, y un índice de fuente es la misma clase de problema en miniatura. Las ejecuciones de texto enriquecido, junto con dos tipos más de registro de gráfico que llevan una fuente de leyenda o eje, almacenan una referencia de fuente como un entero crudo, un índice 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 a una tipografía, tamaño, o color completamente distintos allí. 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 de formato hace que la propia búsqueda sea delicada —el índice numerado en archivo se 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 posición hacia abajo antes de comparar fuentes y una posición hacia arriba 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é le pasa a una fórmula que ya apunta fuera del libro?
Una fórmula que llega hasta un tercer libro antes de que se llame a AddCopy siquiera es el único caso que el viaje de ida y vuelta por texto no puede transportar, porque el propio descompilador de fórmula a texto de HotXLS deliberadamente no sintetiza texto de corchetes [Book]Sheet! para una referencia externa, y el compilador del otro lado tampoco acepta esa sintaxis como entrada —así que este único caso se ejecuta a través de un segundo mecanismo que nunca toca texto en absoluto. Cuando la reparación de migración de nombres descrita arriba todavía 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 reenlace dedicada, RebindExternRefsInTree, que lo recorre nodo por 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 empaqueta tres coordenadas separadas en un solo campo y cada una de ellas es privada del libro que la escribió: cuál libro externo, una ranura en la propia lista de libros externos del destino asignada en cualquier orden en que ese libro haya registrado dichos libros; cuál hoja dentro de la propia lista de hojas de ese libro externo, almacenada como un índice basado en 1 acotado específicamente a ese libro externo, un dominio de numeración completamente distinto de los propios IDs de hoja internos del destino; y el rango de celdas en sí, coordenadas de fila y columna simples que no necesitan traducción porque nunca fueron relativas al libro en primer lugar. Equivoque 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 quejarse. Un tipo de nodo derrota incluso este reenlace a nivel de árbol: una referencia a un nombre definido, un índice en la propia tabla de nombres privada de su libro exactamente de la misma manera 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 reenlace se encuentra con una referencia de nombre en cualquier parte del árbol, abandona la fórmula entera en lugar de escribir una parcialmente correcta. Incluso cuando el reenlace 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é de la misma manera en que el propio Excel almacena en caché el último valor conocido de cualquier referencia externa hasta que se actualizan los enlaces explícitamente, que es el valor predeterminado correcto, ya que recalcular a través de un enlace en vivo hacia otro archivo es exactamente el tipo de operación que se quiere disparar una vez, deliberadamente, no en cada apertura
Lo que cuesta este diseño
La maquinaria de descompilar-y-recompilar de AddCopy no es gratuita, y el costo vale la pena planificarlo antes de programar un trabajo de consolidación grande, no después. Copiar una hoja dentro del mismo libro toma la ruta barata, una duplicación directa en memoria del árbol compilado, porque cada índice dentro de él ya es válido en el libro donde se está quedando; una copia entre libros paga por un análisis genuino en cada celda de fórmula en su lugar, descompilar a texto y luego compilar ese texto de nuevo desde nada, y aunque la diferencia no vale 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 a su alrededor. El orden de copia importa por una segunda razón más allá de la velocidad: una fórmula que referencia una hoja a la que AddCopy todavía no ha llegado en este lote falla su recompilación por la misma razón que lo hace una fórmula que referencia 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á esa fórmula degradarse exactamente como se describió arriba, texto de cadena o un respaldo de enlace externo apuntando directamente de vuelta al archivo de origen del que acaba de venir. Y como cada libro de origen en un lote de consolidación normalmente se crea de forma independiente, vale la pena probar explícitamente el único modo de fallo del que ningún archivo de origen individual podría haber advertido: cinco libros de sucursal que cada uno totaliza los números de una sucursal par 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 lugar y el recálculo se ejecuta sobre el conjunto combinado
La copia de hoja de cálculo entre libros se incluye como comportamiento estándar de AddCopy en el componente Excel HotXLS para Delphi para Delphi y C++Builder; la página del producto lleva la referencia completa de la API de hoja de cálculo y libro, incluido el comportamiento de gráfico, texto enriquecido, y referencia externa descrito aquí