HotXLS를 사용하여 Delphi에서 동적 배열 스필(Spill) 수식 구현

HotXLS는 Delphi 및 C++Builder에서 Excel 365 동적 배열 스필(Spill, 동적 배열의 결과를 여러 셀에 걸쳐 나타내는 기능) 수식을 네이티브로 평가합니다. XLOOKUP이나 FILTER를 포함하는 통합 문서(Workbook)를 입력하면 수식 엔진이 결과 집합을 계산하고 고정된 출력 사각형 영역에 데이터를 채워 넣어, Excel과 정확히 동일한 값을 도출합니다. HotXLS Delphi Excel 컴포넌트는 스필 수식을 계산을 소유하는 하나의 루트 셀과 이로부터 값을 읽어오는 종속 셀들의 블록으로 모델링하며, 이것이 코드를 작성하기 전에 머릿속에 그려두어야 할 전체 구조입니다

이 모델이 중요한 이유는 일상적인 작업이 결코 추상적이지 않기 때문입니다. 고객이 Excel 365에서 생성된 .xlsx 파일을 보냈는데 시트 전체가 =FILTER(...) 및 =XLOOKUP(...)으로 가득 차 있다면, 개발자의 서비스는 Excel이 설치되지 않은 헤드리스 서버 환경에서 정확히 동일한 수치를 재현해 내야 하며, 채우기 스필 값을 읽거나 직접 새로운 스필 영역을 작성해야 합니다. 동적 배열은 수식 계산의 규칙을 '하나의 수식, 하나의 셀'에서 '하나의 수식, 셀들의 사각형 영역'으로 바꾸어 놓았으며, HotXLS 엔진은 미리 그리드를 강제로 확장해 두는 임시방편을 쓰는 대신 이 규칙을 충실하게 따릅니다

Delphi에서 XLOOKUP과 FILTER를 어떻게 평가하나요?

HotXLS는 lxCalc.pas의 단일 평가기 진입점인 CalcDynArrayFunc를 통해 모든 동적 배열 함수를 호출하므로, 전체 함수군이 하나의 스필 코드 경로를 공유합니다. 지원되는 함수 집합은 최초의 두 함수인 XLOOKUP 및 FILTER와 함께, 이들의 계열인 XMATCH, SORT, UNIQUE, SEQUENCE 등입니다. 이들 각각은 스칼라(Scalar) 값이 아닌 2차원 Variant 배열을 반환합니다: SEQUENCE(3;2;1;1)은 3x2 그리드를 생성하고, SORT는 오름차순 또는 내림차순 키 열을 기준으로 행을 재정렬하며, UNIQUE는 중복된 행을 축소하여 첫 번째로 일치하는 행만 남기고, XMATCH는 XLOOKUP과 동일하게 완전 일치, 와일드카드, 다음으로 작은 값, 다음으로 큰 값 등의 매치 모드로 1부터 시작하는 값의 위치를 보고합니다. 호출당 하나의 숫자만 반환하는 스칼라 통계 분포 함수와 달리, 이러한 동적 배열 함수는 데이터의 전체 형태(행렬)를 전달합니다

이러한 수식이 이미 작성된 통합 문서를 계산하려면, 문서를 열고 Recalculate를 한 번 호출한 뒤 스필 데이터가 채워진 셀들을 읽으면 됩니다. TXLSXWorkbook.Recalculate는 종속성 그래프를 추적하여 위상 정렬 순서대로 각 더티(Dirty) 수식을 평가하므로, 스필 루트 셀은 단 한 번만 계산되고 결과 요소들이 멤버 셀들에 순차적으로 기록됩니다

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  r, c: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-customer.xlsx');
    Book.Recalculate;                 // 스필을 포함한 모든 수식을 평가합니다
    Sheet := Book.Sheets[1];          // Sheets는 1부터 시작합니다
    // XLOOKUP / FILTER가 채워진 영역(예: D2:F9)의 블록을 다시 읽습니다.
    for r := 2 to 9 do
      for c := 4 to 6 do
        Writeln(Sheet.Cells[r, c].Text);
  finally
    Book.Free;
  end;
