Delphi에서의 이름 정의 및 교차 시트 수식 (HotXLS)

정의된 이름(Defined name)은 상수, 셀 범위 또는 수식 식을 대신하는 식별자(레이블)로서, 통합 문서에 한 번 저장되면 필요한 모든 위치에서 심볼릭하게 참조될 수 있습니다. 수식에 TaxRate를 입력하면 수식 엔진은 이 이름을 리터럴 0.08이나 Data!$A$2:$D$100 범위 등 해당 정의 내용으로 해석합니다. 교차 시트 참조(Cross-sheet reference)는 이와 교차하는 개념입니다: Data!D2처럼 주소 앞에 시트명을 접두사로 붙여 다른 시트의 셀에 접근합니다. 이 두 개념을 결합하면 요약 시트에서 상세 시트의 수치들을 실제 주소 대신 이름을 통해 계산할 수 있으며, 이는 자동화 엔진이 조립하고 향후 회계 담당자가 검증할 통합 문서에 매우 유용한 설계 패턴입니다

losLab의 XLS 및 XLSX 파일용 네이티브 Delphi 라이브러리인 HotXLS는 생성, 검색 및 삭제 기능과 함께 두 포맷의 이름 테이블을 노출하며, 프로세스 내에서 이름과 교차 시트 참조를 해석하는 수식 엔진을 탑재하고 있습니다. 두 포맷은 개별 클래스 계층 구조를 가지므로, 이 이름 API 간의 세세한 차이점들이 한 포맷에서 다른 포맷으로 코드를 이식(Porting)할 때 버그를 유발할 수 있습니다

인터페이스를 공유하지 않는 두 개의 이름 저장소

XLS 엔진의 경우, TXLSWorkbook.GetNames는 BIFF 이름 테이블에 이름을 기록하는 Add(Name, RefersTo, Visible) 오버로드를 포함하는 IXLSNames 컬렉션을 반환합니다. 개별 항목은 Name, RefersTo, 해석된 RefersToRange, Delete 메서드를 동반하는 IXLSName 개체로 래핑되어 제공됩니다. XLSX 엔진의 경우, TXLSXWorkbook.DefinedNames는 Add, FindByName, DeleteByName 메서드를 제공하는 TXLSXDefinedNames 컬렉션입니다

이름을 조회(Lookup)하는 규칙의 차이는 컴파일 시점이 아닌 이식(Porting) 작업 중에 두드러지게 나타납니다. XLS 컬렉션의 기본 Item 프로퍼티는 Variant 형식을 허용하므로 Names[0]과 Names['TaxRate'] 방식 모두 정상 작동합니다. 반면 XLSX 컬렉션은 이러한 기본 프로퍼티를 제공하지 않으므로 FindByName('TaxRate')를 명시적으로 호출해야 하며, 일치하는 이름이 없는 경우 nil을 반환합니다. 따라서 특정 포맷용으로 작성한 코드가 다른 포맷에서 그대로 빌드되는 것은 우연일 뿐이며, 대개 IDE의 경고 대신 런타임에 nil 참조 에러로 오동작하게 됩니다

이름 범위(Scope)는 나중에 추가할 옵션이 아닌 최초의 설계 결정 사안입니다

정의된 이름은 모든 시트의 수식에서 참조할 수 있는 통합 문서 범위(Workbook-scoped) 이름이거나, 해당 소유 시트의 수식에서만 참조할 수 있는 시트 범위(Sheet-scoped) 이름입니다. XLSX API에서 이 차이는 하나의 선택적 매개변수로 구별됩니다. DefinedNames.Add(AName, AFormula)는 통합 문서 수준의 이름을 생성하며, Add(AName, AFormula, ASheetIndex)는 특정 시트에 이름을 할당합니다. 정의된 이름을 읽을 때, TXLSXDefinedName.SheetIndex는 통합 문서 범위에 대해 -1을 반환하고 시트 범위에 대해서는 0부터 시작하는 시트 인덱스를 반환합니다

이름의 가시 범위(Scope)는 이름 충돌 방지 정책의 역할도 겸하므로, 첫 이름을 정의하기 전에 이를 결정해 두는 것이 좋습니다. Excel은 통합 문서 수준의 Total과 함께 개별 시트 내에서 국소적으로 작동하는 시트 범위의 Total 이름을 동시에 허용하며, 해당 시트의 수식은 시트 범위 이름을 먼저 해석합니다. 파일 생성 엔진은 이 특성을 정밀하게 활용해야 합니다. 여러 시트에서 동시 공유해야 하는 비즈니스 원칙(세율, 환율, 보고 기간 등)은 통합 문서 범위로 지정해야 합니다. 특정 시트 내부에서만 임시로 활용하는 도우미 범위 등은 다른 영역을 침범하지 않고 외부 영향도 받지 않도록 시트 범위로 가두는 것이 훨씬 안전합니다

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... Data!A2:D100 영역을 상세 데이터 행으로 채웁니다 ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // 통합 문서 범위의 상수
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // 통합 문서 범위의 셀 범위
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // 시트 인덱스 1번에만 유효하게 스코프 제한

    // XLSX 수식에는 시작 기호인 '='를 쓰지 않습니다
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

