Bài viết kỹ thuật: HotXLS Conditional Formatting and Rich Text in Delphi bằng tiếng Việt

Một quy tắc conditional formatting trong OOXML thực chất là hai thứ tách biệt khoác chung một cái tên. Điều kiện (một phép so sánh, một công thức, một khớp văn bản) quyết định ô nào đủ điều kiện. Giao diện hiển thị (một bản ghi định dạng vi sai, dxf theo thuật ngữ ECMA-376) quyết định các ô đó trông ra sao. Hộp thoại của Excel che giấu đường nối này bằng cách bắt bạn điền cả hai cùng lúc. HotXLS thì không. Tạo một quy tắc cellIs từ Delphi và bỏ qua style, quy tắc vẫn hợp lệ, vùng vẫn đúng, công thức vẫn cho ra true đúng trên những ô cần thiết, và không có gì đổi màu, bởi vì chỉ thị của quy tắc đó là "true, không tô gì cả". Khoảng cách giữa điều kiện và hệ quả đó là điều đầu tiên cần làm đúng, và nó chiếm phần lớn các trường hợp quy tắc trông có vẻ đúng trong Manage Rules nhưng lại chẳng highlight gì cả

HotXLS ghi conditional formatting một cách gốc vào cả file BIFF8 .xls lẫn OOXML .xlsx, và làm điều tương tự cho các run rich text và một mô hình cell-style dùng chung pool. Ba tính năng này chia sẻ với nhau nhiều dây nối hơn những gì bề mặt API phẳng gợi ý, và những chỗ mà kết quả xuất ra lệch khỏi ý định thường chính là các mối nối giữa chúng

Một điều kiện cần một hệ quả: style dxf

Trên worksheet XLSX, các quy tắc so sánh đến từ AddConditionalFormat, hàm này nhận một range, một operator từ TXLSXCfOperator, và một công thức hoặc một giá trị literal, rồi trả về chỉ số của quy tắc mới trong collection ConditionalFormats của sheet. Đối tượng quy tắc tại chỉ số đó lộ ra một thuộc tính Style, và đó chính là nơi phần highlight tồn tại. Gán một fill vào đó và các ô đủ điều kiện sẽ nhận fill đó. Bỏ qua không đụng vào nó và bạn đã tạo ra quy tắc vô hình được mô tả ở trên

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // Biến động âm: tô nền đỏ nhạt
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Order ID trùng lặp cũng được gắn cờ theo cách tương tự
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Quy tắc công thức tùy chỉnh: highlight các hàng mà giá trị thực tế không đạt 90% mục tiêu
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

Màu ở đây là các giá trị ARGB 32-bit, nên $FFFFC7CE chính là màu "light red" của Excel mà bạn biết từ hộp thoại, với một byte alpha hoàn toàn mờ đục đứng trước phần RGB. Mọi loại quy tắc kích hoạt theo điều kiện từng ô đều theo cùng một khuôn mẫu tạo-rồi-style. Các bộ khớp văn bản (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) trả về một chỉ số để bạn style sau đó, và AddCondFormatTop10, AddCondFormatAboveAverage, cùng các bộ dò ô trống và lỗi cũng vậy. Học khuôn mẫu này một lần và cả họ hàng văn bản-và-so sánh sẽ hoạt động giống hệt nhau

Data bar, color scale, và icon set tự tô màu cho chính mình

Các loại quy tắc trực quan hoạt động theo chiều ngược lại. Chúng mang giao diện hiển thị ngay bên trong định nghĩa quy tắc và bỏ qua hoàn toàn thuộc tính Style. Gán một fill vào một quy tắc data bar và không có gì xảy ra, điều này đọc lên giống như một bug cho tới khi cách phân loại trở nên rõ ràng: AddCondFormatDataBar nhận màu thanh bar như một tham số trực tiếp, các color scale hai điểm và ba điểm cũng nhận màu các điểm cuối theo cách tương tự, và AddCondFormatIconSet chọn một trong 26 kiểu icon set như icsTrafficLights3. Không có bản ghi style riêng nào để quên ở đây, bởi vì hoàn toàn không tồn tại bản ghi style riêng nào cả

Các tham số đáng để suy nghĩ kỹ trên những lệnh gọi này là các điểm neo giá trị, có kiểu TXLSCfValueKind. Một điểm cuối của bar hay scale có thể đặt tại giá trị nhỏ nhất hoặc lớn nhất của range, tại một con số literal, tại một phần trăm hay một percentile, hoặc tại kết quả của một công thức. Các giá trị mặc định, min-of-range và max-of-range, hoạt động tốt trên dữ liệu demo gọn gàng rồi phản bội bạn trên dữ liệu thực có outlier: một giá trị vọt quá cao kéo giãn cả scale và làm phẳng mọi bar khác xuống thành một mẩu nhỏ xíu. Khi một dashboard cần được đọc xuyên suốt nhiều kỳ, hãy neo các điểm cuối vào những con số cố định hoặc percentile thay vì để mặc định, để nửa bar trong tháng Ba mang cùng ý nghĩa số lượng như nửa bar trong tháng Tư. Một bar tự động co giãn theo scale chỉ có thể so sánh với chính nó mà thôi

