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

HotXLS responde la pregunta que todo pipeline de hojas de cálculo tarde o temprano tiene que hacerse: si los números almacenados en un workbook todavía coinciden con las fórmulas que los produjeron. CalculateAndVerify recalcula todo el grafo de dependencias en un overlay aislado, compara cada resultado contra el valor cacheado 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 archivo de hoja de cálculo guarda dos cosas por celda con 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, no necesariamente. Un archivo que pasó por una biblioteca vieja, un recálculo parcial, una parte XML editada a mano o una herramienta que escribió valores sin recomputarlos le presentará felizmente un total que ya no se sigue de sus entradas, y nada en el formato de archivo lo marca

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

Porque es invisible en toda ruta de lectura ordinaria. Abra el archivo en un visor, lea la celda vía una API, expórtela a CSV o PDF, y obtiene el número cacheado. La fórmula está ahí mismo en la misma celda, y nadie las compara. El desfase solo sale a la luz cuando alguien abre el workbook en Excel, que recalcula al cargar bajo la mayoría de las configuraciones, y de repente un reporte que se firmó el trimestre pasado muestra totales distintos

La auditoría existe para volver esa comparación una operación deliberada y programada en lugar de un accidente. Es el equivalente en hojas de cálculo de verificar un checksum: lo bastante barata para correr en un pipeline de entrada, y lo único que convierte un problema silencioso de integridad de datos en un reporte sobre el que 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 tres preguntas distintas. La CalculateAndVerify sin parámetros devuelve un conteo de discrepancias, que es todo lo que necesita un health check. La sobrecarga con un arreglo de salida de discrepancias le da las celdas. La sobrecarga que toma TXLSRecalcAuditOptions devuelve un TXLSCalculationAuditReport completo, que es la que hay que alcanzar 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 recomputado aterriza en un overlay en lugar del caché de la celda, y el overlay se inyecta al frente mismo del callback de lectura de celdas en ambos engines del workbook. Esa ubicación es lo que vuelve autoconsistente la auditoría: cuando B1 se recomputa y C1 depende de B1, C1 ve el valor de esta pasada de auditoría, no el viejo cacheado. Sin eso, un único error aguas arriba se reportaría una vez y luego se absorbería, y cada celda aguas abajo parecería concordar con una entrada equivocada

Las celdas cuyo valor recomputado coincide con el caché no entran al overlay en absoluto. No es una micro-optimización, es lo que mantiene la auditoría asequible. Un workbook limpio con cien mil fórmulas realiza cero escrituras al 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 entrada y algo que corre una vez por 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 lo que quiere es la maquinaria incremental que mantiene un workbook vivo al día en lugar de auditar uno almacenado, ese es un mecanismo distinto, descrito en recálculo incremental y el grafo de dependencias

Las fallas 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 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 workbook ausente. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled y xlcaiInternalFailure completan el set

Una distinción vale la pena enunciarla porque invierte un supuesto común. Un código de error Excel positivo es un resultado, no una falla. Una celda que legítimamente evalúa a #DIV/0! calculó correctamente, así que la auditoría guarda ese error en el overlay y lo compara contra el caché como cualquier otro valor. Un workbook lleno de celdas de error intencionales produce cero hallazgos, y un workbook donde un error apareció o desapareció desde que los valores se cachean produce exactamente los hallazgos que usted quiere

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

Leer una cadena de falla

Cuando una fórmula falla al evaluarse, saber qué celda falló rara vez basta, porque la falla suele estar tres niveles abajo en una cadena de referencias. Por eso cada issue carga un string Stack renderizado con el frame más externo primero, en la forma Sheet1!A1 > Sheet1!B2 > Data!C7, de modo que el reporte apunte a la celda que realmente se rompió y no a la celda que usted casualmente miraba

El recorder está acotado. MaxStackFrames tiene default 64 con un piso de 8, y la cadena fallida más profunda es la que se conserva: un frame interno registra la cadena cuando la falla se origina ahí, y los frames externos que se desenrollan después no la sobrescriben. Si alguna cadena superó el presupuesto, Report.StackTruncated queda activo, lo que le dice la diferencia entre una cadena corta y una cadena que usted no vio completa

// Solo lectura por defecto. ApplyResults compromete el overlay solo tras
// una auditoría completamente exitosa, bajo un write guard que rechaza el
// commit si la estructura del workbook cambió mientras la auditoría corría
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // comparación exacta, expone el drift
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 para en el próximo nodo
end;

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

Solo cuando la auditoría volvió completamente limpia de issues de clase falla, que es precisamente la condición que ApplyResults le impone. El commit ocurre tras una pasada completamente exitosa, sin cancelación, y pasa un guard estructural: el engine binario vigila un identificador de cambio del workbook, el engine OOXML toma una instantánea de la generación de estructura por hoja. Si algo se movió mientras la auditoría corría, los resultados describen un workbook 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 exactamente lo que el commit viene a reparar. Los issues de clase falla sí lo bloquean, porque un workbook donde algunas fórmulas no pudieron evaluarse quedaría reparado a medias, y un workbook reparado a medias es peor que uno sin reparar del que usted sabe que debe desconfiar

La tolerancia es una decisión de política, no un default

La comparación por defecto es una tolerancia absoluta de 1E-6 con la tolerancia relativa deshabilitada, que preserva el comportamiento clásico y acepta silenciosamente un drift de 4E-7. Suele ser lo correcto: las diferencias de orden de evaluación en punto flotante entre lo que produjo el archivo 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 en cero cuando la pregunta es otra: si está tratando de averiguar si un evaluador cambió de comportamiento entre versiones, o si una herramienta de terceros reescribe valores de una forma sutilmente distinta. En cero, el mismo drift de 4E-7 se vuelve visible, y todo lo demás también. Elija la tolerancia según la pregunta que esté haciendo, y registre la elección junto al reporte, porque un reporte sin su tolerancia no es interpretable

Dos capacidades vecinas completan el panorama. Cuando quiera saber por qué una fórmula individual 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 quiera respetar los valores cacheados sin recálculo alguno, por ejemplo en una ruta de entrada que debe reproducir el archivo exactamente como llegó, ese modo está descrito en leer valores de fórmula cacheados sin recalcular. La auditoría es lo que se sienta entre esas dos: le dice si confiar en el caché es seguro. Viene con el HotXLS Delphi spreadsheet component para el engine binario y el OOXML