Сравнение двух книг Excel в Delphi с помощью HotXLS

HotXLS сравнивает две книги через TXLSXWorkbookCompare, которая сопоставляет листы по имени, обходит заполненные ячейки каждой пары и сообщает об отличиях в виде структурированного списка записей различий и, по запросу, в виде одной читаемой строки на каждое отличие. Установка Excel не требуется, и сравнение выполняется целиком на загруженной объектной модели в Delphi или C++Builder

Потребность в этом обычно возникает, когда кто-то впервые спрашивает, что изменилось. Финансовая книга возвращается после проверки, ночной экспорт перегенерируется после изменения кода, или два отдела присылают версии одного и того же шаблона. Открытие обоих файлов бок о бок работает для одного листа и отказывает для двадцати. Побайтовое сравнение файлов не отвечает вообще ни на что, поскольку два сохранения одной и той же книги отличаются способами, которые никого не волнуют

Что считается отличием?

Сравнение сообщает о восьми видах, и этот набор намеренно небольшой: лист добавлен или удалён, заполненная ячейка добавлена или удалена, значение ячейки изменилось, формула ячейки изменилась, объединённый диапазон добавлен или удалён. Всё выражается относительно левой книги как базовой, поэтому добавленный элемент существует только справа, а удалённый — только слева

Листы сопоставляются по имени, а не по позиции. Поэтому изменение порядка листов вообще не даёт отличий, что почти всегда и есть желаемое поведение: пользователь, перетаскивающий вкладку, — не изменение данных. Лист, присутствующий только с одной стороны, даёт одну запись уровня листа, а не разворачивает каждую заполненную ячейку внутри него, что делает отчёт о двух структурно различных книгах читаемым, а не растянутым на тысячи строк

Значение или формула, и как каждое сравнивается

Каждая ячейка даёт сигнатуру, и правило простое: ячейка с формулой сравнивается по тексту формулы с ведущим знаком равенства, а ячейка без формулы сравнивается по значению, преобразованному в текст. Это различие важнее, чем кажется на первый взгляд. Две ячейки могут показывать одно и то же отображаемое число, при этом одна — литерал, а другая — формула, и считать их равными означало бы скрыть именно ту правку, которую важнее всего заметить в проверяемой книге

Это также означает, что формула, чей текст не изменился, не даёт отличия, даже если её кешированный результат изменился, — и это правильное поведение при сравнении авторского содержимого, но неправильное, если вы пытаетесь обнаружить дрейф пересчёта. Для второго вопроса пересчитайте обе книги перед сравнением, чтобы сравниваемые значения были теми, что формулы действительно выдают сегодня

Выполнение сравнения

Compare принимает две загруженные книги и возвращает количество найденных отличий. Список отличий затем доступен по индексу или может быть выгружен в любой TStrings:

uses
  lxHandleX, lxCompare;

var
  Left, Right: TXLSXWorkbook;
  Cmp: TXLSXWorkbookCompare;
  Lines: TStringList;
begin
  Left := TXLSXWorkbook.Create;
  Right := TXLSXWorkbook.Create;
  Cmp := TXLSXWorkbookCompare.Create;
  Lines := TStringList.Create;
  try
    if (Left.Open('baseline.xlsx') <> 1) or
       (Right.Open('reviewed.xlsx') <> 1) then
      Exit;

    if Cmp.Compare(Left, Right) = 0 then
      Writeln('workbooks are equivalent')
    else
    begin
      Cmp.Report(Lines);              // одна читаемая строка на отличие
      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;

Строка, сформированная Report, читается как value changed: Data!A2: 10 -> 99, чего достаточно для рецензента и достаточно для сообщения коммита. Это интерфейс для человека. Программный интерфейс — сама запись отличия, и её следует использовать, когда сравнение питает решение, а не документ

Управление логикой через структурированные отличия

Каждое отличие открывает свой вид, имя листа, единичные строку и столбец для записей уровня ячейки, ссылку A1 для записей уровня объединения, а также левый и правый текст. Записи уровня листа и уровня объединения сообщают строку и столбец как ноль, что позволяет отличить их друг от друга без проверки вида:

var
  I: Integer;
  D: TlxCompareDiff;
  FormulaEdits: Integer;
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 R%dC%d: %s => %s',
            [D.Sheet, D.Row, D.Col, D.LeftText, D.RightText]));
        end;
      lckSheetAdded, lckSheetRemoved:
        Writeln(Format('structure: %s', [D.Describe]));
      lckMergeAdded, lckMergeRemoved:
        Writeln(Format('layout: %s at %s', [D.Describe, D.Ref]));
    end;
  end;

  // Политика проверки, блокирующая только на изменениях формул
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

Два свойства вывода стоит знать перед написанием проверок против него. Порядок записей уровня ячейки следует внутреннему порядку обхода хранилища ячеек, поэтому тесты стоит писать независимо от порядка. А сигнатура формулы несёт собственный ведущий знак равенства, что означает, что строка описания, построенная конкатенацией, может показать сдвоенное ==; проверяйте значения полей, а не разбирайте описательную строку, когда результат управляет логикой

Где сравнение книг окупается

Три применения оправдывают эту функцию сами по себе. Регрессионное тестирование генератора отчётов: держите заведомо корректную книгу, перегенерируйте, сравните и завершайте сборку ошибкой при любом неожиданном отличии. Проверка изменений: передайте рецензенту читаемый отчёт вместо двух файлов. И проверка миграции: после конвертации партии устаревших книг сравните каждый результат с источником, чтобы доказать, что ничего не потеряно

Этот третий случай естественно сочетается с проходами инвентаризации и аудита, описанными в статье рабочее место аудита и конвертации книг, где подсчёт содержимого книги происходит до конвертации, а сравнение — после. Если ваши отличия скапливаются вокруг вставленных строк, правила перезаписи ссылок в статье корректировка ссылок формул при вставке и удалении объясняют, почему формулы, выглядящие неизменными, сообщаются как изменённые

Ограничения, названные прямо

Сравнение охватывает значения, формулы, объединения и присутствие листов. Оно не сравнивает числовые форматы, шрифты, заливки, правила условного форматирования, проверки данных, диаграммы, изображения или именованные диапазоны. Ячейка с идентичным значением, но изменённым форматом с General на Currency, не даёт отличия, что корректно для сравнения данных и недостаточно для проверки форматирования

Ячейки со значением даты заслуживают отдельного предупреждения: они сравниваются по своему текстовому преобразованию, поэтому книга, хранящаяся в системе дат 1904, и книга в системе 1900 могут сравниться равными или неравными неожиданным для вас образом, если базовые серийные номера различаются. Правила системы дат описаны в статье серийные номера дат и система 1904. Когда форматирование или точность на уровне объектов часть вопроса, сочетайте сравнение с проходом аудита, подсчитывающим эти функции с каждой стороны

Сравнение книг, аудит и конвертация работают на одном движке для Delphi и C++Builder; полный список возможностей — на странице компонента HotXLS для электронных таблиц Delphi