Đọc cache công thức Excel trong Delphi không cần recalc

HotXLS, thư viện Excel gốc cho Delphi và C++Builder, đọc giá trị mà Excel đã lưu cạnh một công thức thông qua TryGetCachedFormulaValue và IXLSFormulaCacheReader. Không entry point nào trong hai cái gọi bộ tính toán, decompile token công thức, cập nhật dirty state, hay ghi bất cứ thứ gì ngược lại vào model, nên một workbook bạn chỉ đọc vẫn giữ nguyên như lúc mở

Kịch bản dẫn đến tính năng này nhàm chán và cực kỳ phổ biến. Một job chạy đêm mở vài trăm workbook do người khác tạo ra, rút một cột tổng từ mỗi tệp, và đẩy các con số vào kho dữ liệu. Các con số tổng đã nằm sẵn trong tệp — Excel đã tính và lưu chúng. Thế nhưng ngay khoảnh khắc job hỏi một ô công thức về giá trị của nó, một thư viện chỉ có một câu trả lời duy nhất cho câu hỏi đó dựng graph phụ thuộc và tính lại cả sheet, và một job lẽ ra bị chặn bởi I/O biến thành một benchmark tính toán

Vì sao đọc một ô công thức lại tốn một vòng tính lại toàn phần?

Vì một getter giá trị trên ô công thức là một yêu cầu tạo ra giá trị, và cách duy nhất đúng trong mọi trường hợp để tạo ra nó là đánh giá công thức. Đó là default đúng cho một ứng dụng chỉnh sửa workbook, và default sai cho một pipeline trích xuất chúng. Tệ hơn, việc đánh giá không thoát khỏi side effect: nó ghi kết quả ngược vào ô, lật các cờ dirty, và có thể phân giải khác với ứng dụng sinh ra tệp khi một hàm không được hỗ trợ hay một tham chiếu ngoài bị gãy. Một job mà bạn mô tả với đội vận hành là read-only lại lặng lẽ tạo ra một workbook không còn khớp với bản trên đĩa, và nếu sau đó có thứ gì lưu nó, tệp trên đĩa cũng đổi theo

Đọc giá trị cache là nửa còn lại của hợp đồng. Nó trả lời một câu hỏi hẹp hơn — ứng dụng sinh ra tệp đã lưu gì ở đây? — và từ chối trả lời bất cứ thứ gì khác. Khi bạn thật sự muốn số mới, HotXLS vẫn cho bạn recalculation lũy tiến dựa trên graph phụ thuộc; vấn đề là trích xuất và đánh giá nên là hai lệnh gọi khác nhau, chứ không phải một lệnh gọi với hai tâm trạng

Ba thông tin độc lập về một ô

Nói kết luận trước: một giá trị công thức cache mang ba thông tin độc lập, và gộp chúng vào một Variant duy nhất làm mất thông tin bạn cần. TXLSFormulaCacheInfo tách chúng thành State, Kind và Value. TXLSFormulaCacheState ghi nguồn gốc qua năm trường hợp — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated và xlfcsInvalidated — trong khi TXLSFormulaCacheValueKind phân loại payload thành xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean hoặc xlfcvError. Chính sự tách bạch này cho phép báo cáo sự hiện diện một cách trung thực: một blank được cache, một chuỗi rỗng được cache, một False được cache, một số 0 được cache và một error được cache đều là giá trị thật, nên sự hiện diện không bao giờ được suy từ VarIsEmpty hay VarIsNull. TryGetCachedFormulaValue chỉ trả True với xlfcsLoaded và xlfcsCalculated, và vẫn điền một trạng thái chẩn đoán được khi trả False

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex, Row và Col đều là 1-based ở đây
    if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
      Writeln('cached value: ', VarToStr(Info.Value))
    else
      Writeln('no usable cache, state ordinal ', Ord(Info.State));
  finally
    Book.Free;
  end;
