Ghi dạng luồng HotXLS cho công việc hàng loạt trên máy chủ Delphi

Giả sử một dịch vụ Delphi chạy ban đêm sinh ra một tệp XLSX cho mỗi khách hàng, vài trăm tệp, một số trong đó rộng tới 400.000 dòng. Profile nó lên và điều bất ngờ hiếm khi nằm ở vòng lặp điền ô. Nó nằm ở lệnh gọi SaveAs. Với writer mặc định, mỗi worksheet được serialize thành một chuỗi XML duy nhất nằm trong bộ nhớ trước khi chuỗi đó được nén vào zip OOXML, và với một sheet rộng, chuỗi tạm thời đó có thể lớn hơn hẳn cả model ô mà nó được dựng lên từ đó. Vậy nên một job xây dựng dữ liệu của mình một cách thoải mái và đứng yên ở mức 800 MB sẽ vọt qua giới hạn container 2 GB ngay trong lúc lưu, và trình OOM killer sẽ nộp báo cáo lỗi lúc 3 giờ sáng khi chẳng có ai đang theo dõi cả. HotXLS, thư viện bảng tính thuần của losLab cho Delphi và C++Builder, có một thuộc tính nhắm thẳng vào cú vọt đó: StreamingWrite. Xung quanh nó là hai đòn bẩy nữa quyết định một worker batch có ở trong ngân sách bộ nhớ và thời gian của nó hay không, cụ thể là các callback ghi theo từng dòng và cách bộ style pool hành xử bên trong một vòng lặp chật hẹp

Đường lưu mặc định đệm những gì, và StreamingWrite thay đổi điều gì

Writer XLSX mặc định ưu tiên sự đơn giản. Nó render toàn bộ XML của worksheet, rồi mới đưa chuỗi hoàn chỉnh cho bộ nén zip. Đó là sự đánh đổi đúng đắn cho đại đa số workbook, nơi toàn bộ XML của cả sheet vừa gọn trong vài megabyte. Nó ngừng đúng đắn khi dạng đã serialize của một sheet chạy tới hàng trăm megabyte. XML bảng tính vốn dài dòng: mỗi ô số tốn hàng chục ký tự markup, và chuỗi chứa tất cả những thứ đó phải liền mạch. Trên một đồ thị bộ nhớ, dấu hiệu này khó mà bỏ sót. Một cao nguyên phẳng kéo dài trong lúc các dòng được điền, rồi một cú vọt hình tam giác sắc nhọn trong lúc SaveAs, rồi sụp xuống ngay khi zip được flush

Đặt Book.StreamingWrite := True chuyển SaveAs sang một worksheet writer phát ra XML của sheet trực tiếp vào luồng zip ngay khi nó được sinh ra. Chuỗi trung gian không bao giờ được cấp phát, và cú vọt hình tam giác kia phẳng lì xuống thành nhiễu nền

Hãy chính xác về những gì cờ đó thực sự mang lại, vì thổi phồng nó dẫn tới những kế hoạch năng lực sai lầm. Cờ này chỉ thay đổi đường lưu. Việc dựng workbook vẫn cấp phát toàn bộ model ô trong bộ nhớ, nên cao nguyên trong pha điền dữ liệu vẫn cao y hệt như trước. Thứ biến mất là cú vọt serialize từng chồng lên trên cao nguyên đó vào lúc lưu, và với một job điền 400 nghìn dòng, cú vọt đó thường là toàn bộ sự khác biệt giữa việc vừa khít một ngân sách bộ nhớ và việc thổi bay nó. Thuộc tính này mặc định là False để giữ nguyên hành vi lịch sử, nên việc bật nó lên là một dòng tường minh mà bạn viết ra một cách có chủ đích

Một lượt export hàng loạt với cờ đã bật

Book := TXLSXWorkbook.Create;
try
  BoldIdx := Book.Fonts.Add('Calibri', 11, True, False); // chỉ mục pool, bắt đầu từ 0
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
    if (R mod 1000) = 0 then
      Sheet.Cells[R, 2].FontIndex := BoldIdx + 1;        // tại ô thì bắt đầu từ 1
  end;
  Book.StreamingWrite := True;   // stream XML của sheet thẳng vào zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

Cells[R, C] tạo ô theo yêu cầu, giúp thân vòng lặp luôn gọn gàng. Có hai giới hạn lưới đáng ghi nhớ: 1.048.576 dòng và 16.384 cột, được phơi ra qua XlsxMaxRow và XlsxMaxCol. Một nguồn dữ liệu vượt quá giới hạn dòng phải được tự chia nhỏ ra nhiều sheet trong chính code của bạn. Không có gì ở phía sau nhận ra sự vượt giới hạn đó hay tự sửa nó giúp bạn cả, và tệp đơn giản là kết thúc bị cắt cụt ngay tại giới hạn đó

