Tạo tệp Excel trong Delphi không cần Office Automation

Nếu công việc duy nhất của một server là tạo ra tệp Excel, thì nó không có lý do gì để chạy Excel. Cài Office lên một build agent hay một dịch vụ báo cáo rồi điều khiển nó qua COM automation là một thiết kế sai, và đó đã là thiết kế sai kể từ khi cách làm này ra đời. Chính Microsoft cũng nói vậy, trong hướng dẫn không hề mềm đi suốt hai mươi năm qua: Office không được xây dựng cũng không được cấp phép để tự động hóa từ một tiến trình phía server chạy không người giám sát. Câu trả lời đúng là viết trực tiếp các byte BIFF và OOXML, không có bóng dáng Excel nào trong bức tranh cả. Đó chính là toàn bộ tiền đề của HotXLS, một thư viện Object Pascal thuần chạy đọc và ghi các định dạng bảng tính, nhờ vậy không có ứng dụng desktop nào để treo, để rò rỉ, hay để trả tiền theo từng seat

Vì sao điều khiển EXCEL.EXE từ một dịch vụ luôn thất bại

COM automation điều khiển từ xa một chương trình desktop, mà một chương trình desktop lại ngầm giả định ba thứ mà một Windows service không thể cung cấp: một user profile đã được nạp, một interactive window station, và một con người đang nhìn vào màn hình. Lấy mất những thứ đó đi thì lỗi sẽ xuất hiện dưới dạng mà không máy phát triển nào từng tái hiện được. Một hộp thoại khôi phục tệp, một lỗi add-in, hay một hộp thoại kích hoạt license mở ra trên một desktop chẳng ai nhìn thấy, và lệnh automation đã kích hoạt nó thì không bao giờ trả về nữa. Bên gọi cuối cùng hết thời gian chờ và chết; còn tiến trình Excel thì thường không chết theo, nó tồn tại như một tiến trình mồ côi giữ khóa tệp và đầu độc lần chạy kế tiếp. Ai đã từng chứng kiến mười một tiến trình EXCEL.EXE lạc lõng chất đống dưới một tài khoản dịch vụ đều biết phần còn lại của câu chuyện đó

Câu chuyện về khả năng mở rộng cũng chẳng khá hơn, kể cả khi không có gì sập cả. Một tiến trình Excel là một pipeline chỉ xử lý được một workbook tại một thời điểm, mỗi lần truy cập property đều phải trả giá cho việc marshaling COM giữa các tiến trình, và cỗ máy chạy đoạn code đó lại mang một giấy phép Office mà chính điều khoản của nó lại loại trừ đúng cách dùng này. Hầu hết các nhóm chạm phải những giới hạn này từng lần một, qua mỗi lần gián đoạn dịch vụ, và đó cũng là cách mà việc "loại bỏ lớp COM" thường xuất hiện trên một roadmap

Trước khi bắt đầu viết lại, hãy giải quyết một câu hỏi về phạm vi, vì nó quyết định bao nhiêu phần việc thực sự cần làm. Code COM gần như không bao giờ chỉ đơn thuần gán giá trị cho ô. Nó còn gọi Workbook.SaveAs với các hằng số định dạng, ép tính lại, đẩy thiết lập in ấn, đôi khi còn động tới clipboard. Hãy rà lại code cũ và ghi chú xem hành vi nào trong số đó thực sự có mặt trong kết quả đầu ra, vì mỗi hành vi lại rơi vào một góc khác nhau của thư viện thuần, và một vài trong số đó (rõ nhất là tương tác clipboard) không còn ý nghĩa gì ở phía server nên nên bỏ đi thay vì chuyển sang

Hai engine thuần, hai mô hình sở hữu bộ nhớ

HotXLS thay thế tiến trình Excel bằng hai cách triển khai định dạng trực tiếp. Một engine record-stream BIFF8 (TXLSWorkbook, unit lxHandle) xử lý .xls. Một bộ ghi package OOXML (TXLSXWorkbook, unit lxHandleX) tạo ra .xlsx tuân thủ ECMA-376 / ISO/IEC 29500. Không có gì cần đăng ký, không có gì cần cài lên server, và bạn có thể mở cùng lúc bao nhiêu workbook tùy ý miễn bộ nhớ còn cho phép

Điều khiến người mới hay vấp phải là hai facade này sở hữu bộ nhớ theo hai cách khác nhau, và sự khác biệt đó im lặng cho tới khi nó gây sập:

var
  Book: IXLSWorkbook;          // tham chiếu interface: tự động giải phóng
  Sheet: IXLSWorksheet;
  BookX: TXLSXWorkbook;        // đối tượng thường: bạn phải tự giải phóng
  SheetX: TXLSXWorksheet;
