Un total general que Excel muestra como 37 puede volver como 67 después de que un programa abra el archivo .xls y lo guarde sin editar una sola celda. Los 30 extra no son aleatorios. Son exactamente la suma de los dos subtotales de grupo, 10 y 20, que están dentro del rango que el total general cubre. Para que ese número se mueva tienen que fallar dos cosas a la vez: el guardado tiene que recalcular cada fórmula a la salida, y quien recalcule tiene que olvidar que un SUBTOTAL se salta otras celdas SUBTOTAL. Elimine cualquiera de las dos y el archivo sobrevive, por eso el primer problema puede estar escondido detrás del segundo sin que nadie lo note
¿Por qué cambia el valor de una fórmula al guardar un archivo XLS?
Un guardado cambia el valor de una fórmula cuando el escritor calcula el número que almacena junto a cada fórmula en lugar de copiar el que cargó. Cada registro Formula de BIFF8 lleva un campo FormulaValue de ocho bytes ([MS-XLS] §2.4.127, disposición del valor en §2.5.133) que contiene el último resultado que Excel mostró para esa celda. Un escritor tiene que poner algo en esos ocho bytes, y el camino de código más corto es entregar cada fórmula al motor de cálculo. Una vez que eso pasa, cada guardado es un recálculo completo que nadie pidió, y cada punto donde ese motor y Excel discrepan se convierte en un número cambiado en un archivo que el usuario cree intacto
El desacuerdo del SUBTOTAL da un buen ejemplo, porque la regla de la hoja de cálculo es simple y la trampa está en el tipado Variant de Delphi. Excel define que cada número de función SUBTOTAL ignora las celdas cuya propia fórmula es otro SUBTOTAL. Para una hoja cuyas filas de detalle suman 37 (las filas bajo dos grupos que subtotalizan 10 y 20, más 7 fuera de todo grupo), el total general es 37. Un motor que no detecta los subtotales anidados cuenta el 10 y el 20 una segunda vez y aterriza en 67. Detectarlos suele significar una comprobación corta sobre el texto de la fórmula, y la versión Delphi obvia compila, corre y nunca coincide con nada:
// Una función auxiliar que usted escribe, por ejemplo al escanear una hoja
// en busca de fórmulas que un SUBTOTAL envolvente tiene que omitir
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// La prueba tentadora nunca tiene éxito en Delphi Unicode: un String
// asignado a un Variant es varUString, no varOleStr
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // acepta varString, varOleStr y varUString
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(');
end;
// Uso con HotXLS (hojas, filas y columnas son 1-based):
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...VarIsStr es la prueba que el evaluador de HotXLS aplica a la misma pregunta, y también trata las celdas AGGREGATE como excluidas de un subtotal envolvente, como hace Excel. Arreglar el predicado no es toda la historia, eso sí. Con una comprobación correcta, el guardado que recalcula escribe 37 solo porque el motor casualmente coincide con Excel en este archivo concreto. La próxima fórmula que el motor trate un poco distinto volverá a moverse. Las reglas de qué celdas se saltan SUBTOTAL y AGGREGATE, filas ocultas incluidas, están tratadas en el artículo sobre filas ocultas de SUBTOTAL y AGGREGATE. Con guardado cache-first, un archivo que usted nunca pidió computar vuelve al disco con 37 en la celda, diga lo que dijera el motor
Qué escribe un guardado XLS clásico en cada registro Formula
HotXLS guarda un libro BIFF8 priorizando la caché: para cada celda de fórmula, TXLSWorkbook.SaveAs escribe el valor en caché siempre que TryGetCachedFormulaValue informaría uno, y solo ejecuta el evaluador cuando no. El estado de la caché lo decide. Una celda en estado xlfcsLoaded (el valor vino del archivo de origen) o xlfcsCalculated (HotXLS lo calculó en esta sesión) tiene su CacheInfo.Value escrito byte a byte. Una celda en estado xlfcsMissing o xlfcsInvalidated se evalúa al guardar. Eso encaja con cómo trata Excel un guardado: una instantánea de lo que muestra cada celda, que en modo de cálculo manual puede estar muy desfasada, escrita fielmente de todos modos
El camino de evaluación sigue ahí por buenas razones. Una fórmula que usted asigna en código a través de Cells[Row, Col].Formula no tiene caché todavía, y asignar una fórmula nueva a una celda cargada la mueve a xlfcsInvalidated, porque el resultado viejo pertenece a la expresión vieja. Ambas se calculan durante el guardado, así que un libro generado desde cero abre en Excel mostrando números calculados en vez de ceros. Si hasta el evaluador es incapaz de producir un valor (una función no soportada, un enlace externo roto), el escritor almacena un cero y activa fAlwaysCalc, el bit 0 de los flags del registro en §2.4.127, para que Excel recalcule la celda al abrir en lugar de fiarse del placeholder
Una propiedad parece relacionada y no lo está. TXLSWorkbook.RecalcOnSave refleja la opción «Recalculate workbook before saving» de Excel, del registro CalcSaveRecalc ([MS-XLS] §2.4.37). Solo le importa a Excel, y solo en modo de cálculo manual, y HotXLS simplemente la escribe en el archivo. SaveAs no la consulta al decidir qué entra en FormulaValue. El round trip de abajo es la manera más rápida de ver el contrato en uno de sus propios archivos:
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// Hoja, fila y columna son 1-based
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // las celdas en caché se escriben tal cual
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// Esperado: After.Value igual a Before.Value, y After.State vuelve a ser
// xlfcsLoaded, porque vino del archivo guardado
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;El lado de lectura de este contrato, incluido lo que significa cada estado y por qué un blanco en caché o un False siguen contando como valor, está descrito en leer valores de fórmula en caché de Excel en Delphi sin recalcular. Ejecute la comprobación de arriba sobre la celda del total general de un libro con subtotales anidados y las dos líneas imprimen el mismo número
¿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 de fórmula, pero la expresión llega un registro más tarde, y ese hueco es donde un lector descuidado pierde la caché. BIFF8 almacena una fórmula compartida como un registro ShrFmla ([MS-XLS] §2.4.260) situado justo después del registro Formula de la celda superior izquierda del grupo. Cada celda miembro, la raíz incluida, lleva un rgce formado por un único token PtgExp (§2.5.198): el byte $01 seguido de la fila y la columna de la raíz. Las celdas seguidoras son fáciles, porque cuando aparecen la expresión de la raíz ya existe y su propio FormulaValue está en su propio registro. La raíz no es fácil. Cuando se lee su registro Formula, tiene un valor en caché de 37 y todavía ninguna expresión
Instalar una expresión en una celda limpia su caché, y eso tiene que seguir siendo cierto, porque una fórmula reemplazada realmente deja el resultado viejo sin sentido. Aplicada a ciegas al registro ShrFmla, en cambio, la misma regla tira el 37 de la raíz en el momento en que llega la expresión, TryGetCachedFormulaValue reporta la raíz como xlfcsMissing, y el guardado recurre al evaluador exactamente para la celda que un usuario es más propenso a mirar. El lector de HotXLS retiene el valor decodificado de la raíz mientras espera el registro ShrFmla, instala la expresión y después devuelve el valor cargado con estado xlfcsLoaded. Las fórmulas de array (el registro Array, §2.4.4) siguen el mismo orden de registros y reciben el mismo tratamiento. La forma String de la caché nunca tuvo este problema, porque su texto llega en un registro String separado y se empareja con la celda por su fila y columna, así que su posición en el stream nunca importa
Si además trata el lado OOXML, el artículo sobre la expansión si de fórmulas compartidas XLSX explica por qué el formato de paquete no tiene el hueco de orden de registros pero trae sus propias trampas de expansión
Los seguidores de una fórmula compartida poseen una copia desplazada de la expresión
La expresión de un registro ShrFmla se escribe relativa a la celda raíz, así que cada seguidor necesita su propia copia desplazada según su distancia a la raíz. Cópiela literal y un grupo con raíz en B1 con =A1*3 entrega el mismo =A1*3 a B2 y B3. HotXLS da a cada seguidor una copia cuyas referencias relativas están desplazadas según la distancia de fila y columna del seguidor, así que B2 posee un verdadero =A2*3 y B3 posee =A3*3
Eso es también por qué un round trip simple no puede decirle si las expresiones de los seguidores están bien. Los seguidores conservan sus valores cargados a través de un guardado, así que una copia literal pasaría. Una comprobación que demuestre algo tiene que cambiar una entrada y recalcular. La rutina de abajo escribe un grupo compartido con UseSharedFormulas activado (su valor por defecto es False, que escribe un registro Formula por celda), reabre el archivo, edita A2 y observa B2:
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3 se convierten en un grupo ShrFmla
Book.SaveAs(FileName); // aún sin cachés: evaluados a 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // edite la entrada del follower
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Esperado: 12, aún cargado
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Esperado: 30, de =A2*3
finally
Book.Free;
end;
end;Un seguidor que hubiera reutilizado =A1*3 imprimiría 6 en la segunda línea. La primera línea importa igual: muestra que editar un literal deja el valor cargado de la fórmula dependiente en su sitio hasta que algo recalcule
Lo que el guardado cache-first le deja a usted
El guardado cache-first preserva lo que se cargó. No rastrea si lo que se cargó sigue siendo cierto. Cambiar una celda de la que depende una fórmula marca el grafo de dependencias para el siguiente recálculo, pero la celda dependiente conserva su valor xlfcsLoaded, y SaveAs escribe ese número desfasado salvo que usted llame antes a Recalculate o lea la Value de la celda, lo que computa la fórmula y la mueve a xlfcsCalculated. Recalculate devuelve el número de celdas de fórmula que no pudo evaluar, así que un resultado distinto de cero merece quedar registrado antes del guardado
En el pipeline común un programa abre un libro que otro construyó, rellena unas etiquetas o una cabecera, y guarda. Nada en ese archivo debería cambiar de valor, y con el guardado cache-first nada cambia. Un programa que edita entradas es dueño del paso de recálculo, y ese paso pertenece a una única llamada Recalculate visible junto a las ediciones, donde cualquiera que lea el código pueda verla. Excel hace el mismo compromiso en modo de cálculo manual. El escritor XLSX tiene su propia política, RecalcBeforeSave con los modos xrmManual, xrmIfDirty y xrmAlways, fijada en la clase de libro XLSX e independiente de todo lo anterior
Del diseño se siguen dos fronteras. El camino de la caché solo ayuda a las celdas en estado xlfcsLoaded o xlfcsCalculated, así que un generador que escribe miles de fórmulas nuevas y nunca las evalúa sigue pagando una evaluación por celda durante el guardado. Y el contrato gobierna lo que un guardado escribe, no lo que un recálculo explícito computa: un archivo intacto conserva los números que Excel almacenó, mientras que un Recalculate escribe los resultados del evaluador de HotXLS. Compare ambos sobre unos cuantos libros representativos antes de pasar un pipeline a guardados recalculados
El HotXLS Delphi Spreadsheet Component lee y escribe libros BIFF8 clásicos y XLSX desde Delphi y C++Builder sin Excel ni automatización OLE instalados. Su página de producto documenta las llamadas de libro, valores en caché y recálculo usadas en estos ejemplos