HotXLS giờ tính toán được các tham chiếu bảng cấu trúc, nên =SUM(Table1[Amount]) cho ra một con số thay vì bị bỏ qua. Bộ giải quyết xử lý Table[Column], Table[[Column]], các dải cột như Table[[Q1]:[Q4]], và các item specifier [#Data], [#All], [#Headers] và [#Totals], giải quyết từng cái theo mô hình bảng của sổ làm việc ngay tại thời điểm phân tích cú pháp trong khi văn bản công thức gốc round-trip nguyên văn
Một dạng bị cố tình bỏ qua, và đó chính là dạng người dùng gặp phải đầu tiên. Cách viết tắt hàng hiện tại [@Column] không được hỗ trợ, vì một lý do cấu trúc đáng để hiểu rõ thay vì né tránh một cách mù quáng
Vì sao một tham chiếu cấu trúc không đơn thuần là một dải ô có tên thân thiện?
Vì một tên đã định nghĩa đóng băng một địa chỉ, còn một tham chiếu bảng thì không. Viết DataBlock làm một tên trỏ tới Sheet1!$A$2:$D$100 và nó giữ nguyên hình chữ nhật đó cho đến khi có gì đó ghi đè lên. Viết Sales[Amount] và nó có nghĩa là "cột Amount của bảng Sales", bất kể phạm vi của bảng đó là gì tại thời điểm công thức được tính toán. Thêm hai mươi hàng vào bảng và tổng sẽ bao phủ chúng; không có tham chiếu nào cần điều chỉnh vì ngay từ đầu chưa từng có một địa chỉ nào trong công thức
Chính tính chất mang tính biểu tượng đó khiến tham chiếu này không thể được giải quyết bằng thay thế chuỗi. Bộ giải quyết phải tìm bảng theo tên trong sổ làm việc, tra cột theo văn bản tiêu đề của nó, quyết định item specifier được yêu cầu bao phủ những hàng nào, rồi tạo ra một hình chữ nhật cụ thể. HotXLS làm điều này trong quá trình biên dịch công thức thông qua mô hình bảng, đó là lý do một công thức được viết trước khi bảng phát triển vẫn được tính toán dựa trên phạm vi hiện tại của bảng
Ngữ pháp mà HotXLS giải quyết được
Tập ngữ pháp specifier được hỗ trợ bao phủ một kết quả hình chữ nhật duy nhất và đáng để nói rõ, vì tài liệu của Excel trình bày một phạm vi lớn hơn nhiều so với hầu hết các bộ máy triển khai được. HotXLS chấp nhận [Col] và biến thể có ngoặc [[Col]], các item specifier trần [#Data], [#All], [#Headers] và [#Totals], dạng kết hợp [[#Data],[Col]], một dải bên trong một item specifier như [[#Data],[Col1]:[Col2]], và một dải thuần [Col1]:[Col2]
Tập đó cho bạn mọi hình dạng tham chiếu tạo ra một khối liền mạch duy nhất: một cột, một dãy các cột liền kề, một lát body-only hoặc bao gồm cả header của một trong hai. Các phép hợp không liền kề và kết quả đa vùng nằm ngoài tập đó. Khi một tham chiếu không thể giải quyết được, công thức vẫn giữ hành vi bỏ-qua-không-có-giá-trị như trước thay vì thay thế bằng một phỏng đoán, nên một tham chiếu không giải quyết được sẽ không bao giờ trở thành một con số sai trông có vẻ hợp lý
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Sales');
Cols.Add('Region');
Cols.Add('Q1');
Cols.Add('Q2');
Cols.Add('Amount');
Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
// ... ghi hàng tiêu đề và 24 hàng dữ liệu ...
Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;Vì sao dạng hàng hiện tại bị loại trừ có chủ đích?
[@Column] và [#This Row] có nghĩa là "ô của cột đó trên hàng nơi công thức này đang sống". Vì vậy giá trị đó phụ thuộc vào vị trí của ô đang tính toán, không chỉ vào bảng. Đó là một loại tham chiếu khác: không phải một hình chữ nhật mà trình biên dịch có thể giải quyết một lần, mà là một phép giải quyết theo từng ô phải được thực hiện lại cho mỗi hàng mà công thức đó chiếm giữ
HotXLS trả về False từ bộ giải quyết dải bảng cho các dạng đó, việc này định tuyến chúng vào đường bỏ-qua-không-có-giá-trị. Văn bản công thức được giữ nguyên và ghi lại không đổi, nên một sổ làm việc dùng [@Amount] vẫn mở đúng trong Excel sau một chu kỳ round-trip qua ứng dụng của bạn; chỉ giá trị do HotXLS tính toán là vắng mặt. Giữa việc lựa chọn một giá trị vắng mặt và một giá trị được tính sai theo hàng, sự vắng mặt là điều bạn có thể phát hiện
Cách khắc phục thực tế mang tính cơ học: trong một sổ làm việc bạn tự tạo ra, hãy viết tham chiếu tương đối kiểu A1 tương đương, đây cũng chính là những gì Excel lưu trữ nội bộ cho phần lớn logic phạm vi bảng. Trong một sổ làm việc bạn chỉ xử lý, hãy để nguyên công thức và đọc giá trị đã cache mà Excel đã lưu sẵn, đây thường là điều một pipeline tải-rồi-báo-cáo mong muốn
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Table: TXLSXTable;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('sales.xlsx') <> 1 then Exit;
Sheet := Book.Sheets[1];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// Tra cứu kiểu recordset trên phần thân bảng, kết quả hàng đánh số từ 1
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;Điều gì xảy ra khi bảng đổi hình dạng
Các tham chiếu cấu trúc bị vô hiệu hóa thay vì bị âm thầm trỏ lại khi thứ chúng đặt tên biến mất. Xóa một cột và các công thức tham chiếu tới cột đó bị vô hiệu hóa theo đúng cách Excel vô hiệu hóa chúng; xóa hoặc đổi tên bảng và các tham chiếu tới nó cũng được xử lý tương tự. Đây là hành vi đúng đắn và nó phản ánh việc điều chỉnh tham chiếu thông thường, được mô tả trong điều chỉnh tham chiếu công thức khi chèn và xóa, nơi công việc của bộ máy là giữ cho công thức trung thực thay vì chỉ giữ cho chúng trông có vẻ hợp lệ
Việc tăng trưởng hàng là trường hợp ngược lại và không cần điều chỉnh gì cả. Vì tham chiếu đặt tên bảng chứ không phải một hình chữ nhật, việc thêm hàng bên trong phạm vi bảng mở rộng những gì [#Data] bao phủ mà không đụng đến bất kỳ công thức nào. Đó chính là tính chất khiến các bảng đáng để dùng trong một mẫu báo cáo: hàng tổng vẫn cộng dồn mọi thứ mà việc nhập liệu tạo ra, bất kể cuối cùng có bao nhiêu hàng
Kỷ luật round-trip
HotXLS giữ nguyên văn bản công thức gốc. Một sổ làm việc được tải với SUM(SalesTable[Amount]) được lưu lại với SUM(SalesTable[Amount]), không phải với địa chỉ đã giải quyết SUM(D2:D25). Điều này quan trọng hơn vẻ ngoài của nó: một người dùng mở đầu ra của bạn trong Excel mong đợi thấy đúng công thức họ đã viết, và một địa chỉ đã giải quyết sẽ âm thầm biến một mô hình tự duy trì thành một mô hình dễ vỡ ngừng bao phủ các hàng mới
Hai khả năng liên quan hoàn thiện bức tranh này. Bản thân định nghĩa bảng, bao gồm cả các bảng không có tiêu đề và các chú thích riêng theo từng bảng, round-trip qua mô hình bảng được mô tả trong kiểm tra dữ liệu, AutoFilter và bảng Excel. Và khi nhiều ô cùng chia sẻ một mẫu, XLSX lưu trữ chúng một lần dưới dạng công thức chia sẻ, được mở rộng và phát ra lại như trình bày trong mở rộng si của công thức chia sẻ. Các tham chiếu cấu trúc bên trong công thức chia sẻ đi qua cả hai đường xử lý, nên cả hai đều cần hoạt động đúng, và chúng đã hoạt động đúng
HotXLS đọc và ghi XLS, XLSX và ODS từ Delphi và C++Builder mà không cần cài Excel và không cần tự động hóa Office, tự tính toán công thức bằng bộ máy riêng của mình. Mô hình bảng, bộ máy công thức và API tính lại được tài liệu hóa tại trang thành phần bảng tính HotXLS cho Delphi