Delphi HotXLS의 Excel 조건부 서식 평가

HotXLS는 Delphi와 C++Builder용 네이티브 스프레드시트 컴포넌트이며, 버전 2.209.0부터 Excel이 보통 자기 안에만 담아두는 질문에 답할 수 있습니다. 이 정확한 셀에 대해 어떤 조건부 서식 규칙이 발동하고, 그것이 채우기, 글꼴, 데이터 막대, 아이콘 중 무엇으로 해석되는가 하는 질문입니다. 이 답은 출력이 HTML 리포트든, PDF든, 여러분이 직접 그리는 그리드든 필요해지는 바로 그 순간 필요해집니다

이는 규칙을 만드는 것과는 다른 문제입니다. 앞선 두 글이 저작 측면을 다룹니다. 조건부 서식과 리치 텍스트 스타일은 범위에 규칙과 차등 서식을 붙이는 것을, 고정된 조건부 서식 분할은 행과 열이 삽입되거나 삭제될 때 규칙 범위에 일어나는 일을 다룹니다. 둘 다 구조적입니다. 이 글은 의미론에 관한 것입니다. 이미 규칙을 가진 워크북이 주어졌을 때 하이라이트를 계산하는 문제입니다

파일 형식이 어떤 셀이 켜지는지 알려주지 않는 이유

짧게 답하면 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는 부분 문자열이 존재한다는 뜻입니다. 간극은 집계 계열에서 벌어집니다. 27개의 채워진 숫자 셀에 대해 rank="10"과 percent="1"을 가진 top10 규칙은 몇 개의 셀을 하이라이트할까요? 2.7은 숫자가 아닙니다. 반올림, 내림, 올림 — 명세는 침묵하며, 잘못 고르면 여러분의 PDF는 고객이 옆에 펼쳐둔 워크북과 다른 결과를 보여주게 됩니다

단일 셀 규칙과 TCondFormatRule.Evaluate가 멈추는 지점

HotXLS는 저렴한 절반을 먼저 처리했습니다. 2.199.0에서 추가된 lxCondFormat.pas의 TCondFormatRule.Evaluate는 나머지 범위에 대해서는 아무것도 모른 채 하나의 규칙이 하나의 셀에 대해 발동하는지만 답합니다. 이는 cellIs 뒤의 여덟 가지 BIFF 비교 연산자(between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), 상대 참조가 올바르게 재기준화되도록 셀 위치에서 평가되는 자유형 expression 규칙, 네 가지 텍스트 조건자, 그리고 blank와 error 조건자를 처리합니다. 임계값은 셀 위치에서 TXLSCalculator.GetRangeValue를 통해 해석된 FFormula1과 FFormula2에서 오며, 경계가 뒤바뀐 경우 거부되지 않고 맞바꿔집니다

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;

이 메서드가 정직한 부분은 무엇을 추측하기를 거부하느냐입니다. top10, aboveAverage, belowAverage, duplicateValues, uniqueValues는 False를 반환하는데, 어려워서가 아니라 셀 하나로는 결정 불가능하기 때문입니다. 이것들은 하나같이 전체 도메인에 대한 통계량을 필요로 합니다. dataBar, colorScale2, colorScale3, iconSet이라는 네 가지 시각적 계열은 다른 이유로 False를 반환합니다. 이들은 애초에 Boolean을 만들어내지 않고 렌더링 페이로드를 만들어내며, Boolean 반환 타입은 이들에게 맞지 않는 형태입니다

워크시트 수준 평가기는 어떻게 시트 재스캔을 피하는가

공유되는 모든 값을 생성 시점에 딱 한 번만 계산하고 다시는 계산하지 않는 방식으로 그렇게 합니다. lxHandleX.pas의 TXLSXConditionalFormatEvaluator는 TXLSXWorksheet.CreateConditionalFormatEvaluator를 통해 만들어지는 하나의 워크시트에 대한 불변 스냅샷이며, 그 전체 설계는 칠해지는 셀마다 전체 범위 스캔이 촉발되는 순진한 구현에 대한 방어입니다

