Trình tính toán công thức HotXLS và hàm tự định nghĩa trong Delphi

Một thư viện bảng tính chỉ lưu chuỗi công thức và một thư viện có formula engine hoạt động thực sự là hai sản phẩm khác nhau trông giống hệt nhau cho tới khoảnh khắc bạn hỏi một trong hai cái đó một con số. Phần lớn mã bảng tính Delphi không bao giờ nhận ra khoảng cách này, bởi vì Excel che lấp nó: viết SUM(B2:B501) vào một ô, lưu lại, và Excel sẽ tính lại tổng ngay khoảnh khắc một con người mở file lên. Loại con người ra khỏi vòng lặp, chạy cùng workbook đó qua một pipeline server xuất thẳng ra CSV, và sự khác biệt đó không còn là chuyện lý thuyết nữa. File CSV mang theo văn bản chữ =SUM(B2:B501) ở nơi lẽ ra phải là một con số, bởi vì chưa từng có gì thực sự đánh giá công thức đó cả

Đó là lằn ranh mà HotXLS đứng ở phía đúng. Nó đối xử với một công thức theo đúng cách các định dạng file làm: như văn bản đã lưu cộng thêm một kết quả cache tùy chọn, nên một lượt xuất CSV trần trụi tái tạo công thức nấu ăn chứ không phải món ăn. Nhưng nó cũng mang theo một calculation engine bạn có thể gọi trực tiếp, cùng một engine ở cả hai lớp giao diện XLS và XLSX, cộng thêm một móc nối để giải quyết những tên hàm engine chưa từng nghe tới. HotXLS là một thư viện Object Pascal thuần gốc đọc và ghi XLS và XLSX từ Delphi và C++Builder mà không cần Excel automation, và nửa phần tính toán của nó chính là thứ biến các công thức đã lưu trở lại thành giá trị theo yêu cầu

Công thức được lưu trữ, không được đánh giá ngay lập tức

Viết một công thức vào một ô không tính toán gì cả. Vào lúc lưu, workbook ghi lại văn bản công thức. Ở phía XLS nó cũng ghi lại các cờ được điều khiển bởi RecalcOnSave, mặc định là True và bảo Excel tính lại khi mở. Mô hình đó đúng cho các file dành cho Excel và sai cho các pipeline tiêu thụ giá trị ô trực tiếp, dù đó là xuất CSV, xuất HTML, hay mã của chính bạn đọc lại ô. Với những trường hợp đó, hãy đánh giá tường minh bằng Calculate. Nó tồn tại tại bốn điểm vào: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook, và TXLSXWorksheet đều lộ ra function Calculate(const Formula: WideString): Variant

// đánh giá ngay trong tiến trình, rồi chuyển đi giá trị thay vì công thức nấu ăn
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // giờ CSV mang theo con số

Biểu thức đưa cho Calculate là văn bản công thức Excel thông thường. Tham chiếu liên trang tính, defined name, và các hàm lồng nhau đều được giải quyết đối chiếu với workbook đang nằm trong bộ nhớ hiện tại, điều này khiến lệnh gọi hữu ích vượt xa việc chỉ vá lại các bản xuất CSV. Hãy coi nó như một cơ chế assertion. Một trình tạo vừa mới ghi năm trăm hàng chi tiết có thể hỏi workbook về tổng lớn của chính nó và so sánh con số đó với giá trị nó đã tính độc lập trong Pascal, bắt được một lỗi range lệch-một trước khi một kiểm toán viên của khách hàng phát hiện ra

Nó cũng vạch ra chiến lược test đúng đắn cho kết quả xuất nặng về công thức. Excel vẫn là bản triển khai tham chiếu của ngôn ngữ công thức, nên với số ít công thức mang hệ quả nghiệp vụ, hãy giữ một file fixture đã được phê duyệt mà giá trị kỳ vọng của nó do chính Excel tạo ra, và để pipeline build đánh giá các công thức của workbook được tạo ra bằng Calculate đối chiếu với những fixture đó. Khác biệt khi đó sẽ lộ ra dưới dạng test thất bại trong Delphi thay vì dưới dạng sai lệch bị một khách hàng phát hiện khi so sánh hai báo cáo

Thêm các hàm nghiệp vụ với OnUserFunction

