Xuất bảng tính Excel sang CSV, TSV, HTML và RTF từ Delphi bằng HotXLS

Hãy hình dung một job chạy ban đêm dựng ra một workbook hóa đơn bằng mã và xuất nó ra dưới dạng CSV để một hệ thống downstream nhập vào. Các con số trông đúng trong Excel. File CSV mở sạch sẽ trong một trình soạn thảo văn bản. Rồi trình nhập liệu nghẹn ngay ở cột tổng, bởi vì trường số tiền ở hàng 42 đọc ra =SUM(D2:D41), tức công thức dưới dạng văn bản chữ, chứ không phải con số mà nó lẽ ra phải tính ra. Không có gì bị hỏng cả. Đây là hành vi đã được tài liệu hóa, và đó là điều đầu tiên cần hiểu về việc xuất dữ liệu từ HotXLS: trình ghi tuần tự hóa mô hình ô đúng như nó đang đứng, và một ô công thức mà giá trị của nó chưa từng được tính ra chỉ có phần văn bản công thức để đưa ra mà thôi

Vì sao file CSV của bạn chứa công thức thay vì con số

HotXLS lưu văn bản công thức và giá trị đã tính như hai thứ tách biệt nhau. SaveAsCSV không chạy calculation engine trên đường xuất ra, đây là chủ đích thiết kế: một thao tác xuất không nên làm biến đổi workbook, và không nên chịu rủi ro bị treo trên một chuỗi công thức bệnh hoạn. Các file mà chính Excel đã lưu mang theo kết quả được cache bên cạnh công thức, nên việc xuất lại những file đó hoạt động đúng như bạn mong đợi. Cái bẫy chỉ đặc thù cho những workbook do chính mã của bạn tạo ra, nơi công thức đã được ghi nhưng chưa từng được tính. Cách khắc phục là làm cho các giá trị tồn tại trước khi bạn xuất, dùng chính engine Calculate vốn cũng giải quyết các tham chiếu liên trang tính và các hàm tùy chỉnh:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('invoice-run.xlsx');
    Sheet := Book.Sheets[0];

    // Hiện thực hóa kết quả công thức để CSV mang số, không phải văn bản '=...'
    for R := 2 to 41 do
      if Sheet.Cells[R, 4].Formula <> '' then
        Sheet.Cells[R, 4].Value := Book.Calculate(Sheet.Cells[R, 4].Formula);

    Book.SaveAsCSV('feed.csv', 0, ',');    // sheet 0, dấu phẩy
    Book.SaveAsCSV('feed.tsv', 0, #9);     // cùng sheet, dạng TSV
  finally
    Book.Free;
  end;
end;

Hãy để ý xem vòng lặp đó thực sự làm gì: nó ghi đè các ô công thức bằng giá trị đã tính của chúng. Điều đó hoàn toàn đúng cho một lượt xuất dùng-một-lần-rồi-bỏ, và sai nếu bạn định lưu lại workbook đó thành .xlsx sau đó, bởi vì bạn vừa thay thế các công thức sống bằng những con số đã đóng băng. Hãy xuất từ một bản sao, hoặc giới hạn phạm vi ghi-lại sao cho nó chỉ chạm vào lượt xuất mà thôi. Engine đứng sau Calculate còn đi xa hơn thế này, bao gồm cả việc đăng ký các hàm riêng của bạn, đó là chủ đề của formula engine và các hàm tùy chỉnh trong HotXLS

Trình ghi có dấu phân cách đảm bảo những gì

Đường xuất CSV tạo ra UTF-8 kèm byte order mark, ký tự xuống dòng CRLF, và cách đặt dấu nháy theo RFC 4180. Bất kỳ trường nào chứa dấu phân cách, một dấu nháy, hay một ký tự xuống dòng đều được bọc lại, và các dấu nháy nằm bên trong bị nhân đôi. Ngày tháng hiển thị theo dạng yyyy-mm-dd hh:nn:ss bất kể định dạng hiển thị của ô là gì. Đó là lựa chọn đúng đắn cho một bên tiêu thụ là máy móc, dù nó gây bất ngờ cho bất kỳ ai kỳ vọng định dạng hiển thị trên màn hình sẽ được giữ nguyên. Các ô rich text bị làm phẳng bằng cách nối các run của chúng lại với nhau

Những giá trị mặc định đó dàn xếp trước phần lớn tranh cãi với một bên nhập liệu trước khi chúng kịp nổ ra, nhưng hai trong số đó dù sao vẫn nên có mặt trong hợp đồng giao diện của bạn. Cái đầu tiên là BOM. Đó là thứ cho phép Excel mở file với các ký tự có dấu còn nguyên vẹn, thế nhưng một số ít trình phân tích nghiêm ngặt lại coi ba byte đó là dữ liệu; nếu bên của bạn là một trong số đó, hãy bóc chúng ra khi bàn giao. Cái thứ hai là TSV. Nó hoàn toàn không phải một tính năng riêng biệt, chỉ là cùng một trình ghi được gọi với #9 làm dấu phân cách, nên mọi thứ nói ở trên đều áp dụng cho nó không đổi. Sheet cần xuất được chọn bằng chỉ số đánh số từ 0 trong overload nhiều tham số, còn dạng rút gọn một tham số SaveAsCSV(FileName) lấy sheet đang active

Xuất HTML là một ảnh chụp nhanh, không phải một định dạng trao đổi

Trong khi CSV vứt bỏ mọi thứ ngoại trừ giá trị, SaveAsHTML cố gắng giữ lại giao diện hiển thị: một <table> cho mỗi sheet, các vùng merge được biểu diễn bằng colspan và rowspan, kiểu style cơ bản của ô được nhúng thẳng vào dưới dạng CSS. Các màu phụ thuộc theme bị bỏ qua thay vì được giải quyết, nên một template dựa dẫm vào các theme slot sẽ ra kết quả trông đơn giản hơn nhiều so với khi nhìn trong Excel. Hãy đặt màu RGB tường minh cho bất cứ thứ gì cần sống sót qua chuyến đi này. Đối tượng options điều khiển lớp vỏ bọc bên ngoài:

var
  Opts: TXLSXHtmlExportOptions;
begin
  Opts := TXLSXHtmlExportOptions.Create;
  try
    Opts.Title := 'Weekly settlement';
    Opts.TableClass := 'report-grid';     // móc nối cho stylesheet của trang chủ
    Opts.WriteDocument := True;           // trang đầy đủ, không phải một fragment
    if Book.SaveAsHTML('settlement.html', 0, Opts) <> 0 then
      raise Exception.Create('Sheet index out of range');
  finally
    Opts.Free;
  end;
end;

Hai chi tiết trong đoạn mã đó xứng đáng được chú ý. Chuyển WriteDocument thành False và kết quả xuất ra trở thành một fragment table trần trụi thay vì một trang đầy đủ, đây là điều bạn muốn khi chèn một bản xem trước vào một layout đã có sẵn: đặt TableClass và để stylesheet của trang chủ lo phần theme. Quy ước giá trị trả về ở đây cũng ngược lại với hầu hết các lệnh gọi khác của HotXLS. SaveAsHTML trả về 0 khi thành công và -1 cho một chỉ số sheet không hợp lệ, nên một thói quen kiểm tra = 1 sẽ báo cáo mọi lượt xuất thành công là một thất bại. Khi bạn cần một vùng thay vì cả một sheet, chẳng hạn để gửi email hoặc nhúng một khối duy nhất, TXLSXRange.SaveAsHTML xuất bất kỳ range hình chữ nhật nào theo cùng những quy tắc render đó

Kết quả xuất RTF và nơi nó vẫn còn xứng đáng có mặt

Đích thứ tư ghi ra các bảng RTF 1.6, mỗi lần gọi SaveAsRTF ứng với một sheet. Độ rộng cột được xấp xỉ khoảng 96 twip cho mỗi ký tự độ rộng cột. Giới hạn về mặt cấu trúc cần biết là các ô đã merge không span trong kết quả xuất ra: chỉ ô neo mang nội dung của nó, còn các ô bị che phủ xuất ra dưới dạng trống. Điều đó loại RTF ra khỏi những template nặng về layout. Nó vẫn xứng đáng có mặt như con đường ít trở ngại nhất để đưa kết quả dạng bảng vào một trình xử lý văn bản hoặc vào một hệ thống quản lý tài liệu kiểu cũ ra đời trước khi HTML được tiếp nhận rộng rãi

Round-trip: nhập CSV mang tính phá hủy theo chủ đích thiết kế

Đọc CSV trở lại vào có hợp đồng riêng của nó. OpenCSV xóa sạch toàn bộ workbook và dựng lại nó thành một sheet duy nhất tên Sheet1. Về bản chất nó là một constructor, không phải một phép merge, nên đừng bao giờ gọi nó trên một workbook vẫn còn giữ nội dung chưa lưu. Truyền #0 làm dấu phân cách sẽ kích hoạt tính năng tự động dò dấu phân cách. Cờ ADetectTypes điều khiển việc thăng cấp kiểu dữ liệu: khi bật, các chuỗi số trở thành số, các chuỗi ISO-8601 trở thành ngày tháng, và true/false trở thành boolean. Hãy tắt nó khi nguồn dữ liệu mang theo các định danh có số 0 ở đầu, mã zip, hay mã sản phẩm, tất cả những thứ mà việc thăng cấp kiểu sẽ âm thầm làm hỏng thành số (một số 0 ở đầu đơn giản là biến mất ngay khoảnh khắc 00123 trở thành 123). Cả hai lớp giao diện đều lộ ra cùng chức năng import này. Kết hợp nó với các lệnh gọi xuất ở trên và bạn có một cầu nối định dạng không cần Excel được cài đặt ở bất cứ đâu trong pipeline, kịch bản được nói tới trong tạo báo cáo từ cơ sở dữ liệu ra Excel với HotXLS

Xuất thẳng vào một stream

Mọi trình ghi ở đây đều có một overload nhận stream nằm ngay cạnh phiên bản nhận tên file: CSV, HTML, RTF, và cả các định dạng workbook. Trong mã phía server, những overload đó chính là thứ nên dùng tới. Một endpoint web phục vụ tải xuống CSV có thể ghi vào một TMemoryStream và đưa thẳng nó cho đối tượng response, không cần file tạm, không cần job dọn dẹp, và không có va chạm giữa hai request tình cờ chọn cùng một tên tạo ra. Điều tương tự cũng đúng khi đẩy các bản xuất vào blob storage hoặc đính kèm chúng vào email gửi đi. Hệ thống file hoàn toàn biến mất khỏi bức tranh

Kiểu mẫu đó cộng hưởng với cách thư viện được triển khai. Cả hai lớp giao diện đều là những bộ đọc và ghi Object Pascal thuần gốc, nên không cần cài Excel, không cần COM automation, và không có nút thắt cổ chai theo từng process nào tuần tự hóa các request trên server. Mỗi request có thể sở hữu đối tượng workbook của riêng nó, chạy bước ghi-lại kết quả tính toán từ phần đầu, và stream bản xuất của nó song song với các request lân cận. Bộ nhớ là tài nguyên duy nhất cần để mắt tới. Mô hình workbook sống trong RAM suốt thời gian xuất, nên một dịch vụ mở những file rất lớn chỉ để phát lại chúng dưới dạng CSV nên giới hạn số job chạy đồng thời, hoặc xếp hàng những job quá khổ, thay vì để một đợt tăng traffic quyết định working set

Một núm điều chỉnh nhỏ hơn: đặt IncludeBOM trên các option HTML khi fragment đó sẽ được lưu thành một file độc lập mà một công cụ downstream nào đó sẽ dò tìm encoding. Khi bạn phục vụ HTML trực tiếp qua HTTP, hãy để phần khai báo charset cho response header lo thay vào đó

Khi các byte vẫn ra sai

Câu hỏi hỗ trợ phổ biến nhất về xuất CSV chính là vấn đề mở đầu bài viết này khoác một bộ áo khác: Excel hiển thị mojibake thay vì các ký tự có dấu. Bản năng là đổ lỗi cho trình ghi, nhưng nó phát ra một BOM UTF-8 chính vì lý do này, và file gần như luôn luôn đúng khi nó rời khỏi mã của bạn. Có thứ gì đó nằm giữa đó và Excel đã ăn mất BOM. Một lượt truyền FTP ở chế độ text, một phép sao chép stream bỏ qua ba byte đầu tiên, một proxy mã hóa lại trên đường truyền qua: bất kỳ thứ nào trong số đó cũng sẽ bóc mất dấu hiệu đó và để Excel tự đoán encoding, mà nó đoán rất tệ. Hãy chẩn đoán điều này ở ranh giới, không phải trong lệnh gọi xuất. Mở file đã giao trong một trình xem hex và xác nhận EF BB BF vẫn là thứ đầu tiên trong đó

Đó là sợi chỉ xuyên suốt cả bốn định dạng. Lệnh gọi xuất là phần dễ, và HotXLS đưa ra một lựa chọn hợp lý tại mỗi quyết định mà trình ghi phải đối mặt. Những thất bại nằm ở các đường nối, nơi văn bản công thức gặp một trình phân tích muốn một con số, nơi một BOM gặp một phương tiện truyền tải không giữ nó nguyên vẹn, nơi một ô đã merge gặp mô hình bảng phẳng của RTF. Mỗi điều đó là một sự thật cần được viết vào hợp đồng giữa bộ xuất của bạn và bất cứ thứ gì tiêu thụ nó, bởi vì bên tiêu thụ không thể đọc ra ý định của bạn từ các byte. Để xem danh sách phương thức đầy đủ trên cả hai lớp giao diện workbook, trang sản phẩm HotXLS Delphi Component mang đầy đủ tài liệu tham khảo