Trình ghi XLS chỉ bao phủ bốn loại quy tắc, không hơn

Phía BIFF8 kế thừa từ hệ thống cũ không phải là một bản sao thu nhỏ của phía XLSX; đó là một tập con có chủ đích. Lớp giao diện XLS chỉ có thể tạo đúng bốn hình dạng quy tắc điều kiện: data bar, color scale hai màu, color scale ba màu, và icon set, được xuất ra dưới dạng bản ghi CF12 vào luồng dữ liệu. Nó không có API tạo cho các quy tắc cellIs, expression, hay text. Các quy tắc thuộc những loại đó đã tồn tại sẵn trong một file bạn mở ra thì được đọc, giữ nguyên, và ghi lại không đổi, nên việc mở rồi lưu lại một file .xls của khách hàng không bao giờ làm hỏng định dạng đã có trong đó. Điều bạn không thể làm là tạo mới highlight theo ngưỡng từ đầu vào một file .xls. Các lựa chọn ở đây là giả lập bằng những fill ô thông thường được tính toán trong mã, hoặc biến sản phẩm đầu ra thành .xlsx, nơi cả họ quy tắc đầy đủ đều sẵn có

Đây là một ràng buộc cần chốt trước khi lớp dữ liệu tồn tại, chứ không phải sau đó, bởi vì nó thay đổi quyết định về định dạng file cho bất kỳ thứ gì có hình dạng dashboard. Một nhóm chọn .xls vì tính tương thích rồi sau đó viết spec cho một báo cáo KPI với ngưỡng cellIs đã chọn hai thứ không khớp với nhau, và thời điểm rẻ hơn để nhận ra điều này là ngay lúc quyết định định dạng chứ không phải ba tuần sau khi đã xây dựng xong

Xếp chồng quy tắc, priority, và các range chồng lấn

Các dashboard thực tế hiếm khi chỉ chạy một quy tắc cho mỗi range. Một cột biến động có thể mang một data bar thể hiện độ lớn, một quy tắc cellIs cho ngưỡng cứng, và một quy tắc expression ở cấp hàng nằm trên cả hai để xử lý các trường hợp leo thang. Mỗi TXLSXConditionalFormat lộ ra một giá trị Priority, và Excel giải quyết các quy tắc cạnh tranh nhau theo thứ tự priority. Khi hai quy tắc cùng muốn tô một ô, người thắng được quyết định bởi một con số bạn đặt ra, chứ không phải bởi thứ tự mà một người review tình cờ cuộn qua trong hộp thoại Manage Rules

Hãy đối xử với priority theo cách một phần mềm vẽ đối xử với z-order. Gán nó một cách có chủ đích ở bất cứ đâu hai quy tắc có thể chạm tới cùng những ô, và để lại khoảng trống giữa các giá trị để một quy tắc thêm sau có thể chèn vào mà không phải đánh số lại toàn bộ phần còn lại. Ở những nơi các quy tắc không thể va chạm nhau, chẳng hạn một data bar chỉ giới hạn trong cột E và một quy tắc text chỉ giới hạn trong cột G, thứ tự tạo là đủ tốt rồi và priority không đáng để bận tâm. Hãy dồn sự chú ý đó vào ranh giới của range thay vào đó, bởi vì những bug tốn kém ở đây gần như không bao giờ là lỗi đảo priority. Chúng là những range như B2:B200 trên một báo cáo đã phình ra thành 350 hàng, nơi phần đuôi không được bao phủ hiển thị như những ô bình thường trông y hệt dữ liệu lành mạnh. Hãy suy ra mọi range của quy tắc từ đúng giá trị số-hàng-cuối-cùng vốn cũng chi phối chuỗi biểu đồ và các range validation ở nơi khác trong workbook, và phần đuôi sẽ không còn bị rơi rớt nữa

Có một thói quen kiểm chứng xứng đáng được duy trì. Sau khi tạo, hãy mở file trong Excel, chọn range đã định dạng, và duyệt qua Manage Rules một lần cho mỗi thay đổi template. Conditional formatting là một trong số ít lĩnh vực mà trình render có thẩm quyền duy nhất chính là ứng dụng tiêu thụ file đó, nên một unit test chạy trên XML chỉ chứng minh rằng quy tắc đã được ghi ra, chứ không chứng minh rằng Excel tô nó đúng như bạn muốn. Một phút nhìn qua bằng mắt sẽ khép lại khoảng cách đó

