Hầu hết mọi thành phần của định dạng nhị phân Excel kế thừa là một bản ghi đơn lẻ với kiểu hai byte sạch sẽ và độ dài hai byte. Một ô là một bản ghi LABELSST hoặc NUMBER. Một vùng được hợp nhất là một MERGEDCELLS. Bạn có thể đọc hầu hết một trang tính bằng cách duyệt qua từng bản ghi một và điều phối theo từ chỉ kiểu. PivotTable phá vỡ nhịp điệu đó. Một bảng PivotTable đơn lẻ không phải là một bản ghi, nó là một chương trình nhỏ được tạo thành từ hàng tá bản ghi hợp tác trải rộng trên hai vị trí khác nhau trong cùng một luồng tài liệu hợp hợp OLE (OLE compound document stream), và các mối quan hệ giữa chúng mang tính vị trí, đóng gói bit (bit-packed), và không chấp nhận sai sót. Đây là cấu trúc mà hầu hết các trình đọc BIFF8 hoặc là bỏ qua hoàn toàn hoặc giữ lại dưới dạng các byte mờ, bởi vì việc ghi một cấu trúc như vậy từ đầu đồng nghĩa với việc tái tạo lại mọi tham chiếu chéo mà chính Excel duy trì.
Lý do một bảng PivotTable rất khó xử lý là vì nó thực sự là hai sản phẩm được hàn gắn lại với nhau. Có bộ nhớ đệm pivot (pivot cache), một ảnh chụp nhanh tự chứa của dữ liệu nguồn với luồng phụ riêng của nó, và có dạng hiển thị bảng (table view), bố cục cho biết trường nào nằm trên trục nào. Bộ nhớ đệm và dạng hiển thị tham chiếu lẫn nhau bằng chỉ mục. Chỉ cần sai lệch một chỉ mục và tệp tin sẽ mở ra kèm lỗi làm mới dữ liệu hoặc một lưới trống rỗng âm thầm.
Pivot cache là một luồng phụ riêng biệt
Bộ nhớ đệm tồn tại trong luồng toàn cục của sổ làm việc dưới dạng một luồng phụ BIFF hoàn chỉnh, được đóng khung bởi một bản ghi BOF có loại tài liệu là 0x0006 (giá trị đánh dấu một pivot cache, trái ngược với 0x0005 cho sổ làm việc hoặc 0x0010 cho một trang tính) và được đóng lại bằng bản ghi EOF tương ứng. Bên trong khung đó, cấu trúc được cố định. Một bản ghi SXDB là phần tiêu đề bộ nhớ đệm. Nó mang số lượng bản ghi, số lượng trường bộ nhớ đệm, và định danh luồng mà dạng hiển thị bảng sẽ trích dẫn để liên kết chính nó với bộ nhớ đệm này. Mỗi cột nguồn sau đó đóng góp một bản ghi định nghĩa trường SXFDB theo sau bởi một SXFDBType phân loại nó, và sau đó là các giá trị duy nhất mà cột đó sở hữu, được phát ra dưới dạng một bản ghi mục có kiểu (typed item record) cho mỗi giá trị riêng biệt.
SXDBB và kỹ thuật đóng gói bit tạo nên sự thú vị
Bản chỉ mục cho mỗi bản ghi là phần kỳ lạ nhất về mặt kỹ thuật của toàn bộ cấu trúc, và nó tồn tại trong bản ghi SXDBB. Kiểu mã hóa đơn giản nhất sẽ lưu trữ chỉ mục mục của từng trường dưới dạng một từ 16-bit. Excel không làm vậy. Nó đóng gói chỉ mục của từng trường vào chính xác số lượng bit cần thiết để định vị các mục của trường đó, không thừa không thiếu. Chiều rộng là ceil(log2(itemCount + 1)) bit. Giá trị + 1 rất quan trọng: giá trị bổ sung là một lính gác (sentinel) có nghĩa là "trống, không có giá trị cho trường này trong bản ghi", do đó một trường có ba mục riêng biệt cần biểu diễn bốn trạng thái và vì thế chiếm hai bit, chứ không phải một bit mà chỉ riêng ba mục gợi ý. Một trường không có mục nào sẽ đóng góp không bit và bị bỏ qua hoàn toàn trong quá trình đóng gói.
Các bit cho một bản ghi được nối lại với nhau trên tất cả các trường, sau đó bản ghi tiếp theo bắt đầu trên một ranh giới byte mới. Các bản ghi được căn chỉnh theo byte (byte-aligned), chứ không phải đóng gói bit từ đầu đến cuối, điều này làm cho việc truy cập ngẫu nhiên vào bảng có thể thực hiện được với chi phí là một vài bit đệm (padding bits) trên mỗi hàng. Việc đóng gói trong một byte tuân theo quy tắc bit ít quan trọng nhất trước tiên (least-significant-bit first). Một khi bạn chấp nhận hai quy tắc đó, bộ mã hóa chỉ là một bộ bơm bit đơn giản, và bộ giải mã là hình ảnh phản chiếu của nó.
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit;
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;Lý do chi tiết này không thể bỏ qua là giới hạn trần 8224 byte trên một bản ghi BIFF đơn lẻ. Mọi bản ghi trong định dạng này, bao gồm cả các bản ghi pivot, phải chứa phần dữ liệu của nó trong tối đa 8224 byte, và một pivot cache bận rộn với hàng nghìn hàng nguồn sẽ vượt qua giới hạn đó từ rất lâu trước khi nó phát ra tất cả các hàng. Do đó bảng chỉ mục được chia nhỏ ra. HotXLS giới hạn một thân bản ghi SXDBB đơn lẻ ở mức 8220 byte, tức là giới hạn bản ghi 8224 trừ đi bốn byte tiêu đề bản ghi của kiểu và độ dài, chia nhỏ số đó cho chiều rộng byte của một bản ghi đóng gói để biết có bao nhiêu hàng đầy đủ khớp vừa, và sau đó phát ra lượng bản ghi SXDBB nối tiếp tương ứng khi số lượng hàng yêu cầu. Mỗi bản ghi nối tiếp bắt đầu lại một cách sạch sẽ trên một ranh giới bản ghi, do đó không có hàng nào bị cắt ngang qua hai bản ghi. Một trình đọc biết chiều rộng bit trên mỗi bản ghi có thể duyệt qua mọi SXDBB theo trình tự giống như thể chúng là một mảng bit liên tục.
Bố cục dạng hiển thị: SXLI cho thân bảng, SXPI cho trang
Sau khi bộ nhớ đệm được dựng, dạng hiển thị bảng là phần thứ hai. Cốt lõi của nó là các mục dòng trục (axis line items), các hàng của thân pivot liệt kê mọi sự kết hợp của các giá trị trường hàng và trường cột mà bảng vẽ ra. Chúng được mang trong các bản ghi SXLI (loại bản ghi 0x00B5, được mô tả trong [MS-XLS] §2.4.275). Một bản ghi SXLI chứa nhiều dòng, một lần nữa cho đến khi giới hạn 8224 byte buộc tạo một bản ghi mới, và nó sử dụng một mẹo nén nhỏ: mỗi dòng chỉ lưu trữ điểm khác biệt của nó so với dòng phía trên, được biểu thị dưới dạng một số lượng tiền tố chung, do đó một trục lồng nhau sâu không lặp lại các giá trị trường bên ngoài trên mỗi hàng. Dòng tổng cộng (grand-total) và dòng đầu tiên của bất kỳ bản ghi nào luôn đặt số đếm tiền tố đó về không để trình đọc không bao giờ phải nhìn ngược lại qua một ranh giới bản ghi nhằm tái tạo lại một dòng.
Trục trang, các hộp thả xuống của bộ lọc nằm phía trên một bảng PivotTable, là một bản ghi riêng biệt. Bản ghi SXPI (loại bản ghi 0x00B6, [MS-XLS] §2.4.276) mang một mục nhập mười byte cho mỗi trường trang: chỉ mục trường pivot isxvd, mục bộ nhớ đệm được chọn iCache, một từ vị trí ipos, và một id đối tượng kế thừa objId. Giá trị iCache là giá trị cần lưu ý. Một trường trang hiển thị "(All)", không lọc gì cả, sẽ lưu trữ lính gác 0x7FFD thay vì một chỉ mục mục thực tế. Một pivot được xây dựng bằng mã nguồn sẽ mở ra với mỗi trường trang được đặt thành "(All)" cho đến khi bên gọi chọn trước một mục, tại thời điểm đó chỉ mục cache của mục đó sẽ thay thế lính gác và Excel mở ra với bộ lọc đã được áp dụng. Bên cạnh chúng là các bản ghi phụ trợ mô tả các trường riêng lẻ và định dạng của chúng, SXVD và SXVDEx cho các định nghĩa hiển thị trường, SXIVD cho danh sách chỉ mục trường sắp xếp thứ tự từng trục, và SXFormat cho định dạng số, mỗi bản ghi lập chỉ mục quay ngược lại cùng một bộ nhớ đệm mà các dòng thân bảng tham chiếu.
Hai bộ ghi trong một: raw blobs và mô hình có kiểu (typed model)
Có một lý do cấu trúc khiến HotXLS duy trì hai đường dẫn hoàn toàn riêng biệt để ghi một bảng PivotTable, và điều đó xuất phát trực tiếp từ các yêu cầu về độ chính xác dữ liệu. Khi một sổ làm việc được đọc từ đĩa, các bản ghi pivot của nó được ghi bởi Excel hoặc bởi một công cụ tạo lập khác, và họ có thể sử dụng các biến thể bản ghi, các điểm kỳ lạ về thứ tự, hoặc các bản ghi mở rộng mà không bộ ghi bên thứ ba nào mô hình hóa đầy đủ. Điều an toàn duy nhất cần làm với các byte đó là trả lại chúng mà không thay đổi. Vì vậy, một bảng PivotTable đến từ một tệp tin được đánh dấu cờ FromRawBlobs = True, và khi lưu, bộ ghi sẽ phát lại các blob bản ghi được bảo toàn một cách nguyên văn. Không có gì được tái tạo, không có gì được diễn giải lại, và một quy trình khứ hồi qua mở và lưu sẽ ổn định về mặt byte.
Một bảng PivotTable do chương trình tự dựng là trường hợp ngược lại. Không có các byte ban đầu để bảo toàn, chỉ có mô hình đối tượng có kiểu (typed object model): một TXLSPivotCache với các trường và danh sách mục của nó, và một TXLSPivotTable với các chỉ định trục của nó. Bảng đó được đánh dấu cờ FromRawBlobs = False, và bộ ghi sẽ tuần tự hóa nó theo cách phức tạp hơn, phát ra một luồng phụ cache BOF = 0x0006 mới, đóng gói bảng chỉ mục SXDBB từ các chỉ mục mục mà mô hình có kiểu nắm giữ, và bố trí các bản ghi SXLI và SXPI từ cấu hình trục. Cờ này là thứ cho phép cả hai loại cùng tồn tại trong một sổ làm việc. Không có nó, một bộ ghi đơn lẻ sẽ phải loại bỏ độ chính xác của các bảng đã đọc vào hoặc từ chối tạo mới các bảng khác. Bất kỳ bản ghi mở rộng cụ thể của nhà sản xuất nào mà một bảng đọc vào mang theo đều được giữ dưới dạng các bản ghi bổ sung, có thể truy cập được thông qua danh sách SupplementalRecords của bảng, do đó một bảng được kiểm tra thông qua mô hình có kiểu không bị mất đi các phần mà mô hình không mô tả.
Xây dựng một bảng PivotTable trong mã nguồn
Tất cả các cơ chế phía trên nằm sau một lệnh gọi duy nhất. Phương thức AddPivotTable nhận phạm vi nguồn theo định dạng A1, ô đích nơi góc trên bên trái của bảng neo vào, và một tên gọi. Nó phân tích cú pháp phạm vi, quét nó để suy luận các kiểu trường và dựng bộ nhớ đệm (tái sử dụng bộ nhớ đệm hiện có nếu một bảng khác đã liên kết với cùng một phạm vi), và trả về một đối tượng TXLSPivotTable có kiểu với một trường cho mỗi cột nguồn, mọi trường ban đầu đều nằm ngoài trục. Sau đó, bạn đặt các trường lên các trục và chọn một phép tổng hợp dữ liệu. Chữ ký hàm diễn ra chính xác như vậy, và bộ nhớ đệm, việc đóng gói SXDBB, cùng các bản ghi hiển thị đều được tự động tạo ra cho bạn tại thời điểm lưu.
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;Hàng đầu tiên của phạm vi nguồn được đọc dưới dạng tiêu đề đặt tên cho các trường bộ nhớ đệm, do đó phương thức AddRowField('Region') khớp một cột bằng văn bản tiêu đề của nó chứ không phải bằng vị trí. Bởi vì bảng trả về là một mô hình có kiểu với cờ FromRawBlobs = False, bộ ghi sẽ đi theo đường dẫn dựng từ đầu: nó xây dựng một bộ nhớ đệm tự chứa không phụ thuộc vào việc phạm vi nguồn còn tồn tại tại thời điểm làm mới dữ liệu hay không, đây chính là thuộc tính bạn muốn khi PivotTable sẽ được chuyển đến người nhận vốn có thể di chuyển hoặc xóa dữ liệu cơ sở bên dưới.
Việc đọc và đối chiếu các bản ghi pivot và bộ nhớ đệm của một tệp tin bạn không tự tạo lập, bao gồm cả đường dẫn bảo toàn raw-blob, được trình bày trong hướng dẫn kiểm tra sổ làm việc và bàn làm việc chuyển đổi. Khi phạm vi nguồn lên đến hàng chục nghìn hàng và luồng SXDBB trải dài trên nhiều bản ghi tiếp theo, các kỹ thuật trong ghi chú hiệu năng sổ làm việc lớn sẽ giữ cho việc dựng bộ nhớ đệm không chiếm quyền kiểm soát thời gian chạy của bạn. Cả hai kết hợp tự nhiên với bộ ghi pivot đi kèm trong thành phần bảng tính HotXLS dành cho Delphi và C++Builder bên cạnh các API ô, công thức, biểu đồ và định dạng được đề cập ở những bài viết khác trên blog này.