생성자 안에서 네 가지 일이 일어납니다. 서로 다른 각 멀티 영역 sqref는 TXlsxCfRangeSnapshot으로 딱 한 번 파싱되므로, 같은 범위를 공유하는 열 개의 규칙은 하나의 파싱과 하나의 통계 계산 패스를 공유합니다. 그 패스는 채워진 셀에 대해 평균, 모집단 표준편차, 최솟값, 최댓값을 한 번의 순회로 스트리밍하며, Top/Bottom이나 백분위 규칙이 실제로 순서를 필요로 할 때만 정렬된 숫자 배열을 유지합니다. 중복과 고유 키는 유니코드에 안전하게 구축되고 조회할 때마다가 아니라 한 번 일괄 정렬됩니다. 그다음 행 축은 모든 영역 경계에서 밴드로 잘리므로, EvaluateCell은 밴드를 이진 검색하며 그 행에 도달할 수 있는 규칙만 방문합니다

네 번째가 규모가 커질 때 가장 중요합니다. =A1>AVERAGE($A$1:$A$100) 같은 상대 규칙 수식은 도메인의 모든 셀에서 다른 의미를 가지며, 명백한 구현이라면 셀마다 새로운 구문 트리를 컴파일할 것입니다. TXlsxCfRulePlan은 이를 딱 한 번 컴파일하고 가역적인 좌표 오프셋을 통해 같은 트리를 재평가하는데, 이는 셀마다 구문 트리를 할당하지 않으면서도 Excel의 앵커 동작을 보존합니다. 그다음 규칙들은 priority로 계층화되며, StopIfTrue가 설정된 규칙에서 일치가 나오면 Excel이 단락 평가하는 것과 정확히 똑같이 루프를 끊습니다

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은 실제로 상위 10% 규칙을 어떻게 반올림하는가

내림하며, 최솟값은 1이고, 컷오프에서의 동점자를 포함합니다. 이는 ISO 29500-1 어디에도 적혀 있지 않습니다. 직접 만든 워크북으로 Excel 16을 실험하고 애플리케이션이 어떤 셀을 하이라이트하는지 읽어서 확인한 값입니다. HotXLS는 정확히 이를 구현합니다. 순위 개수는 Floor(Count * Min(Rank, 100) / 100)이며, 0이 되면 1로 올리고, 채워진 개수로 클램프되며, 컷오프 값은 그다음 >=로 비교되므로 경계와 같은 모든 셀이 요청된 개수를 초과하더라도 하이라이트됩니다. 27개의 값과 10% 규칙은 두 개의 셀을, 그리고 두 번째와 동점인 추가 셀들을 하이라이트합니다

평균 이상 규칙은 두 번째 모호함을 숨기고 있었습니다. stdDev="1"을 가진 aboveAverage는 평균보다 표준편차 1만큼 위에 있는 셀을 선택하지만, 표본 표준편차와 모집단 표준편차는 베셀 보정만큼 차이가 나며 이는 조건부 서식이 정확히 사용되는 작은 범위에서 눈에 띄게 다른 결과를 냅니다. Excel 16은 모집단 표준편차를 사용하고 HotXLS는 이를 그대로 맞추며, equalAverage 플래그는 편차 밴드가 관여하지 않을 때만 엄격한 비교를 포함 비교로 바꿉니다. 중복과 고유 규칙은 대신 키 동일성에 기반합니다. 한 셀이 숫자 100을 담고 다른 셀이 텍스트 "100"을 담고 있다면 Excel은 이를 같은 중복 키로 취급하므로, HotXLS는 원시 문자열을 비교하는 대신 숫자 텍스트를 숫자 키 공간으로 정규화합니다. 빈 셀은 반대 경우입니다. 진짜 빈 셀은 범위 집계에는 참여하지만 그 자체는 서식이 적용되지 않으므로, 한 열의 빈 셀들이 서로의 중복인 것처럼 모두 켜지지는 않습니다

색상 스케일과 아이콘 세트: 보간과 경계 규칙

