판매 실적 27개가 놓인 열에 상위 10퍼센트 규칙 하나를 걸면 Excel은 셋을 칠합니다. 27의 10퍼센트는 2.7이므로 반올림하는 렌더러는 셋, 올림하는 렌더러는 셋, 내림하는 렌더러는 둘을 얻습니다. 세 가지 추측 가운데 두 가지가 이 열에서는 Excel과 우연히 일치하지만 둘 다 틀렸습니다. Excel은 개수를 내림해 둘로 만든 다음, 88에서 두 번째 값과 동률을 이룬 세 번째 셀도 함께 칠합니다. 값을 하나만 바꿔도 이 행운의 추측들은 어긋나기 시작합니다
Excel 밖에서 조건부 서식을 그리는 일의 어려움은 이것이 전부입니다. 규칙은 파일 안에 있지만 "어떤 셀이 켜지고 어떤 색으로 켜지는가"에 대한 답은 파일 안에 없습니다. 출력이 HTML 보고서든 PDF든 직접 칠하는 그리드든, 여러분이 계산해야 하고, 그 산술이 숨는 곳이 집계 규칙 계열입니다. 규칙을 작성하는 것은 별개 주제로 조건부 서식과 리치 텍스트 스타일에서, 행이 이동할 때 규칙 범위에 일어나는 일은 고정된 조건부 서식의 분할에서 다룹니다. 이 글은 평가에 관한 것입니다
conditionalFormatting 요소가 말하는 것과 말하지 않는 것
ECMA-376과 ISO 29500-1은 저장소를 정의하고 거기서 멈춥니다. conditionalFormatting 요소(§18.3.1.18)는 sqref와 cfRule 자식 목록(§18.3.1.10)을 담습니다. 각 규칙에는 type, 선택적 operator, priority, stopIfTrue 플래그, 한두 개의 formula 자식, 그리고 시각 계열을 위한 cfvo 임계값 집합이 붙습니다. 이 모든 것은 사용자가 대화상자에서 클릭한 것을 충실히 기록할 뿐, 알고리즘은 아닙니다
많은 규칙 타입에서는 이 간극이 무해합니다. operator="greaterThan"인 cellIs는 크다를 뜻하고, containsText는 부분 문자열 존재를 뜻합니다. 문제는 판정이 범위 전체에 의존하는 규칙부터입니다. percent="1"인 top10은 소수 개수가 어떻게 반올림되는지 말하지 않고, stdDev="1"인 aboveAverage는 편차가 표본인지 모집단인지 말하지 않으며, 어느 쪽도 컷오프와 같은 값이 자격을 갖추는지 말하지 않습니다. 잘못 고르면 여러분의 PDF가 고객이 옆 창에 열어 둔 워크북과 어긋납니다
TCondFormatRule.Evaluate: 한 셀이 혼자 결정할 수 있는 것
HotXLS에는 두 개의 평가 표면이 있고, 어디까지 답할 의향이 있는지가 서로 다릅니다. 클래식(.xls) 엔진은 lxCondFormat.pas의 TCondFormatRule.Evaluate를 노출하는데, 계산기와 시트 인덱스, 셀 위치, 셀 값을 받아 규칙 하나에 대한 Boolean을 반환합니다. 여기에는 여덟 가지 cellIs 연산자(between, not between, equal, not equal, greater, less, greater or equal, less or equal), 셀 위치에서 평가되어 상대 참조가 올바르게 재조정되는 자유 형식 expression 규칙, 네 가지 텍스트 술어, 빈 셀과 오류 술어가 포함됩니다. 임계값 수식은 셀 위치에서 해석되고, 경계가 뒤집혀 온 between 규칙은 거부 대신 경계를 서로 바꿉니다. 텍스트 비교는 Excel처럼 대소문자를 구분하지 않습니다
// 클래식 엔진: 규칙 하나, 셀 하나, Boolean 하나
function AnyRuleMatches(Calculator: TXLSCalculator; CondFormat: TCondFormat;
SheetIndex, Row, Col: Integer; const Value: Variant): Boolean;
var
I: Integer;
begin
Result := False;
for I := 0 to CondFormat.RuleCount - 1 do
// top10, 평균, 중복, 기간, 시각 계열은 여기서는 False를
// 반환합니다. 범위 전체가 필요한 규칙들입니다
if CondFormat.Rule(I).Evaluate(Calculator, SheetIndex, Row, Col, Value) then
Exit(True);
end;이 메서드는 top10, aboveAverage, belowAverage, duplicateValues, uniqueValues에 대해 False를 반환합니다. 셀 하나는 다른 값들을 보지 않고는 자신이 상위 10퍼센트 안에 드는지 알 수 없으므로, 답하는 것은 추측이 됩니다. 상대 날짜 규칙(timePeriod)도 이 경로에서는 False를 반환합니다. 네 시각 계열, 즉 dataBar, colorScale2, colorScale3, iconSet은 다른 이유로 False를 반환합니다. 예 아니오를 만들지 않고 색, 바 길이, 아이콘 인덱스를 만들기 때문에 Boolean에는 담을 곳이 없습니다. 따라서 이 호출의 False는 "일치하지 않음"만큼이나 "여기서 결정되지 않음"이라는 뜻이기도 합니다. 비교 규칙을 빠르게 확인하는 용도로는 괜찮지만, 출하하는 무엇에도 쓰기엔 안전하지 않습니다
워크시트 평가기는 셀마다 시트를 다시 훑지 않으려면 어떻게 할까
범위 전체의 일을 생성 시점에 한 번 다 합니다. lxHandleX.pas의 TXLSXConditionalFormatEvaluator는 한 워크시트의 조건부 서식에 대한 불변 스냅샷으로, TXLSXWorksheet.CreateConditionalFormatEvaluator에서 얻으며 호출자가 소유하고 해제합니다. 순진한 대안, 즉 칠하는 셀마다 규칙 범위를 훑어 평균이나 순위를 다시 계싼하는 방식은 10,000셀 내보내기를 규칙당 10,000회의 범위 훑기로 만듭니다
평가기는 생성 시점에 서로 다른 각 sqref를 한 번씩 파싱하므로, 같은 범위 위의 열 규칙이 파싱 한 번과 채워진 셀 한 번 순회를 공유합니다. 그 순회는 평균, 모집단 편차, 최솟값, 최댓값을 한 번의 걸음으로 수집하고, Top/Bottom 규칙이나 백분위 임계값이 실제로 순서 통계를 필요로 할 때만 숫자의 정렬 사본을 유지합니다. 중복과 고유 규칙은 값 키를 한 번 만들어 정렬해 두므로 이후 조회는 이진 탐색입니다. 규칙 수식은 한 번 컴파일되고 셀마다 셀 오프셋을 적용해 재평가되므로, =A1>AVERAGE($A$1:$A$100)은 행마다 다시 파싱되지 않으면서 상대 참조와 절대 참조가 Excel과 정확히 같게 동작합니다. 행은 묶여서 EvaluateCell이 요청된 행에 닿을 수 있는 범위의 규칙만 방문합니다
규칙은 priority 순서, 낮은 번호 먼저로 적용되며 동률은 파일 내 위치로 갈립니다. 각 서식 속성은 그 속성을 설정하는 첫 번째 일치 규칙에서 가져오고, 낮은 우선순위 규칙은 아직 비어 있는 속성만 채웁니다. 굵게를 설정하는 우선순위 1 규칙과 빨간 채움을 설정하는 우선순위 2 규칙이 모두 표현되어 셀은 빨간 바탕에 굵게 나옵니다. StopIfTrue 규칙에 일치하면 그 셀의 순회가 끝납니다
procedure PaintConditional(Sheet: TXLSXWorksheet; Canvas: TCanvas;
FirstRow, LastRow, FirstCol, LastCol: Integer);
var
Evaluator: TXLSXConditionalFormatEvaluator;
Res: TXLSXCfCellResult;
Row, Col: Integer;
begin
// 렌더 패스당 스냅샷 하나. 행과 열은 1-based입니다
Evaluator := Sheet.CreateConditionalFormatEvaluator;
try
for Row := FirstRow to LastRow do
for Col := FirstCol to LastCol do
begin
if not Evaluator.EvaluateCell(Row, Col, Res) then
Continue;
if Res.HasFillColor then
Canvas.Brush.Color := TColor(Res.FillColor and $00FFFFFF); // BGR, TColor와 같은 배치
if Res.HasDataBar then // DrawBar는 여러분의 자체 루틴입니다
DrawBar(Row, Col, Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
if Res.HasIcon then // DrawIcon은 여러분의 자체 루틴입니다
DrawIcon(Row, Col, Res.IconSetType, Res.IconIndex);
if Res.ShowCellValue and Res.HasNumberFormat and
Sheet.Cells.HasCell(Row, Col) then // DrawCellText는 여러분의 자체 루틴입니다
DrawCellText(Row, Col,
Evaluator.FormatValue(Sheet.Cells[Row, Col].Value, Res.NumberFormatId));
end;
finally
Evaluator.Free;
end;
end;스냅샷은 패스당 한 번 만들어지도록 의도된 것입니다. 안쪽 루프 안에서 만들면 셀마다 모든 것을 다시 컴파일하고 다시 훑는데, 이 설계가 존재하는 이유 자체가 바로 그 비용을 피하는 것입니다. 데이터 바 규칙이 showValue="0"이거나 아이콘 집합이 아이콘만 표시로 설정되면 ShowCellValue는 False로 돌아와서, 렌더러는 바나 글리프를 그리고 숫자는 생략합니다
상위 10퍼센트 규칙은 실제로 몇 개의 셀을 밝힐까
개수는 내림하고, 절대 1 미만으로 내려가지 않으며, 컷오프와 동률인 값은 모두 포함됩니다. 명세에는 이렇게 쓰여 있지 않습니다. Excel이 그렇게 동작하는 것이고, 그림의 27개 값을 열에 입력하고 상위 10%를 적용해 칠해진 셀을 세어 보면 1분 안에 확인할 수 있습니다. TXLSXConditionalFormatEvaluator는 순위 개수를 Floor(Count * Min(Rank, 100) / 100)로 계산하고, 0이 되면 1로 올리고, 채워진 숫자 셀 수로 잘라 내고, 정렬된 값에서 컷오프를 읽은 다음 모든 셀을 >=로 비교합니다. 27개 값의 열에서는 개수 2, 컷오프 88, 그리고 88을 담은 셀이 둘이라 세 개가 칠해집니다. Bottom-N 규칙은 <=로 이것을 거울처럼 반영합니다
평균 이상 규칙에는 두 번째 모호함이 있습니다. stdDev="1"이면 이 규칙은 평균보다 최소 한 표준편차 위인 셀을 고르고, 표본 편차와 모집단 편차는 Bessel 보정만큼 다릅니다. 사람들이 이 규칙을 실제로 쓰는 짧은 범위에서 가장 문제가 됩니다. 다섯 값에서 둘은 약 12퍼센트 차이가 납니다. HotXLS는 STDEV.P가 반환하는 것과 같은 모집단 편차를 쓰므로, 아무 렌더러나 빨리 검사하는 방법은 범위 옆에 =AVERAGE(r)+STDEV.P(r)을 놓고 어떤 셀이 그 선을 넘는지 보는 것입니다. 편차 밴드에 대한 비교는 경계를 포함하고, 평범한 이상/이하 비교는 규칙이 equalAverage="1"을 갖지 않으면 엄격합니다
중복과 고유 규칙은 무엇이 "같은 값"인지에 달려 있습니다. 숫자 100과 텍스트 "100"은 같은 키를 공유합니다. 숫자형 텍스트는 숫자 키 공간으로 정규화되기 때문입니다. 텍스트 키는 대소문자를 구분하지 않고 비교하며, TRUE와 1은 별개로 남고 종류가 다른 오류 값도 별개입니다. *나 ?를 담은 텍스트는 COUNTIF 식 와일드카드 패턴으로 일치하므로, Q*를 담은 셀은 Q로 시작하는 모든 값과 짝이 됩니다. 빈 셀은 반대 방향의 함정입니다. 범위 안의 진짜 빈 셀은 중복으로 칠해지지 않아서 끝내지 못한 열이 위에서 아래까지 켜지지 않는 반면, 빈 문자열을 저장하는 셀은 빈 셀과 함께 셉니다
색 스케일, 데이터 바, 아이콘 집합은 숫자로 돌아옵니다
시각 계열은 렌더 준비가 된 값으로 해석되며, 그 경계가 손으로 쓴 렌더러가 흐트러지는 지점입니다. 색 스케일에서 EvaluateCell은 위치를 0에서 1 구간으로 잘라 넣고 각 RGB 채널을 절삭하며 보간하므로, 최소 정지점 아래의 값은 외삽된 색 대신 최소 색을 얻습니다. 세 정지점 스케일은 중간점 임계값 위의 값에 중간-최대 쌍을, 그 외에는 최소-중간 쌍을 씁니다. 첫 임계값과 마지막 임계값이 같은 수로 해석되는 스케일은 0으로 나누는 대신 마지막 색을 반환합니다. 백분위 임계값은 이웃한 정렬 값 사이를 선형 보간합니다. PERCENTILE.INC 정의입니다
데이터 바는 셀 너비에 정규화됩니다. 전부 양수인 범위에서 DataBarAxis는 0이고 DataBarEnd는 규칙의 최소-최대 바 길이(기본 10퍼센트와 90퍼센트) 사이를 달리므로, 가장 작은 값도 짧은 바를 얻고 가장 큰 값은 셀을 가득 채지 못합니다. 범위가 0을 가로지르면 축이 비례 위치에 앉고 DataBarEnd는 바가 멈추는 지점을 표시하며, 음수 값은 축의 반대편에 놓입니다. 방향, 그라디언트, 테두리, 음수 바 색은 같은 레코드에 채워지고 테마 색과 틴트는 이미 해석되어 있습니다
아이콘 집합은 임계값마다의 엄격함에 주의가 필요합니다. 첫 번째 다음의 모든 cfvo는 자체 gte 플래그를 갖기 때문입니다. 평가기는 임계값을 위로 올라가며 ThresholdEqualsInclude가 말하는 대로 임계값마다 >= 또는 >를 적용하고, 값이 넘는 가장 높은 임계값이 이깁니다. 뒤집힌 집합은 해석된 인덱스를 뒤집고, 아이콘별 재정의는 IconSetType으로 다른 계열의 글리프로 대체할 수 있으며, 해석할 수 없는 임계값은 그 셀의 규칙을 멈춰서 그럴듯한 틀린 아이콘 대신 아이콘이 아예 없게 만듭니다
그리드, HTML 내보내기, PDF가 함께 쓰는 하나의 결과
TXLSXCfCellResult는 렌더러에 필요한 모든 것을 나릅니다. 테마 틴트가 적용된 채움 및 글꼴 색, 굵게, 기울임, 밑줄, 취소선, number format id, 데이터 바 기하와 색, 아이콘 계열과 인덱스가 그것입니다. HotXLS는 같은 레코드를 HTML 내보내기와 PDF 내보내기, TXLSWorkbookViewer에 먹여서 세 출력이 셀 단위로 일치하게 유지합니다. 뷰어는 활성 워크시트의 평가기를 처음 칠할 때 하나 만들어 스크롤과 선택, 다시 칠하기에 걸쳐 재사용하고, 시트를 바꾸거나 다른 워크북을 할당하면 버립니다
// 살아 있는 뷰어 뒤에서 편집하기: 캐시된 스냅샷은 변경을 보지 못합니다
Sheet := Viewer.XlsxWorkbook.Sheets[1]; // 1-based 시트 인덱스
Sheet.Cells[5, 2].Value := 4200; // 평균과 최댓값, 순위를 움직입니다
Viewer.RefreshConditionalFormats; // 스냅샷을 버리고 다시 칠합니다스냅샷이 불변이므로, 그 자리에서의 편집은 RefreshConditionalFormats가 실행될 때까지 평균과 순위, 색 스케일 끝점을 낡은 채로 둡니다. 여러분 코드도 마찬가지입니다. 편집에 걸쳐 유지하는 평가기는 해제했다가 다시 만들어야 합니다. CreateConditionalFormatEvaluator는 호출된 순간의 값을 붙잡기 때문입니다
상대 날짜 규칙은 처음에 결정할 문제입니다. 워크시트 평가기는 timePeriod 규칙을 머신의 현재 날짜와 비교하므로, "이번 주" 규칙은 오늘 만든 PDF와 다음 화요일에 내보낸 같은 워크북에서 다르게 그려집니다. 올바른 동작이지만 아카이브가 재현 가능하기를 기대한다면 지원 티켓 한 장의 소재입니다. 보관용 보고서라면 timePeriod 규칙을 셀에 저장된 보고서 날짜와 비교하는 expression 규칙으로 바꾸세요. 7일 창이라면 =AND(A2>=$Z$1-6,A2<=$Z$1) 같은 식입니다. 그러면 출력이 워크북에만 의존합니다. Excel은 조건부 서식 수식 안의 구조화된 테이블 참조도 거부하므로, 규칙은 열의 셀 범위를 가리켜야 합니다
대상이 파일이 아니라 화면이라면, 같은 결과 레코드가 이 블로그의 다른 글에서 다루는 커스텀 VCL 스프레드시트 그리드도 움직입니다. 규칙 모델과 평가기 API 레퍼런스, 평가판 빌드는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 있습니다