begin
  // xuất .xls BIFF8 - không gọi Free; refcount của interface tự quản lý
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.Name := 'Report';
  Sheet.Cells.Item[1, 1].Value := 'Generated without Excel';
  Book.SaveAs('report.xls');

  // xuất .xlsx OOXML - vòng đời tường minh
  BookX := TXLSXWorkbook.Create;
  try
    SheetX := BookX.Sheets.Add('Report');
    SheetX.Cells[1, 1].Value := 'Generated without Excel';
    BookX.SaveAs('report.xlsx');
  finally
    BookX.Free;
  end;
end;

Facade XLS được đếm tham chiếu (reference-counted) thông qua interface IXLSWorkbook. Hãy khai báo biến theo kiểu interface và đừng bao giờ gọi Free trên nó; còn nếu giữ cùng đối tượng đó trong một biến kiểu object thường rồi tự gọi free, thì refcount sẽ giải phóng nó lần thứ hai. Facade XLSX lại là một object bình thường, cần một khối try..finally bình thường. Cách đánh địa chỉ ô bắt đầu từ 1 ở cả hai phía, đây là điểm duy nhất mà hai bên thống nhất với nhau. Còn các collection của sheet thì không: Entries ở phía XLS bắt đầu từ 1, trong khi chỉ mục Items của XLSX bắt đầu từ 0, và lỗi lệch-một đó biên dịch trơn tru dù bạn viết sai theo hướng nào, rồi chỉ lộ ra khi chạy thực tế

Ghi workbook thẳng vào một HTTP response

Một tác vụ export phía server thường chẳng có lý do gì để đụng tới đĩa. Tệp tạm đòi hỏi một chính sách dọn dẹp, xung đột nhau khi có nhiều request đồng thời, và để lại dữ liệu khách hàng nằm trên các volume mà chẳng ai nghĩ tới việc kiểm toán. Cả hai facade đều nhận một TStream qua các overload của SaveAs, nên workbook có thể đi thẳng vào response:

Mem := TMemoryStream.Create;
Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Data');
  Sheet.Cells[1, 1].Value := 'Generated ' + DateTimeToStr(Now);
  Book.SaveAs(Mem);          // ghi bắt đầu từ vị trí HIỆN TẠI của stream
  Mem.Position := 0;         // tua lại về đầu trước khi giao stream đi
  Response.ContentType :=
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
  Response.ContentStream := Mem;   // từ giờ framework sở hữu Mem
finally
  Book.Free;
end;

Dòng rewind chính là dòng xứng đáng có comment riêng. SaveAs(Stream) ghi bắt đầu từ vị trí hiện tại của stream và không bao giờ tự seek lại về 0 sau đó. Quên mất Mem.Position := 0 thì client sẽ nhận về một tệp tải xuống 0 byte, hoặc Excel sẽ báo tệp bị hỏng. Đây là lỗi phổ biến nhất trong code workbook hướng ra web, và cũng là lỗi tai quái nhất, vì nó lướt qua trót lọt mọi unit test chỉ kiểm tra rằng stream có độ dài khác 0

Chỉ một routine dựng workbook duy nhất có thể vươn tới mọi định dạng xuất khác mà không cần tái cấu trúc gì cả. SaveAsCSV đáp ứng yêu cầu kiểu "cứ đưa tôi dữ liệu thô", SaveAsHTML xử lý trường hợp "nhét nó vào một trang portal", SaveAsRTF nuôi các pipeline tài liệu, còn SaveAsODS đáp ứng yêu cầu bắt buộc dùng OpenDocument, tất cả đều có cả overload theo tệp lẫn theo stream. Một routine export duy nhất cộng thêm một tham số định dạng thay thế cho thứ vốn từng là bốn macro COM riêng biệt. TXLSXHtmlExportOptions của bộ xuất HTML mang theo title, CSS class, và một công tắc chuyển giữa fragment hoặc tài liệu đầy đủ, nhờ đó trường hợp portal không phải dính vào việc dùng regex chỉnh sửa lại markup đã xuất ra

Giá trị công thức khi không có tiến trình Excel nào để tính chúng

Dưới COM automation, Excel từng tự tính lại mọi thứ miễn phí, và bỏ COM đi thì âm thầm tước mất điều đó. SaveAs lưu công thức dưới dạng text mà không tính giá trị của chúng; các con số chỉ xuất hiện khi Excel mở tệp lên và tính lại, hành vi mà facade XLS cho phép bạn tinh chỉnh qua RecalcOnSave và CalculationMode. Với một tệp gửi cho con người thì đó là đúng đắn. Nhưng nó lại sai với một dịch vụ cần xác nhận một tổng số trước khi gửi đi, và sai với việc xuất CSV, vốn ghi ra text công thức thay vì kết quả của nó. Cả hai trường hợp đều phải tính toán ngay trên server bằng engine tích hợp sẵn:

