Một bảng tính với triệu dòng và hàng chục cột là dữ liệu xuất hoàn toàn bình thường từ một job báo cáo cơ sở dữ liệu. Mở nó theo cách thông thường, bằng cách tải toàn bộ workbook vào TXLSWorkbook, và tiến trình phải tạo ra tất cả mười hai triệu ô đó như các đối tượng sống trước khi dòng logic nghiệp vụ đầu tiên của bạn chạy. File trên đĩa có thể là sáu mươi megabyte XML nén. Cây đối tượng mà nó mở ra lớn gấp nhiều lần, và tất cả phải thường trú cùng lúc vì mô hình được thiết kế để truy cập ngẫu nhiên. Đối với một báo cáo bạn định đọc từ đầu đến cuối rồi bỏ đi, đó là lượng bộ nhớ rất lớn dành cho một cấu trúc bạn không cần
Có một con đường thứ hai qua cùng file đó. Thay vì xây dựng mô hình, bạn quét XML worksheet theo chiều tiến, từng ô một, và để mỗi ô trôi qua sau khi bạn đã xem nó. Không có gì tích lũy. Bộ nhớ gần như không thay đổi dù sheet có nghìn dòng hay mười triệu, vì reader không bao giờ giữ nhiều hơn phần nó đang phân tích cộng với vài bảng tra cứu nhỏ. Đây là những gì HotXLS direct reader làm, và phần còn lại của bài viết này giải thích tại sao nó giữ bộ nhớ nhỏ và cái nó cho bạn đổi lại
Tại sao mô hình trong bộ nhớ không thể mở rộng
File XLSX là gói ZIP chứa các phần XML được mô tả bởi ECMA-376. Mỗi worksheet là một phần riêng, xl/worksheets/sheetN.xml, và bên trong mỗi dòng là phần tử <row> chứa các phần tử ô <c>. Đường load thông thường đọc phần đó và xây dựng một đối tượng có thể truy địa chỉ cho mỗi ô để sau đó bạn có thể hỏi Cells[12345, 7] và nhận câu trả lời trong thời gian không đổi. Truy cập ngẫu nhiên là toàn bộ mục đích của mô hình workbook, và chính điều đó làm cho việc chỉnh sửa, tính toán công thức và định dạng trở nên tiện lợi
Chi phí là truy cập ngẫu nhiên đòi hỏi mọi thứ phải có mặt đồng thời. Bạn không thể chỉ mục vào một cấu trúc mà bạn mới chỉ xây dựng một phần. Vì vậy, bộ nhớ đỉnh của một lần load đầy đủ là hàm của số lượng ô, và trên một sheet với hàng triệu ô được điền, hàm đó rơi vào mức mà dịch vụ của bạn không muốn, đặc biệt nếu có nhiều job như vậy chạy cùng lúc trên máy dùng chung. Khi mẫu truy cập bạn thực sự cần là tuần tự, trả tiền cho truy cập ngẫu nhiên là trả tiền cho khả năng bạn sẽ không dùng
Quét SAX chỉ tiến không xây dựng cây
Direct reader mở gói ZIP và duyệt từng phần worksheet bằng pull parser kiểu SAX. SAX ở đây có nghĩa là parser báo cáo các sự kiện phân tích khi nó gặp chúng, một phần tử bắt đầu, một đoạn văn bản, một phần tử kết thúc, rồi tiếp tục. Nó không giữ cây nút nào đằng sau. Reader theo dõi dòng và cột hiện tại từ các thuộc tính r, thu thập kiểu ô, chỉ mục style, giá trị và văn bản công thức khi các sự kiện đến, và khi thẻ đóng </c> được nhìn thấy nó phát ra một ô và quên nó. Ô tiếp theo tái sử dụng cùng một số ít biến cục bộ
Vì không có gì được giữ lại giữa các ô, dấu chân bộ nhớ không tăng theo số lượng ô. Đây là thuộc tính đáng nắm giữ. Sheet hai trăm dòng và sheet hai mươi triệu dòng tốn reader cùng lượng bộ nhớ thường trú, và sự khác biệt giữa chúng chỉ là thời gian quét chạy. Bạn từ bỏ truy cập ngẫu nhiên, tính năng nổi bật của mô hình, và đổi lại bạn nhận được giới hạn bộ nhớ mà số lượng ô không thể vượt qua
Cái gì thường trú và tại sao hai phần đó
Quét không hoàn toàn không có trạng thái, và các ngoại lệ rất đáng chú ý. Hai bảng nhỏ phải được giữ trong bộ nhớ suốt thời gian chạy, vì một ô tự nó không mang đủ thông tin để diễn giải mà không cần chúng
Thứ nhất là bảng shared string. Trong SpreadsheetML, một ô văn bản không lưu trữ văn bản của chính nó. Nó mang t="s" và một dữ liệu số là chỉ mục vào xl/sharedStrings.xml, một danh sách loại trừ trùng lặp duy nhất của mọi chuỗi riêng biệt trong workbook. Đây là một giao dịch tiết kiệm không gian tốt cho các file mà các nhãn giống nhau lặp lại trên hàng nghìn dòng, nhưng có nghĩa là reader phải tải bảng chuỗi đó lên trước và giữ nó thường trú, vì bất kỳ ô nào ở bất kỳ đâu trong bất kỳ sheet nào đều có thể tham chiếu bất kỳ mục nào trong nó. Bảng được định kích thước theo số chuỗi riêng biệt, không phải theo số lượng ô, nên nó duy trì ở mức khiêm tốn ngay cả trên các sheet khổng lồ
Thứ hai là ánh xạ number-format từ phần styles. Một ô số và một ô ngày tháng giống nhau hoàn toàn trên wire: cả hai đều là một số thuần túy, vì ngày tháng trong SpreadsheetML chỉ là số đếm ngày tuần tự. Điều duy nhất phân biệt chúng là style của ô, trỏ qua cellXfs trong xl/styles.xml đến một number-format id. Để báo cáo ngày tháng là ngày tháng thay vì số tuần tự thô, reader tải bảng style-to-format đó và giữ nó thường trú. Mọi thứ khác trong file, dữ liệu ô thực tế chiếm phần lớn byte, đều stream qua mà không được lưu trữ
Mỗi ô báo cáo một loại và một giá trị
Mỗi ô được phát ra đến dưới dạng bản ghi TXLSDirectCell. Nó mang chỉ mục và tên sheet, dòng và cột 1-based, Kind ngữ nghĩa, Value dưới dạng Variant, văn bản Formula không có dấu bằng đứng đầu, và StyleIndex thô. Kind là một trong xdkNumber, xdkString, xdkBoolean, xdkDate, hoặc xdkError, nên bạn có thể phân nhánh theo ý nghĩa của ô thay vì suy ra lại từ các thuộc tính. Một ô công thức báo cáo loại của kết quả được cache, cùng với văn bản công thức bên cạnh, nên một tổng tính toán đến dưới dạng số cũng cho biết nó được tạo ra như thế nào
type
TReportScan = class
procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
end;
procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
var Abort: Boolean);
begin
case Cell.Kind of
xdkString: AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
xdkNumber: AddToTotals(Cell.Col, Double(Cell.Value));
xdkDate: NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
xdkError: LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
end;
end;Phân biệt ngày tháng với số
Câu hỏi về ngày tháng xứng đáng được xem xét kỹ hơn vì đây là nơi hầu hết các scanner ngây thơ gặp vấn đề. Không có kiểu ngày tháng trên một ô số. Một ô chứa giá trị tuần tự 46000 có thể là số lượng, giá cả, hoặc ngày 17 tháng 2 năm 2025, và file chỉ cho bạn biết cái nào thông qua number-format id được tiếp cận qua style của ô. ECMA-376 dành riêng một khối id format tích hợp có ý nghĩa cố định trên mọi producer tuân thủ, và các id chứa ngày tháng nằm trong hai dải: 14 đến 22 cho các format ngày giờ chuẩn, và 45 đến 47 cho các format thời gian đã trôi qua như [h]:mm:ss. Khi DetectDates bật, mặc định là bật, reader giải quyết style của mỗi ô số thành format id của nó, và ô có id thuộc các dải dành riêng đó được báo cáo là xdkDate với Value đã được chuyển thành Delphi TDateTime. Các format tùy chỉnh cũng được kiểm tra bằng cách xem xét code format để tìm token ngày giờ, nhưng các dải dành riêng là xương sống đáng tin cậy. Tắt DetectDates và bảng styles thậm chí không được tải, mọi ô số đều đến dưới dạng xdkNumber, và quét gọn hơn một chút
Bỏ qua sheet và dừng sớm
Quét tuần tự có một lợi thế thầm lặng mà truy cập ngẫu nhiên không thể sánh kịp: bạn có thể dừng. Sự kiện OnSheet kích hoạt trước khi mỗi worksheet được mở, và nó cho bạn hai công tắc. Đặt SkipSheet và toàn bộ phần đó không bao giờ được phân tích, đây là cách bạn chỉ quét những sheet bạn quan tâm trong workbook nhiều sheet mà không phải trả tiền để đọc phần còn lại. Đặt Abort và toàn bộ quét kết thúc ngay lập tức. Sự kiện OnCell mang Abort riêng của nó, nên bạn có thể dừng ngay khi tìm thấy những gì bạn đang tìm kiếm, một dòng cụ thể, một giá trị sentinel, cuối một khối tiêu đề, mà không cần đọc hàng triệu ô còn lại. Trên quét chỉ tiến, dừng thực sự miễn phí, vì công việc bạn bỏ qua là công việc chưa xảy ra
procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
// Chỉ quét bảng tính "Data"; bỏ qua phần còn lại chưa đọc
SkipSheet := SheetName <> 'Data';
end;Đếm ô mà không cần handler
Một cải tiến gần đây đáng đề cập vì nó biến một câu hỏi phổ biến thành một lần gọi đơn giản rẻ. Reader đếm mọi ô được điền mà nó đi qua, và nó làm điều này dù có hay không có OnCell handler được gắn. Trước đây, khi không có handler, số lượng ô được điền trả về là không, vì đếm là tác dụng phụ của việc phát. Giờ đây, đếm độc lập với phát. Điều đó có nghĩa là bạn có thể hỏi một câu hỏi, workbook này thực sự có bao nhiêu ô được điền, và nhận câu trả lời với chi phí của một lần quét không có callback nào. ReadFile và ReadStream đều trả về tổng đó dưới dạng Int64, và cùng con số đó có thể truy cập sau đó qua thuộc tính CellCount. Kết quả -1 báo hiệu rằng file không thể mở hoặc không phải gói OOXML
var
Reader: TXLSDirectReader;
Populated: Int64;
begin
Reader := TXLSDirectReader.Create;
try
// Không có trình xử lý OnCell: một cuộc điều tra dân số ô có dữ liệu thuần túy, bộ nhớ vẫn gần như không đổi
Populated := Reader.ReadFile('quarterly_export.xlsx');
if Populated < 0 then
raise Exception.Create('Not a readable XLSX package')
else
Writeln(Format('%d populated cells (CellCount = %d)',
[Populated, Reader.CellCount]));
finally
Reader.Free;
end;
end;Để quét đầy đủ, bạn gắn handler và gọi ReadFile theo cách hoàn toàn giống nhau. Sự tương phản với load đầy đủ là toàn bộ điểm mấu chốt: trong khi việc load quarterly_export.xlsx vào một workbook sẽ mở rộng mọi ô thành một đối tượng thường trú và giữ tất cả, direct reader chỉ giữ các shared string và bảng style trong khi mười hai triệu ô chảy qua OnCell của bạn từng cái một. Phép tính chạy trên mỗi ô không để lại gì đằng sau, vì vậy bộ nhớ đỉnh được xác định bởi số chuỗi riêng biệt của workbook, không phải số dòng của nó
Direct reader là công cụ phù hợp khi công việc là đọc một workbook lớn một lần và trích xuất hoặc tóm tắt nó. Khi bạn cần truy cập ngẫu nhiên của mô hình đầy đủ nhưng muốn nó hoạt động tốt trên file lớn, phần điều chỉnh trong ghi chú về hiệu suất workbook lớn trong Delphi đề cập đến đường dẫn đó. Và khi hướng ngược lại, tạo đầu ra lớn thay vì tiêu thụ, hướng dẫn streaming-write cho server batch job áp dụng cùng nguyên tắc bộ nhớ không đổi cho việc viết. Tất cả đều được cung cấp như một phần của HotXLS Component cho Delphi và C++Builder, cùng với các API đọc, viết, công thức và định dạng được đề cập ở nơi khác trên blog này