Chặn việc lưu XLS âm thầm tính lại công thức trong Delphi

HotXLS, thư viện Excel thuần cho Delphi và C++Builder, lưu một workbook BIFF8 .xls cổ điển theo hướng cache trước: TXLSWorksheet.WriteFormula hỏi TXLSWorkbook.TryGetCachedFormulaValue giá trị mà Excel lưu bên cạnh mỗi công thức và chỉ gọi bộ tính giá trị khi cache đó thiếu hoặc đã bị vô hiệu. Một workbook bạn mở ra và chưa từng đụng vào sẽ lưu lại đúng những con số cũ, còn kết quả mới cần một lời gọi Recalculate tường minh thay vì là hệ quả ngầm của SaveAs

Bug buộc phải đưa giao kèo này ra ánh sáng thì nhỏ đến mức ngại. Một tệp corpus tên nested-subtotals.xls chứa một tổng lớn ở R2C4 với giá trị cache là 37. Mở nó bằng HotXLS, hỏi TryGetCachedFormulaValue cho ô đó, nhận 37. Lưu mà không đổi một ô nào, mở bản đã lưu, hỏi đúng câu đó, nhận 67. Không có API nào bị yêu cầu tính toán gì cả, vậy mà một con số trong tệp đã dịch đúng 30 đơn vị — và 30 tình cờ là tổng của hai subtotal nhóm, 10 và 20, nằm trong vùng mà tổng lớn bao phủ

Vì sao lưu một tệp XLS lại làm đổi giá trị công thức?

Hai lỗi độc lập phải trùng khớp thì 37 mới thành 67, và vá riêng một cái sẽ che mất cái kia. Cái thứ nhất mang tính cấu trúc: writer cổ điển tính lại mọi công thức ở mọi lần lưu. Cái thứ hai là một phép kiểm tra kiểu không bao giờ đúng với công thức nạp từ đĩa, khiến bộ tính giá trị đếm các ô SUBTOTAL lồng nhau hai lần. Tệp corpus chỉ đơn giản là đầu vào đầu tiên mà ở đó việc tính lại lúc lưu cho ra đáp án khác Excel và có người đem hai đáp án ra so. Lỗi cấu trúc thì dễ nói: trước v2.382.3, TXLSWorksheet.WriteFormula cùng người anh em dùng chung công thức WriteFormulaWithTExp lấy trường FormulaValue tám byte của mọi record Formula bằng cách gọi TXLSWorkbook.GetFormulaValue, tức chính bộ tính giá trị. Cache mà ParseFormula đã cẩn thận giải mã từ tệp nguồn lúc nạp chưa bao giờ được hỏi tới ở chiều ghi ra. Thực chất mỗi lần lưu là một lần tính lại toàn bộ với API tính lại cấp workbook bị bỏ qua, nên không có gì bạn đặt trên workbook có thể ngăn nó. Bất cứ chỗ nào bộ tính giá trị của HotXLS bất đồng với Excel, dù là một hàm chưa hỗ trợ một cách chính đáng hay chỉ là một bug thường, đều trở thành một thay đổi dữ liệu âm thầm khi lưu

Lỗi thứ hai nằm trong callback xử lý subtotal lồng nhau mà bộ tính giá trị dùng. Excel định nghĩa mọi dạng SUBTOTAL là bỏ qua các ô mà công thức của chính chúng cũng là một SUBTOTAL, nên bộ tính toán trong lxCalc.pas bật cờ FIgnoreSubtotalCells trong lúc tổng hợp và hỏi workbook, qua TXLSWorkbook.GetClassicIsSubtotalCell, xem từng ô trong vùng có phải như vậy không. Callback đó lấy text công thức dưới dạng Variant rồi kiểm tra bằng VarType(f) = varOleStr. Text quay về từ GetUnCompiledFormula dưới dạng String của Delphi, mà một String gán vào Variant là varUString, không bao giờ là varOleStr. Vị từ đó sai với mọi ô trong mọi tệp đã nạp, các subtotal nhóm bị cộng vào tổng lớn lần thứ hai, và trong một lần lưu có tính lại tất cả, 10 + 20 + 7 thành 67

// HotXLS 2.381 và trước đó: một Variant công thức dựng từ String
// là varUString, nên phép so này chưa bao giờ đúng
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr chấp nhận varString, varOleStr và varUString,
// và AGGREGATE cũng được loại khỏi subtotal bao ngoài như Excel làm
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

v2.382.0 phát hành bản vá VarIsStr và, nhân lúc còn trong cùng hàm, dạy callback rằng các ô AGGREGATE cũng được loại khỏi subtotal bao ngoài. Chỉ thế đã đủ làm assertion của corpus qua, vì 37 sau khi tính lại giờ khớp với 37 đã nạp. Nhưng nó không làm thư viện trở nên trung thực: lần lưu vẫn tính lại, và bài test chỉ xanh vì bộ tính giá trị tình cờ đồng ý với Excel trên đúng tệp đó. Các quy tắc về việc SUBTOTAL và AGGREGATE bỏ qua những ô nào, kể cả dòng bị ẩn, được trình bày trong bài về SUBTOTAL, AGGREGATE và dòng ẩn; điều quan trọng ở đây là không bộ tính giá trị nào được có tiếng nói trên một tệp mà bạn không yêu cầu nó tính

