HotXLS là component bảng tính bản địa cho Delphi và C++Builder, và kể từ phiên bản 2.209.0 nó có thể trả lời câu hỏi mà Excel thường giữ cho riêng mình: đối với đúng ô này, những quy tắc định dạng có điều kiện nào kích hoạt, và chúng phân giải ra fill, font, data bar hoặc icon nào. Đó là câu trả lời bạn cần ngay khi đầu ra của bạn là một báo cáo HTML, một PDF, hoặc một lưới bạn tự vẽ
Đây là một vấn đề khác với việc tạo quy tắc. Hai bài viết trước đó phủ phần soạn thảo: định dạng có điều kiện và kiểu rich text nói về việc gắn quy tắc và định dạng vi sai vào một range, và phân vùng định dạng có điều kiện neo cố định nói về những gì xảy ra với range của một quy tắc khi hàng và cột được chèn hoặc xóa. Cả hai đều mang tính cấu trúc. Bài này nói về ngữ nghĩa: cho một workbook đã mang sẵn quy tắc, tính toán phần được tô sáng
Vì sao định dạng file không cho bạn biết ô nào sáng lên
Câu trả lời ngắn gọn là ECMA-376 và ISO 29500-1 định nghĩa cách lưu trữ, không phải cách đánh giá. Một phần tử conditionalFormatting (§18.3.1.18) mang một sqref và một danh sách các phần tử con cfRule (§18.3.1.10), và mỗi quy tắc mang một type, một operator tùy chọn, một priority, một cờ stopIfTrue, một hoặc hai phần tử con formula, và với các họ trực quan là một tập ngưỡng cfvo. Mỗi thứ trong số đó mô tả trung thực những gì người dùng đã cấu hình, và không thứ nào trong số đó là một thuật toán. Với một nửa các kiểu quy tắc, khoảng hở đó không quan trọng: cellIs với operator="greaterThan" nghĩa là lớn hơn, và containsText nghĩa là chuỗi con có mặt. Khoảng hở mở ra ở các họ tổng hợp. Một quy tắc top10 với rank="10" và percent="1" trên 27 ô số có dữ liệu sẽ tô sáng bao nhiêu ô? Hai phẩy bảy không phải một số. Làm tròn, làm tròn xuống, hay làm tròn lên — đặc tả im lặng, và chọn sai nghĩa là PDF của bạn không khớp với workbook mà khách hàng đang mở kế bên
Quy tắc một ô và điểm dừng của TCondFormatRule.Evaluate
HotXLS lấy nửa dễ trước. TCondFormatRule.Evaluate trong lxCondFormat.pas, được thêm vào ở 2.199.0, trả lời liệu một quy tắc có kích hoạt cho một ô hay không mà không cần biết gì về phần còn lại của range. Nó xử lý tám toán tử so sánh BIFF đứng sau cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), các quy tắc expression tự do được đánh giá tại ô để tham chiếu tương đối định vị lại đúng, bốn vị từ văn bản, và các vị từ blank và error. Ngưỡng đến từ FFormula1 và FFormula2 được phân giải qua TXLSCalculator.GetRangeValue tại vị trí ô, và các cận bị đảo ngược được hoán đổi thay vì bị từ chối
var
I: Integer;
Rule: TCondFormatRule;
Value: Variant;
begin
Value := Sheet.Cells[Row, Col].Value;
for I := 0 to CondFormat.RuleCount - 1 do
begin
Rule := CondFormat.Rule(I);
// Single-cell verdict only. Aggregate and visual kinds answer False.
if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
ApplyHighlight(Row, Col, Rule.Style);
end;
end;Phần trung thực của phương thức đó là những gì nó từ chối đoán. top10, aboveAverage, belowAverage, duplicateValues và uniqueValues trả về False, không phải vì chúng khó mà vì chúng không thể quyết định được từ một ô — mỗi cái trong số đó cần một thống kê trên toàn miền dữ liệu. Bốn họ trực quan, dataBar, colorScale2, colorScale3 và iconSet, trả về False vì một lý do khác: chúng không bao giờ tạo ra một boolean cả, chúng tạo ra một payload render, và một kiểu trả về Boolean là hình dạng sai cho chúng
Làm sao một bộ đánh giá cấp worksheet tránh quét lại toàn bộ sheet?
Bằng cách tính mọi đại lượng dùng chung một lần, tại thời điểm khởi tạo, và không bao giờ tính lại. TXLSXConditionalFormatEvaluator trong lxHandleX.pas là một snapshot bất biến cho một worksheet, được xây qua TXLSXWorksheet.CreateConditionalFormatEvaluator, và toàn bộ thiết kế của nó là một hàng rào chống lại cách triển khai ngây thơ nơi mỗi ô được tô sáng kích hoạt một lượt quét toàn range
Bốn việc diễn ra trong constructor. Mỗi sqref nhiều vùng riêng biệt được phân tích đúng một lần thành một TXlsxCfRangeSnapshot, nên mười quy tắc dùng chung một range sẽ dùng chung một lần phân tích và một lượt thống kê. Lượt đó stream trung bình, độ lệch trên tổng thể, giá trị nhỏ nhất và lớn nhất trên các ô có dữ liệu trong một lượt duyệt duy nhất, và chỉ giữ lại một mảng số đã sắp xếp khi một quy tắc Top/Bottom hoặc phân vị thực sự cần thống kê thứ tự. Khóa trùng lặp và duy nhất được xây an toàn với Unicode và sắp xếp theo lô một lần thay vì theo từng lần tra cứu. Sau đó trục hàng được cắt thành các dải tại mỗi ranh giới vùng, nên EvaluateCell tìm nhị phân một dải và chỉ ghé thăm những quy tắc có range có thể với tới hàng đó
Điều thứ tư quan trọng nhất ở quy mô lớn. Một công thức quy tắc tương đối như =A1>AVERAGE($A$1:$A$100) có ý nghĩa khác nhau ở mỗi ô trong miền, và cách triển khai hiển nhiên biên dịch một cây cú pháp mới cho mỗi ô. TXlsxCfRulePlan biên dịch nó một lần và đánh giá lại cùng cây đó qua các độ lệch tọa độ có thể đảo ngược, điều này giữ nguyên hành vi neo của Excel mà không cần cấp phát cây cú pháp cho mỗi ô. Các quy tắc sau đó được phân lớp theo priority, và một khớp trên một quy tắc có StopIfTrue được đặt sẽ ngắt vòng lặp, y hệt cách Excel đoản mạch
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
begin
Evaluator := Sheet.CreateConditionalFormatEvaluator;
try
if Evaluator.EvaluateCell(Row, Col, Res) then
begin
if Res.HasFillColor then
Canvas.Brush.Color := TColor(Res.FillColor);
if Res.HasIcon then
// IconIndex is zero-based inside Res.IconSetType
DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
if Res.HasDataBar then
// DataBarAxis and DataBarEnd are normalised to 0..1
DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if not Res.ShowCellValue then
Exit; // showValue="0" on the rule hides the number
end;
finally
Evaluator.Free;
end;
end;Excel thực sự làm tròn một quy tắc Top 10 phần trăm như thế nào?
Nó làm tròn xuống, tối thiểu là một, và bao gồm các giá trị bằng nhau tại ngưỡng cắt. Điều đó không được viết ra ở bất kỳ đâu trong ISO 29500-1 — nó được xác định bằng cách thăm dò Excel 16 với các workbook xây tay và đọc lại xem ứng dụng đã tô sáng ô nào. HotXLS triển khai chính xác như vậy: số lượng thứ hạng là Floor(Count * Min(Rank, 100) / 100), nâng lên 1 khi kết quả bằng không, kẹp về số lượng có dữ liệu, và giá trị ngưỡng cắt sau đó được so sánh bằng >= nên mọi ô bằng biên đều được tô sáng ngay cả khi điều đó vượt quá số lượng yêu cầu. Hai mươi bảy giá trị và một quy tắc 10 phần trăm tô sáng hai ô, cộng thêm bất kỳ ô nào khác bằng với ô thứ hai
Quy tắc trên-trung-bình giấu một sự mơ hồ thứ hai: aboveAverage với stdDev="1" chọn các ô cao hơn trung bình một độ lệch chuẩn, nhưng độ lệch mẫu và độ lệch tổng thể khác nhau bởi hiệu chỉnh Bessel và chúng bất đồng rõ rệt trên các range nhỏ, chính xác là nơi định dạng có điều kiện được dùng nhiều nhất. Excel 16 dùng độ lệch tổng thể, và HotXLS khớp với nó, với cờ equalAverage chuyển phép so sánh nghiêm ngặt thành bao gồm chỉ khi không có dải độ lệch nào đang tham gia. Quy tắc trùng lặp và duy nhất lại bật danh tính khóa thay vào đó. Nếu một ô giữ số 100 và một ô khác giữ văn bản "100", Excel coi chúng là cùng một khóa trùng lặp, nên HotXLS chuẩn hóa văn bản số vào không gian khóa số thay vì so sánh chuỗi thô. Ô trống là trường hợp đối xứng: một ô trống thật sự tham gia vào việc đếm range nhưng bản thân nó không được tô, nên các ô trống trong một cột không tự động sáng lên như trùng lặp của nhau
Color scale và icon set: nội suy và quy tắc ranh giới
Các họ trực quan phân giải thành các con số sẵn sàng để render thay vì boolean, và hành vi biên của chúng được xác định theo cùng cách. Với một color scale có ngưỡng số tường minh, HotXLS kẹp phân số vị trí vào khoảng đóng từ không đến một, rồi nội suy theo từng kênh với việc cắt bớt thay vì làm tròn — một giá trị dưới ngưỡng nhỏ nhất nhận màu nhỏ nhất thay vì một màu ngoại suy, một scale ba điểm chọn cặp của nó bằng cách so sánh với điểm giữa, và một scale suy biến mà hai đầu của nó mang cùng ngưỡng sẽ sụp về màu cao nhất thay vì chia cho không. Icon set cần một kiểu chăm chút ngược lại, vì mỗi cfvo sau cái đầu tiên mang tính nghiêm ngặt so sánh riêng của nó: HotXLS đọc ThresholdEqualsInclude theo từng ngưỡng và áp dụng >= hoặc > tương ứng, duyệt lên trên để ngưỡng cao nhất được thỏa mãn thắng chỉ số icon. Một tập đảo ngược lật chỉ số đã phân giải thay vì lật các ngưỡng, ghi đè theo từng icon có thể kéo một glyph từ một họ khác, và bất kỳ ngưỡng không hợp lệ nào cũng hủy quy tắc thay vì tạo ra một icon sai trông có vẻ hợp lý
Cấp dữ liệu cho một lưới, một export HTML và một PDF từ một kết quả
Vì EvaluateCell trả về một TXLSXCfCellResult đã phân giải đầy đủ — màu fill và font vi sai đã áp dụng sắc thái theme, bold, italic, underline, id định dạng số, các đoạn bar dương và âm theo hướng, vị trí trục, họ và chỉ số icon — mọi bên tiêu thụ đều đọc cùng một bản ghi và không bên nào cần hiểu nội bộ của quy tắc. HotXLS dùng đúng một đường đó cho export HTML, export PDF và trình xem tương tác, đó là cách thực tế duy nhất để giữ ba trình render không trôi dạt khỏi nhau. Phiên bản 2.210.0 đã kết nối nó vào TXLSWorkbookViewer, hàm này cache một bộ đánh giá đã chuẩn bị cho mỗi worksheet đang hoạt động và tái sử dụng nó qua việc cuộn, chọn và vẽ lại, giải phóng nó khi workbook hoặc worksheet thay đổi — xây lại snapshot ở mỗi Paint sẽ đánh bại toàn bộ thiết kế thời điểm khởi tạo. Cache đó cũng là lý do TXLSWorkbookViewer.RefreshConditionalFormats tồn tại: snapshot là bất biến, nên nếu bạn thay đổi workbook đính kèm tại chỗ, thống kê tổng hợp và ngưỡng đã phân giải sẽ cũ cho đến khi bạn gọi nó
// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200; // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats; // drop evaluator, repaintNhững gì bộ đánh giá sẽ không làm thay bạn
Ba ranh giới đáng được nói rõ ràng. TCondFormatRule.Evaluate một-ô kinh điển và TXLSXConditionalFormatEvaluator cấp worksheet là hai bề mặt khác nhau với năng lực khác nhau, và bộ một-ô cố tình từ chối các họ tổng hợp và trực quan thay vì xấp xỉ chúng — nếu bạn cần Top/Bottom hoặc một color scale, hãy xây bộ đánh giá. Các khoảng ngày tương đối phụ thuộc vào đồng hồ máy tại thời điểm đánh giá, nên một quy tắc timePeriod render khác nhau trong một PDF được tạo hôm nay và một PDF được tạo tuần sau, đó là hành vi đúng nhưng vẫn là một ticket hỗ trợ đang chờ xảy ra nếu kho lưu trữ của bạn được kỳ vọng ổn định về byte. Ranh giới thứ ba mang tính ngữ pháp hơn là kỹ thuật: ngữ pháp công thức định dạng có điều kiện cấm tham chiếu bảng có cấu trúc, nên một quy tắc không thể địa chỉ hóa một cột bảng theo tên như một công thức worksheet có thể, và đó là một ràng buộc của định dạng chứ không phải của việc triển khai
Nếu bạn đang xây đầu ra báo cáo, một pipeline export hoặc một lưới tùy chỉnh phải khớp với Excel từng ô một, cùng kết quả đã phân giải đó cũng điều khiển lưới bảng tính VCL tùy chỉnh được mô tả ở nơi khác trên blog này. Tài liệu API đầy đủ, mô hình quy tắc và bản dùng thử cho component bảng tính Delphi HotXLS có sẵn trên trang sản phẩm