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
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
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