เทียบความต่างสมุดงาน 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