HotXLS, la librería nativa de Excel para Delphi y C++Builder, guarda un libro .xls clásico de BIFF8 con prioridad de caché: TXLSWorksheet.WriteFormula le pide a TXLSWorkbook.TryGetCachedFormulaValue el valor que Excel guardó junto a cada fórmula y solo llama al evaluador cuando esa caché falta o quedó invalidada. Un libro que usted abrió y nunca tocó guarda de vuelta los mismos números, y los resultados frescos exigen una llamada explícita a Recalculate en vez de ser un efecto secundario oculto de SaveAs
El bug que obligó a sacar este contrato a la luz era vergonzosamente pequeño. Un archivo del corpus llamado nested-subtotals.xls tiene un total general en R2C4 cuyo valor en caché es 37. Ábralo con HotXLS, pida TryGetCachedFormulaValue para esa celda y obtenga 37. Guárdelo sin cambiar una sola celda, abra la copia guardada, haga la misma pregunta y obtenga 67. A la API no se le había pedido calcular nada y aun así un número del archivo se había movido exactamente 30 — y resulta que 30 es la suma de los dos subtotales de grupo, 10 y 20, que están dentro del rango que cubre el total general
¿Por qué guardar un archivo XLS cambia el valor de una fórmula?
Tienen que alinearse dos defectos independientes para que ese 37 se vuelva 67, y arreglar cualquiera de los dos por separado habría escondido el otro. El primero era estructural: el writer clásico recalculaba todas las fórmulas en cada guardado. El segundo era una comprobación de tipo que nunca podía ser verdadera para una fórmula cargada desde disco, lo que hacía que el evaluador contara dos veces las celdas SUBTOTAL anidadas. El archivo del corpus fue simplemente la primera entrada en la que un recálculo al guardar dio una respuesta distinta de la de Excel y alguien comparó las dos. El defecto estructural es fácil de enunciar: antes de la v2.382.3, TXLSWorksheet.WriteFormula y su hermano de fórmulas compartidas WriteFormulaWithTExp obtenían el campo FormulaValue de ocho bytes de cada registro Formula llamando a TXLSWorkbook.GetFormulaValue, que es el evaluador. La caché que ParseFormula había decodificado con cuidado del archivo de origen al cargar nunca se consultaba a la salida. En la práctica, cada guardado era un recálculo completo con la API de recálculo a nivel de libro puenteada, así que nada de lo que usted configurara en el libro lo habría detenido. Cualquier punto donde el evaluador de HotXLS discrepara de Excel, fuera una función legítimamente no soportada o un simple bug, se convertía en un cambio silencioso de datos al guardar
El segundo defecto vivía en el callback de subtotales anidados que usa el evaluador. Excel define todas las formas de SUBTOTAL como ignorantes de las celdas cuya propia fórmula es otro SUBTOTAL, así que la calculadora en lxCalc.pas arma FIgnoreSubtotalCells durante la agregación y le pregunta al libro, a través de TXLSWorkbook.GetClassicIsSubtotalCell, si cada celda del rango lo es. Ese callback traía el texto de la fórmula como Variant y lo probaba con VarType(f) = varOleStr. El texto vuelve de GetUnCompiledFormula como un String de Delphi, y un String asignado a un Variant es varUString, nunca varOleStr. El predicado era falso para todas las celdas de todos los archivos cargados, los subtotales de grupo se sumaban al total general una segunda vez, y en un guardado que recalculaba todo, 10 + 20 + 7 se volvía 67
// HotXLS 2.381 y anteriores: un Variant de fórmula construido desde un String
// es varUString, así que esta comparación nunca daba verdadero
Result := (VarType(f) = varOleStr) and
(SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
SameText(Copy(f, 1, 10), '=SUBTOTAL('));
// HotXLS 2.382.0: VarIsStr acepta varString, varOleStr y varUString,
// y AGGREGATE queda excluido de los subtotales que lo contienen, como hace Excel
if VarIsStr(f) then
Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
SameText(Copy(f, 1, 10), 'AGGREGATE(') or
SameText(Copy(f, 1, 11), '=AGGREGATE(');La v2.382.0 publicó el fix de VarIsStr y, ya que estaba en la misma función, le enseñó al callback que las celdas AGGREGATE también quedan excluidas de los subtotales que las contienen. Eso solo ya hizo pasar la aserción del corpus, porque el 37 recalculado ahora coincidía con el 37 cargado. No hizo honesta a la librería: el guardado seguía recalculando, y la prueba solo daba verde porque el evaluador coincidía con Excel en ese archivo en particular. Las reglas de qué celdas se saltan SUBTOTAL y AGGREGATE, incluidas las filas ocultas, están en el artículo sobre filas ocultas en SUBTOTAL y AGGREGATE; lo que importa aquí es que ningún evaluador debería tener voto en un archivo que usted no le pidió calcular
¿Qué garantiza Excel sobre los valores en caché al guardar?
Excel trata un guardado como una foto, no como un evento de cálculo. El valor que se escribe en el campo FormulaValue de un registro Formula ([MS-XLS] §2.4.127, diseño en §2.5.133) es lo que la celda muestra en ese momento, que en modo de cálculo manual puede llevar años obsoleto, y Excel igual lo escribe tal cual. El recálculo es una operación aparte, con su propio disparador. HotXLS ahora sigue la misma regla en los guardados clásicos: WriteFormula y WriteFormulaWithTExp llaman primero a TryGetCachedFormulaValue, toman CacheInfo.Value cuando el estado es xlfcsLoaded o xlfcsCalculated, y solo caen a GetFormulaValue para xlfcsMissing y xlfcsInvalidated. La mitad de lectura de este contrato, incluido qué significa cada estado y por qué un blanco o un False en caché igual cuentan como valor, está descrita en Leer valores de fórmulas en caché de Excel en Delphi sin recalcular
El camino de respaldo se conserva a propósito, no se eliminó. Una fórmula que usted asignó en esta sesión a través de Cells[Row, Col].Formula llega sin caché, y una fórmula que reemplazó sobre una celda cargada queda marcada xlfcsInvalidated por _SetCompiledFormula; ambas se evalúan al guardar exactamente como antes, así que un libro generado igual abre en Excel con números. Cuando ni el evaluador puede producir un valor, el writer emite un payload en cero y activa fAlwaysCalc (bit 0 de grbit en §2.4.127) para que Excel recalcule la celda al abrir en vez de confiar en el marcador de posición
procedure RoundTripWithoutRecalc(const Source, Target: string);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
Book.Open(Source);
// hoja, fila y columna con base 1: R2C4 en la primera hoja
if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
raise Exception.Create('R2C4 carries no usable cache');
Book.SaveAs(Target); // ningún evaluador involucrado para celdas en caché
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, 2, 4, After);
// Before.Value = After.Value = 37 para nested-subtotals.xls
// Un guardado que recalculara habría escrito 67 aquí
finally
Book.Free;
end;
end;¿Dónde guarda su valor en caché la raíz de una fórmula compartida de BIFF?
En su propio registro Formula, como cualquier otra celda con fórmula, y eso es justo lo que hizo de la celda raíz de un grupo compartido el único punto donde el guardado con caché primero seguía perdiendo. Una fórmula compartida en BIFF8 se guarda como un registro ShrFmla ([MS-XLS] §2.4.260) que sigue al registro Formula de la celda superior izquierda, y cada celda miembro, la raíz incluida, lleva un rgce formado por un solo token PtgExp (§2.5.198): el primer byte de la expresión parseada es $01, seguido de la fila y la columna de la celda raíz. Las celdas seguidoras son autosuficientes — HotXLS lee el FormulaValue de cada una y resuelve la expresión buscando la fórmula compilada de la raíz. La celda raíz es distinta, porque cuando se parsea su registro Formula la expresión todavía no existe; llega un registro después
Ese hueco de un registro es donde se fue la caché. TXLSReader.ParseFormula decodifica el valor en caché y, al ver un PtgExp cuyas coordenadas son las de la propia celda, se acuerda de la celda en FSharedFormulaRow y FSharedFormulaCol y publica la caché en la celda. Cuando llega el registro ShrFmla ($04BC), ParseSharedFormula compila la expresión y la instala con _SetCompiledFormula, y _SetCompiledFormula hace lo que tiene que hacer ante cualquier cambio de fórmula: limpia FCachedFormulaValue y devuelve el estado a xlfcsMissing. El 37 cargado de la raíz se tiraba entonces antes de que nadie pudiera leerlo, TryGetCachedFormulaValue reportaba la raíz como sin caché, y el writer de caché primero caía obedientemente al evaluador justo para la celda que todo el mundo estaba mirando. El registro Array (§2.4.4) comparte el mismo orden y tenía el mismo agujero
El fix de la v2.382.3 añade un tercer campo, FSharedFormulaCachedValue, junto a las coordenadas pendientes de la raíz. ParseFormula deja ahí la caché decodificada cuando reconoce una raíz, y tanto ParseSharedFormula como ParseArrayFormula la reproducen a través de _SetCellCachedFormulaValue justo después de instalar la expresión compilada, y después reinician el guardado temporal a Unassigned. La variante String de la caché no se ve afectada por nada de esto, porque su payload llega en un registro String aparte y se enruta por coordenadas de celda, no por orden de registros. Si usted trabaja con el lado OOXML del mismo concepto, el artículo sobre la expansión si de fórmulas compartidas en XLSX explica por qué el formato de paquete no tiene ese problema de orden pero sí sus propias trampas de expansión
¿Por qué las seguidoras de una fórmula compartida necesitan un desplazamiento relativo?
Porque la expresión guardada en ShrFmla está escrita con referencias relativas a la celda raíz, y una seguidora que la reutiliza tal cual evalúa las referencias de la raíz en lugar de las suyas. El reader viejo instalaba Value.GetCopy() en cada seguidora, una copia profunda sin desplazamiento, así que un grupo con raíz en B1 con =A1*3 le daba =A1*3 también a todas las seguidoras. El guardado con caché primero de hecho enmascaraba esto en archivos cargados, porque las seguidoras tenían su propio FormulaValue y nunca necesitaban la expresión para guardarse bien; salía a la superficie en cuanto algo recalculaba. El reader ahora instala TXLSCompiledFormula.GetCopy(row - srow, col - scol), que recorre el árbol de sintaxis y desplaza cada referencia relativa por la distancia de la seguidora a la raíz, así que la seguidora en B2 tiene un =A2*3 de verdad
La prueba de regresión que fija ambos comportamientos vale la pena leerla, porque se niega a dejar pasar una coincidencia. Arma un libro con =A1*3 y =A2*3 sobre las entradas 2 y 4, y después inyecta las cachés deliberadamente equivocadas 999 y 888 mediante _SetCellCachedFormulaValue, una vez con UseSharedFormulas activado y otra desactivado. Tras guardar y recargar, ambas celdas deben seguir reportando 999 y 888 — prueba de que el guardado no tocó ni la caché de la raíz ni la de la seguidora. Solo después de un Recalculate explícito deben pasar a ser 6 y 12, prueba de que la expresión desplazada de la seguidora es correcta. Una prueba que sembrara los valores verdaderos también habría pasado con el writer viejo, que es justamente el motivo de sembrar los equivocados
var
Book: TXLSWorkbook;
Info: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('quarterly-model.xls');
Book.Sheets[1].Cells[1, 1].Value := 5; // cambiar una entrada
// Las cachés cargadas de fórmulas dependientes NO se invalidan con una
// edición de literal, así que un SaveAs normal conservaría los números viejos.
// Pida un recálculo cuando de verdad quiera resultados frescos:
Book.Recalculate;
if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
Writeln('B1 now ', VarToStr(Info.Value),
', state ordinal ', Ord(Info.State)); // xlfcsCalculated
Book.SaveAs('quarterly-model-updated.xls');
finally
Book.Free;
end;
end;Lo que el contrato de caché primero no hace por usted
El guardado con caché primero conserva lo que se cargó; no sigue la pista de si lo cargado sigue siendo verdad. Cambiar un literal del que depende una fórmula marca sucio el grafo de dependencias para el evaluador, pero deja en pie la caché xlfcsLoaded de la celda dependiente, y el writer clásico escribirá ese valor obsoleto tan contento salvo que usted llame a Recalculate o lea primero el Value de la celda, lo que lo calcula y mueve el estado a xlfcsCalculated. Es el mismo intercambio que hace Excel en modo de cálculo manual, y es el correcto para un pipeline que abre archivos de terceros, edita unas cuantas etiquetas y guarda — pero significa que un libro que edita entradas debe hacerse cargo de su paso de recálculo de forma explícita. La política RecalcBeforeSave del writer de XLSX no cambia con este trabajo y tiene su propio modo manual que preserva las cachés en el mismo espíritu. De ahí se siguen dos límites más pequeños: el camino de caché primero solo ayuda a las celdas cuyo estado es xlfcsLoaded o xlfcsCalculated; un generador que escribe fórmulas y nunca las evalúa sigue pagando una evaluación por celda al guardar, exactamente como antes. Y el fix de subtotales anidados corrige qué celdas se salta el evaluador, no todas las funciones que el evaluador implementa — un archivo cuyas fórmulas HotXLS no puede calcular igual que Excel ahora es seguro para round-trip sin tocarlo, pero un Recalculate deliberado sobre ese archivo seguirá produciendo la respuesta de la librería y no la de Excel, y conviene que usted compare las dos antes de confiar en un guardado recalculado
Los guardados clásicos con caché primero, las cachés restauradas de raíces de fórmulas compartidas y de matriz, el desplazamiento de referencias relativas para las seguidoras compartidas y las reglas corregidas de anidamiento de SUBTOTAL y AGGREGATE vienen todos en el HotXLS Delphi Spreadsheet Component estándar para Delphi y C++Builder, sin depender de Excel ni de ningún servidor de automatización OLE; la página de producto trae la referencia completa de API para el libro, el lector de caché y los puntos de entrada de recálculo que se usan aquí