정의된 이름이 반드시 특정 셀 범위를 참조해야 할 필요는 없습니다. 위 코드의 TaxRate처럼 순수 상수 0.08을 가리킬 수 있으며, 이것이 비즈니스 원칙을 관리하는 가장 깔끔한 방법입니다. Excel 이름 관리자 창에 한 번만 정의해 두면 모든 시트 수식에서 이를 심볼릭하게 참조할 수 있으므로, 세율이 바뀌더라도 수동으로 수식 10여 개를 수정하는 대신 생성 코드 상의 한 줄만 변경해 손쉽게 대응할 수 있습니다

한쪽 플랫폼에만 적용해야 하는 등호(=) 기호

수식 입력 채널은 포팅된 코드에서 가장 빈번하게 버그를 유발하는 구역인데, 두 포맷 엔진이 등호(=) 기호에 대해 상이하게 반응하기 때문입니다. XLS 셀은 시작 기호인 =를 접두사로 포함하는 문자열을 Value 프로퍼티에 기입하여 수식을 할당받습니다. 반면 XLSX 셀은 접두사가 없는 순수 수식을 전송받는 전용 Formula 프로퍼티를 제공합니다. TXLSXCell.Formula에 '=SUM(A1:A10)'을 기록하면 등호 기호가 수식의 메타 구분자가 아닌 본문 데이터의 일부로 취급되어, XLS 플랫폼과 전혀 다르게 오동작하게 됩니다

var
  Book: IXLSWorkbook;   // interface-counted: do not Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // 상세 데이터 행들을 이미 포함한 'Data' 시트가 존재한다고 가정합니다
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False는 이름 관리자(Name Manager)에서 이름을 숨깁니다

  // XLS 수식은 '=' 접두사와 함께 Value를 통해 기입됩니다
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

위 예시는 XLS 엔진의 또 다른 두 가지 특이사항을 보여줍니다. 첫째, 시트 컬렉션이 1부터 시작하므로 0부터 시작하는 XLSX Sheets[0]과 대조적으로 Sheets[1]이 첫 번째 시트가 됩니다. 둘째, Add 메서드의 세 번째 매개변수를 통해 숨겨진(Hidden) 이름을 생성할 수 있습니다: 파일 내에 실재하고 수식에서도 연산 가능하지만, 사용자의 Excel 이름 관리자 화면에는 나타나지 않습니다. 숨겨진 이름은 사용자가 임의로 수정하거나 지워서는 안 되는 프로그램 내부 구조용 변수를 정의하는 최적의 수단입니다

교차 시트 참조 및 행 이동 시의 동작 양상

두 수식 엔진 모두 표준 교차 시트 참조 구문을 해석할 수 있습니다. 기호가 없는 순수 시트명은 Data!A1처럼 바로 사용할 수 있고, 공백이나 문장 부호가 포함된 시트명은 'Sheet With Space'!A1처럼 홑따옴표로 감싸서 작성합니다. 정의할 이름의 RefersTo 정의 문자열 안에서는 대개 Data!$A$2:$D$100처럼 절대 참조 주소($)를 사용하십시오. 정의된 이름 내의 상대 참조 주소는 해당 이름을 가져다 쓰는 셀의 위치를 기준으로 재계산되며, 이는 Excel 표준 기능이지만 의도치 않게 작동할 경우 심각한 오계산을 초래하기 쉽습니다

구조적인 편집 과정에서 교차 시트 관리 시스템의 진가가 드러나며, XLSX 엔진은 이 과정에서 정의된 이름의 일관성을 정교하게 추적합니다. InsertRows 및 DeleteRows 연산은 셀, 병합 영역, 하이퍼링크 및 차트 앵커와 함께 정의된 이름의 적용 영역 또한 연동하여 변경시키므로, 데이터 블록 위에 행을 새로 끼워 넣더라도 Data!$A$2:$D$100 범위 이름이 데이터 영역을 정확하게 계속 조준합니다. 수식 편집 시 유념해야 할 주의 사항이 하나 있습니다: 행 삽입 기능은 편집 대상 시트를 참조하는 경로만 재조정합니다. 예를 들어 Data 시트에 행이 기입될 때 이를 참조하는 Summary 시트의 Data!D2:D100 수식은 정상 조율됩니다. 추측하는 대신 엔진의 계산 능력을 활용해 직접 검증해 볼 수 있습니다:

