Un total general que Excel muestra como 37 puede volver como 67 después de que un programa abre el archivo .xls y lo guarda sin editar una sola celda. Los 30 de más no son al azar: son exactamente la suma de los dos subtotales de grupo, 10 y 20, que están dentro del rango que cubre el total general. Para que ese número se mueva tienen que fallar dos cosas a la vez: el guardado tiene que recalcular todas las fórmulas en la salida, y lo que recalcule tiene que olvidar que un SUBTOTAL salta otras celdas SUBTOTAL. Elimine cualquiera de las dos y el archivo sobrevive, que es por lo que el primer problema puede estar escondido detrás del segundo sin que nadie lo note
¿Por qué guardar un archivo XLS cambia el valor de una fórmula?
Un guardado cambia el valor de una fórmula cuando el writer computa 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, diseño del valor en §2.5.133) que guarda el último resultado que Excel mostró para esa celda. Un writer tiene que poner algo en esos ocho bytes, y el camino de código más corto es entregarle 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 discrepa de Excel se convierte en un número cambiado en un archivo que el usuario cree intacto
La discrepancia del SUBTOTAL es 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 todos los números de función SUBTOTAL ignoran 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 cualquier grupo), el total general es 37. Un motor que no detecta los subtotales anidados cuenta el 10 y el 20 otra vez y cae en 67. Detectarlos normalmente significa una prueba corta sobre el texto de la fórmula, y la versión obvia en Delphi compila, corre y nunca coincide con nada:
// A helper you write yourself, for example when scanning a sheet
// en busca de fórmulas que un SUBTOTAL contenedor tiene que saltar
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 contenedor, como hace Excel. Corregir el predicado no es toda la historia, sin embargo. Con una prueba correcta, el guardado que recalcula escribe 37 solo porque el motor casualmente coincide con Excel en este archivo en particular. La próxima fórmula que el motor maneje un poco distinto se va a mover otra vez. Las reglas de qué celdas saltan SUBTOTAL y AGGREGATE, filas ocultas incluidas, están cubiertas en el artículo sobre filas ocultas en SUBTOTAL y AGGREGATE. Con guardado de caché primero, un archivo que usted nunca pidió computar vuelve al disco con 37 en la celda, sea lo que fuera que el motor hubiera hecho de él
Qué escribe un guardado XLS clásico en cada registro Formula
HotXLS guarda un libro BIFF8 con la caché primero: para cada celda con fórmula, TXLSWorkbook.SaveAs escribe el valor en caché siempre que TryGetCachedFormulaValue reportaría uno, y corre el evaluador solo cuando no. El estado de la caché lo decide. Una celda en estado xlfcsLoaded (el valor vino del archivo fuente) o xlfcsCalculated (HotXLS lo computó 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 coincide con cómo trata Excel un guardado: una foto de lo que muestra cada celda, que en modo de cálculo manual puede estar bastante desactualizada, escrita fielmente de todos modos
El camino de evaluación se queda por buenas razones. Una fórmula que usted asigna en código a través de Cells[Row, Col].Formula todavía no tiene caché, 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 computan durante el guardado, así que un libro generado desde cero abre en Excel mostrando números computados en lugar de ceros. Si hasta el evaluador no puede producir un valor (una función no soportada, un vínculo externo roto), el writer guarda 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 confiar en el marcador de posición
Una propiedad parece relacionada y no lo es. 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, solo en modo de cálculo manual, y HotXLS simplemente la escribe en el archivo. SaveAs no la consulta al decidir qué va en FormulaValue. El round trip de abajo es la forma más rápida de ver el contrato sobre 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 como están
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 qué significa cada estado y por qué un blanco o un False en caché igual cuentan como valor, está descrito en Leer valores de fórmulas en caché de Excel en Delphi sin recalcular. Corra la verificació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 de BIFF?
En su propio registro Formula, como cualquier otra celda con fórmula, pero la expresión llega un registro más tarde, y ese hueco es donde un reader descuidado pierde la caché. BIFF8 guarda una fórmula compartida como un registro ShrFmla ([MS-XLS] §2.4.260) colocado 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 solo 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 para 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 verdad, porque una fórmula reemplazada de verdad deja el resultado viejo sin sentido. Aplicada a ciegas al registro ShrFmla, la misma regla tira el 37 de la raíz justo cuando llega la expresión, TryGetCachedFormulaValue reporta la raíz como xlfcsMissing, y el guardado cae al evaluador justo para la celda que un usuario tiene más probabilidades de mirar. El reader de HotXLS retiene el valor decodificado de la raíz mientras espera el registro ShrFmla, instala la expresión y después repone el valor cargado con estado xlfcsLoaded. Las fórmulas de matriz (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 aparte y se empareja con la celda por su fila y columna, así que su posición en el stream nunca importó
Si usted también trabaja el lado OOXML, el artículo sobre la expansión si de fórmulas compartidas en XLSX explica por qué el formato de paquete no tiene ese hueco de orden de registros pero trae sus propias trampas de expansión
Las seguidoras de una fórmula compartida tienen una copia desplazada de la expresión
La expresión de un registro ShrFmla está escrita relativa a la celda raíz, así que cada seguidora necesita su propia copia desplazada por su distancia a la raíz. Cópiela tal cual y un grupo con raíz en B1 con =A1*3 le entrega el mismo =A1*3 a B2 y a B3. HotXLS le da a cada seguidora una copia cuyas referencias relativas están desplazadas por la distancia de fila y columna de la seguidora, así que B2 tiene un =A2*3 de verdad y B3 tiene un =A3*3
Eso también explica por qué un round trip simple no puede decirle si las expresiones de las seguidoras están bien. Las seguidoras conservan sus valores cargados a través de un guardado, así que una copia tal cual pasaría. Una verificación que pruebe algo tiene que cambiar una entrada y recalcular. La rutina de abajo escribe un grupo compartido con UseSharedFormulas activado (por defecto es False, lo 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 vuelven un grupo ShrFmla
Book.SaveAs(FileName); // aún sin cachés: evaluado a 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // edita la entrada de la seguidora
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Esperado: 12, todavía cargado
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Esperado: 30, sale de =A2*3
finally
Book.Free;
end;
end;Una seguidora 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 en pie el valor cargado de la fórmula dependiente hasta que algo recalcule
Lo que el contrato de caché primero le deja a usted
El guardado con caché primero conserva lo que se cargó. No sigue la pista de si lo que se cargó sigue siendo verdad. Cambiar una celda de la que depende una fórmula marca el grafo de dependencias para el próximo recálculo, pero la celda dependiente conserva su valor xlfcsLoaded, y SaveAs escribe ese número obsoleto salvo que usted llame primero a Recalculate o lea el Value de la celda, lo que computa la fórmula y la mueve a xlfcsCalculated. Recalculate devuelve la cantidad de celdas con fórmula que no pudo evaluar, así que un resultado distinto de cero vale la pena registrarlo antes del guardado
En el pipeline común, un programa abre un libro que otro construyó, llena unas etiquetas o un encabezado, y guarda. Nada en ese archivo debería cambiar de valor, y con guardado de caché primero nada cambia. Un programa que edita entradas es dueño del paso de recálculo, y ese paso pertenece a una llamada Recalculate visible junto a las ediciones, donde cualquiera que lea el código puede verla. Excel hace el mismo intercambio en modo de cálculo manual. El writer de 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 de arriba
Del diseño se siguen dos fronteras. El camino de 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 igual paga una evaluación por celda durante el guardado. Y el contrato gobierna lo que escribe un guardado, no lo que computa un recálculo explícito: un archivo sin tocar conserva los números que Excel guardó, mientras que un Recalculate escribe los resultados del evaluador de HotXLS. Compare los dos sobre unos libros representativos antes de cambiar 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, de valores en caché y de recálculo usadas en estos ejemplos