Artículo técnico

Comparar dos libros de trabajo de Excel en Delphi con HotXLS

Dos copias del mismo libro trimestral pueden mostrar 1,250.00 en la celda D2 y aun así no estar de acuerdo sobre qué es esa celda: en una es un literal tecleado, en la otra es =SUM(A2:C2). Un revisor que alterna entre los archivos ve pantallas idénticas. Una comparación byte a byte de los dos paquetes .xlsx va para el otro lado y reporta casi todo como diferente, porque un archivo zip escrito dos veces rara vez coincide byte por byte. Ninguna de las dos comprobaciones responde la pregunta que la gente hace de verdad después de una ronda de revisión, que es qué celdas, fórmulas y hojas cambiaron

Esa pregunta aparece en unos cuantos lugares predecibles. Un libro financiero vuelve de revisión, una exportación nocturna se regenera después de un cambio de código, o dos departamentos devuelven sus propias ediciones de una plantilla. La inspección lado a lado sirve para una hoja y es inútil para veinte, y justo el intercambio fórmula-versus-literal de arriba es lo que un par de ojos cansados no ve

Diagrama general de la comparación de libros de HotXLS en Delphi: baseline.xlsx y reviewed.xlsx alimentan a TXLSXWorkbookCompare, que empareja hojas por nombre, recorre las celdas almacenadas, y emite records de diferencias estructurados más un reporte legible
TXLSXWorkbookCompare convierte dos libros HotXLS cargados en una lista de diff estructurada para la lógica y un reporte legible para los revisores

Comparar dos libros de trabajo con TXLSXWorkbookCompare

HotXLS compara libros de trabajo mediante TXLSXWorkbookCompare de la unidad lxCompare. Compare(Left, Right) toma dos objetos TXLSXWorkbook cargados y devuelve el número de diferencias encontradas. La lista de diferencias queda disponible por índice a través de Diff(I) y Count, y Report agrega una línea legible por diferencia a cualquier TStrings. Excel no participa en ningún momento; la comparación corre sobre el modelo de objetos que HotXLS ya cargó, así que funciona igual en un trabajo de consola, un servicio o una aplicación de C++Builder

uses
  SysUtils, Classes, lxHandleX, lxCompare;

var
  Left, Right: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare;
  Lines: TStringList;
begin
  Left := TXLSXWorkbook.Create;
  Right := TXLSXWorkbook.Create;
  Cmp := TXLSXWorkbookCompare.Create;
  Lines := TStringList.Create;
  try
    // Open devuelve 1 si tuvo éxito; una apertura fallida debe frenar aquí,
    // si no, se comparan libros vacíos y el resultado no significa nada
    if Left.Open('baseline.xlsx') <> 1 then
      raise Exception.Create('cannot open baseline.xlsx');
    if Right.Open('reviewed.xlsx') <> 1 then
      raise Exception.Create('cannot open reviewed.xlsx');

    if Cmp.Compare(Left, Right) = 0 then
      Writeln('workbooks are equivalent')
    else
    begin
      Cmp.Report(Lines);              // una línea legible por diferencia
      Lines.SaveToFile('workbook-diff.txt');
      Writeln(Format('%d difference(s) written', [Cmp.Count]));
    end;
  finally
    Lines.Free;
    Cmp.Free;
    Right.Free;
    Left.Free;
  end;
end;

Las comprobaciones de apertura explícitas importan más de lo que parece. Compare devuelve 0 y limpia su lista cuando cualquiera de los argumentos es nil, y dos libros vacíos también comparan como iguales, así que un resultado cero solo significa "equivalentes" cuando ambos archivos realmente se cargaron. Una línea típica de workbook-diff.txt se lee value changed: Data!A2: 10 -> 99

¿Qué cuenta como diferencia por omisión?