// 계산 엔진은 프로세스 내에서 이름과 교차 시트 참조를 해석합니다
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate는 디스크에 저장하는 과정 없이 현재 통합 문서 상태를 바탕으로 임의의 식을 즉시 평가하므로, 파일 생성 루틴 테스트용 단언문(Assertion) 작성에 매우 유용한 기능입니다. Pascal 코드에서 직접 데이터를 정산한 값과 수식 엔진이 계산한 값을 대조해 보면 견고한 검증 루프를 완성할 수 있습니다. 수식 엔진 가이드에서 엔진의 계산 대상, 주기 및 커스텀 함수로 이를 확장하는 기법을 상세히 다룹니다

프로퍼티 계층이 소유하는 _xlnm 이름들

생성된 파일을 저수준 파일 분석기로 열어보면 직접 기입하지 않은 _xlnm.Print_Area, _xlnm.Print_Titles 등의 특수 항목들이 발견되곤 합니다. 이는 OOXML(ECMA-376 / ISO 29500) 표준이 인쇄 영역 및 반복 인쇄 행 등의 인쇄 사양을 예약어 식별자를 통해 이름 테이블에 저장하는 방식입니다. HotXLS는 이를 시트의 전용 프로퍼티로 추상화하여 관리하므로, 개발자가 PrintArea 또는 PrintTitleRows 프로퍼티를 지정하면 그에 부합하는 _xlnm.* 레코드가 자동으로 생성됩니다

전형적인 함정은 이 예약 영역을 수동으로 편집하려 시도하는 것입니다. PrintArea 프로퍼티를 수정하는 상태에서 DefinedNames.Add를 통해 _xlnm.Print_Area 이름을 별개로 직접 추가하면 파일 내에 동일 예약어에 대해 두 개의 상충하는 정의가 혼재하게 되며, 이로 인해 Excel이 비정상적으로 구동되게 만듭니다. _xlnm.으로 시작하는 모든 식별자는 프로퍼티 전용으로 간주하십시오. 인쇄 설정을 검사할 때도 이름 테이블 대신 프로퍼티 필드를 조회해야 합니다. 보안 및 페이지 인쇄 설정 가이드에서 관련 프로퍼티들을 상세히 소개합니다

설계를 완성하기 전에 알아두어야 할 두 가지 제약 조건

정의된 이름은 구형 XLS 파일을 XLSX 파일로 일괄 변환해 주는 SaveXLSWorkbookAsXLSX 브리지 함수를 관통하지 못합니다. 이 브리지 메서드는 셀 데이터와 기초 서식만 복사하도록 사양이 작성되어 있으며 이름 정의 테이블 복사는 지원하지 않으므로, 변환 완료 후 대상 파일에 이름 정보가 누락됩니다. 따라서 파일 형식 변경 후 DefinedNames.Add를 사용하여 이름을 재구성해야 합니다. 이 단계는 번거로워 보이지만, XLS의 모호한 스코프 설정을 XLSX 플랫폼에 맞춰 정교하게 재조정하는 계기가 될 수 있습니다

또 다른 제약은 수식 문자열과 시트명 사이의 어긋남(Drift) 현상입니다. Excel 프로그램 상에서 시트 이름을 변경하면 수식이나 이름 내의 시트 참조 주소가 일제히 자동 조율되어 정합성이 유지됩니다. 그러나 자동화 프로그램의 소스 코드 수준에서 문자열을 조합해 수식을 만들 때, 특정 곳의 시트명 참조와 시트 생성 시 명칭을 다르게 입력하면 유실된 시트 참조 주소 오류가 발생하게 됩니다. 따라서 시트명을 단일 Delphi 상수로 선언하여 시트 생성 함수인 Sheets.Add와 수식 문자열 결합 로직에 공통 공급하십시오. 이는 원시 셀 주소인 B17 등에 값을 직접 붓는 대신 출력 셀에 이름을 지정하여 사용하는 개발 원칙과 결이 같습니다. 디자이너가 합계 행 위에 행을 추가하더라도 이름 참조 방식은 계속 안전하게 셀을 지시하지만 리터럴 주소 방식은 엉뚱한 셀에 데이터를 기입하게 됩니다. 템플릿 기반 보고서 생성 가이드에서 이 구조를 적극 활용합니다

두 포맷 전체의 정의된 이름 API 및 수식 엔진 사양서는 HotXLS 컴포넌트와 함께 제공됩니다