end;

Vì sao giá trị cache bị thiếu?

Có đúng bốn lý do khiến TryGetCachedFormulaValue trả về False, và state cho biết lý do nào đang áp dụng. xlfcsNotFormula nghĩa là ô chứa một literal hoặc không có gì, và tọa độ ngoài phạm vi được gộp vào cùng câu trả lời. xlfcsMissing nghĩa là ô đúng là công thức nhưng producer không lưu payload giá trị cho nó — kết quả phổ biến khi một generator ghi công thức và để Excel điền kết quả vào lần mở đầu tiên. xlfcsInvalidated nghĩa là văn bản công thức bị thay sau khi load, nên giá trị từng có ở đó mô tả một biểu thức không còn tồn tại. xlfcsCalculated, ngược lại, là trường hợp thành công: nó đánh dấu giá trị do chính code của bạn hoặc bộ đánh giá HotXLS tạo ra trong phiên làm việc này, khác với xlfcsLoaded vốn đến từ tệp

Sự trung thực về một cache thiếu quan trọng hơn việc bịt lỗ hổng. HotXLS từ chối bịa ra một giá trị, và khi lưu nó cũng nghiêm ngặt như vậy — chỉ xlfcsLoaded và xlfcsCalculated phát giá trị cache, trong khi xlfcsMissing và xlfcsInvalidated chỉ ghi công thức thay vì đóng băng một con số cũ kỹ vào tệp. Điều đó để lại cho bạn ba phản hồi hợp lý trong pipeline: bỏ qua dòng và ghi nhận khoảng trống, chủ động tính lại đúng một workbook đó và chấp nhận cái giá, hoặc đánh giá rồi đối chiếu. Nếu con số sau đánh giá không khớp với thứ ứng dụng sinh ra tệp lẽ ra sẽ ghi, bộ theo dõi đánh giá công thức là công cụ để tìm chỗ hai phép tính rẽ nhánh, thay vì đoán từ kết quả

Một reader dùng chung cho ba engine classic, OOXML và ODF

Một pipeline không nên quan tâm tệp vừa mở là BIFF, OOXML hay ODF. IXLSFormulaCacheReader là entry point chỉ-đọc duy nhất cho cả ba: cả TXLSWorkbook.CreateFormulaCacheReader lẫn TXLSXWorkbook.CreateFormulaCacheReader đều trả về một adapter nhẹ bọc trên phép tra cứu ô thưa mà mỗi engine vốn đã dùng, với tọa độ sheet, hàng và cột 1-based giống hệt nhau. Các lớp workbook cố ý không tự hiện thực interface này — một tham chiếu interface tới workbook sẽ đổi ngữ nghĩa sở hữu của nó và cho phép caller lách qua lease vòng đời. Thay vào đó, việc hủy workbook xóa con trỏ thô bên trong lease đó, và bất kỳ reader nào vẫn đang được code của bạn giữ sẽ raise EXLSFormulaCacheReaderInvalidated ở lần truy vấn kế tiếp thay vì dereference bộ nhớ đã giải phóng. Đó là kiểm tra vòng đời fail-fast, không phải cam kết concurrency

var
  Reader: IXLSFormulaCacheReader;
  Info: TXLSFormulaCacheInfo;
  Row, Missing, Errors: Integer;
  Total: Double;
begin
  Reader := Book.CreateFormulaCacheReader;
  Total := 0;
  Missing := 0;
  Errors := 0;
  for Row := 2 to LastRow do
    if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
    begin
      case Info.Kind of
        xlfcvNumber: Total := Total + Double(Info.Value);
        xlfcvError:  Inc(Errors);
      end;
    end
    else if Info.State = xlfcsMissing then
      Inc(Missing);
  // Không bộ tính toán nào chạy, không cờ dirty nào đổi, Book giữ nguyên
end;

Các byte cache thực sự nằm ở đâu

