HotXLS, la librería Excel nativa para Delphi y C++Builder, guarda un libro clásico BIFF8 .xls priorizando la caché: TXLSWorksheet.WriteFormula pregunta a TXLSWorkbook.TryGetCachedFormulaValue por el valor que Excel guardó junto a cada fórmula y solo llama al evaluador cuando esa caché falta o está invalidada. Un libro que abriste y no tocaste nunca devuelve los mismos números al guardarse, y los resultados frescos requieren 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. Ábrelo con HotXLS, pide TryGetCachedFormulaValue para esa celda, obtén 37. Guárdalo sin cambiar una sola celda, abre la copia guardada, haz la misma pregunta, obtén 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é cambia el valor de una fórmula al guardar un archivo XLS?
Para que aquel 37 se convirtiera en 67 tenían que alinearse dos defectos independientes, y arreglar solo uno habría escondido el otro. El primero era estructural: el escritor clásico recalculaba todas las fórmulas en cada guardado. El segundo era una comprobación de tipo que nunca podía ser cierta en una fórmula cargada de disco, y 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 daba 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 esmero del archivo de origen al cargar no se consultaba nunca 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 pudieras ajustar en el libro lo habría detenido. Cualquier punto donde el evaluador de HotXLS discrepara de Excel, ya fuera una función legítimamente no soportada o un bug sin más, 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 de lxCalc.pas arma FIgnoreSubtotalCells durante la agregación y pregunta al libro, a través de TXLSWorkbook.GetClassicIsSubtotalCell, si cada celda del rango es una. Ese callback obtení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 convertía en 67
// HotXLS 2.381 y anteriores: un Variant de fórmula construido desde un String
// es varUString, así que esta comparación nunca acertaba
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 se excluye de los subtotales contenedores 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 arreglo de VarIsStr y, ya que estaba en la misma función, le enseñó al callback que las celdas AGGREGATE también se excluyen de los subtotales que las contienen. Solo con eso pasó la aserción del corpus, porque el 37 recalculado ya coincidía con el 37 cargado. Pero eso no hacía honesta a la librería: el guardado seguía recalculando, y el test solo estaba en verde porque el evaluador daba la casualidad de coincidir con Excel en ese archivo concreto. Las reglas sobre 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 sobre un archivo que no le has pedido calcular
¿Qué garantiza Excel sobre los valores en caché al guardar?
Excel trata un guardado como una foto fija, 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, disposición en §2.5.133) es lo que la celda muestra en ese momento, que en modo de cálculo manual puede estar obsoleto desde hace años, y Excel lo escribe fielmente de todos modos. El recálculo es una operación aparte con su propio disparador. HotXLS sigue ahora 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 con xlfcsMissing y xlfcsInvalidated. La mitad de lectura de este contrato, incluido qué significa cada estado y por qué un vacío o un False en caché siguen contando como valor, se describe en leer valores de fórmula en caché de Excel en Delphi sin recalcular
El camino de respaldo se mantiene a propósito, no se elimina. Una fórmula que asignaste en esta sesión mediante Cells[Row, Col].Formula llega sin caché, y una fórmula que reemplazaste en una celda cargada queda marcada como xlfcsInvalidated por _SetCompiledFormula; ambas se evalúan al guardar exactamente como antes, así que un libro generado sigue abriéndose en Excel con números dentro. Cuando ni el evaluador puede producir un valor, el escritor emite una carga cero y pone fAlwaysCalc (bit 0 de grbit de §2.4.127) para que Excel recalcule la celda al abrir en vez de fiarse del valor de relleno
procedure RoundTripWithoutRecalc(const Source, Target: string);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
Book.Open(Source);
// Hoja, fila y columna 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 entra en juego para celdas con 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 BIFF?
En su propio registro Formula, como cualquier otra celda con fórmula, y eso es justo lo que convirtió a la celda raíz de un grupo compartido en el único sitio donde el guardado con prioridad de caché 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, raíz incluida, lleva un rgce formado por un único 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 autocontenidas — 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 perdió la caché. TXLSReader.ParseFormula decodifica el valor en caché y, al ver un PtgExp cuyas coordenadas coinciden con las de la propia celda, se queda con 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 debe hacer ante cualquier cambio de fórmula: limpiar FCachedFormulaValue y devolver el estado a xlfcsMissing. El 37 cargado de la raíz se tiraba, por tanto, antes de que nadie pudiera leerlo, TryGetCachedFormulaValue informaba de la raíz como sin caché, y el escritor con prioridad de caché caía obedientemente al evaluador justo para la celda que todo el mundo estaba mirando. El registro Array (§2.4.4) tiene el mismo orden y tenía el mismo agujero
El arreglo de la v2.382.3 añade un tercer campo, FSharedFormulaCachedValue, junto a las coordenadas pendientes de la raíz. ParseFormula guarda ahí la caché decodificada cuando reconoce una raíz, y tanto ParseSharedFormula como ParseArrayFormula la reproducen con _SetCellCachedFormulaValue justo después de instalar la expresión compilada, y luego reinician el guardado a Unassigned. La variante String de la caché no se ve afectada por todo esto, porque su carga llega en un registro String aparte y se encamina por coordenadas de celda, no por orden de registros. Si trabajas 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 el problema de orden equivalente pero sí sus propias trampas de expansión
¿Por qué necesitan un desplazamiento relativo las seguidoras de una fórmula compartida?
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 vez de las suyas. El lector antiguo instalaba Value.GetCopy() en cada seguidora, una copia profunda sin desplazamiento, así que un grupo con raíz en B1 con =A1*3 daba también =A1*3 a todas las seguidoras. El guardado con prioridad de caché enmascaraba esto en los archivos cargados, porque las seguidoras tenían su propio FormulaValue y no necesitaban la expresión para guardarse bien; salía a la luz en cuanto algo recalculaba. El lector instala ahora TXLSCompiledFormula.GetCopy(row - srow, col - scol), que recorre el árbol de sintaxis y desplaza cada referencia relativa según la distancia de la seguidora a la raíz, de modo que la seguidora en B2 tiene un auténtico =A2*3
El test de regresión que fija ambos comportamientos merece leerse, porque se niega a dejar pasar una casualidad. Construye un libro con =A1*3 y =A2*3 sobre las entradas 2 y 4, y luego inyecta las cachés deliberadamente erróneas 999 y 888 mediante _SetCellCachedFormulaValue, una vez con UseSharedFormulas activado y otra desactivado. Tras guardar y recargar, ambas celdas deben seguir informando de 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 6 y 12, prueba de que la expresión desplazada de la seguidora es correcta. Un test que sembrara los valores verdaderos habría pasado también con el escritor antiguo, que es justo el motivo de sembrar los falsos
var
Book: TXLSWorkbook;
Info: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('quarterly-model.xls');
Book.Sheets[1].Cells[1, 1].Value := 5; // cambia una entrada
// Las cachés cargadas de fórmulas dependientes NO se invalidan con
// una edición literal, así que un SaveAs normal mantendría los números viejos.
// Pide un recálculo cuando de verdad quieras 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 prioridad de caché no hace por ti
El guardado con prioridad de caché conserva lo que se cargó; no lleva la cuenta de si lo cargado sigue siendo cierto. Cambiar un literal del que depende una fórmula marca el grafo de dependencias como sucio para el evaluador, pero deja en su sitio la caché xlfcsLoaded de la celda dependiente, y el escritor clásico escribirá tan contento ese valor obsoleto a menos que llames a Recalculate o leas antes el Value de la celda, lo que lo calcula y mueve el estado a xlfcsCalculated. Es el mismo trueque 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 encargarse explícitamente de su paso de recálculo. La política RecalcBeforeSave del escritor XLSX no cambia con este trabajo y tiene su propio modo manual que conserva las cachés en el mismo espíritu. De aquí salen dos límites más pequeños: el camino con prioridad de caché 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 igual que antes. Y el arreglo 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 sepa calcular igual que Excel ahora es seguro de ida y vuelta sin tocarlo, pero un Recalculate deliberado sobre ese archivo seguirá dando la respuesta de la librería y no la de Excel, y conviene comparar ambas antes de fiarte de un guardado recalculado
Los guardados clásicos con prioridad de caché, las cachés restauradas de la raíz 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 lleva la referencia completa de la API para el libro, el lector de caché y los puntos de entrada de recálculo que se usan aquí