Điền dòng mà không chịu chi phí Variant theo từng ô

Mỗi lần gán Cells[R, C].Value phải trả giá cho một lượt tra cứu ô và một lượt chuyển đổi Variant. Ở mười nghìn dòng chẳng ai nhận ra. Ở một triệu dòng, mỗi dòng hai mươi cột, chi phí theo từng lệnh gọi đó trở thành chi phí chi phối cả pha điền dữ liệu, và trình profiler sẽ chỉ thẳng vào nó. Các interface xử lý theo lô cho phép bạn đưa cho writer nguyên cả một dòng mỗi lần thay vì vậy. WriteRows điều khiển một callback cung cấp một dòng cho mỗi lần gọi:

procedure TBulkExporter.FillRow(Sender: TObject; SheetIndex, Row, FirstCol,
  LastCol: Integer; var Values: Variant; var Skip: Boolean;
  var Cancel: Boolean);
begin
  if not FReader.Next then
  begin
    Cancel := True;              // nguồn dữ liệu đã cạn: dừng gọn gàng
    Exit;
  end;
  Values := VarArrayCreate([FirstCol, LastCol], varVariant);
  Values[FirstCol]     := FReader.RecordId;
  Values[FirstCol + 1] := FReader.CustomerName;
  Values[FirstCol + 2] := FReader.Amount;
end;

// điền dòng 2..100001, cột A..C, lấy dữ liệu từ reader
Sheet.WriteRows(2, 1, 100001, 3, FillRow);

Cờ Cancel là thứ biến một khoảng dòng cố định thành "tối đa N dòng", đây là hình dạng tự nhiên khi số dòng đến từ một truy vấn mà bạn chưa chạy xong. Skip là cú chạm nhẹ hơn: nó để một dòng riêng lẻ trống mà không dừng cả lượt chạy. Ngoài việc điền ô, callback này hóa ra còn là một chỗ tốt để chứa các mối bận tâm vận hành mà nếu không sẽ bị gắn chắp vá một cách vụng về vào vòng lặp điền dữ liệu. Một bộ đếm tiến độ tích tắc mỗi nghìn dòng, một token hủy được job scheduler thăm dò, một bộ giới hạn tốc độ đọc từ cơ sở dữ liệu nguồn: tất cả những thứ đó nằm gọn ở một chỗ thay vì bị luồn xuyên suốt qua code ghi ô. Ở phía đọc, ForEachRow và ForEachCell phản chiếu cùng mẫu hình đó, điều này quan trọng khi một job batch vừa tiêu thụ vừa sinh ra các tệp lớn

Style pool trả công cho việc kéo ra ngoài vòng lặp

Mô hình style của XLSX là một tập các pool dùng chung. Fonts.Add, Fills.AddSolid, và Borders.Add đều trả về một chỉ mục pool bắt đầu từ 0, và một ô tham chiếu tới một font bằng cách lưu chỉ mục đó cộng thêm một vào FontIndex, trong đó số 0 được dành riêng cho mặc định của workbook. Cái +1 đó nằm ngay trong ví dụ bulk ở trên. Quên nó đi thì ô sẽ âm thầm lấy nhầm style, bởi vì một lỗi lệch-một trong chỉ mục style pool vẫn là một chỉ mục hợp lệ và không có gì ném lỗi cả

Kỷ luật đi kèm theo đó là tạo mọi đối tượng style trước vòng lặp dòng, rồi tham chiếu chỉ mục của nó bên trong vòng lặp. Fonts.Add loại bỏ trùng lặp các định nghĩa giống hệt nhau, nên gọi nó mỗi dòng một lần chỉ lãng phí CPU. Alignments.Add mới là cái bẫy, vì nó trả về một mục mới toanh ở mỗi lần gọi. Bên trong một vòng lặp 100 nghìn dòng, điều đó chôn vùi styles.xml dưới một trăm nghìn bản ghi alignment trùng lặp, làm phình to tệp trên đĩa và làm chậm mọi lần mở sau này trong Excel khi các bản trùng đó bị phân tích lại. Hãy dựng mỗi style một lần duy nhất bên ngoài vòng lặp, rồi tham chiếu chỉ mục của nó bao nhiêu lần tùy bạn cần

Stream, thư mục tạm, và vòng lặp batch bao quanh tất cả

