Một ô theo sau trong một shared formula của XLSX không mang văn bản công thức nào cả. Phần tử <f t="shared" si="N"/> của nó trỏ tới một ô master ở nơi khác trong sheet, và bộ đọc phải dựng lại văn bản đó bằng cách dịch chuyển công thức master theo hiệu số hàng và cột. HotXLS Component cho Delphi và C++Builder thực hiện phép mở rộng đó tại thời điểm mở tệp, nên mọi ô theo sau đều báo cáo một công thức đầy đủ
Nếu bạn từng tải một tệp XLSX thực tế trong một thư viện bên thứ ba và thấy rằng một cột gồm một nghìn công thức chỉ có văn bản ở đúng một ô và chuỗi rỗng ở 999 ô còn lại, bạn đã gặp tính năng này từ phía sai. Không có gì hỏng cả. Tệp đang làm đúng những gì ECMA-376 cho phép nó làm, và bộ đọc đơn giản là dừng lại đúng chỗ mà XML dừng lại
Vì sao ô shared formula lại trống?
Vì định dạng cố tình chỉ lưu công thức một lần duy nhất. Trong ECMA-376 Phần 1 và ISO/IEC 29500-1, phần tử <f> (§18.3.1.40) mang một thuộc tính t kiểu ST_CellFormulaType, và giá trị shared nghĩa là ô này tham gia vào một nhóm được nhận dạng bởi thuộc tính si. Đúng một ô trong nhóm, ô master, cũng mang một thuộc tính ref cho biết dải mà nhóm đó áp dụng, và chỉ ô đó mang văn bản công thức dưới dạng nội dung phần tử. Mọi ô khác trong nhóm là ô theo sau. Nó lặp lại t="shared" và cùng si đó, và nội dung phần tử của nó rỗng. Excel ghi các nhóm này một cách mạnh mẽ, vì một thao tác fill-down trên một cột 200.000 hàng thu gọn từ 200.000 chuỗi công thức xuống còn một chuỗi cộng thêm 199.999 phần tử placeholder tí hon. Khoản tiết kiệm đó là có thật và cái giá rơi hoàn toàn lên bộ đọc: nếu không mở rộng, ô theo sau không mang ý nghĩa gì tự thân cả
Phép dịch chuyển là một phép dịch (translation), không phải một phép sao chép văn bản
HotXLS giải quyết một ô theo sau bằng cách định vị ô master được đăng ký dưới cùng si, tính hiệu số hàng và cột từ điểm neo (anchor) của master tới ô hiện tại, và dịch chuyển mọi tham chiếu trong công thức master theo hiệu số đó. Các chiều tương đối di chuyển, các chiều tuyệt đối thì không, và các tham chiếu hỗn hợp chỉ di chuyển nửa không-tuyệt-đối của chúng. Các chuỗi literal hoàn toàn được bỏ qua, nên một công thức tình cờ chứa văn bản "A1" giữ nguyên văn bản đó không đổi ở mọi ô theo sau
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;Thuộc tính ref là một cánh cổng, không phải trang trí. Một ô theo sau có tọa độ nằm ngoài dải áp dụng của master không được mở rộng, vì khi đó tệp đang đưa ra một tuyên bố mà nhóm đó không hỗ trợ. Tương tự, khi một phép dịch chuyển sẽ đẩy một tham chiếu lên trên hàng một hoặc sang trái cột A, HotXLS phát ra #REF! cho token đó thay vì âm thầm kẹp nó lại, đây chính là những gì bản thân Excel sẽ tạo ra cho cùng một chỉnh sửa. Phép dịch này là anh em gần với, nhưng không giống với, việc viết lại tham chiếu xảy ra khi bạn chèn hoặc xóa hàng. Đường đó có quy tắc riêng về việc một dải làm gì khi một chỉnh sửa cắt qua nó, và nó được trình bày riêng trong bài viết về điều chỉnh tham chiếu công thức khi chèn và xóa. Mở rộng shared đơn giản hơn: nó là một offset thuần túy từ một điểm neo đã biết, được áp dụng đúng một lần, tại thời điểm phân tích
Bộ dịch chuyển phải bao quát những hình dạng tham chiếu nào?
Tất cả chúng, nếu không việc mở rộng sẽ là một lỗi mất dữ liệu được ngụy trang. Một bộ dịch chuyển ngây thơ chỉ hiểu A1 và A1:B2 sẽ làm hỏng hoặc làm mất những dạng kỳ lạ hơn, và các workbook thực tế đầy rẫy chúng. Bộ dịch shared-formula của HotXLS nhận diện toàn bộ họ A1 trước khi nó quyết định cái gì cần di chuyển. Các tham chiếu workbook ngoài như [Book.xlsx]Sheet1!A1 và tham chiếu 3D như Sheet1:Sheet3!A1 giữ nguyên tiền tố của chúng trong khi tham chiếu ô ở cuối dịch chuyển. Tên sheet có trích dẫn sống sót, kể cả trường hợp gai góc khi sheet được đặt tên đúng là A1, nên 'A1'!A1 chỉ dịch chuyển phần sau dấu chấm than. Cột toàn phần A:A chỉ di chuyển chiều cột của nó và không gì khác; hàng toàn phần 1:1 chỉ di chuyển chiều hàng của nó và không gì khác; $A:$A hoàn toàn không di chuyển. Các tham chiếu bảng có cấu trúc như Table[A1] được để nguyên, vì phần trong ngoặc vuông là một tên cột, không phải một tọa độ
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]Tên hàm chính là cái bẫy âm thầm ở đây. Một bộ quét token chỉ đơn thuần bắt các chữ cái theo sau bởi các chữ số sẽ vui vẻ viết lại LOG10 thành LOG11 khi dịch xuống một hàng. HotXLS đòi hỏi một ranh giới tham chiếu trước một token ứng viên và sau nó, nên một định danh tiếp tục vào một chữ cái, chữ số, gạch dưới, dấu chấm, hay một dấu ngoặc mở không phải là một tham chiếu ô. Nếu bạn đang làm việc với họ ký hiệu khác, cùng vấn đề ranh giới đó xuất hiện theo cách khác, và bài viết về ký hiệu R1C1 trình bày nơi hai mô hình này phân kỳ
Vì sao một phần tử f tự đóng lại nuốt mất giá trị tiếp theo?
Vì một phần tử tự đóng không tạo ra sự kiện end-element nào cả. Đây là lỗi tốn kém nhất trong toàn bộ tính năng này, và nó không đặc thù cho bất kỳ bộ phân tích XML nào. Trong TXMLReader, <f t="shared" si="4"/> chỉ phát ra đúng một sự kiện Element với IsEmptyElement đặt là True, và không bao giờ phát ra EndElement tương ứng. Một bộ phân tích chỉ đóng trạng thái thu công thức của nó khi có EndElement do đó vẫn ở lại bên trong công thức, và văn bản tiếp theo nó thấy, chính là kết quả đã cache bên trong <v>, bị nối thêm vào buffer công thức. Tệ hơn, trạng thái đó sống sót qua cả ranh giới ô, nên ô tiếp theo sở hữu một <f> thật sự có văn bản công thức của nó bị ô trước đó hấp thụ mất. Cách sửa là kết thúc trạng thái công thức ngay tại chính sự kiện Element bất cứ khi nào IsEmptyElement là True, và chạy toàn bộ việc giải quyết ô theo sau ngay tại đó thay vì chờ đợi. Điều đó nghĩa là đọc t, si, ref, aca, và ca từ các thuộc tính, áp dụng mở rộng shared, ghi các thuộc tính tính-lại lên ô, và xóa trạng thái shared, tất cả bên trong nhánh xử lý phần tử rỗng. Lưu ý rằng định dạng cho phép cả hai cách viết, <f t="shared" si="4"/> và <f t="shared" si="4"></f>, và cách viết thứ hai thì có phát ra một EndElement. Một bộ đọc đúng phải xử lý cả cặp đó giống hệt nhau, đó là lý do HotXLS bao quát cả hai cách viết trong cùng một tệp hồi quy
Giá trị si thưa, không theo thứ tự, và hàng đợi chờ xử lý
Thuộc tính si là một số nguyên không dấu do tệp cung cấp, không phải một vị trí mảng bạn kiểm soát. Không có gì trong schema yêu cầu các chỉ số shared phải liền mạch, phải bắt đầu từ không, hay phải xuất hiện theo thứ tự tăng dần, và không có gì ngăn một tệp thù địch hoặc đơn thuần kỳ lạ dùng si="4294967290" trên ô đầu tiên. Vì vậy định cỡ một mảng tra cứu theo si lớn nhất quan sát được là một nguyên thủy làm cạn kiệt bộ nhớ, không phải một tối ưu hóa. HotXLS thay vào đó giữ đường mở workbook trên một bảng thưa đã sắp xếp: các nhóm shared được đăng ký dưới khóa số nguyên của chúng trong một TStringList đã sắp xếp, biến việc tra cứu thành một tìm kiếm nhị phân trên bất kể bao nhiêu nhóm thực sự tồn tại, không liên quan gì tới kích thước số học của các chỉ số. Thứ tự là nửa còn lại của vấn đề. Một master thông thường đứng trước các ô theo sau của nó theo thứ tự tài liệu, nhưng đó là một quy ước chứ không phải một quy tắc, nên bất kỳ ô theo sau nào không thể giải quyết si của nó tại thời điểm nó được phân tích sẽ đi vào một hàng đợi chờ xử lý. Khi sheet hoàn tất, hàng đợi được phát lại dựa trên bảng nay đã đầy đủ, và các master tới muộn giải quyết các ô mồ côi của chúng. Các ô không bao giờ tìm được master giữ một công thức rỗng, đây là kết quả trung thực cho một tệp tham chiếu tới một nhóm mà nó chưa từng định nghĩa
Mở rộng shared formula mà không tải toàn bộ workbook
Các bộ đọc streaming đối mặt với cùng yêu cầu đó dưới một ngân sách bộ nhớ chặt hơn nhiều, và chúng giải quyết nó bằng một bảng cục bộ theo worksheet. Cả TXLSDirectReader và TXLSRowCursor đều mở rộng các ô theo sau thành các công thức đầy đủ theo từng ô trong khi vẫn giữ hành vi bộ nhớ giới hạn và chiếu (projection) của chúng, nên một lượt duyệt chỉ-tiến trên một sheet 300 MB vẫn trao cho bạn văn bản công thức thật
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;Hai ràng buộc rơi ra từ thiết kế đó. Thứ nhất, việc chiếu không bao giờ được phép bỏ qua master. Một bộ lọc hàng được thiết lập bằng FirstRow và LastRow, hay một bộ lọc cột được xây bằng IncludeColumn, có thể bỏ qua việc phát ra ô master cho callback của bạn, nhưng bộ phân tích vẫn phải ghi lại si, tọa độ neo, dải áp dụng, và văn bản công thức của nó, nếu không mọi ô theo sau bên trong phép chiếu sẽ giải quyết thành không có gì. Chỉ công việc phía ô-theo-sau, việc dịch chuyển và giải mã giá trị, mới an toàn để bỏ qua. Thứ hai, bảng đó thuộc phạm vi từng worksheet và vòng đời của nó phải được quản lý một cách rõ ràng: TXLSRowCursor giữ một thực thể trong suốt một lượt duyệt sheet và xóa nó khi khởi động lại, chuyển sheet, hết tệp, có ngoại lệ, và đóng lại, nên một nhóm được định nghĩa trên sheet một không bao giờ có thể rò rỉ sang sheet hai. Vì đường streaming là một vòng lặp nóng, nó dùng một bảng hash số nguyên kiểu open-addressing thay vì bảng chuỗi đã sắp xếp, tránh được một phép chuyển số-nguyên-sang-chuỗi cho mỗi ô
Điều gì xảy ra khi lưu, và ranh giới nằm ở đâu
Một khi một ô theo sau đã được mở rộng, nó trở thành một công thức thông thường, và HotXLS ghi nó trở lại dưới dạng một phần tử <f> độc lập không có t="shared" và không có si. Vòng đọc-ghi ổn định và các kết quả <v> đã cache vẫn sống sót, nhưng đầu ra lớn hơn đầu vào đối với một sheet dùng shared nhiều, và việc nhóm mà Excel đã tạo ra không được tái tạo lại khi lưu. Nếu tính trung thực ở mức byte của các nhóm shared quan trọng với bạn hơn việc có văn bản công thức thật trong mọi ô, đây là sự đánh đổi bạn đang chấp nhận. Phía XLS thì khác, ngẫu nhiên thay: bản ghi SHRFMLA của BIFF8 có cách mã hóa riêng và bộ ghi riêng của nó, với một công tắc bật/tắt nhóm shared trên workbook
Hai thứ có liên quan rõ ràng không phải là shared formula dù chúng chia sẻ chung phần tử <f>. Công thức mảng CSE kiểu cũ dùng t="array" với một ref bao phủ dải được neo; mảng động (dynamic array) dùng cùng cách viết t="array" nhưng được nhận dạng bằng một thuộc tính cm nối chuỗi qua cellMetadata tới một bản ghi XLDAPR. Xử lý một ô tràn (spill) của mảng động như một ô theo sau shared hay CSE là một lỗi đúng đắn thực sự, và sự phân tách đó được trình bày trong bài viết về công thức mảng động và tràn. Hãy đọc ba trường hợp này như ba bộ phân tích tình cờ chia sẻ chung một tên tag, và đoạn mã sẽ giữ được sự trung thực
Việc mở rộng shared formula, các bộ đọc streaming, và bộ dịch tham chiếu được mô tả ở đây được cung cấp như một phần của thành phần Excel HotXLS cho Delphi và C++Builder; trang sản phẩm có đầy đủ tài liệu tham khảo API công thức và đọc trực tiếp, bao gồm các thuộc tính chiếu được dùng ở trên