Copy giữa các workbook và tái ràng buộc công thức trong HotXLS với Delphi

Phương thức AddCopy của HotXLS copy một worksheet từ một workbook Excel này sang một workbook khác bằng cách giải biên dịch mọi công thức trên sheet đó thành văn bản kiểu A1 và biên dịch lại văn bản đó bên trong workbook đích, thay vì copy trực tiếp cây công thức đã biên dịch, vì các tham chiếu chuỗi dữ liệu biểu đồ, chỉ số font rich text, và việc đánh số liên kết ngoài đều được gán độc lập bên trong mỗi file workbook

Lỗi này lộ ra đúng trong loại workbook bạn sẽ đoán được: một job cuối tháng kéo một sheet ra từ báo cáo của mỗi chi nhánh và nối nó vào một file tổng hợp. Mở kết quả ra và một biểu đồ tổng phụ vẽ ra hoàn toàn con số của một chi nhánh khác, một ghi chú từng đậm và đỏ trong nguồn giờ quay lại thành văn bản đen thuần túy, và một công thức từng kéo một mức thuế từ một workbook tra cứu đồng hành giờ hiển thị một con số đông cứng mà không ai giải thích được. Không có gì ném ra ngoại lệ ở đây — file vẫn mở, các con số trông hợp lý, và thiệt hại nằm đó cho đến khi ai đó nhận ra một biểu đồ với tiêu đề sai nằm cạnh nó

Vì sao AddCopy không thể chỉ copy cây công thức đã biên dịch?

AddCopy không thể di chuyển cây công thức đã biên dịch mà không đổi, vì một công thức BIFF đã biên dịch không phải văn bản độc lập — nó là một chuỗi token, và một số token đó là các số nguyên nhỏ chỉ giải quyết đúng bên trong workbook đã tạo ra chúng. Một tham chiếu 3D như Sheet2!A1:A10 không mang theo tên chữ nghĩa Sheet2 một khi nó được biên dịch; nó mang theo một trường mà spec BIFF gọi là ixti (HotXLS giữ cùng giá trị đó trong cây đã biên dịch riêng của mình dưới tên trường FExternID), một chỉ số vào bảng EXTERNSHEET riêng của workbook đó, được đánh số theo bất cứ cách nào workbook cụ thể đó tình cờ đăng ký các sheet và các sổ ngoài của nó. Di chuyển token đó mà không đổi vào một workbook có bảng EXTERNSHEET được xây dựng theo một thứ tự khác và chỉ số 3 không còn có nghĩa là Sheet2 nữa — nó có nghĩa là bất cứ sheet nào tình cờ chiếm slot 3 ở đó, và Excel không có cách nào để đánh dấu lỗi này, vì xét theo định dạng file, công thức đó hoàn toàn được định dạng tốt. Đây chính xác là lỗi mà TXLSWorksheets.AddCopy tồn tại để tránh: được gọi từ tập hợp sheet riêng của một trong hai workbook trong code Delphi hay C++Builder, nó copy một worksheet — giá trị ô, định dạng, công thức, biểu đồ, comment, gộp, page setup, và nhiều hơn nữa — từ một workbook nguồn có thể là hoặc không phải workbook bạn đang gọi nó trên đó, và nối kết quả vào workbook đích dưới một tên bạn chọn hoặc một bản sao đã khử nhập nhằng của tên gốc

var
  Summary, Branch: IXLSWorkbook;   // interface-counted: do not Free
begin
  Summary := TXLSWorkbook.Create;
  Branch := TXLSWorkbook.Create;
  Branch.Open('branch-east.xls');

  // Appends a copy of Branch's first sheet onto Summary, renamed to
  // stay unique inside the destination workbook
  Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
  Summary.SaveAs('consolidated.xls');
end;

Cách khắc phục: giải biên dịch thành văn bản, biên dịch lại trong đích