Không có điều gì trong số này đòi hỏi một file system cả. Cả hai facade đều mang theo các overload TStream trên toàn bộ bề mặt IO của chúng, trong đó có Open và SaveAs và SaveAsCSV và SaveAsHTML và SaveAsODS, nên một worker batch có thể render thẳng vào một TMemoryStream để đưa vào blob storage hay một HTTP response mà chẳng bao giờ đụng tới đĩa. Có một cạnh sắc cần nhớ. SaveAs(Stream) ghi bắt đầu từ vị trí hiện tại của stream và không tự rewind sau đó, nên hãy tự đặt Position := 0 trước khi đưa stream cho bất cứ thứ gì sẽ giao nó đi, nếu không bên tiêu thụ sẽ đọc được 0 byte. Facade XLS thêm hai núm điều khiển của riêng nó. SetTempDir trỏ các tệp tạm của BIFF writer vào một volume có đủ dung lượng và băng thông IO để hấp thụ chúng, điều này quan trọng trên các server nơi đường dẫn temp mặc định nằm trên một đĩa hệ thống chật hẹp. UseSharedFormulas gộp các thân công thức lặp lại vào các nhóm dùng chung, một mức giảm kích thước thực sự cho hình dạng báo cáo kinh điển nơi một công thức được sao chép xuống suốt cả một cột

Bản thân vòng lặp batch vẫn cố tình giữ vẻ tẻ nhạt:

for FileName in SourceFiles do
begin
  Book := TXLSXWorkbook.Create;        // thể hiện mới tinh: không rò rỉ trạng thái
  try
    Book.StreamingWrite := True;
    if Book.Open(FileName) <> 1 then
      Continue;                        // một đầu vào lỗi không được làm chết cả lô
    Book.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
  finally
    Book.Free;
  end;
end;

Một thể hiện workbook mới tinh cho mỗi tệp chỉ tốn vài micro giây và loại bỏ hẳn cả một nhóm lỗi nhiễm chéo giữa các tệp: style, defined name, và document property của tệp 17 không có đường nào rò rỉ sang tệp 18. Việc bỏ qua-và-tiếp-tục khi Open thất bại cũng xứng đáng công sức của nó không kém, vì một tệp tải lên bị cắt cụt trong một lô 600 tệp chỉ nên tốn của bạn đúng một dòng log thay vì cả phần còn lại của lượt chạy. Cũng đáng nêu ra là điều mà chặng CSV cố tình không làm. SaveAsCSV ghi công thức ra dưới dạng text nguyên văn và không bao giờ tính giá trị của chúng, nên một lô chuyển đổi mà bên tiêu thụ mong chờ những con số đã tính sẵn phải chạy Calculate trên các ô liên quan trước, hoặc bắt đầu từ những workbook đã sẵn mang kết quả cache từ một lần tính trước đó

Mô hình đồng thời: một workbook cho mỗi thread

Đối tượng của cả hai facade đều không thread-safe, và thiết kế chưa bao giờ giả vờ điều ngược lại. Vì không có trạng thái toàn cục nào được chia sẻ giữa các thể hiện, nên quy tắc mở rộng đơn giản là một workbook cho mỗi worker thread, không chia sẻ một workbook giữa các thread. Một pool gồm N worker, mỗi worker sở hữu TXLSXWorkbook của riêng mình, mở rộng gần như tuyến tính cho tới khi bộ nhớ trở thành trần giới hạn, và cái trần đó là thứ bạn có thể gán một con số cụ thể: model ô đồng thời lớn nhất nhân với số worker, cộng thêm bất kỳ chi phí lúc lưu nào mà StreamingWrite đã làm phẳng đi. Khi hàng đợi chất sâu, hãy áp đặt áp lực ngược ngay tại hàng đợi job thay vì bên trong writer. Một thread bị bỏ đói đã viết dở một workbook thì chẳng tạo ra được gì hữu ích cả, trong khi một job chờ vài giây để có một worker rảnh sẽ hoàn tất trọn vẹn

Để có bức tranh tinh chỉnh toàn diện hơn, bao gồm shared formula, việc bỏ qua đồ họa ở phía đọc, và các đòn bẩy riêng cho XLS, xem hướng dẫn hiệu năng cho workbook lớn. Các job batch có dòng lấy thẳng từ một truy vấn được trình bày riêng trong các mẫu hình export cho báo cáo Delphi

HotXLS biên dịch thẳng vào dịch vụ Delphi hay C++Builder của bạn dưới dạng Object Pascal thuần, không phụ thuộc bên ngoài nào; các phiên bản và giấy phép có trên trang sản phẩm HotXLS Delphi Component