Auditar cachés de fórmulas de Excel con HotXLS Deep Recalc

HotXLS responde a la pregunta que todo pipeline de hojas de cálculo tarde o temprano tiene que hacerse: si los números almacenados en un libro todavía coinciden con las fórmulas que los produjeron. CalculateAndVerify recalcula todo el grafo de dependencias en un overlay aislado, compara cada resultado con el valor en caché que ya está en la celda, y reporta las discrepancias. Por defecto no cambia nada

La razón por la que esto importa es que un fichero de hoja de cálculo almacena dos cosas por celda de fórmula: la fórmula y el último valor que alguien calculó para ella. Excel las mantiene sincronizadas. Todo lo demás en el mundo puede que no. Un fichero que pasó por una biblioteca antigua, un recálculo parcial, una parte XML editada a mano o una herramienta que escribió valores sin recalcularlos presentará encantado un total que ya no se deduce de sus entradas, y nada en el formato de fichero lo señala

¿Por qué un valor en caché que discrepa de su fórmula es tan peligroso?

Porque es invisible en todas las rutas de lectura ordinarias. Abra el fichero en un visor, lea la celda mediante una API, expórtela a CSV o PDF, y obtiene el número en caché. La fórmula está ahí mismo en la misma celda, y nadie las compara. La discrepancia solo sale a la luz cuando alguien abre el libro en Excel, que recalcula al cargar bajo la mayoría de configuraciones, y de repente un informe que se firmó el trimestre pasado muestra totales distintos

La auditoría existe para convertir esa comparación en una operación deliberada y programada y no en un accidente. Es el equivalente de hoja de cálculo a verificar un checksum: lo bastante barata para correr en un pipeline de ingesta, y lo único que convierte un problema silencioso de integridad de datos en un informe sobre el que se puede actuar

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Hay tres sobrecargas y responden a tres preguntas distintas. La CalculateAndVerify sin parámetros devuelve un recuento de discrepancias, que es todo lo que necesita una comprobación de salud. La sobrecarga con un array out de discrepancias le da las celdas. La sobrecarga que toma TXLSRecalcAuditOptions devuelve un TXLSCalculationAuditReport completo, que es la que hay que coger cuando necesita saber no solo que un valor discrepa sino por qué la auditoría no pudo evaluar algo

El overlay, y por qué la auditoría no escribe

Cada valor recalculado aterriza en un overlay en lugar de en la caché de celdas, y el overlay se inyecta al frente mismo del callback de lectura de celda en ambos motores de libro. Esa colocación es lo que hace la auditoría autoconsistente: cuando B1 se recalcula y C1 depende de B1, C1 ve el valor de esta pasada de auditoría, no el viejo en caché. Sin eso, un único error aguas arriba se reportaría una vez y luego se absorbería, y cada celda aguas abajo parecería estar de acuerdo con una entrada errónea

Las celdas cuyo valor recalculado coincide con la caché no entran en el overlay en absoluto. No es una micro-optimización, es lo que mantiene la auditoría asequible. Un libro limpio con cien mil fórmulas realiza cero escrituras de overlay y la pasada se queda dentro de un presupuesto de 1.35x frente a un recálculo completo, que es la diferencia entre algo que puede correr en cada ingesta y algo que corre una vez al trimestre

La evaluación sigue un orden topológico serial derivado del grafo de dependencias, con cada nodo marcado dirty primero, así que cada celda se calcula exactamente una vez después de sus entradas. Si quiere la maquinaria incremental que mantiene al día un libro vivo en lugar de auditar uno almacenado, ese es otro mecanismo, descrito en el recálculo incremental y el grafo de dependencias

Los fallos se clasifican, no se amontonan

Una celda que la auditoría no puede evaluar no es el mismo hallazgo que una celda cuyo valor discrepa, y TXLSCalculationAuditIssueKind mantiene las categorías separadas. xlcaiCacheMismatch es la discrepancia de valor. xlcaiMissingFunction y xlcaiMissingName dicen que el evaluador se encontró algo que no implementa o no puede resolver. xlcaiUnsupportedArguments cubre formas de argumentos fuera del subconjunto soportado. xlcaiExternalReferenceDenied y xlcaiExternalReferenceMissing separan un rechazo de política de un libro ausente. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled y xlcaiInternalFailure completan el conjunto