SheetX.Cells[1, 1].Value := 1200;
SheetX.Cells[2, 1].Value := 950;
SheetX.Cells[3, 1].Formula := 'SUM(A1:A2)';   // facade XLSX: không có tiền tố '='
Total := BookX.Calculate('SUM(A1:A2)');       // tính toán ngay trên server
if Total <> 2150 then
  raise Exception.Create('reconciliation failed before delivery');

Quy ước khác biệt giữa hai facade lại cắn bạn một lần nữa ở đây. Phía XLSX gán biểu thức qua Cell.Formula mà không có dấu bằng ở đầu; phía XLS lại ghi chúng qua Cell.Value với một dấu '=' đứng trước. Mang nguyên code từ bên này sang bên kia mà không sửa, quy ước sai đó sẽ lưu lại một chuỗi text chỉ trông giống công thức, mà không có lỗi nào báo cho bạn biết. Khi công thức của một workbook cần chạm vào logic nghiệp vụ riêng của bạn, callback OnUserFunction cho phép engine giao lại các tên hàm chưa biết cho code Delphi xử lý ngay tại thời điểm tính toán. Đó chính là thứ thay thế thuần cho các add-in UDF vốn hay ẩn mình bên trong chính những bảng tính mà một hệ thống COM-automation đã lớn lên cùng

Những góc khuất khi triển khai chỉ lộ ra trên server

Một vài chi tiết quyết định việc triển khai diễn ra suôn sẻ hay rối rắm, và chi tiết đầu tiên là đồ thị unit. Bộ xuất dataset kiểu kéo-thả TDataToXLS kéo theo Forms, Controls, và Dialogs của VCL. Vô hại trong một công cụ desktop; nhưng trong một dịch vụ console, nó lôi theo cả VCL đằng sau nó. Các unit lõi lxHandle và lxHandleX chỉ cần tới Windows, Classes, SysUtils, và Variants, nên một dịch vụ thuần túy nên tự viết vòng lặp dataset của riêng mình dựa trên API lõi thay vì import component chỉ vì tiện

Rồi còn vấn đề threading nữa. Các thể hiện workbook không thread-safe, nhưng chúng cũng không chia sẻ bất kỳ trạng thái toàn cục nào, nên mẫu hình mở rộng tốt nhất lại là mẫu hình đơn giản nhất: một đối tượng workbook cho mỗi job, hoặc cho mỗi worker thread. Cách đó mang lại khả năng sinh báo cáo song song, điều mà một tiến trình Excel dùng chung duy nhất không bao giờ làm được. Một request handler tự tạo, tự điền dữ liệu, tự lưu rồi tự giải phóng workbook của riêng nó thì không cần khóa nào cả, và bán kính ảnh hưởng của một lỗi thu hẹp từ "tiến trình Excel dùng chung bị kẹt cứng cho tất cả mọi người" xuống còn "chỉ riêng request này ném ra một exception", điều mà cơ chế xử lý lỗi sẵn có của bạn đã biết cách xử lý

Việc nhắm đúng định dạng là điều cuối cùng trong số đó. TXLSWorkbook.SaveAs mặc định ghi ra BIFF (xlExcel97), và việc đẩy nội dung XLS sang .xlsx phải đi qua cầu nối SaveXLSWorkbookAsXLSX với độ trung thực giảm đi. Hãy chọn facade theo đúng định dạng bạn định gửi ra ngay từ lúc thiết kế, thay vì xây dựng bằng một định dạng rồi mới chuyển đổi ở cuối pipeline

Với nửa còn lại là phần nạp dữ liệu của một dự án thay thế điển hình, các mẫu hình export từ database sang workbook bao trùm cả component lẫn vòng lặp tự viết tay, và một khi số dòng chạm tới sáu chữ số thì các kỹ thuật tối ưu hiệu năng cho workbook lớn trở thành ranh giới giữa vài phút và vài giây. Các báo cáo dựng từ layout do designer duy trì được trình bày trong bài hướng dẫn sinh báo cáo từ template

HotXLS được cung cấp dưới dạng mã nguồn Object Pascal cho Delphi và C++Builder; các phiên bản, giấy phép, và tài liệu tham chiếu API đầy đủ có trên trang sản phẩm HotXLS Delphi Component