HotXLS giải quyết vấn đề đánh chỉ số bằng cách không bao giờ để chính cây đã biên dịch vượt qua ranh giới workbook. Với mỗi ô công thức trong một lượt copy xuyên workbook, AddCopy giải biên dịch công thức nguồn thành cùng văn bản kiểu A1 mà một người dùng sẽ thấy trong thanh công thức của Excel, sau đó đưa văn bản đó cho workbook đích, workbook này phân tích nó trở lại thành một cây bằng cách dùng các bảng riêng của nó từ đầu — một tham chiếu có đủ tên sheet như Data!D2:D100 tại thời điểm đó chỉ là một chuỗi, và một chuỗi có cùng ý nghĩa trong bất kỳ workbook nào, nên nếu đích đã có sẵn một sheet tên Data, tham chiếu đó giải quyết đúng mà hoàn toàn không cần dịch chỉ số nào, vì chưa từng có một chỉ số thô nào đang bay lơ lửng để dịch. HotXLS chỉ trả giá cho chuyến đi khứ hồi này khi thực sự cần: copy một sheet trong cùng workbook đi theo một đường rẻ hơn nơi cây đã biên dịch đơn giản được nhân bản trong bộ nhớ, vì mỗi chỉ số bên trong nó đã hợp lệ ở nơi nó đang ở lại, và đường vòng qua văn bản chỉ chạy một khi AddCopy phát hiện nguồn và đích thực sự là các instance workbook khác nhau. Cũng đáng nói rõ việc viết lại này không phải là gì. Nó không liên quan gì đến việc dịch chuyển hàng và cột chạy khi bạn chèn hoặc xóa hàng bên trong một sheet đơn, thứ mà một bài viết đồng hành nói đến chi tiết — engine đó viết lại văn bản A1 tại chỗ để theo dõi các ô đã di chuyển vài hàng lên hay xuống trong một workbook, trong khi engine này chạy khi một công thức rời khỏi hoàn toàn workbook đã biên dịch nó, nơi các hàng đã di chuyển không phải vấn đề và việc đánh số riêng của workbook mới là vấn đề

// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);

Điều gì xảy ra nếu đích chưa có sheet đó, hay tên đó, chưa?

Việc biên dịch lại của AddCopy chỉ thành công khi workbook đích đã có mọi thứ mà văn bản công thức tham chiếu đến, và hai khoảng trống lộ ra trong thực tế là một sheet cùng tên chưa được copy sang trong lô này, và một tên đã định nghĩa phạm vi workbook chưa từng tồn tại trong đích. HotXLS không ném ra một ngoại lệ khi việc biên dịch lại thất bại giữa chừng một lượt copy sheet — phép gán Value của ô âm thầm lưu văn bản công thức như một chuỗi thuần túy thay vào đó, một chế độ lỗi có chủ đích, có thể kiểm tra thay vì âm thầm, vì một ô công thức bất ngờ hiển thị văn bản chữ nghĩa như =SUM(Q1!B2:B12) thay vì một con số đã tính là dấu hiệu cho thấy có gì đó ở thượng nguồn của lượt copy không giải quyết được. Trước khi bỏ cuộc, AddCopy thử một lần sửa chữa: nó duyệt qua cây cú pháp của công thức thất bại thu thập mọi ID tên đã định nghĩa mà công thức chạm tới, và với mỗi tên phạm vi workbook tồn tại trong nguồn nhưng chưa có trong đích, nó copy tên đó sang và biên dịch lại cùng văn bản một lần thứ hai. Các tên phạm vi sheet nằm ngoài những gì lượt sửa chữa này có thể khắc phục, vì một tên chỉ hiển thị với các công thức trên một sheet của workbook nguồn không có slot tương đương để di cư vào, và một đích đã sở hữu sẵn một tên với cùng cách viết được để nguyên thay vì bị ghi đè, dựa trên giả định rằng một tên mà caller đã chủ động tạo trước là tên họ muốn được tôn trọng. Bên trong một workbook đơn, việc tra cứu tên của một công thức xuyên sheet duyệt từ phạm vi sheet lên phạm vi workbook tự động, đó là cơ chế mà bài viết về tên đã định nghĩa và công thức xuyên sheet của HotXLS nói đến; vượt qua một ranh giới workbook thật sự loại bỏ hoàn toàn tấm lưới an toàn đó, và một tên phải được chủ động mang qua nếu không công thức phụ thuộc vào nó sẽ suy giảm thành văn bản

Tham chiếu chuỗi dữ liệu biểu đồ cần cùng cách khắc phục, nhưng một đường code khác