end;

만약 통합 문서에 엔진이 지원하지 않는 커스텀 함수가 필요한 경우, 동일한 평가기를 사용해 사용자 정의 시트 함수 훅을 통해 자체 로직을 연결할 수 있으며, 커스텀 함수가 Variant 배열을 반환하도록 설정해 기본 함수처럼 정상적으로 스필 효과를 내도록 할 수 있습니다

SetArrayFormula를 사용한 스필 범위 고정

HotXLS는 스필 영역의 크기를 자의적으로 짐작하지 않습니다: 사용자가 출력 사각형 영역을 지정하면 TXLSRange.SetArrayFormula가 수식을 해당 영역에 고정시킵니다. 이 메서드는 수식을 한 번 컴파일하여 좌측 상단 루트 셀에 고정된 범위와 함께 구문 트리를 저장하고, 사각형 내의 다른 모든 셀에는 루트를 약하게 참조하는 경량 수식을 할당합니다. 재계산 시 루트는 단 한 번만 평가되고 그 행렬 요소들이 각 멤버 셀로 직접 분산 전송되며, 이는 스필 루트가 증분 재계산 종속성 그래프에서 하나의 노드로 나타나는 방식과 같습니다. 이 경로는 파일에서 스필 값을 추출하는 것보다 새로운 스필 영역을 파일에 다시 기록할 때 사용됩니다

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('report.xlsx');
    Sheet := Book.Sheets[1];
    // 결과 크기(3행 2열 그리드)에 맞춰 사각형 영역을 설정합니다.
    if Sheet.Range['A1:B3'].SetArrayFormula('=SEQUENCE(3;2;1;1)') > 0 then
      Book.Recalculate;               // A1:B3 영역에 행 방향으로 1부터 6까지 채웁니다
    Book.SaveAs('report-out.xlsx');
  finally
    Book.Free;
  end;
end;

명시적으로 고정 좌표를 사용하는 구조로 인해 한 가지 한계가 존재합니다: HotXLS는 대화형 Excel처럼 스필 영역의 크기를 자동으로 조절하지 않습니다. Excel은 입력 매개변수가 바뀜에 따라 스필 영역을 동적으로 가감하며 타겟 셀이 다른 데이터로 차 있는 경우 #SPILL! 에러를 유도합니다. HotXLS에서는 SetArrayFormula에 인수로 넘긴 사각형 면적이 그대로 결과로 굳어지므로, 예상되는 결과에 맞춰 개발자가 사각형 크기를 정해야 하며, 함수가 영역 이상으로 많은 행을 출력하더라도 넘치는 데이터는 저장되지 못합니다

연산자를 통한 원소별 배열 브로드캐스팅(Broadcasting)

HotXLS는 연산 대상 중 한쪽이 배열인 경우 연산자를 원소별(Element-wise)로 적용합니다. 도우미 함수 ApplyArrayBinaryOp는 1차원 및 2차원 Variant 배열에 대해 +, -, *, /, ^ 연산을 처리합니다: 스칼라 피연산자는 모든 원소로 브로드캐스트되고, 두 배열은 형태가 일치해야 하며 그렇지 않은 경우 연산이 거부되고, 0으로 나누는 연산은 크래시를 내는 대신 에러 값을 전파합니다. 스칼라 산술 경로는 그대로 유지되므로, 이 기능은 스필 범위나 행렬 함수 결과처럼 최소 하나의 피연산자가 실제 배열인 경우에만 활성화됩니다. 즉, 열에 고정된 =D2:D13*1.1과 같은 수식은 각 원소에 차례로 곱해지며, =D2:D13*E2:E13은 형태가 동일한 두 열을 위치별로 곱하게 됩니다

// 스칼라 브로드캐스트: 고정 영역의 모든 셀이 D(n) * 1.1 결과를 얻습니다
Sheet.Range['F2:F13'].SetArrayFormula('=D2:D13*1.1');
// 형태가 동일한 두 배열을 원소별로 곱합니다
Sheet.Range['G2:G13'].SetArrayFormula('=D2:D13*E2:E13');
Book.Recalculate;

