HotXLS, thành phần Excel cho Delphi và C++Builder, tự động tách một quy tắc định dạng có điều kiện hoặc xác thực dữ liệu thành hai hay nhiều đối tượng quy tắc riêng biệt bất cứ khi nào việc chèn hoặc xóa hàng/cột cắt phạm vi bao phủ của quy tắc thành các mảnh cần các điểm neo công thức tương đối khác nhau, sau đó gán lại cho mỗi quy tắc định dạng có điều kiện một số ưu tiên mới, duy nhất. Hành vi này đã có từ phiên bản 2.196 của engine XLSX và chạy tự động, không có thiết lập nào để tắt đi. Điều kiện kích hoạt hẹp nhưng phổ biến: một quy tắc cellIs hoặc expression có công thức đọc một ô tương đối so với phạm vi của chính nó, nằm trên một worksheet mà sau này có một hàng được chèn hoặc xóa ở đâu đó giữa đúng phạm vi đó
Hầu hết các bài viết về tự động hóa Excel dừng lại ở vấn đề văn bản công thức: dịch các số hàng và cột bên trong mỗi SUM() và mỗi VLOOKUP() để các tham chiếu vẫn trỏ đúng ô. Nửa câu chuyện đó là thật, và được nói đến trong bài viết đồng hành về cách HotXLS viết lại tham chiếu công thức khi hàng và cột dịch chuyển, nhưng một định dạng có điều kiện hay một quy tắc xác thực dữ liệu không chỉ là một công thức nằm trong một ô. Nó ghép một công thức với một phạm vi, sqref theo thuật ngữ ECMA-376, và cả hai phải di chuyển cùng nhau. Khi một chỉnh sửa cấu trúc cắt phạm vi đó thành hai mảnh cần hai độ lệch tương đối khác nhau để giữ đúng, việc giữ một đối tượng quy tắc với một chuỗi công thức không còn là một lựa chọn nữa, và giả vờ ngược lại là cách một quy tắc highlight âm thầm bắt đầu so sánh sai hàng
Vì sao chèn một hàng lại tách một quy tắc định dạng có điều kiện thay vì chỉ dịch chuyển nó?
Một quy tắc định dạng có điều kiện hay xác thực dữ liệu giữ đúng một công thức cho toàn bộ phạm vi của nó, được đánh giá tương đối so với một ô neo duy nhất, nên một khi một chỉnh sửa buộc hai phần của phạm vi đó cần hai độ lệch tương đối khác nhau, một công thức không thể mô tả đúng cả hai phần nữa. ECMA-376 biểu diễn phạm vi bao phủ của một quy tắc bằng thuộc tính sqref trên phần tử conditionalFormatting hoặc dataValidation, và Excel đánh giá Formula1 và Formula2 như thể văn bản đó đã được gõ vào ô trên-cùng-bên-trái của sqref đó rồi điền xuống phần còn lại của nó, giống cách một công thức tương đối thông thường điền xuống một cột. Hãy hình dung một quy tắc highlight chênh lệch trên B2:B50 đánh dấu bất kỳ con số thực tế nào vượt ngân sách của nó, được xây dựng như một quy tắc cellIs có Formula1 là văn bản chữ nghĩa C2, nghĩa là so sánh ô B của hàng hiện tại với ô C của cùng hàng đó
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Chèn hàng ngăn cách đó tại hàng cũ 25 và các hàng phía trên điểm chèn không di chuyển, nên phần quy tắc của chúng vẫn đọc Formula1 là C2 đúng đắn. Các hàng từng là 25 đến 50 trượt xuống thành 26 đến 51, và với chúng, C2 giờ hoàn toàn là ô sai, vì hàng 26 cần so sánh với C26, không phải với một con số ngân sách nằm hai mươi mấy hàng phía trên nó
HotXLS quyết định khi nào một quy tắc cần tách như thế nào
HotXLS chỉ tạo thêm đối tượng quy tắc khi hình học thực sự đòi hỏi điều đó: một hàm nội bộ, XlsxBuildShiftedRuleParts, duyệt qua từng vùng rời rạc trong sqref của quy tắc, tính ra ô neo của vùng đó trước chỉnh sửa là gì và trở thành gì sau đó, và kiểm tra xem mọi mảnh kết quả có cần cùng một mức hiệu chỉnh độ lệch tương đối hay không. Nếu mọi mảnh đồng thuận, một quy tắc sống sót, sqref của nó được dựng lại thành hợp của các mảnh đã dịch chuyển và công thức của nó được tính lại nền một lần. Một lần tách thực sự chỉ xảy ra khi các mảnh không đồng thuận, chính xác là trường hợp B2:B50 ở trên, nơi khối trên giữ nguyên neo gốc và khối dưới cần một neo mới
Việc tính lại nền công thức của một mảnh là một thao tác hai bước tái sử dụng cỗ máy mà HotXLS đã sẵn có cho các nhóm công thức chia sẻ OOXML: trước tiên công thức được dịch như thể nó ban đầu đã được neo tại ô trên-cùng-bên-trái riêng của mảnh đó, dùng cùng phép toán độ lệch tương đối mở rộng một công thức chia sẻ trên toàn phạm vi của nó, sau đó kết quả chạy qua cùng bộ quét dịch chuyển hàng-và-cột viết lại các công thức worksheet thông thường. Đó là cách Formula1 đi từ C2 sang C26 qua hai bước thay vì một trường hợp đặc biệt viết tay: dịch C2 tới trước 23 hàng để có C25, như thể quy tắc luôn bắt đầu ở đó, sau đó để lần dịch chuyển thông thường tại hàng 25 đẩy nó tiếp lên C26. Mọi thuộc tính khác, màu tô, dừng-nếu-đúng, chính toán tử, đều đi theo không đổi vào đối tượng quy tắc mới, nên cả hai nửa vẫn tô các ô đúng màu như trước giờ vẫn vậy
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Thanh dữ liệu và bộ biểu tượng có tách theo cùng cách với quy tắc cellIs không?
Không: HotXLS chỉ phân tách các loại quy tắc mà tính đúng đắn của chúng thực sự phụ thuộc vào một công thức tương đối theo từng vùng, các phép so sánh cellIs và quy tắc expression, và để mọi loại định dạng có điều kiện khác là một đối tượng quy tắc duy nhất mà sqref của nó đơn giản lớn lên để bao phủ các mảnh đã dịch chuyển như một hợp nhiều vùng. Nội bộ, nhánh rẽ này chỉ là một kiểm tra Kind đơn giản, cf.Kind in [cfkCellIs, cfkExpression], không có gì lạ hơn thế. Thanh dữ liệu, thang màu hai và ba màu, bộ biểu tượng, xếp hạng trên và dưới, và các bộ dò trùng lặp, ô trống, và lỗi mang theo một payload, một màu thanh, một tập điểm dừng thang màu, một họ biểu tượng, mô tả toàn bộ phạm vi bao phủ cùng lúc thay vì một phép so sánh tương đối theo từng ô, nên việc tách chúng thành nhiều đối tượng quy tắc có mức ưu tiên sẽ không mang lại tính đúng đắn nào và chỉ thêm quy tắc phải quản lý. Khi một chỉnh sửa chia phạm vi của chúng, HotXLS gộp các mảnh lại thành một quy tắc với sqref nhiều vùng và neo lại payload như một đơn vị duy nhất thay vì nhân bản một đối tượng quy tắc mới cho mỗi mảnh. Sự phân biệt này khớp với phân loại loại quy tắc trong bài viết cơ bản về định dạng có điều kiện và rich text: thanh dữ liệu, thang màu, và bộ biểu tượng đã tách biệt khỏi các quy tắc cellIs bằng cách hoàn toàn bỏ qua thuộc tính Style, và giờ hóa ra chúng cũng tách biệt khỏi việc neo lại theo từng vùng vì cùng lý do nền tảng đó
Vì sao mức ưu tiên của quy tắc thay đổi sau một chỉnh sửa cấu trúc?
Mức ưu tiên thay đổi vì mỗi bản sao bắt đầu với đúng giá trị ưu tiên của quy tắc mà nó tách ra từ đó, và HotXLS chạy một lượt chuẩn hóa sau đó để giải quyết các trùng lặp thành một thứ tự sạch sẽ, không có khoảng trống thay vì để hai quy tắc ngang hàng cùng một hạng. Một hàm nội bộ thứ hai, XlsxNormalizeConditionalFormatPriorities, lấy mức ưu tiên hiện tại của mỗi định dạng có điều kiện, rơi về vị trí của quy tắc đó trong tập hợp cho bất kỳ quy tắc nào chưa từng được đặt tường minh, sắp xếp toàn bộ danh sách theo kiểu ổn định để các quy tắc ngang hàng giữ thứ tự tương đối gốc, và đánh số lại kết quả đã sắp xếp thành một chuỗi liền mạch 1, 2, 3 không có khoảng trống và không lặp lại. HotXLS chạy nó một lần trước khi một lượt dịch chuyển bắt đầu, để việc nhân bản bắt đầu từ một nền sạch, và lại chạy sau mỗi lần tách và mỗi quy tắc bị rỗng bị loại bỏ, nên file được lưu không bao giờ có hai mục quy tắc cùng đòi cùng một mức ưu tiên. Điều này quan trọng nếu bạn đã làm theo lời khuyên trong bài viết cơ bản về định dạng có điều kiện để chừa khoảng trống giữa các giá trị ưu tiên để một quy tắc sau này có thể chen vào mà không cần đánh số lại phần còn lại: các khoảng trống đó tồn tại cho đến lần chỉnh sửa hàng hoặc cột tiếp theo chạm vào worksheet đó, rồi sụp lại, vì việc chuẩn hóa chỉ đảm bảo tính duy nhất và thứ tự ổn định, không đảm bảo lược đồ đánh số gốc của bạn quay lại không đổi
Quy tắc xác thực dữ liệu cũng tách, nhưng không có mức ưu tiên để đánh số lại
Các quy tắc xác thực dữ liệu đi qua cùng logic phân tách phạm vi như các định dạng có điều kiện cellIs và expression, và khác với định dạng có điều kiện, mọi loại xác thực đều đi qua đường xử lý đó một cách đồng nhất: HotXLS không có một họ phi-công-thức riêng biệt cho xác thực dữ liệu theo cách thanh dữ liệu và bộ biểu tượng có cho định dạng có điều kiện, nên một quy tắc danh sách hay số nguyên thông thường được phân tách bởi đúng hàm xử lý một công thức tùy chỉnh tương đối. Điều khác biệt là mức ưu tiên: ECMA-376 hoàn toàn không cho phần tử dataValidation thuộc tính priority nào, nên không có bước đánh số lại cho xác thực theo cách có cho định dạng có điều kiện. Hãy hình dung một xác thực công thức tùy chỉnh giữ số tiền thực tế của mỗi hàng không vượt quá ngân sách riêng của nó trong cột bên cạnh
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Điều này quan trọng vì cùng lý do bài viết cơ bản về xác thực dữ liệu cảnh báo không nên gắn một quy tắc trước khi số lượng hàng đã cố định: một xác thực chỉ bao phủ đúng các ô chữ nghĩa bạn đã cho nó, và một chỉnh sửa cấu trúc sau này có thể để lại hai hay nhiều quy tắc làm công việc mà một quy tắc từng làm. Không có gì hỏng về mặt chức năng: mỗi ô trong phạm vi gốc vẫn được xác thực bởi thứ gì đó, nhưng code giả định một mục DataValidations cho mỗi cột sẽ bắt đầu đánh chỉ số sai sau lần chỉnh sửa đầu tiên chạm vào nó. Có một trần cứng cho việc này có thể đi xa đến đâu: nếu việc tách sẽ đẩy một worksheet vượt quá 65.534 quy tắc xác thực dữ liệu, HotXLS ném ra một ngoại lệ thay vì ghi ra một file mà Excel sẽ âm thầm từ chối, đây là thư viện từ chối tạo ra một workbook hỏng thay vì một giới hạn mà việc sử dụng thông thường khó có thể chạm tới
Cần kiểm tra gì sau một lượt chèn hoặc xóa hàng loạt
Hai điều đáng kiểm tra sau khi một script chạy một loạt chỉnh sửa hàng hoặc cột trên một sheet đầy định dạng có điều kiện và xác thực là tổng số quy tắc và thứ tự ưu tiên, vì cả hai đều có thể trôi dạt theo những cách dễ bị bỏ sót trong code review và rõ ràng ngay khi ai đó mở Manage Rules trong Excel. Một chỉnh sửa hiếm khi gây nhiều thiệt hại: một lần chèn duy nhất giữa một quy tắc cellIs tạo ra nhiều nhất hai đối tượng quy tắc từ một quy tắc. Rủi ro tăng lên khi một quy trình sinh báo cáo chèn từng hàng một trong một vòng lặp trên một sheet đã mang sẵn nhiều quy tắc neo công thức: mỗi lượt có thể tách lại các quy tắc mà một lượt trước đã tách, và năm quy tắc cellIs gốc có thể kết thúc thành nhiều lần con số đó dưới dạng các mảnh giá trị thấp bao phủ những dải nhỏ của phạm vi gốc. Việc gộp các chỉnh sửa cấu trúc thành lô, chèn toàn bộ khối mới trong một lệnh gọi thay vì từng hàng một, giữ số lượng quy tắc gắn liền với số lượng neo thực sự khác biệt thay vì số lượng chỉnh sửa đã thực hiện
Việc phân tách quy tắc và chuẩn hóa mức ưu tiên đi kèm như hành vi tiêu chuẩn của engine XLSX 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 chỉnh sửa worksheet, bao gồm các phương thức định dạng có điều kiện và xác thực dữ liệu được mô tả ở đây