Sin opciones activadas, TXLSXWorkbookCompare reporta ocho tipos de diferencia: una hoja agregada o eliminada, una celda agregada o eliminada, un valor cambiado, una fórmula cambiada, y un rango combinado agregado o eliminado. Los valores del enum son lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded y lckMergeRemoved. Todo se mide contra el libro de trabajo de la izquierda como línea base, así que un elemento agregado existe solo en el de la derecha y un elemento eliminado solo en el de la izquierda

Diagrama de los ocho tipos de diferencias de libro de HotXLS agrupados en nivel de hoja, celda y combinación, cada uno medido contra el libro izquierdo como línea base, con fila y columna cero marcando las entradas de nivel de hoja y de nivel de combinación
Por omisión HotXLS reporta ocho tipos de diferencia en tres niveles, todos expresados contra el libro de la izquierda, así que reordenar hojas no reporta nada en absoluto

Las hojas se emparejan por nombre, y la coincidencia ignora mayúsculas y minúsculas, así que Data a la izquierda se empareja con DATA a la derecha. Arrastrar una pestaña a otra posición, por lo tanto, no produce ninguna entrada. Renombrar una hoja es otra historia: Q3 renombrada a Q3 Final aparece como una línea sheet removed y una línea sheet added, porque nada en el archivo registra que las dos son la misma hoja. Una hoja que existe solo en un lado produce una única entrada a nivel de hoja en lugar de una línea por celda dentro de ella, lo que mantiene el reporte legible cuando dos libros difieren en estructura

Las celdas se emparejan por posición. La comparación recorre las celdas almacenadas de cada par de hojas, y eso incluye celdas que solo guardan formato. Una celda con formato pero vacía que existe a la izquierda y no a la derecha se reporta como cell removed: Data!C4 was (blank). Los rangos combinados se comparan por su texto A1, así que A1:D1 ensanchado a A1:E1 produce un merge removed y un merge added

¿Por qué una fórmula y un literal con el mismo número reportan un cambio?

Porque la fórmula es el contenido de autoría. Cuando cualquiera de los lados de un par de celdas lleva una fórmula, TXLSXWorkbookCompare compara el texto de la fórmula, y una fórmula de un lado contra un literal del otro es una entrada lckFormulaChanged aunque ambas muestren 1,250. La línea del reporte para el ejemplo inicial es formula changed: Data!D2: 1250 -> =SUM(A2:C2), y el registro lleva LeftKind = lcvNumber y RightKind = lcvFormula. El texto de la fórmula se compara exactamente como está guardado, con un signo igual inicial agregado para mostrar

Los valores literales se comparan como valores tecleados. Un número se compara con un número, texto con texto, y un número 10 contra el texto "10" es un cambio aunque ambas celdas impriman los mismos caracteres. El reporte muestra las etiquetas de tipo siempre que los tipos difieran: value changed: Data!B7: [number] 10 -> [text] 10. Ese caso es común en archivos que pasan por idas y vueltas de CSV o por copiar y pegar de usuarios, y es el que rompe búsquedas más adelante

Una fórmula cuyo texto no cambió no reporta nada por omisión, aun cuando su resultado en caché difiera. Para revisar contenido de autoría, eso es correcto. Para probar regresiones de un generador es demasiado laxo, porque una hoja de entradas modificada puede mover todos los totales sin tocar una sola fórmula. Activar lcoFormulaCachedResults en Options agrega una entrada lckFormulaCacheChanged cada vez que los resultados en caché difieren, o cuando un lado tiene valor en caché y el otro no, lo que se reporta como (missing cache). Para pruebas de regresión de generadores, active esa opción; un total que se desvía con fórmulas intactas es exactamente la falla para la que existen esas pruebas

Los resultados en caché solo son tan frescos como el último cálculo, así que recalcule ambos libros primero cuando la pregunta es "qué producen estas fórmulas hoy". TXLSXWorkbook.Recalculate devuelve lxOk si tuvo éxito; con la iteración desactivada, un ciclo de referencias devuelve lxErrorRef. El ruido de punto flotante es la otra fuente de falsas alarmas, y SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) lo resuelve para literales numéricos y resultados en caché por igual:

