Audit cache công thức Excel bằng deep recalc của HotXLS

HotXLS trả lời câu hỏi mà mọi spreadsheet pipeline sớm muộn cũng phải hỏi: các con số lưu trong workbook còn khớp với những công thức đã sinh ra chúng hay không. CalculateAndVerify tính lại toàn bộ dependency graph vào một overlay cách ly, so từng kết quả với cached value đã nằm trong cell, và báo các chỗ lệch nhau. Mặc định thì nó không đổi gì cả

Lý do chuyện này quan trọng là một file spreadsheet lưu hai thứ cho mỗi formula cell: công thức và giá trị cuối cùng mà ai đó đã tính cho nó. Excel giữ hai thứ đồng bộ. Mọi thứ khác trên đời thì chưa chắc. Một file từng đi qua một library cũ, một lần tính lại một phần, một XML part bị sửa tay hay một công cụ ghi value mà không tính lại sẽ sẵn sàng trình ra một tổng không còn suy ra được từ input, và chẳng có gì trong file format gắn cờ cho chuyện đó

Vì sao một cached value lệch khỏi công thức lại nguy hiểm đến thế?

Vì nó vô hình trên mọi đường đọc thông thường. Mở file bằng viewer, đọc cell qua API, export sang CSV hay PDF, bạn nhận con số từ cache. Công thức nằm ngay đó trong cùng cell, và chẳng ai đem ra so. Sự lệch nhau chỉ lộ khi ai đó mở workbook bằng Excel — thứ tính lại lúc load dưới đa số setting — và bỗng một report đã được duyệt quý trước hiện ra những tổng khác

Cuộc audit tồn tại để biến phép so sánh đó thành một operation có kế hoạch, có chủ ý thay vì một tai nạn. Nó là bản tương đương spreadsheet của việc kiểm tra checksum: rẻ đến mức chạy được ngay trong intake pipeline, và là thứ duy nhất biến một vấn đề toàn vẹn dữ liệu câm lặng thành một report bạn hành động được

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Có ba overload và chúng trả lời ba câu hỏi khác nhau. CalculateAndVerify không tham số trả về một con số mismatch, đủ cho một health check. Overload với out array các mismatch đưa cho bạn các cell. Overload nhận TXLSRecalcAuditOptions trả về một TXLSCalculationAuditReport đầy đủ — cái để với tới khi bạn cần biết không chỉ value nào lệch mà cả vì sao audit không evaluate nổi một thứ gì đó

Overlay, và vì sao audit không ghi

Mọi giá trị tính lại đều đậu vào overlay chứ không vào cell cache, và overlay được tiêm ngay phía trước callback đọc cell trong cả hai workbook engine. Chính vị trí đó làm audit tự nhất quán: khi B1 được tính lại và C1 phụ thuộc B1, C1 nhìn thấy giá trị từ lần audit này chứ không phải cached value cũ. Thiếu nó, một lỗi phía thượng nguồn chỉ được báo một lần rồi bị hấp thụ, và mọi cell hạ nguồn sẽ trông như đồng tình với một input sai

Những cell mà giá trị tính lại khớp cache thì thậm chí không vào overlay. Đó không phải micro-optimization, đó là thứ giữ cho audit ở mức giá phải chăng. Một workbook sạch với một trăm nghìn công thức thực hiện đúng không overlay write nào và pass gói trong khoảng 1.35x so với một lần tính lại trọn vẹn — khoảng cách giữa thứ bạn chạy được ở mọi lần intake và thứ bạn chỉ dám chạy mỗi quý một lần

Phép evaluate đi theo thứ tự topological tuần tự suy từ dependency graph, với mọi node đánh dấu dirty trước, nên mỗi cell được tính đúng một lần sau khi input của nó xong. Nếu bạn muốn bộ máy incremental giữ cho một workbook đang sống luôn cập nhật thay vì audit một workbook đã lưu, đó là một cơ chế khác, được mô tả trong incremental recalculation và dependency graph

Failure được phân loại, không gộp đống

Một cell audit không evaluate nổi không phải cùng một phát hiện với một cell có giá trị lệch, và TXLSCalculationAuditIssueKind giữ các hạng mục tách bạch. xlcaiCacheMismatch là sự lệch giá trị. xlcaiMissingFunction và xlcaiMissingName nói rằng evaluator gặp thứ nó không triển khai hoặc không resolve được. xlcaiUnsupportedArguments phủ các dạng argument ngoài tập được hỗ trợ. xlcaiExternalReferenceDenied và xlcaiExternalReferenceMissing tách một lời từ chối chính sách khỏi một workbook vắng mặt. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled và xlcaiInternalFailure khép lại bộ sưu tập

