So Sánh Hai Workbook Excel trong Delphi bằng HotXLS

HotXLS so sánh hai workbook thông qua TXLSXWorkbookCompare, hàm này ghép cặp worksheet theo tên, duyệt qua các ô có dữ liệu của từng cặp, và báo cáo những gì khác biệt dưới dạng một danh sách có cấu trúc các bản ghi khác biệt và, khi được yêu cầu, dưới dạng một dòng dễ đọc cho mỗi khác biệt. Không cần cài đặt Excel, và việc so sánh chạy hoàn toàn trên mô hình đối tượng đã tải trong Delphi hoặc C++Builder

Nhu cầu này thường xuất hiện lần đầu khi ai đó hỏi có gì đã thay đổi. Một workbook tài chính quay lại sau khi soát xét, một bản xuất hàng đêm được tạo lại sau khi mã thay đổi, hoặc hai phòng ban gửi các phiên bản của cùng một mẫu. Mở cả hai file cạnh nhau có hiệu quả với một sheet nhưng thất bại với hai mươi sheet. So sánh file byte theo byte không trả lời được gì cả, vì hai lần lưu của cùng một workbook khác nhau theo những cách chẳng ai quan tâm

Điều gì được tính là một khác biệt?

Việc so sánh báo cáo tám loại, và tập hợp này cố tình được giữ nhỏ: một sheet được thêm hoặc bị xóa, một ô có dữ liệu được thêm hoặc bị xóa, một ô có giá trị thay đổi, một ô có công thức thay đổi, và một vùng gộp được thêm hoặc bị xóa. Mọi thứ được biểu diễn dựa trên workbook bên trái làm đường cơ sở, nên một mục được thêm chỉ tồn tại ở bên phải và một mục bị xóa chỉ tồn tại ở bên trái

Các sheet được ghép cặp theo tên chứ không theo vị trí. Do đó việc sắp xếp lại thứ tự worksheet hoàn toàn không tạo ra khác biệt nào, đây gần như luôn là hành vi bạn muốn: người dùng kéo một tab không phải là thay đổi dữ liệu. Một sheet chỉ có mặt ở một bên báo cáo một mục ở cấp sheet thay vì mở rộng từng ô có dữ liệu bên trong nó, giúp báo cáo của hai workbook có cấu trúc khác nhau vẫn dễ đọc thay vì dài hàng nghìn dòng

Giá trị hay công thức, và mỗi thứ được so sánh ra sao

Mỗi ô đóng góp một chữ ký, và quy tắc rất đơn giản: một ô mang công thức được so sánh theo văn bản công thức của nó với dấu bằng đứng đầu, còn một ô không có công thức được so sánh theo giá trị của nó đã chuyển thành văn bản. Sự phân biệt đó quan trọng hơn thoạt nhìn. Hai ô có thể mang cùng con số hiển thị trong khi một ô là giá trị cố định và ô kia là công thức, và coi chúng là bằng nhau sẽ che giấu đúng chỉnh sửa đáng chú ý nhất trong một workbook được soát xét

Điều này cũng có nghĩa là một công thức mà văn bản không đổi sẽ không báo cáo khác biệt ngay cả khi kết quả được cache của nó khác đi, đây là hành vi đúng khi so sánh nội dung do người tạo ra, và là hành vi sai nếu bạn đang cố phát hiện sự trôi dạt tính toán lại. Đối với câu hỏi thứ hai đó, hãy tính toán lại cả hai workbook trước khi so sánh, để giá trị bạn so sánh là những gì công thức thực sự cho ra ngày hôm nay

Chạy một phép so sánh

Compare nhận hai workbook đã tải và trả về số lượng khác biệt tìm thấy. Danh sách khác biệt sau đó có thể truy cập theo chỉ mục, hoặc có thể được đổ vào bất kỳ TStrings nào:

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);              // một dòng dễ đọc cho mỗi khác biệt
      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;

Một dòng do Report tạo ra đọc như value changed: Data!A2: 10 -> 99, đủ dùng cho người soát xét và đủ dùng cho một commit message. Đó là bề mặt hướng đến con người. Bề mặt lập trình là bản thân bản ghi khác biệt, và đó là cái nên dùng khi việc so sánh nuôi cho một quyết định thay vì nuôi cho một tài liệu