uses
  SysUtils, lxCalc, lxHandleX, lxCompare;

function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare): Integer;
begin
  // Traiga ambas cachés al día antes de comparar resultados
  if (Expected.Recalculate <> lxOk) or (Actual.Recalculate <> lxOk) then
    raise Exception.Create('recalculation failed');

  Cmp.Options := [lcoFormulaCachedResults];
  // 1e-9 absoluto o 1e-12 relativo; ambos deben ser finitos y no negativos,
  // si no, se lanza EArgumentException y ningún valor cambia
  Cmp.SetNumericTolerance(1e-9, 1e-12);

  Result := Cmp.Compare(Expected, Actual);
end;

Las tolerancias por omisión son cero, lo que significa comparación exacta. Se aplican solo a números. Fechas, geometría de filas y columnas y estilos siempre se comparan exactamente, así que una tolerancia ajustada para redondeo de moneda nunca oculta una fecha que se movió un segundo

Impulsar lógica a partir de las diferencias estructuradas

Cada registro TlxCompareDiff expone Kind, Sheet, Row y Col en base uno, un Ref A1, LeftText y RightText, y para entradas de celda LeftKind y RightKind. Las entradas a nivel de hoja y de rango combinado llevan cero en Row y Col. Una vez activadas las comprobaciones opcionales, las entradas de geometría de filas llevan solo fila, las de geometría de columnas solo columna, y funciones del libro como los nombres definidos no llevan ninguna, así que ramifique por Kind en lugar de por coordenadas en cero:

var
  I, FormulaEdits: Integer;
  D: TlxCompareDiff;
begin
  FormulaEdits := 0;
  for I := 0 to Cmp.Count - 1 do
  begin
    D := Cmp.Diff(I);
    case D.Kind of
      lckFormulaChanged:
        begin
          Inc(FormulaEdits);
          Writeln(Format('%s!%s: %s => %s',
            [D.Sheet, D.Ref, D.LeftText, D.RightText]));
        end;
      lckValueChanged:
        if D.LeftKind <> D.RightKind then
          Writeln('type change: ' + D.Describe);
      lckSheetAdded, lckSheetRemoved:
        Writeln('structure: ' + D.Describe);
      lckMergeAdded, lckMergeRemoved:
        Writeln('layout: ' + D.Describe);
    end;
  end;

  // Una política de revisión que solo bloquea por ediciones de fórmula
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

El orden de la lista es fijo. Compare ordena las diferencias por nombre de hoja, luego fila, columna, tipo y referencia, así que dos ejecuciones sobre el mismo par de archivos producen el mismo texto de reporte línea por línea. Los nombres de hoja se ordenan por comparación ordinal de cadenas, no por posición de pestaña, y las entradas con fila cero van primero dentro del grupo de cada hoja. Eso hace del texto del reporte un archivo dorado razonable para una prueba de regresión: haga commit del workbook-diff.txt esperado y haga fallar la compilación cuando el nuevo difiera. Para decisiones de política, lea los campos del registro directamente y deje la línea descriptiva para los humanos

Activar las comprobaciones de formato, geometría y objetos

El conjunto por omisión es una comparación de datos, y una celda cuyo formato de número pasó de General a Moneda no reporta nada bajo él. La propiedad Options amplía la comparación una categoría a la vez. lcoCellStyles compara el estilo resuelto de cada par de celdas y lcoBlankStyleRegions hace lo mismo para áreas con formato que no tienen celdas almacenadas, fusionando diferencias adyacentes en rectángulos cuya esquina lejana queda en LastRow y LastCol. lcoAxisGeometry compara alturas de fila y anchos de columna junto con estado oculto, nivel de esquema y contraído. lcoRichTextRuns compara los fragmentos con formato dentro de celdas de texto

