Xuất kết quả cơ sở dữ liệu Delphi sang báo cáo Excel bằng HotXLS

Biến một kết quả truy vấn thành một báo cáo Excel thực chất là ba vấn đề khoác chung một chiếc áo. Mỗi kiểu trường của Delphi phải đáp xuống một ô đúng kiểu Excel tương ứng, hàng tiêu đề phải đọc lên như một báo cáo chứ không phải một bản kê schema, và số, ngày tháng, tiền tệ phải mang theo định dạng sống sót qua chuyến đi. Bỏ qua bất kỳ điều nào trong số đó và file vẫn mở được, vẫn trông hợp lý, và vẫn thất bại ngay khoảnh khắc một người dùng tài chính chọn một cột và chờ một tổng không bao giờ xuất hiện. Các giá trị đã được ghi dưới dạng văn bản, Excel coi chúng như nhãn, và không có exception nào từng được ném ra để cảnh báo bạn

HotXLS là một thư viện bảng tính Object Pascal thuần gốc, ghi file XLS và XLSX trực tiếp từ Delphi và C++Builder, không cần Excel automation. Nó cung cấp hai con đường từ một TDataset tới một workbook: component cắm-là-chạy TDataToXLS, và một vòng lặp viết tay đối chọi với workbook API. Chúng không thể thay thế cho nhau. Component là một công dân VCL đầy đủ được xây trên lớp giao diện XLS, nên lựa chọn đúng phụ thuộc vào nơi mã chạy và định dạng file nào bên tiêu thụ mong đợi. Phần tiếp theo trình bày cả hai con đường, ranh giới nơi component không còn là công cụ đúng nữa, và cách giữ nguyên vẹn kiểu trường dù bạn chọn con đường nào

Kiểu trường mới là hợp đồng xuất thực sự

Trước bất kỳ lệnh gọi API nào, hãy quyết định mỗi kiểu trường Delphi sẽ đáp xuống một ô như thế nào. Một ô nhận một chuỗi Delphi vẫn giữ nguyên là chuỗi. HotXLS không đoán rằng '1,234.50' vốn có ý là một con số, và nó không nên đoán, bởi vì việc phân tích lại phụ thuộc locale chính là cách một dấu phẩy thập phân kiểu Đức biến thành dấu phân cách hàng nghìn trên một server tiếng Anh. Khuôn mẫu đáng tin cậy là gán thông qua các accessor có kiểu: AsFloat hay AsCurrency cho trường số, AsDateTime cho ngày tháng để ô giữ một số serial ngày tháng Excel thực sự thay vì một chuỗi đã định dạng, và AsString chỉ dùng cho những trường thực sự là văn bản

Việc xử lý NULL xứng đáng có một quyết định tường minh thay vì để mặc định. Chuyển đổi một giá trị trường bằng VarToStr biến SQL NULL thành một chuỗi rỗng, tức là một ô văn bản, trong khi bỏ qua phép gán để lại ô thực sự trống, đây là điều mà AVERAGE, COUNT, và các bên tiêu thụ pivot-table mong đợi. Với các cột tiền tệ, hãy quyết định trước khi viết vòng lặp xem NULL có nghĩa là không hay không xác định. Cả hai hiển thị giống hệt nhau một khi ai đó định dạng cột, và sự khác biệt đó thay đổi mọi phép tổng hợp được tính toán ở downstream

Con đường component: TDataToXLS trong ứng dụng VCL

Với một ứng dụng VCL cổ điển đã có sẵn một query nối vào một data module, TDataToXLS là con đường một-lệnh-gọi. Nó duyệt qua bất kỳ hậu duệ TDataset nào, dù là FireDAC, ADO, IBX, hay bất cứ thứ gì khác thực thi giao diện dataset trừu tượng, và tạo ra một worksheet đã style với tiêu đề cột, font, viền, tổng phụ theo nhóm tùy chọn, và tự động tách sheet cho các kết quả truy vấn lớn

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // bất kỳ hậu duệ TDataset nào
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // tiêu đề hiển thị, không phải tên cột thô
    Exporter.GroupFields.Add('CustomerID');   // khối tổng phụ theo mỗi khách hàng
    Exporter.RowsPerSheet := 50000;           // ở dưới mức trần hàng của BIFF8
    Exporter.VisibleFieldsOnly := True;             // tôn trọng Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Hai thuộc tính mang phần lớn trọng lượng thực tế ở đây. HeaderSource := hsDisplayLabel ghi DisplayLabel của mỗi trường thay vì tên cột SQL thô, nên workbook ghi "Customer Name" thay vì CUST_NM. RowsPerSheet tồn tại vì component ghi BIFF8, mà lưới của nó dừng lại ở 65,536 hàng nhân 256 cột; đặt nó thành 50,000 tách một kết quả truy vấn lớn ra nhiều sheet trước khi mức trần định dạng cắt cụt nó. Giao diện hiển thị được xử lý bởi các thuộc tính HeaderFont, DetailFont, GroupColor, và các thuộc tính kiểu viền, còn tập DisableFormat tắt hẳn toàn bộ các hạng mục định dạng khi bên tiêu thụ muốn ô trần trụi. Với bất cứ thứ gì đặt riêng, các sự kiện AfterCell và AfterRow đưa cho bạn range vừa mới ghi để xử lý hậu kỳ

Nơi component dừng lại

Ba ràng buộc được thiết kế sẵn vào TDataToXLS, và biết chúng ngay từ đầu tránh được một lần thiết kế lại khó xử hai sprint sau đó