Rich text: nhiều định dạng bên trong một ô

Một ô rich-text trong mô hình XLSX chứa một danh sách các run, mỗi run là một đoạn văn bản kèm theo thuộc tính font riêng của nó. Bạn dựng danh sách đó riêng ra ngoài như một đối tượng TXLSXRichText, thêm các run vào đó, rồi gắn toàn bộ đối tượng vào một ô. Quy tắc sở hữu là phần dễ gây đau đầu nhất. Gán vào Cell.RichText chuyển quyền sở hữu đối tượng đó sang cho ô, và ô sẽ giải phóng nó trong quá trình hủy của chính nó. Nếu bạn cũng tự giải phóng nó thêm một lần nữa, bạn sẽ có một double-free, kiểu lỗi im lặng suốt lần chạy gây ra nó và chỉ nổi lên thành một crash ở đâu đó chẳng liên quan về sau rất lâu

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' (escalated to regional manager)');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // quyền sở hữu chuyển sang ô: không được Free
end;

Việc gán rõ ràng ColorIsAuto := False không phải là trang trí tùy chọn. Một run mang theo một cờ automatic-color, và một phép gán màu chỉ được tôn trọng khi cờ đó đã được xóa. Đặt Color mà quên ColorIsAuto thì run sẽ ra kết quả in đậm nhưng vẫn đen ngoan cố, không có lỗi nào để chỉ ra nguyên nhân. Các run cũng hỗ trợ gạch ngang, các biến thể gạch chân, và căn chỉnh theo chiều dọc cho superscript và subscript, trong khi PlainText làm phẳng toàn bộ danh sách trở về một chuỗi đơn khi bạn cần xuất hoặc so sánh nội dung văn bản

Rich text ở cấp độ ô chỉ dành riêng cho XLSX. Lớp giao diện XLS không có API công khai để ghi nó, dù các run vẫn có sẵn ở đó trên comment và text box thông qua TextRuns, và các rich string đọc từ một file .xls đã có sẵn vẫn sống sót nguyên vẹn qua một vòng round-trip. Lực kéo ở đây cũng giống như với conditional formatting: bất cứ thứ gì trộn lẫn nhiều định dạng bên trong một ô đều thuộc về trình ghi XLSX

Style pool và lỗi lệch-một vẫn lọt ra sản phẩm

Việc tạo style ô thông thường trong mô hình XLSX chạy qua các collection dùng chung pool trên workbook. Fonts.Add, Fills.AddSolid, và Borders.Add mỗi hàm đều đăng ký một định nghĩa và trả về chỉ số của nó trong pool. Các chỉ số đó đánh số từ 0. Các thuộc tính phía ô tiêu thụ những chỉ số này, chẳng hạn FontIndex, dành riêng giá trị 0 cho "mặc định", nên giá trị bạn gán cho một ô chính là chỉ số trong pool cộng thêm một:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // chỉ số trong pool, đánh số từ 0
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // chỉ số của ô, đánh số từ 1

Bỏ đi + 1 và mọi tiêu đề sẽ rơi trở lại font mặc định. Không có exception nào và không có cảnh báo nào, chỉ có một workbook trông như chẳng ai style nó cả. Lỗi bậc hai ẩn trong vòng lặp: gọi Fonts.Add một lần cho mỗi hàng. Các định nghĩa font giống hệt nhau được loại trùng, nên file không bị hỏng, nhưng công sức đó bị lãng phí, và pool alignment đặc biệt trả về một đối tượng mới toanh ở mỗi lần gọi thay vì gộp các bản trùng lại. Hãy dựng số lượng ít style đó một lần trước vòng lặp và tái sử dụng chỉ số của chúng. Trên các báo cáo hàng trăm nghìn hàng, thay đổi duy nhất đó là một trong những đòn bẩy được nói tới trong tinh chỉnh hiệu năng workbook lớn cho HotXLS. Khi bạn chỉ cần một vẻ ngoài semantic có sẵn, cả hai lớp giao diện đều lộ ra ApplyBuiltinStyle trên các range, ánh xạ sang các style Good, Bad, Neutral, và accent có sẵn của Excel mà không cần bạn động vào các pool chút nào

Conditional formatting, rich text, và pooled style là chặng cuối của một báo cáo, được áp dụng sau khi mô hình dữ liệu và bố cục đã ổn định, và các giai đoạn trước đó là chủ đề của tạo báo cáo dựa trên template với HotXLS. Toàn bộ tài liệu tham khảo về rule, run, và style nằm trên trang sản phẩm HotXLS Delphi Component