Đặt tên vùng dữ liệu và công thức liên kết trang tính trong Delphi với HotXLS

Một defined name là một nhãn đứng thay cho một hằng số, một range ô, hoặc một biểu thức công thức, được lưu một lần trong workbook và được tham chiếu bằng ký hiệu ở bất cứ đâu cần tới nó. Viết TaxRate trong một công thức và engine sẽ giải quyết nó thành bất cứ thứ gì định nghĩa của tên đó nắm giữ, dù đó là hằng số literal 0.08 hay range Data!$A$2:$D$100. Một tham chiếu liên trang tính là ý tưởng trực giao với điều đó: Data!D2 chạm tới một ô trên một sheet khác bằng cách gắn thêm tên sheet vào địa chỉ. Kết hợp cả hai lại và một sheet tóm tắt có thể cộng tổng một sheet chi tiết thông qua một tên không bao giờ nhắc tới một địa chỉ literal, đây chính xác là điều bạn muốn trong một workbook do một trình tạo dựng nên và sau đó một kế toán viên kiểm toán lại

HotXLS, thư viện Delphi thuần gốc của losLab cho file XLS và XLSX, lộ ra bảng tên của cả hai định dạng với quyền tạo, tìm, và xóa, cộng thêm một formula engine giải quyết tên và tham chiếu liên trang tính ngay trong tiến trình. Hai định dạng giữ hai hệ phân cấp class tách biệt, và những khác biệt giữa API tên của chúng là phần khiến mã được chuyển từ định dạng này sang định dạng kia vấp ngã

Hai kho lưu tên không dùng chung một interface

Ở phía XLS, TXLSWorkbook.GetNames trả về một collection IXLSNames mà overload Add(Name, RefersTo, Visible) của nó ghi một tên vào bảng tên BIFF. Từng entry riêng lẻ trả về dưới dạng đối tượng IXLSName mang Name, RefersTo, một RefersToRange đã giải quyết, và một phương thức Delete. Ở phía XLSX, TXLSXWorkbook.DefinedNames là một collection TXLSXDefinedNames với Add, FindByName, và DeleteByName

Các quy ước tra cứu phân kỳ theo cách nổi lên trong quá trình chuyển đổi mã chứ không phải lúc biên dịch. Thuộc tính Item mặc định của collection XLS chấp nhận một Variant, nên cả Names[0] lẫn Names['TaxRate'] đều giải quyết được trên đó. Collection XLSX không có thuộc tính mặc định như vậy; bạn gọi FindByName('TaxRate'), hàm này trả về nil khi tên đó không tồn tại. Mã viết cho một lớp giao diện chỉ biên dịch được trên lớp giao diện kia một cách tình cờ, và lỗi thường lộ ra dưới dạng một truy cập nil lúc runtime chứ không phải một gạch đỏ trong IDE

Scope là quyết định đầu tiên, không phải một cờ bạn thêm vào sau

Một defined name hoặc là workbook-scoped, hiển thị cho công thức trên mọi sheet, hoặc là sheet-scoped, chỉ hiển thị cho công thức trên sheet sở hữu nó. Trong API XLSX, sự phân biệt này chỉ là một tham số tùy chọn duy nhất. DefinedNames.Add(AName, AFormula) tạo một tên ở cấp workbook, trong khi Add(AName, AFormula, ASheetIndex) gắn nó vào một sheet cụ thể. Khi đọc lại, TXLSXDefinedName.SheetIndex trả về -1 cho scope cấp workbook và chỉ số sheet đánh số từ 0 trong các trường hợp khác