Excel bảo đảm điều gì về giá trị cache khi lưu?

Excel coi một lần lưu là một ảnh chụp, không phải một sự kiện tính toán. Giá trị được ghi vào trường FormulaValue của một record Formula ([MS-XLS] §2.4.127, bố cục ở §2.5.133) là bất cứ thứ gì ô đang hiển thị, mà ở chế độ tính toán thủ công thì có thể cũ mèm nhiều năm, và Excel vẫn ghi nó trung thực. Tính lại là một thao tác riêng với trigger riêng. HotXLS giờ theo đúng quy tắc đó cho các lần lưu cổ điển: WriteFormula và WriteFormulaWithTExp gọi TryGetCachedFormulaValue trước, lấy CacheInfo.Value khi trạng thái là xlfcsLoaded hoặc xlfcsCalculated, và chỉ rơi xuống GetFormulaValue với xlfcsMissing cùng xlfcsInvalidated. Nửa còn lại của giao kèo này, ở phía đọc, gồm ý nghĩa từng trạng thái và vì sao một giá trị trống hay False được cache vẫn tính là một giá trị, được trình bày trong Đọc giá trị công thức đã cache trong Excel bằng Delphi mà không cần tính lại

Đường dự phòng được giữ lại có chủ ý, không bị bỏ đi. Một công thức bạn gán trong phiên này qua Cells[Row, Col].Formula đến mà không có cache, và một công thức bạn thay thế trên một ô đã nạp được đánh dấu xlfcsInvalidated bởi _SetCompiledFormula; cả hai đều được tính ở lúc lưu y như trước, nên một workbook sinh ra từ code vẫn mở trong Excel với đầy số liệu. Khi ngay cả bộ tính giá trị cũng không tạo nổi một giá trị, writer phát ra một payload bằng 0 và bật fAlwaysCalc (bit 0 trong grbit của §2.4.127) để Excel tự tính lại ô đó khi mở thay vì tin vào giá trị đặt chỗ

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // sheet, dòng và cột đều 1-based: R2C4 trên sheet đầu tiên
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // không có bộ tính giá trị nào tham gia với ô đã cache
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 với nested-subtotals.xls
    // Một lần lưu có tính lại sẽ ghi 67 ở đây
  finally
    Book.Free;
  end;
end;

Ô gốc của shared formula BIFF giữ giá trị cache ở đâu?

Trong record Formula của chính nó, như mọi ô công thức khác, và đó đúng là thứ khiến ô gốc của một nhóm shared formula thành chỗ duy nhất mà việc lưu theo cache vẫn đánh rơi. Một shared formula trong BIFF8 được lưu dưới dạng record ShrFmla ([MS-XLS] §2.4.260) nằm sau record Formula của ô trên cùng bên trái, và mọi ô thành viên, kể cả ô gốc, đều mang một rgce chỉ gồm một token PtgExp duy nhất (§2.5.198): byte đầu của biểu thức đã parse là $01, tiếp theo là dòng và cột của ô gốc. Các ô follower thì tự đủ — HotXLS đọc FormulaValue của từng ô và phân giải biểu thức bằng cách tra công thức đã biên dịch của ô gốc. Ô gốc thì khác, vì khi record Formula của nó được parse thì biểu thức chưa tồn tại; nó đến muộn hơn một record

Khoảng cách một record đó chính là chỗ cache biến mất. TXLSReader.ParseFormula giải mã giá trị cache và, khi thấy một PtgExp có tọa độ trùng với chính ô đó, ghi nhớ ô vào FSharedFormulaRow và FSharedFormulaCol rồi công bố cache cho ô. Khi record ShrFmla ($04BC) đến, ParseSharedFormula biên dịch biểu thức rồi cài nó bằng _SetCompiledFormula, và _SetCompiledFormula làm điều nó buộc phải làm với mọi thay đổi công thức: xóa FCachedFormulaValue và đặt trạng thái về xlfcsMissing. Vì thế con 37 đã nạp của ô gốc bị ném đi trước khi ai kịp đọc nó, TryGetCachedFormulaValue báo ô gốc là không có cache, và writer lưu theo cache ngoan ngoãn rơi xuống bộ tính giá trị đúng ở ô mà mọi người đang nhìn. Record Array (§2.4.4) có cùng thứ tự đó và cũng có cùng lỗ hổng

Bản vá ở v2.382.3 thêm trường thứ ba, FSharedFormulaCachedValue, bên cạnh tọa độ ô gốc đang chờ. ParseFormula cất giá trị cache đã giải mã vào đó khi nhận ra một ô gốc, và cả ParseSharedFormula lẫn ParseArrayFormula phát lại nó qua _SetCellCachedFormulaValue ngay sau khi cài biểu thức đã biên dịch, rồi đặt lại chỗ cất về Unassigned. Biến thể String của cache không bị ảnh hưởng bởi tất cả chuyện này vì payload của nó đến trong một record String riêng và được định tuyến theo tọa độ ô, không theo thứ tự record. Nếu bạn làm việc với phía OOXML của cùng khái niệm này, bài về khai triển si của shared formula trong XLSX giải thích vì sao định dạng package không có vấn đề thứ tự tương đương nhưng lại có những cái bẫy khai triển riêng