시각적 계열은 Boolean이 아니라 렌더링 준비된 숫자로 해석되며, 그 경계 동작도 같은 방식으로 고정되었습니다. 명시적인 숫자 임계값을 가진 색상 스케일의 경우, HotXLS는 위치 비율을 닫힌 구간 0에서 1로 클램프한 다음, 반올림이 아니라 절삭으로 채널별로 보간합니다. 최솟값보다 낮은 값은 외삽된 색이 아니라 최소 색을 받고, 3단계 스케일은 중간 스톱과 비교해서 자신의 쌍을 고르며, 두 끝이 같은 임계값을 가진 퇴화된 스케일은 0으로 나누는 대신 최상위 색으로 붕괴합니다. 아이콘 세트는 반대 종류의 주의가 필요했습니다. 첫 번째 이후의 각 cfvo가 자기만의 비교 엄격성을 가지기 때문입니다. HotXLS는 임계값마다 ThresholdEqualsInclude를 읽어서 그에 따라 >=나 >를 적용하며, 위로 걸어 올라가면서 만족된 가장 높은 임계값이 아이콘 인덱스를 결정합니다. 뒤집힌 세트는 임계값이 아니라 해석된 인덱스를 뒤집으며, 아이콘별 오버라이드는 다른 계열에서 글리프를 끌어올 수 있고, 유효하지 않은 임계값은 그럴듯해 보이는 잘못된 아이콘을 만드는 대신 규칙 자체를 중단시킵니다

하나의 결과로 그리드, HTML 내보내기, PDF 공급하기

EvaluateCell이 테마 틴트가 이미 적용된 차등 채우기와 글꼴 색, 굵게, 기울임, 밑줄, 숫자 서식 id, 방향별 양수·음수 막대 길이, 축 위치, 아이콘 계열과 인덱스까지 완전히 해석된 TXLSXCfCellResult를 반환하기 때문에, 모든 소비자는 같은 레코드를 읽으며 그중 누구도 규칙 내부를 이해할 필요가 없습니다. HotXLS는 HTML 내보내기, PDF 내보내기, 대화형 뷰어에 이 하나의 경로를 사용하는데, 이는 세 개의 렌더러가 서로 어긋나지 않게 유지하는 유일하게 실용적인 방법입니다. 버전 2.210.0은 이를 TXLSWorkbookViewer에 연결했으며, 이는 활성 워크시트마다 준비된 평가기 하나를 캐시해서 스크롤, 선택, 다시 그리기 전반에 재사용하고 워크북이나 워크시트가 바뀌면 해제합니다. Paint마다 스냅샷을 재구축한다면 생성 시점 설계 전체를 무의미하게 만들 것입니다. 그 캐시 때문에 TXLSWorkbookViewer.RefreshConditionalFormats가 존재합니다. 스냅샷은 불변이므로, 연결된 워크북을 제자리에서 변형하면 이를 호출하기 전까지 집계 통계와 해석된 임계값이 낡은 상태로 남습니다

// 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, repaint

평가기가 여러분을 위해 해주지 않는 것

분명히 짚어둘 만한 경계가 세 가지 있습니다. 전통적인 단일 셀 TCondFormatRule.Evaluate와 워크시트 수준 TXLSXConditionalFormatEvaluator는 능력이 다른 별개의 표면이며, 단일 셀 쪽은 근사하는 대신 일부러 집계와 시각적 계열을 거절합니다. Top/Bottom이나 색상 스케일이 필요하다면 평가기를 만드십시오. 상대적 날짜 기간은 평가 시점의 기계 시계에 의존하므로, timePeriod 규칙은 오늘 생성된 PDF와 다음 주에 생성된 PDF에서 다르게 렌더링됩니다. 이는 올바른 동작이지만, 아카이브가 바이트 단위로 안정적이길 기대한다면 여전히 지원 티켓이 될 수 있습니다. 세 번째는 기술적이라기보다 문법적입니다. 조건부 서식 수식 문법은 구조화된 표 참조를 금지하므로, 워크시트 수식이 할 수 있는 것처럼 이름으로 표 열을 참조하는 규칙은 만들 수 없습니다. 이는 구현이 아니라 형식 자체의 제약입니다

리포트 출력, 내보내기 파이프라인, 또는 셀 단위로 Excel과 일치해야 하는 커스텀 그리드를 만들고 있다면, 같은 해석 결과가 이 블로그의 다른 글에서 설명하는 커스텀 VCL 스프레드시트 그리드도 구동합니다. 전체 API 문서, 규칙 모델, 체험판 다운로드는 HotXLS Delphi 스프레드시트 컴포넌트 제품 페이지에서 확인할 수 있습니다