• Nó là một component VCL theo đúng nghĩa đầy đủ. Unit của nó kéo theo Forms, Controls, và Dialogs, nên liên kết nó vào một job console hay một Windows service kéo theo cả VCL vào file thực thi. Các unit workbook lõi không có phụ thuộc như vậy. Chúng chỉ cần Windows, Classes, SysUtils, và Variants, đó là lý do vì sao mã phía server nên dùng vòng lặp trình bày bên dưới thay vào đó
• Nó được xây trên lớp giao diện XLS. Component điền dữ liệu vào một IXLSWorkbook và ghi ra .xls (BIFF8). Không có thuộc tính nào chuyển nó sang xuất OOXML
• Các sự kiện của nó nói phương ngữ XLS. Tham số Cell: IXLSRange trong AfterCell thuộc về mô hình đối tượng XLS, nên việc tùy biến theo từng ô được viết ở đó là mã kiểu XLS ngay cả khi file được chuyển đổi thành .xlsx sau đó

Tạo ra .xlsx từ kết quả xuất của component

Khi bên tiêu thụ khăng khăng đòi .xlsx nhưng logic xuất đã sẵn nằm trong TDataToXLS, hàm cầu nối trong unit lxXlsxExport chuyển đổi workbook đã điền dữ liệu chỉ trong một lệnh gọi:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// component lộ ra IXLSWorkbook mà nó đã điền dữ liệu
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Hãy coi cầu nối này như một phương tiện chở dữ liệu dạng bảng, không phải một trình chuyển đổi giữ nguyên độ trung thực đầy đủ. Nó sao chép giá trị, công thức, định dạng số, màu fill, thuộc tính font, độ rộng cột, và thiết lập view. Nó có chủ đích không sao chép viền, range đã merge, comment, biểu đồ, hay conditional format. Với một lưới phẳng gồm tiêu đề cộng các hàng thì như vậy là vừa đủ. Với một báo cáo đã style thì không, và cách khắc phục trung thực là tạo XLSX trực tiếp thay vì vá lại file đã chuyển đổi

Vòng lặp viết tay cho service và batch job

Mã phía server nên nhắm thẳng vào TXLSXWorkbook. Hãy để ý sự khác biệt về vòng đời giữa hai lớp giao diện trước khi sao chép bất kỳ mẫu code nào. TXLSWorkbook ở phía XLS được giữ thông qua một interface đếm tham chiếu và không được tự tay giải phóng, trong khi TXLSXWorkbook là một class thuần cần try..finally Free. Trộn lẫn hai quy ước này là một cách đáng tin cậy để tạo ra hoặc một rò rỉ bộ nhớ hoặc một double-free

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // stream thẳng XML của sheet vào file zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Những dòng quan trọng là các phép gán có kiểu và điều kiện chắn IsNull. Ngày tháng đến dưới dạng số serial ngày, số tiền đến dưới dạng double, và các ngày đặt hàng NULL giữ nguyên trạng thái trống thực sự thay vì trở thành chuỗi rỗng. StreamingWrite := True chỉ thay đổi đường lưu: XML của worksheet stream thẳng vào container zip thay vì được lắp ráp thành một chuỗi lớn trước, điều này làm phẳng đỉnh tăng vọt bộ nhớ tại thời điểm SaveAs với số hàng lên tới sáu chữ số. Mọi phương thức save cũng có một overload nhận TStream, nên workbook có thể đi thẳng vào một response HTTP mà không cần chạm tới đĩa. Bài viết về streaming write và batch job trình bày chi tiết kiểu triển khai đó, và bài viết về hiệu năng workbook lớn nói về những việc cần làm khi số hàng tăng cao hơn nữa

Vòng lặp này cũng là con đường có khả năng mở rộng trên nhiều thread. Cả hai engine đều là những trình ghi Object Pascal thuần gốc, luồng bản ghi BIFF8 ở một phía và zip cộng XML OOXML ở phía kia, nên không phần nào của thao tác xuất chạm tới COM automation hay cần một license Excel trên server. Điều đó mang lại cho bạn khả năng song song hóa mà không có nút thắt cổ chai kiểu một-instance, miễn là mỗi thread tự dựng workbook của riêng nó. Các đối tượng workbook không an toàn khi dùng chung giữa các thread, nên quy tắc là mỗi lượt xuất một instance riêng, không bao giờ dùng chung một instance được bảo vệ bằng một lock

Có một giới hạn đáng biết trước khi bạn thiết kế xoay quanh nó. Lưới của XLSX dừng lại ở 1,048,576 hàng nhân 16,384 cột, nên việc tách sheet mà RowsPerSheet xử lý ở phía XLS hiếm khi cần thiết ở đây. Một workbook cả triệu hàng cũng hiếm khi là điều một người tiêu thụ thực sự mong muốn. Khi kết quả truy vấn thực sự lớn tới mức đó, một file có dấu phân cách thường là hợp đồng tốt hơn, và bài viết về xuất CSV và TSV nói về dấu phân cách, hành vi BOM, và lưu ý về việc tính toán công thức áp dụng ở đó

Chọn một điểm khởi đầu

Nếu thao tác xuất nằm trong một công cụ desktop VCL và kết quả .xls là chấp nhận được, hãy bắt đầu với TDataToXLS và khả năng gom nhóm của nó. Đó là lượng mã ít nhất, và cầu nối qua SaveXLSWorkbookAsXLSX luôn sẵn đó khi ai đó sau này yêu cầu .xlsx, miễn là bạn chấp nhận những giới hạn về độ trung thực đã mô tả ở trên. Nếu mã chạy không người giám sát, hoặc bên tiêu thụ yêu cầu .xlsx ngay từ đầu, hãy viết vòng lặp. Cả hai con đường đều đi kèm các dự án demo hoạt động được và là một phần của gói HotXLS Delphi Component