Một chuỗi dữ liệu biểu đồ HotXLS vẽ một phạm vi ô gặp chính xác cùng vấn đề đánh số như một công thức ô thông thường, vì tham chiếu phạm vi dữ liệu của một biểu đồ cũng là một chuỗi token công thức đã biên dịch — spec BIFF gọi bản ghi mang nó là BRAI ([MS-XLS] mục 2.4.51) — nhưng AddCopy không thể sửa nó bằng cách tái sử dụng đường nạp biểu đồ thông thường, vì chính đường đó là thứ tạo ra lỗi. Khi một bản ghi biểu đồ được phân tích từ đĩa trong quá trình mở file thông thường, cây công thức của nó được xây dựng bằng cách dịch các byte thô qua bất cứ instance máy tính nào đang thực hiện việc phân tích; đưa các byte BRAI thô của một biểu đồ nguồn qua bộ nạp bản ghi thông thường riêng của workbook đích thay vào đó, và ixti nhúng trong các byte đó được giải quyết theo bảng EXTERNSHEET của đích, nên chuỗi dữ liệu âm thầm trỏ vào bất cứ sheet nào chiếm slot đó ở đó — cùng loại lỗi như việc copy cây đã biên dịch của một ô mà không đổi, chỉ là khó nhận thấy hơn vì không ai đọc công thức chuỗi dữ liệu biểu đồ theo cách họ đọc công thức ô. HotXLS tránh cái bẫy này bằng một đường clone chuyên biệt thay vào đó: TXLSCustomChart.AssignFrom copy các byte header không-phải-công-thức riêng của mỗi bản ghi biểu đồ nguyên văn, sau đó dựng lại phạm vi gắn kèm thông qua cùng primitive giải-biên-dịch-và-biên-dịch-lại được dùng cho các ô thông thường, nên cây mới được xây dựng theo bảng EXTERNSHEET của đích từ đầu thay vì được diễn giải lại theo nó sau khi thực tế đã xảy ra

Cùng vấn đề đánh số, mỗi lần một chỉ số font

Không phải mọi số cục bộ trong workbook bên trong một biểu đồ hay một ô rich text đều là một công thức, và một chỉ số font là cùng loại vấn đề đó thu nhỏ. Các đoạn rich text, cùng hai loại bản ghi biểu đồ khác mang một caption hay font trục, lưu trữ một tham chiếu font như một chỉ số nguyên thô vào bảng font riêng của workbook sở hữu, và chỉ số đó không có nghĩa gì trong bảng của một workbook khác — nó cũng dễ dàng trỏ vào một kiểu chữ, kích thước, hay màu sắc hoàn toàn khác ở đó. HotXLS giải quyết điều này theo giá trị chứ không phải theo số: nó tra cứu các thuộc tính font thực tế tại chỉ số đó trong bảng nguồn, tìm hoặc tạo một mục khớp trong bảng font của đích, và viết lại chỉ số đã lưu để trỏ vào slot mới đó. Một điểm kỳ quặc của định dạng khiến việc tra cứu tự thân trở nên lắt léo — chỉ số trên file bỏ qua slot 4, một khoảng trống đánh số mà [MS-XLS] mục 2.5.339 ghi chép, nên code phải dịch chỉ số xuống một trước khi so sánh font và dịch lên một trước khi ghi kết quả

// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
  Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
  Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
  Inc(Ifnt);

Điều gì xảy ra với một công thức đã trỏ ra ngoài workbook từ trước?

Một công thức vươn vào một workbook thứ ba trước cả khi bạn gọi AddCopy là trường hợp duy nhất mà chuyến khứ hồi văn bản không thể mang theo, vì bộ giải-biên-dịch công-thức-thành-văn-bản riêng của HotXLS có chủ đích không tổng hợp văn bản ngoặc [Book]Sheet! cho một tham chiếu ngoài, và trình biên dịch ở đầu bên kia cũng không chấp nhận cú pháp đó làm đầu vào — nên trường hợp duy nhất này chạy qua một cơ chế thứ hai hoàn toàn không đụng đến văn bản. Khi lượt sửa chữa di cư tên được mô tả ở trên vẫn để lại một ô dưới dạng chuỗi, và workbook nguồn có một tên file thật, AddCopy chuyển chiến lược: nó deep-copy chính cây công thức đã biên dịch thay vì văn bản của nó, sau đó đưa bản sao đó cho một lượt tái ràng buộc chuyên biệt, RebindExternRefsInTree, duyệt qua nó từng node một. Với mỗi tham chiếu phạm vi nó tìm thấy, lượt đó giải quyết mục EXTERNSHEET của nguồn trở lại thành một cặp tên sheet, và đăng ký, hoặc tái sử dụng, một mục tương đương trong các bảng tham chiếu ngoài riêng của đích, tạo một liên kết workbook-ngoài hoàn toàn mới nếu đích chưa từng tham chiếu file nguồn đó trước đây