Vì sao các ô follower của shared formula cần một phép dịch tương đối?

Vì biểu thức được lưu trong ShrFmla được viết tương đối so với ô gốc, và một follower dùng lại nó nguyên văn sẽ tính các tham chiếu của ô gốc thay vì tham chiếu của chính nó. Reader cũ cài Value.GetCopy() lên từng follower, một bản sao sâu không dịch chuyển gì, nên một nhóm gốc ở B1 với =A1*3 cho mọi follower cũng =A1*3. Việc lưu theo cache thật ra che bug này với các tệp đã nạp, vì các follower có FormulaValue riêng và không cần biểu thức để lưu cho đúng; nó lộ ra ngay khi có gì đó tính lại. Reader giờ cài TXLSCompiledFormula.GetCopy(row - srow, col - scol), đi qua cây cú pháp và dịch mọi tham chiếu tương đối theo khoảng cách từ follower tới ô gốc, nên follower ở B2 sở hữu một =A2*3 thật sự

Bài regression ghim cả hai hành vi này đáng đọc vì nó từ chối để một sự trùng hợp lọt qua. Nó dựng một workbook với =A1*3 và =A2*3 trên các đầu vào 2 và 4, rồi tiêm các giá trị cache cố tình sai 999 và 888 qua _SetCellCachedFormulaValue, một lần với UseSharedFormulas bật và một lần tắt. Sau một lần lưu rồi nạp lại, cả hai ô vẫn phải báo 999 và 888 — bằng chứng rằng lần lưu không đụng tới cache của ô gốc lẫn cache của follower. Chỉ sau một lời gọi Recalculate tường minh chúng mới được thành 6 và 12, bằng chứng rằng biểu thức đã dịch của follower là đúng. Một bài test gieo các giá trị đúng sẽ qua được cả với writer cũ, và đó chính là lý do phải gieo giá trị sai

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // đổi một đầu vào

    // Cache đã nạp của các công thức phụ thuộc KHÔNG bị vô hiệu bởi
    // một sửa đổi literal, nên một SaveAs thường sẽ giữ các số cũ.
    // Hãy yêu cầu tính lại khi bạn thật sự muốn kết quả mới:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

Giao kèo lưu theo cache không làm giúp bạn điều gì

Việc lưu theo cache giữ nguyên những gì đã nạp; nó không theo dõi xem thứ đã nạp còn đúng hay không. Sửa một literal mà một công thức phụ thuộc vào sẽ đánh dấu đồ thị dependency là bẩn đối với bộ tính giá trị, nhưng nó để nguyên cache xlfcsLoaded của ô phụ thuộc, và writer cổ điển sẽ vui vẻ ghi giá trị cũ đó trừ khi bạn gọi Recalculate hoặc đọc Value của ô trước, việc này tính ra giá trị rồi chuyển trạng thái sang xlfcsCalculated. Đây đúng là đánh đổi mà Excel thực hiện ở chế độ tính toán thủ công, và nó là đánh đổi đúng cho một pipeline mở tệp của bên thứ ba, sửa vài nhãn rồi lưu — nhưng nó có nghĩa một workbook có sửa đầu vào phải tự chịu trách nhiệm về bước tính lại của mình một cách tường minh. Chính sách RecalcBeforeSave của writer XLSX không bị thay đổi bởi công việc này và có chế độ thủ công riêng giữ cache theo cùng tinh thần. Hai ranh giới nhỏ hơn suy ra từ đó: đường lưu theo cache chỉ giúp các ô có trạng thái xlfcsLoaded hoặc xlfcsCalculated; một bộ sinh chỉ ghi công thức mà không bao giờ tính chúng vẫn trả một lần tính cho mỗi ô ở lúc lưu, y như trước. Và bản vá subtotal lồng nhau sửa đúng việc bộ tính giá trị bỏ qua những ô nào, chứ không sửa mọi hàm mà nó cài đặt — một tệp có công thức mà HotXLS không tính giống hệt Excel thì giờ an toàn khi round-trip mà không đụng tới, nhưng một lời gọi Recalculate có chủ ý trên tệp đó vẫn cho ra đáp án của thư viện chứ không phải của Excel, và bạn nên so hai đáp án trước khi tin vào một lần lưu có tính lại

Việc lưu tệp cổ điển theo cache, các cache ô gốc của shared formula và array formula được khôi phục, phép dịch tham chiếu tương đối cho follower dùng chung công thức, cùng các quy tắc lồng SUBTOTAL và AGGREGATE đã sửa đều có trong HotXLS Delphi Spreadsheet Component tiêu chuẩn cho Delphi và C++Builder, không phụ thuộc Excel hay bất kỳ OLE automation server nào; trang sản phẩm có đầy đủ tham chiếu API cho workbook, trình đọc cache và các điểm vào tính lại được dùng ở đây