Với tệp .xls classic, cache là trường FormulaValue của bản ghi Formula, tám byte được mô tả trong [MS-XLS] §2.5.133. Khi word cao bằng $FFFF thì payload không phải một IEEE 754 double mà là một variant có gắn thẻ, và layout rất dễ sai tinh vi: variant type nằm trong val[0] và payload boolean hoặc BErr nằm trong val[2], với val[1] không định nghĩa. HotXLS từng đọc payload từ val[1] — kiểu off-by-one chỉ lộ trên đúng những tệp cache một boolean hay một error thay vì một số. Reader và writer shared-formula giờ thống nhất cùng một bộ offset, nên một TRUE được cache sống sót qua một vòng load và save thay vì phân rã thành nhiễu

Độ trung thực kiểu dữ liệu trong các định dạng package là một bài toán riêng với cái bẫy của riêng nó. Trong OOXML, giá trị cache treo trên phần tử c như <v>, với thuộc tính t nêu kiểu theo ECMA-376 Part 1 §18.3.1.4. HotXLS đọc t="e" thẳng vào một Variant varError và ánh xạ ngược về văn bản error chuẩn khi lưu, nên error không bao giờ hóa trang thành số nguyên thường — nhưng RTL của Delphi sẽ không giúp bạn ở đây, vì VarAsType(Integer, varError) raise một ngoại lệ chuyển đổi. Cách dựng khả thi là đặt TVarData.VType và TVarData.VError trực tiếp. Ngày tháng đi theo cùng kỷ luật nhưng theo chiều ngược lại: t="d" và kiểu giá trị ngày của ODF là khai báo kiểu tường minh và trở thành varDate, trong khi cache số của BIFF không mang cờ ngày nào và vì thế vẫn là Double. HotXLS không bao giờ đoán ngày từ number format của ô, vì number format là phần trình bày còn cache là dữ liệu. ODF thêm một trường hợp đáng biết — office:value-type="void" diễn đạt một cache có mặt nhưng không mang giá trị, và vì ODF không có kiểu giá trị error, văn bản trông như error được giữ nguyên dạng văn bản thay vì được nâng cấp thành error

function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
  case Info.State of
    xlfcsNotFormula:  Result := 'not a formula cell';
    xlfcsMissing:     Result := 'formula stored with no cached value';
    xlfcsInvalidated: Result := 'formula replaced since load';
  else
    case Info.Kind of
      xlfcvError:    Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
      xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
      xlfcvBoolean:  Result := BoolToStr(Info.Value, True);
      xlfcvNumber:   Result := FloatToStr(Double(Info.Value));
      xlfcvString:   Result := VarToStr(Info.Value);
    else
      Result := 'present but blank';
    end;
  end;
end;

Shared formula có chia sẻ giá trị cache cho nhau không?

Không, và giả định điều ngược lại chính là cách một lượt quét kết thúc với cùng một con số cho cả cột. Một shared formula OOXML chỉ chia sẻ biểu thức công thức và tối ưu lưu trữ; mỗi ô thành viên vẫn sở hữu <v> riêng. Vì vậy HotXLS không bao giờ lan truyền cache của ô gốc sang một ô theo sau đến mà không có giá trị, và một ô theo sau được load với xlfcsMissing vẫn báo xlfcsMissing sau khi lưu rồi mở lại. Nếu bạn đang tìm hiểu nhóm này được lưu và mở rộng thế nào ngay từ đầu, cơ chế của thuộc tính si của shared formula và quá trình mở rộng được nói riêng; còn với việc đọc cache, quy tắc gói gọn trong một dòng — hỏi từng ô, đừng tin thứ bạn chưa hỏi

Đọc giá trị cache, reader thống nhất đa engine và engine recalculation mà bạn có thể chọn không gọi đều nằm 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 mang tài liệu API đầy đủ cho các entry point workbook và reader xuất hiện trong bài