Dos copias del mismo libro trimestral pueden mostrar 1,250.00 en la celda D2 y aun así discrepar sobre lo que esa celda es: en una es un literal tecleado, en la otra es =SUM(A2:C2). Un revisor que alterna entre los dos archivos ve pantallas idénticas. Una comparación byte a byte de los dos paquetes .xlsx va en la dirección contraria y reporta casi todo como distinto, 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 tras una ronda de revisión: qué celdas, fórmulas y hojas cambiaron
Esa pregunta surge en unos cuantos sitios previsibles. Un libro de finanzas vuelve de revisión, una exportación nocturna se regenera tras un cambio de código, o dos departamentos devuelven sus propias ediciones de una misma plantilla. La inspección lado a lado sirve para una hoja y es desesperada para veinte, y es exactamente el intercambio fórmula-versus-literal de arriba lo que un par de ojos cansados no ve
Comparar dos libros con TXLSXWorkbookCompare
HotXLS compara libros mediante TXLSXWorkbookCompare, en la unidad lxCompare. Compare(Left, Right) recibe dos objetos TXLSXWorkbook cargados y devuelve el número de diferencias encontradas. La lista de diferencias queda disponible por índice vía Diff(I) y Count, y Report añade una línea legible por diferencia a cualquier TStrings. Excel no participa en ningún momento; la comparación se ejecuta sobre el modelo de objetos que HotXLS ya cargó, así que funciona igual en un proceso por lotes de consola, en un servicio o en una aplicación 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 parar aquí,
// de lo contrario 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 explícitas de apertura importan más de lo que parecen. Compare devuelve 0 y vacía 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 se cargaron de verdad. Una línea típica de workbook-diff.txt es value changed: Data!A2: 10 -> 99
¿Qué cuenta como diferencia por defecto?
Sin ninguna opción activada, TXLSXWorkbookCompare informa de ocho tipos de diferencia: una hoja añadida o eliminada, una celda añadida o eliminada, un valor cambiado, una fórmula cambiada, y un rango combinado añadido o eliminado. Los valores del enum son lckSheetAdded, lckSheetRemoved, lckCellAdded, lckCellRemoved, lckValueChanged, lckFormulaChanged, lckMergeAdded y lckMergeRemoved. Todo se mide contra el libro de la izquierda como línea base, de modo que un elemento añadido existe solo a la derecha y un elemento eliminado solo a la izquierda
Las hojas se emparejan por nombre, y la coincidencia ignora mayúsculas, así que Data a la izquierda se empareja con DATA a la derecha. Arrastrar una pestaña a otra posición, por tanto, no produce ninguna entrada. Renombrar una hoja ya 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 informe legible cuando dos libros difieren en estructura
Las celdas se emparejan por posición. La comparación recorre las celdas almacenadas de cada hoja emparejada, y eso incluye las celdas que solo contienen formato. Una celda con formato pero vacía que existe a la izquierda y no a la derecha se informa como cell removed: Data!C4 was (blank). Los rangos combinados se comparan por su texto A1, de modo 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 informan de un cambio?
Porque la fórmula es el contenido redactado. Cuando cualquier lado de un par de celdas lleva una fórmula, TXLSXWorkbookCompare compara el texto de fórmula, y una fórmula en un lado contra un literal en el otro es una entrada lckFormulaChanged aunque ambos muestren 1,250. La línea del informe 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 fórmula se compara tal como está almacenado, con un signo igual inicial añadido para la visualización
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 informe 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 ciclos CSV o por copiar y pegar de usuarios, y es el que rompe búsquedas más adelante
Una fórmula cuyo texto no cambia no informa de nada por defecto, aunque su resultado en caché difiera. Para revisar contenido redactado eso es correcto. Para probar regresiones de un generador es demasiado indulgente, porque una hoja de entrada modificada puede mover todos los totales sin tocar una sola fórmula. Activar lcoFormulaCachedResults en Options añade una entrada lckFormulaCacheChanged siempre que los resultados en caché difieran, o cuando un lado tiene valor en caché y el otro no, lo que se informa como (missing cache). Para pruebas de regresión de un generador, activa esa opción; un total que se desvía con fórmulas intactas es justo el fallo que esas pruebas existen para cazar
Los resultados en caché son tan frescos como el último cálculo, así que recalcular ambos libros primero cuando la pregunta es "qué producen estas fórmulas hoy". TXLSXWorkbook.Recalculate devuelve lxOk si todo va bien; con la iteración desactivada, un ciclo de referencias devuelve lxErrorRef. El ruido de coma flotante es la otra fuente de falsas alarmas, y SetNumericTolerance(AbsoluteTolerance, RelativeTolerance) lo resuelve igual para literales numéricos que para resultados en caché:
uses
SysUtils, lxCalc, lxHandleX, lxCompare;
function CompareGeneratedReport(Expected, Actual: TXLSXWorkbook;
Cmp: TXLSXWorkbookCompare): Integer;
begin
// Trae 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,
// de lo contrario se lanza EArgumentException y ningún valor cambia
Cmp.SetNumericTolerance(1e-9, 1e-12);
Result := Cmp.Compare(Expected, Actual);
end;
Las tolerancias por defecto son cero, lo que significa comparación exacta. Solo se aplican a números. Fechas, geometría de filas y columnas y estilos siempre se comparan de forma exacta, así que una tolerancia afinada para el redondeo de divisa nunca oculta una fecha que se movió un segundo
Dirigir la lógica a partir de las diferencias estructuradas
Cada registro TlxCompareDiff expone Kind, Sheet, la Row y la Col en base uno, una Ref A1, LeftText y RightText, y para las entradas de celda LeftKind y RightKind. Las entradas de nivel de hoja y de combinación 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 las funciones de libro como los nombres definidos no llevan ni una ni otra, así que decide por Kind en lugar de por coordenadas 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 ante 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 informe 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 convierte el texto del informe en un buen golden file para una prueba de regresión: haz commit del workbook-diff.txt esperado y falla la compilación cuando el nuevo difiera. Para decisiones de política, lee los campos del registro directamente y deja la línea descriptiva para los humanos
Activar las comprobaciones de formato, diseño y objetos
El conjunto por defecto es una comparación de datos, y una celda cuyo formato numérico pasó de General a Moneda no informa de nada bajo él. La propiedad Options ensancha la comparación una categoría cada vez. lcoCellStyles compara el estilo resuelto de cada celda emparejada y lcoBlankStyleRegions hace lo mismo para zonas 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 los estados oculto, nivel de esquema y contraído. lcoRichTextRuns compara los tramos 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 las 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 añadió, se eliminó o cambió. Los nombres definidos, las cachés de tablas dinámicas y las cachés de libro de slicer y timeline llevan una Sheet vacía
Lee la salida de estas comprobaciones con las expectativas correctas. Las entradas de estilo, texto enriquecido y funciones informan de resúmenes SHA-256 de una definición normalizada en LeftText y RightText, así que el informe dice que el estilo de la celda C5 cambió, pero no qué propiedad lo hizo. 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, de modo que una fila de 15 puntos explícitos coincide con una sin fijar cuando la altura estándar también es 15. Mantén 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 diseño: solo geometría, combinaciones y formatos condicionales
Cmp.Options := [lcoAxisGeometry, lcoConditionalFormats, lcoDataValidations];
// Acota 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 defecto son 67,108,864 bytes y 1,000,000 de elementos; superar cualquiera de los dos lanza EXLSXSemanticBudget. Cuando Compare lanza, la lista de diferencias de la última llamada exitosa se queda en su sitio, así que quien captura la excepción no debe leer Count como el resultado de la ejecución fallida
Fechas, filas insertadas y dónde compensa el diff
Las celdas que el libro guarda como fechas se comparan como fechas, de forma exacta, y se imprimen como date(...) con el valor numérico de fecha subyacente. Una fecha en un lado contra un número simple en el otro muestra las etiquetas de tipo, la señal habitual de que una columna perdió su formato de fecha al pasar por alguna otra herramienta. Los sistemas de fechas mixtos son la trampa aquí. Un libro en el sistema de fechas 1904 y otro en el 1900 almacenan el mismo día de calendario como seriales que difieren en 1,462, así que comprueba Date1904 en ambos lados antes de fiarte de una columna de cambios de fecha. El trasfondo está en números de serie de fecha y el sistema 1904
El emparejamiento posicional tiene una consecuencia que conviene planificar. Inserta una fila cerca del principio de una hoja y cada celda por debajo se mueve, así que la comparación informa de una racha de celdas cambiadas, añadidas y eliminadas a lo largo de la hoja, y las fórmulas cuyas referencias fueron reescritas por la inserción se informan como cambiadas. Cuando controlas el pipeline, ejecuta una comparación antes del cambio estructural y otra después, de modo que cada informe cubra un tipo de cambio. Las reglas de reescritura están tratadas en ajuste de referencias de fórmula al insertar y eliminar
Tres tareas justifican cablear esto. Pruebas de regresión de un generador de informes: conserva un libro 'known-good', regenera, compara con los resultados en caché activados, y falla ante cualquier diferencia. Revisión de cambios: entrega al revisor el informe ordenado en lugar de dos archivos. Comprobaciones de migración: tras convertir un lote de libros heredados, compara cada resultado con su origen, y luego cuenta formato y funciones de objeto en cada lado con la pasada 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 enumera el resto de lo que ese motor lee y escribe