El contenido a nivel de objeto tiene sus propios interruptores: lcoDefinedNames, lcoDataValidations, lcoConditionalFormats, lcoTables, lcoDrawings (que cubre también imágenes dentro de celdas), lcoPivotTables, lcoPivotCaches, lcoSlicerState y lcoTimelineState. Esas entradas ponen la clave de la función en Ref y dicen mediante Change si se agregó, eliminó o cambió. Nombres definidos, cachés de tablas dinámicas y los cachés de slicer y timeline a nivel de libro llevan un Sheet vacío

Lea la salida de estas comprobaciones con las expectativas correctas. Las entradas de estilo, texto enriquecido y funciones reportan digeridos SHA-256 de una definición normalizada en LeftText y RightText, así que el reporte dice que el estilo de la celda C5 cambió, pero no qué propiedad fue. Las entradas de geometría son legibles, por ejemplo size=15; hidden=0; outline=0; collapsed=0, y una fila sin altura explícita se compara con la altura estándar de la hoja, así que una fila explícita de 15 puntos coincide con una sin ajustar cuando la altura estándar también es 15. Mantenga lcoCellStyles apagado para revisiones de contenido. En un libro grande convierte cada columna reformateada en un muro de hashes que esconde las tres ediciones de celdas que el revisor debía aprobar

// Revisión de geometría: solo geometría, combinaciones y formatos condicionales
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Acote lo que las comprobaciones de objetos pueden capturar: 32 MiB, 500,000 elementos
Cmp.SetFeatureLimits(32 * 1024 * 1024, 500000);
try
  Cmp.Compare(Left, Right);
except
  on E: EXLSXSemanticBudget do
    Writeln('workbooks too large for this check: ' + E.Message);
end;

Las comprobaciones de objetos capturan sus definiciones antes de comparar, y SetFeatureLimits acota esa captura. Los valores por omisión son 67,108,864 bytes y 1,000,000 elementos; exceder cualquiera lanza EXLSXSemanticBudget. Cuando Compare lanza, la lista de diferencias de la llamada exitosa anterior queda en su lugar, así que un llamador que atrapa la excepción no debe leer Count como el resultado de la ejecución fallida

Fechas, filas insertadas y dónde rinde el diff

Las celdas que el libro guarda como fechas se comparan como fechas, exactamente, y se imprimen como date(...) con el valor numérico de fecha subyacente. Una fecha de un lado contra un número simple del otro muestra las etiquetas de tipo, la señal usual de que una columna perdió su formato de fecha al pasar por otra herramienta. Los sistemas de fechas mixtos son la trampa aquí. Un libro en el sistema de fechas 1904 y otro en el sistema 1900 guardan el mismo día calendario como números de serie que distan 1,462, así que verifique Date1904 en ambos lados antes de confiar en una columna de cambios de fechas. El contexto está en números de serie de fecha y el sistema 1904

El emparejamiento posicional tiene una consecuencia que conviene tener prevista. Inserte una fila cerca del inicio de una hoja y cada celda por debajo se mueve, así que la comparación reporta una racha de celdas cambiadas, agregadas y eliminadas a lo largo de la hoja, y las fórmulas cuyas referencias fueron reescritas por la inserción se reportan como cambiadas. Cuando usted controla la canalización, ejecute una comparación antes de la edición estructural y otra después, para que cada reporte cubra un solo tipo de cambio. Las reglas de reescritura están cubiertas en ajuste de referencias de fórmula al insertar y eliminar

Tres trabajos justifican incorporar esto. Pruebas de regresión de un generador de reportes: conserve un libro de trabajo conocido como bueno, regenere, compare con los resultados en caché activados, y falle ante cualquier diferencia. Revisión de cambios: entréguele al revisor el reporte ordenado en lugar de dos archivos. Verificación de migración: después de convertir un lote de libros heredados, compare cada resultado contra su origen, y luego cuente formato y funciones de objeto en cada lado con el pase de inventario de el banco de trabajo de auditoría y conversión de libros

Comparación, recálculo y conversión comparten un mismo motor en HotXLS para Delphi y C++Builder, y la página del componente de hojas de cálculo HotXLS para Delphi lista el resto de lo que ese motor lee y escribe