@ 연산자의 역할 및 레거시 CSE 배열과의 차이

HotXLS는 명시적인 @ 기호를 행을 선택하는 Excel의 암시적 교차 연산자가 아닌 참조 교차(Reference-intersection) 연산자로 해석합니다. 엔진 내부에서 A1:A3 @ B1:B3은 두 범위가 교차하는 단일 셀을 반환하며, 이 연산자는 ^보다 우선순위가 높고 %보다 낮습니다. 한 가지 한계점은 다음과 같습니다: 렉서(Lexer)가 공백 문자를 여전히 스킵하기 때문에, 스페이스로 구분되는 암시적 교차 형식은 아직 토큰화되지 않았으므로 명시적인 @ 기호를 사용해서만 교차 연산을 수행할 수 있습니다

보다 근본적인 차이는 레거시 CSE 배열 수식과 현대적인 동적 배열의 차이이며, HotXLS에서는 두 가지 모두 동일한 고정 메커니즘을 공유합니다. 클래식 배열 수식은 미리 선택해 둔 영역에 Ctrl+Shift+Enter를 사용해 입력하는 {=...} 블록으로, 모든 셀이 하나의 컴파일된 수식을 공유합니다. 동적 배열은 그 결과가 스스로의 형태를 결정하는 단일 수식입니다. HotXLS는 수식이 구형 행렬 식인지 신형 SORT인지 관계없이 항상 사각형 영역을 지정하고 루트 셀이 컴파일된 트리를 소유하고 다른 멤버 셀들이 이를 참조하도록 SetArrayFormula 아래로 이 구조를 통합했습니다. 다만 Excel처럼 영역이 알아서 다시 늘어나는 자동 리플(Ripple) 기능은 지원하지 않으므로, 이 경계선을 기억해 두면 두 개념 모델을 헷갈리지 않고 다룰 수 있습니다

동적 배열 지원의 한계

동적 배열 지원 사양의 경계를 알면 긴 디버깅 시간을 절약할 수 있습니다. SORT는 기본적으로 오름차순이고 정렬 인수 값이 음수일 때 내림차순인 키별 정렬 방향을 준수합니다: 비교 방향은 사용자의 키 열을 Excel과 대조해 직접 확인해 볼 가치가 있는 부분입니다. 정렬 방향이 반대로 지정되면 에러를 내는 대신 행의 정렬 순서가 조용히 뒤집혀 출력될 수 있기 때문입니다. UNIQUE는 각 뚜렷한 행의 첫 번째 발생만 보존하며, 엔진은 원시 중복 카운트에 의존하는 대신 이전 행들을 스캔하여 이를 확인합니다. TABLE 함수(가정 분석 데이터 테이블)는 지원되므로 파일 구조 내에서 온전히 왕복 전송되지만, 실제 대입 결과는 Excel이 이미 저장해 둔 캐시 값을 사용하므로 엔진에서는 가정 분석 그리드를 다시 연산하지 않고 플레이스홀더(Placeholder)로 평가합니다

LET은 가장 명확한 한계를 지닌 함수입니다. HotXLS는 LET을 등록하고 평가 경로를 갖고 있지만, 파서가 이미 정의된 이름이 아닌 단순 변수명을 거부하므로 =LET(x;10;x)는 평가기에 가닿기도 전에 파싱 단계에서 실패합니다. 파서가 바인딩된 변수명에 대한 스코프(Scope)를 인지하기 전까지는 LET을 미지원으로 처리하십시오. 이식성에 대한 사소한 참고 사항: 이 문서들의 수식 예제 문자열은 엔진의 리스트 구분자인 세미콜론(;)을 사용하므로, 수식 텍스트를 조합할 때 프로젝트가 사용하는 구분 기호와 일치시키십시오. XLOOKUP, FILTER부터 SORT, UNIQUE, SEQUENCE, XMATCH를 아우르는 여기에 설명된 동적 배열 함수들은 전체 함수 목록과 각 함수의 매개변수 모드를 완벽하게 기술한 수식 레퍼런스와 함께 HotXLS Delphi Excel 컴포넌트와 함께 제공됩니다