Duyệt qua các ô của một template báo cáo vừa mở lên và một tiêu đề đã merge hành xử như một rò rỉ. Bạn đọc A1 và nhận được "Quarterly Statement"; bạn đọc từ B1 tới F1, những ô nhìn thấy được nằm dưới cùng một dải băng đó, và bạn nhận được không gì cả. Ghi một giá trị vào C1 để vá lại tiêu đề và nó không bao giờ xuất hiện trên màn hình. Lưới không hề mất dữ liệu của bạn. Nó đang làm chính xác điều một phép merge có nghĩa: trong cả XLS và XLSX, một hình chữ nhật đã merge render nội dung của một ô, ô neo trên-cùng-bên-trái, và coi phần còn lại như không gian bị che phủ vẫn giữ giá trị nhưng không bao giờ hiển thị chúng. Người dùng Excel hấp thụ điều này qua thử và sai. Một trình tạo báo cáo phải mã hóa nó thành một quy tắc, bởi vì trong mã được tạo tự động, triệu chứng là một vùng trống không có exception nào để truy vết. HotXLS, một thư viện Object Pascal thuần gốc đọc và ghi cả hai định dạng Excel từ Delphi và C++Builder, lộ ra bảng merge đủ tường minh để bạn có thể lập trình theo đúng quy tắc thay vì tự khám phá lại nó trong một ticket hỗ trợ
Một giá trị, một điểm neo
Một phép merge là một chỉ thị hiển thị được phủ lên trên một lưới không thay đổi hình dạng. Mỗi ô bị che phủ vẫn tồn tại trong file như một slot riêng của nó; bản ghi merge chỉ bảo bên tiêu thụ tô nội dung của điểm neo trải khắp hình chữ nhật đó. Sự phân biệt đó dẫn tới ba hành vi đáng được thấm nhuần trước khi bạn viết bất kỳ mã layout nào. Đọc một ô bị che phủ trả về giá trị đã lưu của chính nó, mà với một banner bạn dựng thường là trống, nên bất kỳ mã nào kiểm tra một tiêu đề đã merge đều phải giải quyết và đọc điểm neo. Ghi vào một ô bị che phủ thành công ở cấp file và không hiện ra ở đâu cả, đó chính là cái bẫy tiêu-đề-vô-hình từ phần mở đầu. Và bỏ merge một vùng sẽ để lộ ra bất cứ thứ gì đã nằm dưới đó suốt thời gian qua, nên một giá trị lạc lối được ghi vào không gian bị che phủ sẽ biến thành một lỗi nhìn thấy được vào ngày ai đó gỡ merge
Ở phía XLSX, bảng đó là một đối tượng hạng nhất. Sheet.MergedCells mang theo Add('A1:C1'), FindAt(Row, Col), DeleteAt, và Items, và lệnh gọi bạn dùng nhiều nhất là FindAt: đưa cho nó bất kỳ tọa độ nào và nó trả về vùng đã merge bao phủ ô đó, hoặc nil khi ô đứng độc lập. Lượt tra cứu duy nhất đó là nền tảng cho cả hai nửa của việc xử lý merge đúng đắn, phần đọc an toàn và phần chắn khi ghi, và cả hai sẽ xuất hiện sau
Hai lớp giao diện, hai kiểu thành ngữ merge
HotXLS giữ engine BIFF8 .xls cổ điển và engine OOXML .xlsx như hai mô hình đối tượng tách biệt, và chúng diễn đạt việc merge khác nhau bởi vì chúng kế thừa từ những quy ước khác nhau. Lớp giao diện XLS theo thành ngữ Excel COM: bạn lấy một range từ một thuộc tính đánh chỉ số hai tham số và gọi Merge với một OleVariant mà giá trị của nó quyết định hình học bạn thu được cuối cùng
var
Book: IXLSWorkbook; // đếm tham chiếu qua interface: không tự Free
Sh: IXLSWorksheet;
begin
Book := TXLSWorkbook.Create;
Sh := Book.Sheets[1]; // collection sheet của XLS đánh số từ 1
Sh.Range['A1', 'F1'].Merge(False); // False = một khối merge duy nhất
Sh.Cells.Item[1, 1].Value := 'Quarterly Statement';
Sh.Range['A3', 'F4'].Merge(True); // True = merge across: một merge cho mỗi hàng
Book.SaveAs('layout.xls');
end;Tham số của Merge chính là phần người ta hay làm sai. Trên một range hai hàng, Merge(True) tạo ra hai merge một-hàng độc lập, đó là "Merge Across" của Excel và chính xác là điều bạn muốn cho một dải tiêu đề xếp chồng cần giữ các hàng của nó tách biệt được. Merge(False) hợp nhất toàn bộ hình chữ nhật thành một khối duy nhất. Range đó cũng báo cáo MergeCells như một cờ trạng thái, trả về vùng chứa nó thông qua MergeArea, và tự gỡ merge bằng Unmerge. Lớp giao diện XLSX lộ ra cùng những thao tác đó dưới các tên khác: Sheet.MergeCells(Row1, Col1, Row2, Col2) nhận các cận số nguyên, TXLSXRange.Merge chấp nhận biến thể Across tương đương, và collection MergedCells giữ kết quả
Một template lớn dần cùng với dữ liệu của nó
Một template báo cáo thực sự không phải một lưới cố định. Tiêu đề và tổng số là cố định, nhưng phần chi tiết ở giữa chúng co giãn theo bất cứ gì query trả về. Khuôn mẫu tỏ ra hiệu quả giữ một hàng chi tiết đã style đầy đủ trong template, nhân bản nó một lần cho mỗi bản ghi, rồi mở ra một khoảng trống phía trước khối tổng số để mọi thứ được neo bên dưới trượt xuống mà không mất định dạng của nó
Sheet.Range['A1:F1'].Merge;
Sheet.Cells[1, 1].Value := 'INVOICE #2026-0611'; // giá trị đi vào điểm neo, A1
Sheet.RowHeight[1] := 28;
TitleFont := Book.Fonts.Add('Calibri', 16, True, False);
Sheet.Cells[1, 1].FontIndex := TitleFont + 1; // chỉ số pool đánh số từ 0, phía ô đánh số từ 1
// hàng 5 là dòng template chi tiết đã style
for I := 0 to ItemCount - 1 do
Sheet.CopyRange(5, 1, 5, 6, 6 + I, 1); // style và công thức đi theo cùng nó
// mở một khoảng trống phía trên khối tổng số; nội dung bên dưới dịch xuống
Sheet.InsertRows(6 + ItemCount, 1);
Sheet.Range['A1:F1'].SetBorders(xlsxEdgeOutline, xlsxBorderMedium);Hai dòng đáng nhìn lại lần thứ hai. Phép gán font mang một lỗi lệch-một cắn âm thầm: Fonts.Add trả về một vị trí pool đánh số từ 0, trong khi một ô lưu một tham chiếu font đánh số từ 1 mà 0 có nghĩa là font mặc định, nên bỏ sót + 1 không ném ra gì cả, nó chỉ đơn giản là style tiêu đề của bạn sai kiểu chữ. Dòng còn lại là CopyRange, di chuyển định dạng và công thức cùng với giá trị. Đó là toàn bộ lý do để nhân bản một hàng template dựng tay thay vì tái tạo vẻ ngoài của nó bằng mã. Một nhà thiết kế sở hữu giao diện hiển thị một lần duy nhất, trong template; trình tạo chỉ có việc đổ dữ liệu vào các bản sao của nó
Sự tách bạch đó còn mở rộng thêm khi layout tái sử dụng được sống trong workbook riêng của nó, chẳng hạn một sheet chứa các dải header và footer dùng chung cho nhiều báo cáo. CopyRangeTo thực hiện cùng phép nhân bản đó xuyên qua ranh giới worksheet, nhận một sheet đích cộng thêm tọa độ đích, nên một trình tạo có thể giữ một sheet template nguyên vẹn và đóng dấu các vùng của nó vào bao nhiêu sheet kết quả tùy theo nhu cầu của công việc. Phương án thay thế, chỉnh sửa template tại chỗ rồi cố khôi phục nó sau đó, là kiểu việc hoạt động tốt cho tới ngày một lượt chạy dừng lại giữa chừng
Những gì InsertRows di chuyển, và những gì nó không di chuyển
Khuôn mẫu template-lớn-dần chỉ hoạt động được bởi vì InsertRows của XLSX là một chỉnh sửa cấu trúc chứ không phải một phép xáo trộn ô. Khi nó mở ra một khoảng trống, nó di dời các vùng đã merge, độ cao hàng, hyperlink, comment, frozen pane, range autofilter, conditional format, data validation, table, defined name, neo hình ảnh, và neo biểu đồ nằm dưới điểm chèn, chứ không chỉ giá trị ô. Đó chính là điều cho phép khối tổng số đến được hàng mới của nó với các merge và định dạng số vẫn nguyên vẹn thay vì đến nơi mà bị tước sạch
Hai giới hạn đã được tài liệu hóa của nó là những thứ cần thiết kế xoay quanh. Việc điều chỉnh công thức chỉ giới hạn trong sheet đang được chỉnh sửa: các tham chiếu bên trong sheet đó được viết lại, và một công thức trên một sheet khác trỏ vào vùng đã dịch chuyển cũng được viết lại, nhưng việc điều chỉnh chỉ theo sát những tham chiếu nhắm vào sheet đang chỉnh sửa, nên bất kỳ lược đồ tham chiếu liên workbook nào cũng xứng đáng có một cuộc kiểm tra riêng thay vì tin tưởng mù quáng. Giới hạn thứ hai sắc bén hơn, và nó nằm ở phía XLS. Pivot table sống sót qua các chu kỳ mở-lưu như những bản ghi thô được bảo tồn, chứ không phải như những đối tượng đã mô hình hóa mà HotXLS có thể di chuyển, nên việc chèn hàng không di dời dấu chân của một pivot. Bất kỳ template nào bạn dựng cho định dạng .xls nên đặt các vùng pivot của nó cách xa hẳn bất kỳ dải nào sẽ lớn dần lên
Từ chối ghi dữ liệu vào không gian layout
Lỗi merged-cell thực sự tới được production không phải là lỗi mang tính thẩm mỹ. Nó mang tính cấu trúc: một hàng chi tiết trôi dạt vào một dải layout đã merge, giá trị của nó rơi vào các ô bị che phủ và biến thành vô hình, và các tổng cột âm thầm ngừng khớp với những gì bất kỳ ai đọc sheet có thể thấy. Bởi vì FindAt trả lời câu hỏi về vùng che phủ cho bất kỳ tọa độ nào, trình tạo có thể từ chối phép ghi đó ngay khoảnh khắc nó sắp xảy ra thay vì giao đi một báo cáo âm thầm đếm thiếu
// từ chối ghi dữ liệu chi tiết vào một vùng layout đã merge
if Sheet.MergedCells.FindAt(Row, 1) <> nil then
raise Exception.CreateFmt('row %d overlaps a merged layout region', [Row]);
Sheet.Cells[Row, 1].Value := Detail.Description;Cùng phép kiểm tra ranh giới đó thuộc về bất cứ nơi nào người dùng sau này sẽ sắp xếp hoặc lọc kết quả xuất ra. Một range có merge bên trong không thể sắp xếp gọn gàng, bởi vì thao tác sort di chuyển các hàng độc lập với nhau và một merge trải qua nhiều hàng không có một hàng duy nhất để đi theo; Excel phản hồi bằng một lỗi hoặc một layout bị xáo trộn. Kỷ luật giữ cho báo cáo đúng đắn mang tính địa lý. Hãy giới hạn merge trong các dải tiêu đề, các đường phân chia phần, và các khối chữ ký, và giữ phần giữa dạng bảng của sheet phẳng. Bài viết về tạo báo cáo từ template phát triển sự tách bạch layout-đối-dữ liệu này thành một workflow đầy đủ dẫn dắt bởi placeholder, và bài viết về conditional formatting và rich text nói về việc style dải dữ liệu phẳng đó
Merge suy giảm ra sao trên đường xuất ra
Một merge là một khái niệm của workbook, và mỗi định dạng xuất hướng-văn-bản tôn trọng nó ở một mức độ khác nhau. Biết trước ba hành vi này tiết kiệm được một chu kỳ QA. Xuất HTML tái tạo merge trung thực, phát ra colspan và rowspan trên một bảng duy nhất, nên một báo cáo hướng trình duyệt giữ được vẻ ngoài dải băng của nó. Xuất RTF hoàn toàn không span cột: văn bản neo rơi vào ô riêng của nó và phần độ rộng còn lại của merge xuất ra dưới dạng các ô trống, điều này khiến một tiêu đề rộng trông như bị đẩy dạt sang trái trong một trình xử lý văn bản. CSV không có khái niệm về merge, nên giá trị neo chiếm một trường và mọi ô bị che phủ xuất ra dưới dạng một trường trống. Bài học rút ra cho một workbook cũng nuôi các bản xuất có dấu phân cách là giữ bất cứ thứ gì mang tính chịu lực ra khỏi hình học đã merge; bài viết về xuất CSV, TSV, và HTML trình bày chi tiết từng định dạng
Có một điều trấn an cho bất kỳ ai đang cân nhắc điều này so với kích thước file: merge tốn gần như không gì ở quy mô báo cáo. Bảng merge nhỏ xíu so với dữ liệu ô, và đọc một ô bị che phủ vẫn đi qua FindAt chứ không phải quét toàn bộ. Áp lực hiệu năng trên các workbook lớn đến từ nơi khác, chủ yếu là sự phình to của style pool và bộ nhớ mà đường lưu giữ lại, điều mà bài viết về hiệu năng workbook lớn bàn tới trực tiếp. Cả hai API merge, các thao tác chỉnh sửa cấu trúc, và các demo template đều đi kèm trong HotXLS Delphi Component