Đây là nơi vấn đề đánh số cục bộ workbook thể hiện rõ nét nhất, vì một token tham chiếu ngoài đóng gói ba tọa độ riêng biệt vào một trường và mỗi tọa độ đều riêng tư đối với workbook đã viết nó: workbook ngoài nào, một slot trong danh sách sổ ngoài riêng của đích được gán theo bất cứ thứ tự nào workbook đó tình cờ đăng ký chúng; sheet nào bên trong danh sách sheet riêng của workbook ngoài đó, lưu trữ như một chỉ số bắt đầu từ 1 có phạm vi riêng cho sổ ngoài cụ thể đó, một miền đánh số hoàn toàn khác với các ID sheet nội bộ riêng của đích; và bản thân phạm vi ô, các tọa độ hàng và cột thuần túy không cần dịch vì chúng chưa bao giờ tương đối theo workbook ngay từ đầu. Sai một trong hai điều đầu tiên và Excel vẫn mở file, vẫn hiển thị một công thức, và đánh giá nó theo các ô ngoài sai mà không phàn nàn gì. Một loại node đánh bại ngay cả việc tái ràng buộc cấp cây này: một tham chiếu đến một tên đã định nghĩa, một chỉ số vào bảng tên riêng tư của chính workbook đó đúng theo cách một chỉ số sheet riêng tư với EXTERNSHEET riêng của nó, không có sửa chữa tương đương cấp cây nào khả dụng — ngay khi lượt duyệt tái ràng buộc gặp một tham chiếu tên ở bất cứ đâu trong cây, nó từ bỏ toàn bộ công thức thay vì viết ra một công thức chỉ đúng một phần. Ngay cả khi việc tái ràng buộc thành công, ô đích không hiển thị một con số vừa được tính lại; nó hiển thị giá trị mà ô nguồn đã mang tại thời điểm copy, giữ trong một slot cache theo đúng cách chính Excel cache giá trị-đã-biết-cuối-cùng của bất kỳ tham chiếu ngoài nào cho đến khi bạn chủ động làm mới liên kết, đó là mặc định đúng đắn, vì việc tính lại xuyên một liên kết sống vào một file khác chính xác là loại thao tác bạn muốn kích hoạt một lần, có chủ đích, thay vì mỗi lần mở

Thiết kế này khiến bạn phải trả giá gì

Cỗ máy giải-biên-dịch-và-biên-dịch-lại của AddCopy không miễn phí, và chi phí đó đáng để lên kế hoạch trước khi bạn viết script cho một job hợp nhất lớn thay vì sau đó. Copy một sheet trong cùng workbook đi theo đường rẻ, một phép nhân bản trong bộ nhớ trực tiếp của cây đã biên dịch, vì mỗi chỉ số bên trong nó đã hợp lệ trong workbook nó đang ở lại; một lượt copy xuyên workbook trả giá cho một lượt phân tích thực sự trên mỗi ô công thức thay vào đó, giải biên dịch thành văn bản rồi biên dịch văn bản đó lại từ đầu, và trong khi khác biệt không đáng đo trên một sheet với vài chục công thức, một workbook nguồn với hàng chục nghìn ô công thức, được copy như một sheet trong số hàng chục trong một job hàng loạt, nên kỳ vọng việc biên dịch lại sẽ chiếm phần lớn thời gian chạy hơn là I/O file xung quanh nó. Thứ tự copy quan trọng vì một lý do thứ hai ngoài tốc độ: một công thức tham chiếu một sheet mà AddCopy chưa đến trong lô này thất bại việc biên dịch lại vì cùng lý do một công thức tham chiếu một sheet thực sự không tồn tại thất bại, nên một job copy sheet B trước sheet A mà công thức của nó phụ thuộc vào sẽ thấy công thức đó suy giảm đúng như mô tả ở trên, văn bản chuỗi hoặc một fallback liên kết ngoài trỏ thẳng trở lại file nguồn nó vừa đến từ đó. Và vì mỗi workbook nguồn trong một lô hợp nhất thường được viết độc lập, đáng để test tường minh cho chế độ lỗi duy nhất mà không file nguồn đơn lẻ nào có thể từng cảnh báo bạn — năm workbook chi nhánh mỗi cái tổng hợp con số của một chi nhánh ngang hàng có thể kết hợp thành một tham chiếu vòng đích thực bên trong workbook tổng hợp mà không file nguồn riêng lẻ nào từng chứa một tham chiếu vòng nào, một chu trình chỉ tồn tại một khi mỗi sheet đã đáp xuống cùng một nơi và việc tính lại chạy trên tập hợp đã kết hợp

Việc copy worksheet xuyên workbook đi kèm như hành vi tiêu chuẩn của AddCopy trong HotXLS Delphi Excel Component dành cho Delphi và C++Builder; trang sản phẩm mang đầy đủ tài liệu tham khảo API worksheet và workbook, bao gồm hành vi biểu đồ, rich text, và tham chiếu ngoài được mô tả ở đây