Một sự phân biệt đáng nói thẳng vì nó đảo ngược một giả định phổ biến. Một Excel error code dương là một result, không phải failure. Một cell hợp lệ evaluate ra #DIV/0! đã tính đúng, nên audit lưu error đó vào overlay và đem so với cache như mọi giá trị khác. Một workbook đầy các cell chứa error có chủ ý cho ra đúng không phát hiện nào, còn một workbook mà error xuất hiện hoặc biến mất từ khi value được cache thì cho ra đúng những phát hiện bạn muốn

Circular reference có cách xử lý riêng. Node trong một cycle không bao giờ vào thứ tự topological, nên từng node được báo riêng lẻ với xlcaiCircularReference, và audit không chạy iterative solver. Đó là một hợp đồng read-only có chủ ý: việc bật iteration ảnh hưởng cách diễn giải result code, không ảnh hưởng audit làm gì. Cơ chế evaluate lặp được nói riêng trong iterative calculation và circular reference

Đọc một chuỗi failure

Khi một formula evaluate thất bại, biết cell nào fail thường là chưa đủ, vì failure thường nằm sâu ba tầng trong một chuỗi reference. Vì vậy mỗi issue mang theo một chuỗi Stack render frame ngoài cùng trước, ở dạng Sheet1!A1 > Sheet1!B2 > Data!C7, để report trỏ vào cell thật sự gãy chứ không phải cell bạn tình cờ đang nhìn

Bộ ghi được giới hạn. MaxStackFrames mặc định 64 với sàn là 8, và chuỗi fail sâu nhất mới là cái được giữ lại: frame trong ghi lại chuỗi khi failure khởi phát ở đó, và các frame ngoài unwind về sau không ghi đè lên. Nếu bất kỳ chuỗi nào vượt ngân sách, Report.StackTruncated được set, cho bạn biết sự khác nhau giữa một chuỗi ngắn và một chuỗi bạn chưa xem hết

// Mặc định là read-only. ApplyResults chỉ commit overlay sau một
// lần audit thành công trọn vẹn, dưới một write guard từ chối commit
// nếu cấu trúc workbook đổi trong lúc audit đang chạy
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // so sánh tuyệt đối, lộ drift
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // audit dừng ở ranh giới node kế tiếp
end;

Khi nào nên để audit sửa workbook?

Chỉ khi audit quay về sạch hoàn toàn các issue hạng failure — và đó chính là điều kiện ApplyResults ép giúp bạn. Commit xảy ra sau một pass thành công trọn vẹn, chưa bị cancel, và qua được một guard cấu trúc: binary engine theo dõi một workbook change identifier, OOXML engine chụp một structure generation theo từng worksheet. Nếu có gì dịch chuyển trong lúc audit chạy, kết quả mô tả một workbook không còn tồn tại và commit bị từ chối

Hãy để ý sự bất đối xứng có chủ ý. Cache mismatch không chặn việc áp dụng, vì chúng đúng là thứ commit tồn tại để sửa. Issue hạng failure thì chặn, vì một workbook mà vài công thức không evaluate nổi sẽ chỉ được sửa một nửa, và một workbook sửa nửa còn tệ hơn một workbook không sửa mà bạn biết phải nghi ngờ

Tolerance là một quyết định chính sách, không phải default

Phép so sánh mặc định là absolute tolerance 1E-6 với relative tolerance tắt, giữ hành vi kinh điển và âm thầm chấp nhận drift cỡ 4E-7. Thường thì đó là điều đúng: khác biệt thứ tự evaluate floating-point giữa thứ đã tạo ra file và evaluator hiện tại sẽ cho ra những khác biệt cỡ đó trên các tổng dài, và báo chúng thành phát hiện toàn vẹn chỉ là nhiễu

Hãy set cả hai tolerance về 0 khi câu hỏi khác đi: khi bạn đang tìm hiểu liệu evaluator có đổi hành vi giữa các version hay không, hay một công cụ của bên thứ ba có ghi lại value theo cách khác một cách tinh vi hay không. Ở mức 0, cùng drift 4E-7 đó trở nên nhìn thấy, và mọi thứ khác cũng vậy. Chọn tolerance theo câu hỏi bạn đang hỏi, và ghi lại lựa chọn đó cạnh report, vì một report không kèm tolerance của nó là một report không thể diễn giải

Hai capability lân cận khép lại bức tranh. Khi bạn muốn biết vì sao một công thức đơn lẻ sinh ra đúng giá trị đó, góc nhìn từng bước trong formula evaluation tracer là công cụ đúng. Khi bạn chủ ý muốn cached value được tôn trọng mà không tính lại gì cả — ví dụ trên một đường intake bắt buộc tái hiện file đúng như lúc nó đến — mode đó được mô tả trong đọc cached formula value mà không tính lại. Audit là thứ nằm giữa hai cực đó: nó cho bạn biết tin vào cache có an toàn không. Tính năng đi kèm HotXLS Delphi spreadsheet component cho cả hai engine binary lẫn OOXML