Una distinción merece enunciarse porque invierte una suposición común. Un código de error positivo de Excel es un resultado, no un fallo. Una celda que legítimamente evalúa a #DIV/0! ha calculado correctamente, así que la auditoría almacena ese error en el overlay y lo compara con la caché como cualquier otro valor. Un libro lleno de celdas de error intencionales produce cero hallazgos, y un libro donde un error apareció o desapareció desde que los valores se guardaron en caché produce exactamente los hallazgos que usted quiere

Las referencias circulares reciben su propio tratamiento. Los nodos en un ciclo nunca entran en el orden topológico, así que cada uno se reporta individualmente como xlcaiCircularReference, y la auditoría no ejecuta el solver iterativo. Es un contrato deliberado de solo lectura: si la iteración está activada afecta a cómo debe interpretarse el código de resultado, no a lo que la auditoría hace. La mecánica de la evaluación iterativa se cubre por separado en el cálculo iterativo y las referencias circulares

Leer una cadena de fallo

Cuando una fórmula falla al evaluarse, saber qué celda falló rara vez basta, porque el fallo suele estar tres niveles abajo en una cadena de referencias. Cada hallazgo lleva por tanto una cadena Stack renderizada con el marco más externo primero, en la forma Sheet1!A1 > Sheet1!B2 > Data!C7, de modo que el informe apunta a la celda que realmente rompió y no a la celda que usted casualmente miraba

El grabador está acotado. MaxStackFrames vale por defecto 64 con un suelo de 8, y la cadena de fallo más profunda es la que se retiene: un marco interior registra la cadena cuando el fallo se origina allí, y los marcos externos desenvolviéndose después no la sobrescriben. Si alguna cadena excedió el presupuesto, Report.StackTruncated se activa, lo que le dice la diferencia entre una cadena corta y una cadena que no vio entera

// Solo lectura por defecto. ApplyResults compromete el overlay solo tras
// una auditoría completamente exitosa, bajo un guard de escritura que
// rechaza el commit si la estructura del libro cambió mientras corría
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // comparación exacta, saca a la luz la deriva
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // la auditoría se detiene en la siguiente frontera de nodo
end;

¿Cuándo debe dejar que la auditoría repare el libro?

Solo cuando la auditoría volvió completamente limpia de hallazgos de clase fallo, que es precisamente la condición que ApplyResults le impone. El commit ocurre tras una pasada completamente exitosa, no fue cancelada, y pasa un guard estructural: el motor binario vigila un identificador de cambio del libro, el motor OOXML toma una instantánea de una generación de estructura por hoja. Si algo se movió mientras la auditoría corría, los resultados describen un libro que ya no existe y el commit se rechaza

Note la asimetría deliberada. Las discrepancias de caché no bloquean la aplicación, porque son justo lo que el commit está ahí para reparar. Los hallazgos de clase fallo sí lo bloquean, porque un libro donde algunas fórmulas no pudieron evaluarse quedaría medio reparado, y un libro medio reparado es peor que uno sin reparar que usted sabe que debe desconfiar

La tolerancia es una decisión de política, no un valor por defecto

La comparación por defecto es una tolerancia absoluta de 1E-6 con la tolerancia relativa desactivada, lo que preserva el comportamiento clásico y acepta en silencio una deriva de 4E-7. Suele ser lo correcto: las diferencias de orden de evaluación en coma flotante entre lo que produjo el fichero y el evaluador actual producirán diferencias de ese tamaño en sumas largas, y reportarlas como hallazgos de integridad es ruido

Ponga ambas tolerancias a cero cuando la pregunta es otra, cuando intenta averiguar si un evaluador cambió de comportamiento entre versiones, o si una herramienta de terceros reescribe valores de una forma sutilmente distinta. A cero, la misma deriva de 4E-7 se vuelve visible, y también todo lo demás. Escoja la tolerancia según la pregunta que esté haciendo, y registre la elección junto al informe, porque un informe sin su tolerancia no es interpretable

Dos capacidades vecinas completan el cuadro. Cuando quiere saber por qué una fórmula concreta produce el valor que produce, la vista paso a paso de el tracer de evaluación de fórmulas es la herramienta correcta. Cuando deliberadamente quiere que se respeten los valores en caché sin recálculo alguno, por ejemplo en una ruta de ingesta que debe reproducir el fichero exactamente como llegó, ese modo se describe en leer valores de fórmula en caché sin recalcular. La auditoría es lo que se sienta entre esas dos: le dice si fiarse de la caché es seguro. Se envía con el componente de hoja de cálculo Delphi HotXLS para ambos motores, binario y OOXML