Delphi와 C++Builder용 Excel 컴포넌트인 HotXLS는 행이나 열의 삽입/삭제가 규칙이 덮는 범위를 서로 다른 상대 수식 앵커가 필요한 조각들로 잘라낼 때마다 조건부 서식이나 데이터 유효성 검사 규칙 하나를 두 개 이상의 개별 규칙 객체로 자동 분할한 뒤, 모든 조건부 서식 규칙에 새롭고 고유한 우선순위 번호를 다시 부여한다. 이 동작은 XLSX 엔진 버전 2.196에서 도입되어 별도 설정 없이 항상 자동으로 실행된다. 발동 조건은 좁지만 흔하다: 자신의 범위를 기준으로 상대적인 셀을 읽는 수식을 가진 cellIs나 expression 규칙이 있는 워크시트에서, 나중에 바로 그 범위 중간 어딘가에 행이 삽입되거나 삭제되는 경우다
Excel 자동화에 관한 대부분의 글은 수식 텍스트 문제에서 멈춘다: 모든 SUM()과 모든 VLOOKUP() 안의 행·열 번호를 이동시켜 참조가 여전히 올바른 셀을 가리키도록 만드는 것이다. 그 절반은 실제 문제이며 행과 열이 이동할 때 HotXLS가 수식 참조를 다시 쓰는 방법에 관한 자매 글에서 다루지만, 조건부 서식이나 데이터 유효성 검사 규칙은 그저 셀 안에 앉아 있는 수식이 아니다. 이는 수식과 범위(ECMA-376 용어로 sqref)를 한 쌍으로 묶고, 이 둘은 함께 움직여야 한다. 구조적 편집이 그 범위를 올바르게 유지하려면 두 개의 서로 다른 상대 오프셋이 필요한 두 조각으로 잘라내면, 수식 문자열 하나를 가진 규칙 객체 하나를 유지하는 것은 더 이상 선택지가 아니게 되며, 그렇지 않은 척하는 것은 하이라이트 규칙이 조용히 잘못된 행을 비교하기 시작하는 지름길이다
행을 삽입하면 왜 조건부 서식 규칙이 그저 이동하는 대신 분할되는가?
조건부 서식이나 데이터 유효성 검사 규칙은 전체 범위에 대해 정확히 하나의 수식만 유지하며, 이는 단일 앵커 셀을 기준으로 평가된다. 그래서 편집이 그 범위의 두 부분이 서로 다른 상대 오프셋을 필요로 하도록 강제하는 순간, 수식 하나로는 더 이상 두 부분 모두를 올바르게 서술할 수 없다. ECMA-376은 규칙의 적용 범위를 conditionalFormatting이나 dataValidation 요소의 sqref 속성으로 표현하며, Excel은 마치 그 텍스트가 sqref의 좌상단 셀에 입력되어 나머지 전체에 채워 넣어진 것처럼 Formula1과 Formula2를 평가한다. 이는 일반 상대 수식이 열 아래로 채워지는 방식과 같다. B2:B50에 걸쳐 실제 수치가 예산을 초과하면 표시하는 분산 하이라이트를 떠올려 보자. 이는 Formula1이 리터럴 텍스트 C2인 cellIs 규칙으로 만들어지며, 현재 행의 B 셀을 같은 행의 C 셀과 비교하라는 뜻이다
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 25이전 25행에 빈 구분 행 하나를 삽입하면 삽입 지점 위쪽 행들은 이동하지 않으므로, 그 규칙 부분은 여전히 Formula1을 C2로 올바르게 읽는다. 이전에 25에서 50이던 행들은 26에서 51로 밀려나며, 이들에게는 C2가 이제 완전히 잘못된 셀이 되는데, 26행은 그보다 스물네 행 위에 있는 예산 수치가 아니라 C26과 비교해야 하기 때문이다
HotXLS는 규칙이 분할되어야 하는지 어떻게 판단하는가
HotXLS는 지오메트리가 정말로 그것을 요구할 때만 추가 규칙 객체를 만든다: 내부 루틴인 XlsxBuildShiftedRuleParts는 규칙의 sqref 안에 있는 서로 분리된 모든 영역을 순회하며, 각 영역의 앵커 셀이 편집 전에 무엇이었고 편집 후에 무엇이 되는지 계산한 뒤, 결과로 나온 모든 조각이 동일한 상대 오프셋 보정을 필요로 하는지 확인한다. 모든 조각이 일치하면 규칙 하나가 그대로 살아남고, 그 sqref는 이동된 조각들의 합집합으로 재구성되며 수식은 한 번만 재기준된다. 진짜 분할은 조각들이 일치하지 않을 때만 일어나는데, 정확히 위의 B2:B50 사례가 그렇다. 위쪽 블록은 원래 앵커를 유지하고 아래쪽 블록은 새 앵커가 필요하다
한 조각의 수식을 재기준하는 것은 HotXLS가 OOXML 공유 수식 그룹을 위해 이미 가지고 있는 메커니즘을 재사용하는 2단계 작업이다: 먼저 수식은 마치 원래 그 조각 자신의 좌상단 셀에 앵커되어 있었던 것처럼 번역되는데, 이는 공유 수식을 그 범위 전체에 확장할 때 쓰는 것과 동일한 상대 오프셋 연산을 사용한다. 그다음 그 결과는 일반 워크시트 수식을 다시 쓰는 것과 동일한 행·열 이동 스캐너를 거친다. 이것이 손으로 작성한 특수 케이스 하나가 아니라 두 번의 이동으로 Formula1이 C2에서 C26으로 가는 방식이다: 먼저 C2를 23행 앞으로 번역해 C25를 얻는다(마치 그 규칙이 항상 거기서 시작한 것처럼), 그다음 25행에서의 일반적인 이동이 이를 C26으로 밀어낸다. 다른 모든 속성 — 채우기 색, stop-if-true, 연산자 자체 — 은 변경 없이 새 규칙 객체로 그대로 따라오므로, 양쪽 절반 모두 항상 칠하던 색을 계속 칠한다
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)데이터 바와 아이콘 세트도 cellIs 규칙과 같은 방식으로 분할되는가?
아니다: HotXLS는 실제로 올바른 동작이 영역별 상대 수식에 의존하는 규칙 종류 — cellIs 비교와 expression 규칙 — 만 분할하며, 다른 모든 조건부 서식 종류는 이동된 조각들을 덮도록 sqref가 다중 영역 합집합으로 단순히 커지는 단일 규칙 객체로 남겨둔다. 내부적으로 이 분기는 cf.Kind in [cfkCellIs, cfkExpression]이라는 단순한 Kind 검사일 뿐, 그 이상 특별할 것이 없다. 데이터 바, 2색·3색 스케일, 아이콘 세트, 상위/하위 순위, 그리고 중복·공백·오류 탐지기는 셀별 상대 비교가 아니라 덮는 전체 범위를 한 번에 서술하는 페이로드 — 막대 색, 스케일 정지점 집합, 아이콘 계열 — 를 가지므로, 이들을 우선순위가 부여된 여러 규칙 객체로 분할해 봐야 정확성 면에서 얻는 것이 없고 관리할 규칙만 늘어난다. 편집이 이들의 범위를 나누면 HotXLS는 그 조각들을 다중 영역 sqref를 가진 규칙 하나로 다시 합치고 페이로드를 조각마다 새 규칙 객체를 복제하는 대신 단일 단위로 재앵커한다. 이 구분은 조건부 서식과 서식 있는 텍스트 기초에 관한 글의 규칙 종류 분류와 일치한다: 데이터 바, 색상 스케일, 아이콘 세트는 이미 Style 속성을 완전히 무시함으로써 cellIs 규칙과 구별되었는데, 이제 보니 같은 근본적인 이유로 영역별 재앵커링에서도 벗어나 있다
구조적 편집 후 규칙 우선순위는 왜 바뀌는가?
우선순위가 바뀌는 이유는 모든 복제본이 자신이 분할되어 나온 규칙과 정확히 같은 우선순위 값을 가진 채 시작하고, HotXLS가 그 뒤에 정규화 패스를 실행해 두 규칙이 같은 순위로 묶인 채 남는 대신 결과로 생긴 중복을 깔끔하고 빈틈없는 순서로 정리하기 때문이다. 두 번째 내부 루틴인 XlsxNormalizeConditionalFormatPriorities는 모든 조건부 서식의 현재 우선순위를 가져오되, 명시적으로 설정된 적 없는 규칙에 대해서는 컬렉션 내 위치로 대체하고, 동률이 원래의 상대 순서를 유지하도록 전체 목록을 안정 정렬한 뒤, 정렬된 결과를 빈틈도 반복도 없는 조밀한 1, 2, 3 순서로 재번호 매김한다. HotXLS는 이동이 시작되기 전에 한 번 실행해 복제가 깨끗한 기준선에서 시작하도록 하고, 모든 분할과 비워진 규칙 제거 후에 다시 실행해 저장되는 파일에 같은 우선순위를 주장하는 규칙 항목 두 개가 절대 생기지 않도록 한다. 이는 나중에 규칙이 나머지를 재번호 매김하지 않고 끼어들 수 있도록 우선순위 값 사이에 간격을 남겨두라는 조건부 서식 기초 글의 조언을 따랐다면 중요하다: 그 간격은 해당 워크시트에 다음 행이나 열 편집이 닿기 전까지는 유지되다가 그 뒤 무너지는데, 정규화는 오직 고유성과 안정된 순서만 보장할 뿐 원래의 번호 매김 체계가 변경 없이 돌아온다는 것을 보장하지 않기 때문이다
데이터 유효성 검사 규칙도 분할되지만, 재번호 매김할 우선순위가 없다
데이터 유효성 검사 규칙은 cellIs·expression 조건부 서식과 동일한 범위 분할 로직을 거치며, 조건부 서식과 달리 모든 유효성 검사 유형이 그 경로를 균일하게 따른다: HotXLS는 데이터 바와 아이콘 세트가 조건부 서식에서 그렇듯 데이터 유효성 검사를 위한 별도의 비수식 계열을 두지 않으므로, 평범한 목록이나 정수 규칙도 상대적 커스텀 수식을 다루는 것과 동일한 루틴으로 분할된다. 다른 점은 우선순위다: ECMA-376은 dataValidation 요소에 priority 속성을 전혀 부여하지 않으므로, 조건부 서식에서와 같은 유효성 검사용 재번호 매김 단계는 존재하지 않는다. 각 행의 실제 금액이 옆 열의 자기 예산을 초과하지 못하게 막는 커스텀 수식 유효성 검사를 떠올려 보자
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)이는 데이터 유효성 검사 기초 글이 행 개수가 확정되기 전에 규칙을 연결하지 말라고 경고하는 것과 같은 이유로 중요하다: 유효성 검사는 여러분이 준 리터럴 셀만 덮으며, 이후의 구조적 편집은 하나가 하던 일을 두 개 이상의 규칙이 하게 만들 수 있다. 기능적으로는 아무것도 깨지지 않는다: 원래 범위의 모든 셀은 여전히 무언가에 의해 검증되지만, 열당 DataValidations 항목 하나를 가정하는 코드는 첫 편집이 그것을 건드린 뒤부터 인덱스를 잘못 짚기 시작한다. 이것이 얼마나 진행될 수 있는지에는 확실한 상한선이 있다: 분할이 워크시트를 65,534개 데이터 유효성 검사 규칙 너머로 밀어낸다면, HotXLS는 Excel이 조용히 거부할 파일을 작성하는 대신 예외를 일으킨다 — 이는 일반적인 사용으로는 도달하기 어려운 한계라기보다 손상된 워크북을 만들어내지 않겠다는 라이브러리의 거부다
대량 삽입이나 삭제 후 확인해야 할 것
조건부 서식과 유효성 검사로 가득한 시트에 대해 스크립트가 대량의 행이나 열 편집을 실행한 뒤 확인할 가치가 있는 두 가지는 전체 규칙 개수와 우선순위 순서다. 둘 다 코드 리뷰에서는 놓치기 쉽지만 누군가 Excel에서 규칙 관리를 열어보는 순간 명백해지는 방식으로 표류할 수 있기 때문이다. 편집 하나는 거의 큰 피해를 주지 않는다: 하나의 cellIs 규칙 중간에 삽입 하나가 일어나면 원래 하나였던 것이 최대 두 개의 규칙 객체가 된다. 위험은 보고서 생성 루틴이 이미 여러 개의 수식 앵커 규칙을 가진 시트에 루프를 돌며 한 번에 한 행씩 삽입할 때 복합적으로 커진다: 각 반복은 이전 반복이 이미 분할한 규칙을 다시 분할할 수 있으며, 다섯 개의 원본 cellIs 규칙이 원래 범위의 조각들을 덮는 그 몇 배에 달하는 가치 낮은 파편들로 끝날 수 있다. 구조적 편집을 배치로 묶어 새 블록 전체를 한 번씩 나눠 삽입하는 대신 한 번의 호출로 삽입하면, 규칙 개수가 수행된 편집 횟수가 아니라 진짜로 서로 다른 앵커 개수에 묶여 있게 된다
규칙 분할과 우선순위 정규화는 Delphi와 C++Builder용 HotXLS Delphi Excel 컴포넌트의 XLSX 엔진 표준 동작으로 제공된다. 제품 페이지에는 여기서 설명한 조건부 서식과 데이터 유효성 검사 메서드를 포함한 전체 워크시트 편집 API 레퍼런스가 실려 있다