Điều khiển logic từ các khác biệt có cấu trúc

Mỗi khác biệt cho biết loại của nó, tên sheet, hàng và cột đánh số từ một đối với các mục cấp ô, một tham chiếu A1 đối với các mục cấp vùng gộp, và văn bản bên trái lẫn bên phải. Các mục cấp sheet và cấp vùng gộp báo cáo hàng và cột bằng không, đây là cách bạn phân biệt chúng mà không cần kiểm tra loại:

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;

  // Một chính sách soát xét chỉ chặn khi có chỉnh sửa công thức
  if FormulaEdits > 0 then
    raise Exception.CreateFmt(
      '%d formula change(s) need sign-off', [FormulaEdits]);
end;

Hai thuộc tính của đầu ra đáng biết trước khi bạn viết assertion dựa vào nó. Thứ tự các mục cấp ô theo thứ tự duyệt nội bộ của kho lưu ô, nên các test nên được viết độc lập với thứ tự. Và chữ ký công thức mang theo dấu bằng đứng đầu riêng của nó, nghĩa là một chuỗi mô tả được xây bằng cách nối chuỗi có thể hiển thị dấu == lặp; hãy kiểm tra giá trị trường thay vì phân tích dòng mô tả khi kết quả điều khiển logic

Việc so sánh workbook mang lại lợi ích ở đâu

Ba trường hợp sử dụng tự chứng minh giá trị của tính năng này. Kiểm thử hồi quy cho một bộ tạo báo cáo: giữ một workbook đã biết là đúng, tạo lại, so sánh, và làm thất bại bản build khi có bất kỳ khác biệt bất ngờ nào. Soát xét thay đổi: đưa cho người soát xét báo cáo dễ đọc thay vì hai file. Và xác minh di chuyển dữ liệu: sau khi chuyển đổi một lô workbook cũ, so sánh mỗi kết quả với nguồn của nó để chứng minh không có gì bị mất

Trường hợp thứ ba đó kết hợp tự nhiên với các lượt kiểm kê và kiểm toán được mô tả trong bàn làm việc kiểm toán và chuyển đổi workbook, nơi đếm những gì một workbook chứa diễn ra trước khi chuyển đổi và so sánh diễn ra sau đó. Nếu các khác biệt của bạn tập trung quanh các hàng được chèn thêm, các quy tắc viết lại tham chiếu trong điều chỉnh tham chiếu công thức khi chèn và xóa giải thích vì sao các công thức trông không đổi lại báo cáo là đã thay đổi

Các giới hạn, nói thẳng

Việc so sánh bao phủ giá trị, công thức, vùng gộp và sự hiện diện của sheet. Nó không so sánh định dạng số, font, tô nền, quy tắc định dạng có điều kiện, xác thực dữ liệu, biểu đồ, hình ảnh hay tên đã định nghĩa. Một ô có giá trị giống hệt nhưng định dạng đã đổi từ General sang Currency sẽ báo cáo không có khác biệt, đây là điều đúng đối với một phép so sánh dữ liệu và không đủ đối với một lượt soát xét định dạng

Các ô mang giá trị ngày tháng cần một lưu ý riêng cụ thể: chúng được so sánh theo văn bản chuyển đổi của mình, nên một workbook lưu trên hệ ngày 1904 và một workbook trên hệ 1900 có thể so sánh bằng nhau hoặc khác nhau theo những cách gây bất ngờ nếu số serial nền tảng khác nhau. Các quy tắc hệ ngày được đề cập trong số serial ngày tháng và hệ 1904. Khi độ trung thực định dạng hoặc cấp đối tượng là một phần của câu hỏi, hãy kết hợp so sánh khác biệt với một lượt kiểm toán đếm những tính năng đó ở mỗi bên

So sánh workbook, kiểm toán và chuyển đổi đều chạy trên cùng một bộ máy cho Delphi và C++Builder; danh sách tính năng đầy đủ có trên trang thành phần bảng tính HotXLS Delphi