Scope đóng vai trò kép như chính sách xử lý va chạm của bạn, và đó là lý do cần chốt nó trước khi bạn viết tên đầu tiên. Excel cho phép một Total cục bộ trên mỗi sheet cộng với một Total ở cấp workbook, và một công thức trên một sheet nhất định sẽ giải quyết tên cục bộ trước. Các workbook được tạo tự động nên dựa vào điều đó một cách có chủ đích. Các giả định nghiệp vụ mà nhiều sheet cùng tiêu thụ, chẳng hạn thuế suất, tỷ giá, và kỳ báo cáo, nên thuộc về scope cấp workbook. Các range hỗ trợ mà chỉ công thức của một sheet tham chiếu tới thì an toàn hơn khi ở sheet-scoped, nơi không gì có thể che khuất chúng và chúng cũng không thể che khuất gì khác

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... điền các hàng chi tiết vào Data!A2:D100 ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // scope workbook, một hằng số
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // scope workbook, một range
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // chỉ giới hạn trong sheet index 1

    // Công thức XLSX không có dấu '=' ở đầu
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Một defined name không nhất thiết phải trỏ tới một range. TaxRate ở trên tham chiếu tới hằng số trần 0.08, và đó là cách sạch sẽ nhất để công bố một giả định nghiệp vụ. Nó chỉ xuất hiện một lần trong Name Manager của Excel, mọi công thức tham chiếu tới nó bằng ký hiệu, và việc thay đổi thuế suất của quý sau chỉ là một lần sửa một dòng trong trình tạo thay vì phải tìm kiếm xuyên qua mười bốn chuỗi công thức đã lắp ráp sẵn

Dấu bằng chỉ thuộc về một phía mà thôi

Kênh nhập công thức là nơi mã đã chuyển đổi hỏng nhiều nhất, bởi vì hai lớp giao diện bất đồng về dấu bằng. Ô XLS nhận công thức thông qua Value với dấu = ở đầu. Ô XLSX có một thuộc tính Formula riêng, nhận biểu thức không có tiền tố đó. Viết '=SUM(A1:A10)' vào TXLSXCell.Formula và dấu bằng trở thành một phần của văn bản biểu thức được lưu trữ thay vì một dấu hiệu đánh dấu, và file sẽ không hoạt động theo cách cùng chuỗi đó đã hoạt động ở phía XLS

var
  Book: IXLSWorkbook;   // đếm tham chiếu qua interface: không được Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // giả định một sheet tên 'Data' đã sẵn chứa các hàng chi tiết
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = ẩn khỏi Name Manager

  // Công thức XLS đi qua Value, kèm tiền tố '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Đoạn mã đó cho thấy thêm hai điểm kỳ quặc ở phía XLS. Collection sheet đánh số từ 1, nên Sheets[1] là sheet đầu tiên, đối lập với Sheets[0] đánh số từ 0 của XLSX. Và tham số thứ ba của Add tạo ra một tên ẩn: hiện diện trong file và có thể dùng được bởi công thức, nhưng vô hình trong Name Manager của Excel. Tên ẩn là phương tiện đúng đắn cho hạ tầng nội bộ của trình tạo mà người dùng cuối không bao giờ nên chỉnh sửa hay xóa nhầm

Tham chiếu liên trang tính, và điều gì xảy ra khi hàng di chuyển

Cả hai formula engine đều chấp nhận cú pháp liên trang tính chuẩn. Tên sheet trơn được gắn trực tiếp như Data!A1; một tên có dấu cách hoặc dấu câu cần dấu nháy đơn, như trong 'Sheet With Space'!A1. Bên trong văn bản RefersTo của một tên, hãy dùng tham chiếu tuyệt đối như Data!$A$2:$D$100 gần như mọi lúc. Một tham chiếu tương đối bên trong một defined name sẽ giải quyết tương đối theo ô đang dùng nó, đây là một tính năng có chủ đích của Excel và là một nguồn gây nhầm lẫn đáng tin cậy khi nó kích hoạt một cách tình cờ

Các chỉnh sửa cấu trúc chính là nơi việc quản lý sổ sách liên trang tính chứng tỏ giá trị của nó, và phía XLSX giữ các tên nhất quán xuyên suốt những chỉnh sửa đó. InsertRows và DeleteRows dịch chuyển các range của defined name cùng với ô, vùng merge, hyperlink, và neo biểu đồ, nên một tên trỏ tới Data!$A$2:$D$100 vẫn bao phủ khối dữ liệu sau khi trình tạo mở ra một khoảng trống phía trên nó. Công thức đi kèm một lưu ý đã được tài liệu hóa: việc chèn hàng chỉ điều chỉnh những tham chiếu nhắm vào sheet đang được chỉnh sửa. Một công thức trên Summary tham chiếu Data!D2:D100 sẽ được viết lại khi hàng được chèn vào Data, đây là trường hợp bạn thường muốn. Hãy xác minh thay vì giả định, bởi vì engine sẽ cho bạn biết với chi phí rất thấp:

// calculation engine giải quyết tên và tham chiếu liên trang tính ngay trong tiến trình
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate đánh giá một biểu thức bất kỳ đối chiếu với trạng thái workbook hiện tại mà không lưu bất cứ thứ gì, điều này khiến nó trở thành nguyên hàm assertion tự nhiên cho các test của trình tạo. Hãy tính giá trị tổng hợp kỳ vọng từ dữ liệu nguồn trong Pascal, đánh giá công thức của chính workbook, rồi so sánh hai kết quả đó. Bài viết về formula engine nói về những gì engine đánh giá, khi nào, và cách mở rộng nó bằng các hàm tùy chỉnh

Các tên _xlnm mà lớp thuộc tính sở hữu

Mở bảng tên của một file được tạo ra trong một trình kiểm tra cấp thấp và bạn sẽ thấy những entry bạn chưa từng viết: _xlnm.Print_Area, _xlnm.Print_Titles, và các anh em của chúng. Đây là cách OOXML (ECMA-376 / ISO 29500) lưu trữ vùng in và các hàng tiêu đề lặp lại, dưới dạng những defined name với định danh dành riêng. HotXLS quản lý chúng thông qua các thuộc tính worksheet chuyên dụng, nên đặt PrintArea hoặc PrintTitleRows sẽ ghi entry _xlnm.* tương ứng giúp bạn

Cái bẫy là tự tay chạm vào namespace dành riêng đó. Thêm một entry _xlnm.Print_Area thông qua DefinedNames.Add trong khi cũng đặt thuộc tính PrintArea và workbook sẽ mang hai định nghĩa xung đột nhau cho cùng một tên dành riêng, một trạng thái mà Excel giải quyết theo những cách không sản phẩm nào nên phụ thuộc vào. Hãy coi mọi định danh bắt đầu bằng _xlnm. như thuộc về lớp thuộc tính. Để kiểm tra thiết lập in, hãy đọc các thuộc tính, không phải bảng tên. Bài viết về bảo vệ và thiết lập trang nói về các thuộc tính vùng in trong bối cảnh cụ thể

Hai ranh giới đáng biết trước khi bạn chốt một thiết kế

Defined name không đi kèm qua cầu nối tiện lợi XLS-sang-XLSX. SaveXLSWorkbookAsXLSX sao chép nội dung ô và định dạng cơ bản, và bảng tên không nằm trong danh sách sao chép đã được tài liệu hóa của nó, nên một workbook phụ thuộc vào các tên của nó sẽ mất chúng khi vượt qua cầu nối. Hãy tạo lại các tên thông qua DefinedNames.Add sau khi chuyển đổi. Bước đó ít vất vả hơn nghe có vẻ, bởi vì nó cho bạn một khoảnh khắc để chuẩn hóa lại scope của chúng thay vì mang nguyên những gì file XLS tình cờ có

Ranh giới còn lại là độ trôi giữa các chuỗi công thức và tên sheet. Excel viết lại các tham chiếu sheet bên trong công thức và tên trong lúc đổi tên tương tác, nên các file mà người dùng chỉnh sửa trong Excel tự giữ được nhất quán. Điểm hở nằm ở phía trình tạo: khi mã Pascal lắp ráp các chuỗi công thức từ một literal tên sheet, việc đổi tên sheet ở một nơi mà quên nơi kia sẽ tạo ra một tham chiếu tới một sheet không còn tồn tại. Hãy giữ tên sheet trong một hằng số Delphi duy nhất và đưa nó vào cả Sheets.Add lẫn phần lắp ráp công thức của bạn, và hai bên sẽ không bao giờ bất đồng. Đây cũng chính là bản năng đứng sau việc đặt tên cho các ô kết quả của một báo cáo thay vì hard-code địa chỉ: một template mà ô tổng của nó được đặt tên vẫn tiếp tục hoạt động sau khi một nhà thiết kế chèn thêm ba hàng phía trên nó, trong khi một trình tạo ghi vào một literal B17 sẽ âm thầm đặt con số của nó vào sai chỗ. Bài viết về tạo báo cáo từ template xây dựng trên chính khuôn mẫu đó

API defined-names đầy đủ cho cả hai định dạng, cùng với tài liệu tham khảo formula engine, đi kèm trong HotXLS Delphi Component