Khi engine gặp một tên hàm nó không nhận ra, nó nêu lên một sự kiện thay vì thất bại ngay lập tức. Gán OnUserFunction trên một trong hai class workbook và bạn có thể tự giải quyết lệnh gọi đó:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args đến dưới dạng một mảng Variant
    Handled := True;
  end;
end;

// đấu nối và sử dụng
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Ba chi tiết đáng chú ý. Thứ nhất, chỉ đặt Handled := True khi bạn thực sự nhận ra tên đó. Để nó là False cho phép engine tiếp tục xử lý hàm-không-xác-định bình thường của nó, nên một handler duy nhất có thể phục vụ nhiều workbook mà không cần nhận vơ mọi thứ đi qua nó. Thứ hai, hãy so sánh tên không phân biệt hoa thường bằng SameText, bởi vì người viết công thức gõ discount( và DISCOUNT( thay thế lẫn nhau. Thứ ba, các tham số đến ở trạng thái đã được đánh giá trước: DISCOUNT(A1) đưa cho bạn giá trị của A1, không phải tham chiếu, nên một hàm không thể biết đầu vào của nó đến từ đâu. Điểm cuối cùng đó thiết lập nên giới hạn mà phần tiếp theo nói tới

Hãy đối xử với phần thân của handler với cùng mức độ phòng thủ như bất kỳ điểm vào bên ngoài nào khác. Mảng Args phản ánh bất cứ điều gì người viết công thức đã gõ, nên hãy xác thực số lượng và kiểu tham số trước khi đánh chỉ số vào nó, và quyết định trước xem một lệnh gọi không hợp lệ sẽ trả về gì: một giá trị lỗi Variant, hay một exception được ném ra. Lựa chọn này quan trọng bởi vì một exception ném ra bên trong handler sẽ lan truyền ra ngoài qua lệnh gọi Calculate đã kích hoạt việc đánh giá. Điều đó chấp nhận được trong một trình tạo được kiểm soát chặt chẽ và thô lỗ trong một service đánh giá các workbook do người dùng viết, nơi một công thức tồi sẽ đánh sập cả request. Trong bối cảnh đó, hãy bắt lỗi bên trong handler và trả về một giá trị đánh dấu mà workflow xung quanh có thể nhận ra và ghi log

Các hàm nhận biết vị trí cần biến thể Ex

Một số hàm phụ thuộc một cách chính đáng vào nơi chúng đang được đánh giá. Một tỷ lệ khác nhau theo từng sheet, một lookup tương đối theo hàng, một hệ số nhân theo vùng chỉ áp dụng trên các sheet khu vực: không cái nào trong số này có thể được trả lời chỉ bằng giá trị tham số. Sự kiện thông thường không thể diễn đạt điều đó, nên engine cung cấp OnUserFunctionEx, giống hệt ngoại trừ một tham số bổ sung:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // cùng một công thức cho ra tỷ lệ khác nhau trên mỗi sheet khu vực
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext mang theo SheetIndex, Row, và Col của ô đang được đánh giá. Nếu kết quả của một hàm phụ thuộc vào vị trí của nó dù chỉ một chút, hãy đấu nối sự kiện Ex ngay từ đầu. Việc chắp thêm context vào một handler đã được ba mươi công thức gọi tới sẵn rồi lộn xộn hơn nhiều so với việc chọn đúng chữ ký ngay từ ngày đầu, và ngoài điểm đó ra hai sự kiện giống nhau tới mức chẳng có lý do gì để bắt đầu với cái hẹp hơn

Hàm tùy chỉnh không đi theo tới Excel

Một hàm tùy chỉnh sống hoàn toàn bên trong tiến trình của bạn. Tên DISCOUNT chỉ mang ý nghĩa trong khi mã Delphi của bạn và event handler của nó đang chạy. Mở file đã lưu trong Excel và DISCOUNT chỉ là một tên không được nhận diện; ô hiển thị #NAME? trừ khi tình cờ có một hàm VBA hoặc add-in khớp tồn tại trên máy người dùng. Đây là sự thật về thiết kế phân biệt một bản demo với một sản phẩm có thể giao được, và nó buộc bạn phải đưa ra một lựa chọn có chủ đích thay vì phát hiện ra sau này

Hãy quyết định, theo từng ô, bạn đang giao hợp đồng nào trong hai loại. Những ô mà người dùng được kỳ vọng sẽ thấy tính lại bên trong Excel phải được dựng từ đúng vốn từ vựng hàm của Excel và không gì khác. Những ô mà logic của chúng mang tính độc quyền nên được đánh giá ngay trong tiến trình bằng Calculate và lưu lại dưới dạng giá trị trần, để hàm tùy chỉnh hành xử như một quy tắc tính toán nội bộ thay vì như nội dung file. Chế độ thất bại đáng tin cậy tạo ra các ticket hỗ trợ chính là vùng lưng chừng: lưu lại một công thức hàm tùy chỉnh và kỳ vọng Excel tôn trọng nó

Có một lợi ích âm thầm của hợp đồng chỉ-giá-trị: nó bảo vệ tài sản trí tuệ. Một quy tắc định giá được đánh giá trong tiến trình Delphi của bạn và giao đi dưới dạng một con số không thể bị reverse-engineer từ workbook theo cách một công thức nhìn thấy được có thể bị, và người dùng không thể phá nó bằng cách chỉnh sửa một ô trung gian. Các trình tạo hóa đơn, bảng kê hoa hồng, và bảng giá gần như luôn thuộc phe này. Trường hợp thực sự cần công thức sống là mô hình what-if tương tác, nơi khách hàng được kỳ vọng sẽ thay đổi đầu vào và theo dõi các tổng số di chuyển, và những mô hình đó phải được dựng từ đúng vốn từ vựng của Excel cộng thêm defined name

Chế độ tính toán, lặp, và R1C1: các núm điều chỉnh của lớp giao diện XLS

Lớp giao diện XLS lộ ra các thiết lập tính toán ở cấp BIFF mà Excel đọc từ file. CalculationMode chấp nhận xlCalcManual, xlCalcAutomatic (mặc định), hoặc xlCalcAutomaticExceptTables, và nó quyết định Excel sẽ hành xử ra sao một khi file được mở. Một workbook mô hình với hàng nghìn công thức thường thân thiện hơn khi được giao ở chế độ manual, để người nhận tự quyết định khi nào cơn bão tính lại xảy ra. EnableIteration (mặc định False), cùng với MaxIterations (mặc định 100) và MaxIterationChange (mặc định 0.001), mở khóa các tham chiếu vòng có chủ đích kiểu hội tụ-lặp xuất hiện trong một số mô hình tài chính. ReferenceStyle chuyển đổi giữa hiển thị A1 và R1C1, và UseFullPrecision phản chiếu tùy chọn precision-as-displayed của Excel

Các thuộc tính này sống trên lớp giao diện XLS bởi vì chúng ánh xạ tới các bản ghi BIFF; khi tạo .xlsx, hãy thiết kế công thức sao cho chúng không phụ thuộc vào các thiết lập lặp, hoặc tính các giá trị đã hội tụ trong Delphi rồi ghi kết quả

Công thức mảng: điểm vào công khai là XLSX

Công thức mảng kiểu CSE truyền thống được tạo thông qua TXLSXRange.SetArrayFormula:

// một công thức mảng trải dài trên A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Phương thức tương đương tồn tại trong hệ phân cấp class của XLS nhưng nằm trong một section private, nên không có cách nào được hỗ trợ để viết mới công thức mảng vào file .xls. Những công thức mảng đã có sẵn trong các file được mở vẫn round-trip nguyên vẹn; điều bạn không thể làm là tạo mới chúng. Quy tắc theo sau đủ đơn giản: khi ngữ nghĩa mảng là một phần của yêu cầu, hãy nhắm tới .xlsx. Nếu một sản phẩm giao nộp .xls truyền thống thực sự cần hành vi mảng, con đường thực dụng là tính kết quả mảng trong Delphi rồi ghi từng giá trị riêng lẻ vào các ô

Hai bài đọc liên quan trên trang này: defined names và công thức liên trang tính nói về việc giải quyết tên mà engine thực hiện, và bài viết về xuất CSV và TSV trình bày chi tiết hành vi xuất khiến việc tính toán tường minh trở nên cần thiết. Tài liệu tham khảo engine đầy đủ, bao gồm tập hàm được hỗ trợ, đi kèm trong HotXLS Delphi Component