Khi một lượt xuất 300,000 hàng vượt quá ngân sách bộ nhớ của nó, số hàng thường bị đổ lỗi. Số hàng thường vô tội. Những phần tốn kém của một workbook lớn là những phần được tạo ra như một tác dụng phụ: một style pool phình to thêm một entry cho mỗi ô bởi vì định dạng được thêm vào bên trong vòng lặp, XML worksheet được lắp ráp thành một chuỗi khổng lồ duy nhất vào lúc lưu, một triệu thân công thức giống hệt nhau được lưu từng cái một. HotXLS, thư viện Delphi thuần gốc của losLab cho file XLS và XLSX, đưa cho bạn một đòn bẩy cụ thể cho từng chi phí này. Không cái nào trong số đó được bật mặc định, bởi vì mỗi cái thay đổi một sự đánh đổi, nên biết đòn bẩy nào khớp với triệu chứng nào mới chính là kỹ năng hiệu năng thực sự
Một workbook lớn tiêu tốn bộ nhớ ở đâu
Có hai chế độ bộ nhớ tách biệt cần suy nghĩ tới. Trong lúc tạo, mô hình ô trong bộ nhớ phình to theo mỗi ô bạn chạm tới: giá trị, định dạng, và công thức đều trở thành đối tượng hoặc entry trong pool. Trong lúc lưu, đường đi mặc định của XLSX còn render XML của mỗi worksheet thành một wide string trước khi nén nó vào container zip, nên mức sử dụng đỉnh là mô hình cộng thêm dạng đã tuần tự hóa của sheet lớn nhất. Một job sống sót qua vòng lặp dựng rồi chết bên trong SaveAs đang chạm vào chế độ thứ hai, không phải chế độ thứ nhất, và cách khắc phục cho cái này không làm gì cho cái kia
Kích thước file tuân theo một quy tắc liên quan: ô chỉ là một trong các nhân tố đóng góp, cùng với style, shared string, công thức, hình ảnh, và comment. Một lượt audit bằng ForEachCell và số lượng collection theo từng sheet cho bạn biết tài nguyên nào thực sự chi phối một file có vấn đề trước khi bạn tối ưu nhầm thứ. Một điểm tinh tế về đo lường: Sheet.Cells.Count ở phía XLSX báo cáo số ô đã được khởi tạo trong kho lưu trữ thưa, không phải diện tích của used range. Một sheet có dữ liệu chiếm một hình chữ nhật 1000 nhân 50 với một nửa số ô trống sẽ đếm ra khoảng 25,000, chứ không phải 50,000. Sự khác biệt đó quan trọng khi bạn so sánh một file "khổng lồ" của khách hàng với các fixture của bạn, bởi vì diện tích used-range và số lượng ô thực tế có thể lệch nhau một bậc độ lớn trong các bố cục tài chính thưa
StreamingWrite sửa đường lưu, không sửa đường dựng
Đặt TXLSXWorkbook.StreamingWrite := True chuyển SaveAs sang một serializer dạng streaming ghi XML worksheet trực tiếp vào stream zip, loại bỏ chuỗi trung gian theo từng sheet. Nó mặc định là False để tương thích hành vi, và bật nó lên chỉ là một thay đổi một dòng:
Book := TXLSXWorkbook.Create;
try
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;
end;
Book.StreamingWrite := True; // XML của sheet stream vào container zip
Book.SaveAs('bulk.xlsx');
finally
Book.Free;
end;Hãy chính xác về những gì điều này mang lại: mô hình ô được dựng bởi vòng lặp chiếm đúng bằng lượng bộ nhớ như trước. StreamingWrite làm phẳng đỉnh tăng vọt lúc lưu, đó là sự khác biệt giữa một batch job hoàn tất và một batch job thất bại ở mốc 95%. Nếu chính vòng lặp dựng đã làm cạn bộ nhớ, những đòn bẩy bạn cần là hai đòn bẩy tiếp theo
Style pool: thêm một lần, tái sử dụng chỉ số
Định dạng XLSX trong HotXLS dựa trên pool: Book.Fonts.Add(...), Fills.AddSolid(...), và Borders.Add(...) trả về một chỉ số pool đánh số từ 0 mà các ô tham chiếu tới. Gọi Fonts.Add với tham số giống hệt nhau bên trong một vòng lặp sẽ được loại trùng, nên nó lãng phí thời gian chứ không lãng phí không gian. Alignments.Add hoạt động khác: nó trả về một đối tượng mới ở mỗi lần gọi, nên việc tạo alignment theo từng ô làm pool phình to tuyến tính theo số hàng. Một thói quen bao phủ cả hai trường hợp. Hãy giải quyết mọi chỉ số pool một lần, bên ngoài vòng lặp, và gán chỉ số bên trong nó
// đưa việc tra cứu pool ra khỏi vòng lặp nóng
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False); // chỉ số pool đánh số từ 0
for C := 1 to 24 do
Sheet.Cells[1, C].FontIndex := HeaderFont + 1; // ô lưu chỉ số đánh số từ 1; 0 = mặc định+ 1 không phải một lỗi đánh máy, và việc quên nó chính là bug kinh điển sinh ra triệu chứng ở đây: các pool phát ra chỉ số đánh số từ 0, trong khi các thuộc tính phía ô coi 0 là "mặc định", nên mọi chỉ số pool phải được dịch thêm một khi gán. Bỏ sót nó và các tiêu đề của bạn sẽ âm thầm hiển thị bằng font mặc định của workbook, một lỗi mà không ai nhận ra cho tới lượt review thương hiệu
Thay lưu lượng Variant theo từng ô bằng callback theo hàng
Mỗi Sheet.Cells[R, C].Value := X đều liên quan tới một lượt tra-cứu-hoặc-tạo ô cộng thêm một phép gán Variant. Ở vài trăm nghìn ô, chi phí trên mỗi lượt truy cập đó trở nên đo lường được trong các profile. HotXLS cung cấp các API callback hàng loạt trên cả hai lớp giao diện (ForEachCell và ForEachRow để đọc, WriteCells và WriteRows để ghi) di chuyển việc duyệt lặp vào bên trong engine và đưa cho mã của bạn cả hàng một lúc:
procedure TLedgerExport.FillRow(Sender: TObject;
SheetIndex, Row, FirstCol, LastCol: Integer;
var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
if Row > FCount then
begin
Cancel := True; // dừng toàn bộ thao tác ghi
Exit;
end;
Values := VarArrayOf([FRows[Row - 1].Account,
FRows[Row - 1].PostedOn,
FRows[Row - 1].Amount]);
end;
// một lệnh gọi engine thay vì hàng trăm nghìn lượt chạm thuộc tính
Sheet.WriteRows(1, 1, FCount, 3, FillRow);Cờ Skip của callback để một hàng không bị đụng tới mà không hủy toàn bộ, còn Cancel kết thúc thao tác sớm, điều này hữu ích khi nguồn là một reader mà độ dài của nó bạn chỉ khám phá dần dần. Kết hợp WriteRows cho việc dựng với StreamingWrite cho việc lưu và đường tạo dữ liệu sẽ không còn điểm nóng theo-từng-ô nào sót lại
Đòn bẩy phía đọc trên lớp giao diện XLS
Các file .xls kiểu cũ lớn có bộ công cụ riêng của chúng. _DisableGraphics := True trước Open bỏ qua hoàn toàn việc phân tích lớp drawing, giúp tăng tốc việc tải các workbook mang theo nhiều năm shape và hình ảnh nhúng tích lũy. Giới hạn ở đây rất cứng: lớp drawing khi đó vắng mặt khỏi mô hình, nên lưu một workbook như vậy sẽ ghi ra một file thiếu drawing của nó. Hãy dành cờ này cho các job phân tích chỉ-đọc. SetTempDir chuyển hướng các file tạm của trình ghi BIFF, điều này quan trọng trên các server nơi vị trí temp mặc định có hạn ngạch hoặc nằm trên bộ nhớ chậm. UseSharedFormulas gom các thân công thức lặp lại vào các bản ghi shared-formula, thu nhỏ các file nơi một cột công thức lặp lại xuống sáu mươi nghìn hàng
Các vòng lặp đọc dữ liệu XLS có một cái bẫy đánh chỉ số đáng được nêu ra vì nó nhân đôi công sức khi được xử lý phòng thủ và làm hỏng kết quả khi bị bỏ sót: UsedRange báo cáo các ranh giới FirstRow, LastRow, FirstCol, và LastCol của nó theo kiểu đánh số từ 0, trong khi Cells.Item[Row, Col] đánh số từ 1. Một lượt quét duyệt qua used range phải cộng thêm một vào mỗi tọa độ lúc truy cập ô, như trong Cells.Item[Row + 1, Col + 1], nếu không nó sẽ đọc một lưới bị lệch theo đường chéo đi một ô, âm thầm bỏ mất hàng và cột cuối cùng và bao gồm một hàng đầu tiên ma. Callback ForEachCell né tránh hoàn toàn sự lệch pha này, đó là thêm một lý do để ưu tiên dùng nó cho các lượt quét toàn sheet
Thăm dò file trước khi tải chúng
Thao tác workbook-lớn rẻ nhất là thao tác bạn tránh được. GetSheetNames trên cả hai lớp giao diện liệt kê các worksheet của một file mà không tải dữ liệu ô. Triển khai XLSX chỉ đọc manifest workbook bên trong file zip và cố tình để lại instance workbook chưa được điền dữ liệu, còn lớp giao diện XLS dừng quét tại ranh giới substream đầu tiên. Điều đó khiến nó trở thành bước kiểm tra tiền-bay đúng đắn cho câu hỏi "job import này nên nhắm vào sheet nào", và CanReadEncrypted trả lời câu hỏi "đây có phải một container đã mã hóa hay không" trước một lần thử Open chắc chắn thất bại
Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
raise Exception.Create('cannot enumerate sheets'); // thất bại sẽ xóa sạch danh sách
// chọn sheet đích, rồi quyết định xem một lần Open đầy đủ có đáng hay không
finally
Book.Free;
Names.Free;
end;Hãy để ý quy ước mã trả về: các hàm thăm dò này báo hiệu thất bại bằng giá trị bằng hoặc dưới 0 và xóa sạch danh sách kết quả, nên hãy kiểm tra <= 0 thay vì so sánh với một giá trị thành công cụ thể duy nhất
Định cỡ cách tiếp cận theo công việc
Với các pipeline chạy không người giám sát tạo ra nhiều file lớn nối tiếp nhau, hai thói quen nữa hoàn thiện bức tranh này. Các đối tượng workbook không an toàn để dùng chung, nhưng không gì ngăn cản một workbook độc lập cho mỗi worker thread, điều này song song hóa việc chuyển đổi hàng loạt một cách gọn gàng. Và khi kết quả xuất đi tới HTTP thay vì đĩa, các overload lưu TStream kết hợp với StreamingWrite để một response lớn không bao giờ hiện thực hóa thành một file tạm. Một chú thích về vận hành cần lưu ý: lưu vào stream ghi từ vị trí hiện tại mà không tua lại, nên hãy đặt Position := 0 trước khi đưa stream cho framework response. Bài viết về streaming write và batch job phát triển khuôn mẫu phía server đó, và bài viết về xuất dữ liệu từ cơ sở dữ liệu cho thấy các đòn bẩy này khớp vào đâu trong một báo cáo dẫn dắt bởi dataset
Cuối cùng, hãy giữ một fixture trường hợp xấu nhất cho mỗi họ báo cáo và đo thời gian nó trong CI. Thoái hóa hiệu năng trong việc tạo tài liệu hiếm khi tự lên tiếng báo hiệu. Một style được thêm bên trong vòng lặp hay một lượt thăm dò bị thay bằng một Open đầy đủ không thay đổi gì về mặt chức năng, và batch chạy đêm chỉ đơn giản là mất thêm bốn mươi phút. Một bài test có đo thời gian trên một fixture nửa triệu ô tiêu biểu biến sự trôi dạt đó thành một build đỏ thay vì một sự cố vận hành
Các bản build đánh giá, dự án demo với một ví dụ tạo hàng loạt, và tài liệu tham khảo API đầy đủ đều có sẵn trên trang HotXLS Delphi Component