LlosLab Software Development BlogPDF, Delphi 및 문서 소프트웨어 개발자를 위한 엔지니어링 노트.

기술 문서

델파이 XLSX 공유 수식 si 전개: 함정들

HotXLS Delphi Component

XLSX의 공유 수식 팔로워는 수식 텍스트를 전혀 갖고 있지 않습니다. 그 <f t="shared" si="N"/> 요소는 시트 어딘가에 있는 마스터 셀을 가리키며, 리더는 마스터 수식을 행과 열 차이만큼 이동시켜 텍스트를 재구성해야 합니다. 델파이와 C++Builder용 HotXLS Delphi Component는 이 전개를 열기 시점에 수행하므로, 모든 팔로워가 완전한 수식을 보고합니다

실제 XLSX를 서드파티 라이브러리로 로드했다가 천 개짜리 수식 열에서 정확히 한 셀에만 텍스트가 있고 나머지 999개는 빈 문자열인 것을 본 적이 있다면, 여러분은 이 기능을 잘못된 쪽에서 만난 것입니다. 손상된 것은 아무것도 없습니다. 파일은 ECMA-376이 허용하는 일을 하고 있을 뿐이며, 리더는 그저 XML이 멈춘 지점에서 멈췄을 뿐입니다

XLSX 공유 수식 그룹 si=4 다이어그램: 마스터 셀 B1이 수식 텍스트를 저장하고 팔로워 B2와 B3는 빈 f 요소를 저장하며, HotXLS는 Delphi에서 열 때 셋 모두를 완전한 셀별 수식으로 확장
파일은 하나의 마스터 수식과 그 si 인덱스를 공유하는 빈 팔로워를 저장합니다. HotXLS는 열기 시점에 모든 팔로워를 완전한 수식 텍스트로 확장합니다

공유 수식 셀이 비어 있는 이유는 무엇인가

포맷이 의도적으로 수식을 한 번만 저장하기 때문입니다. ECMA-376 파트 1과 ISO/IEC 29500-1에서 <f> 요소(§18.3.1.40)는 ST_CellFormulaType 타입의 t 속성을 가지며, 값 shared는 이 셀이 si 속성으로 식별되는 그룹에 속한다는 뜻입니다. 그룹 안에서 정확히 하나의 셀, 즉 마스터만이 그룹이 적용되는 범위를 주는 ref 속성도 함께 가지며, 오직 그 셀만이 수식 텍스트를 요소 콘텐츠로 갖습니다. 그룹의 나머지 모든 셀은 팔로워입니다. t="shared"와 같은 si를 반복하지만 그 요소 콘텐츠는 비어 있습니다. Excel은 이런 그룹을 공격적으로 씁니다. 20만 행짜리 열에 대한 채우기 다운은 20만 개의 수식 문자열을 하나의 문자열과 19만 9999개의 작은 자리표시자 요소로 압축시키기 때문입니다. 절약은 실질적이며 그 비용은 전적으로 리더에게 떨어집니다: 전개 없이는 팔로워 자체로는 아무 의미가 없습니다

이동은 텍스트 복사가 아니라 변환이다

HotXLS는 같은 si 아래 등록된 마스터를 찾고, 마스터 앵커에서 현재 셀까지의 행과 열 차이를 계산한 다음, 마스터 수식의 모든 참조를 그 차이만큼 변환하는 방식으로 팔로워를 해석합니다. 상대 차원은 이동하고, 절대 차원은 이동하지 않으며, 혼합 참조는 절대가 아닌 절반만 이동합니다. 문자열 리터럴은 완전히 건너뛰므로, 우연히 "A1"이라는 텍스트를 포함하는 수식은 모든 팔로워에서 그 텍스트를 변경 없이 유지합니다

const
  // xl/worksheets/sheet1.xml, 핵심 cell만 남김
  SheetXml: WideString=
    '<row r="1"><c r="A1"><v>1</v></c>'+
    '<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
    'A1+$A$1+A$1+$A1+&quot;A1&quot;+SUM(A1:A2)</f><v>7</v></c></row>'+
    '<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
    '<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb:= TXLSXWorkbook.Create;
  try
    Wb.Open(FileName);
    Sh:= Wb.Sheets[1];
    // Master, verbatim
    // B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
    // 한 row 아래 follower: relative row는 이동하고 absolute row는 고정
    // 혼합 A$1은 row를 유지하고 literal은 그대로 literal
    // B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
    ShowMessage(Sh.Cells[2, 2].Formula);
  finally
    Wb.Free;
  end;
end;

ref 속성은 장식이 아니라 게이트입니다. 좌표가 마스터의 적용 범위 밖에 있는 팔로워는 전개되지 않습니다. 그 경우 파일이 그룹이 뒷받침하지 않는 주장을 하고 있는 것이기 때문입니다. 마찬가지로, 이동이 참조를 1행 위나 A열 왼쪽으로 밀어내려 할 때 HotXLS는 그것을 조용히 고정하는 대신 그 토큰에 대해 #REF!를 방출합니다. 이는 같은 편집에 대해 Excel 자신이 만들어낼 결과와 같습니다. 이 변환은 행이나 열을 삽입하거나 삭제할 때 일어나는 참조 재작성과 사촌 관계이지만 같은 것은 아닙니다. 그 경로는 편집이 범위를 가로지를 때 무슨 일이 일어나는지에 대한 자체 규칙을 갖고 있으며, 삽입 및 삭제 중 수식 참조 조정에 관한 글에서 별도로 설명합니다. 공유 전개는 더 단순합니다: 알려진 앵커로부터의 순수한 오프셋이며, 파싱 시점에 한 번만 적용됩니다

이동 처리기가 커버해야 하는 참조 형태는 무엇인가

전부입니다, 그렇지 않으면 전개는 위장한 데이터 손실 버그입니다. A1과 A1:B2만 이해하는 순진한 이동 처리기는 더 특이한 형태들을 손상시키거나 누락시킬 것이며, 실제 워크북은 그런 형태로 가득합니다. HotXLS 공유 수식 변환기는 무엇을 옮길지 결정하기 전에 A1 계열 전체를 인식합니다. [Book.xlsx]Sheet1!A1 같은 외부 워크북 참조와 Sheet1:Sheet3!A1 같은 3D 참조는 접두사를 그대로 유지한 채 뒤쪽의 셀 참조만 이동합니다. 인용 부호로 묶인 시트 이름도 살아남습니다. 시트 이름이 문자 그대로 A1인 성가신 경우까지 포함해서, 'A1'!A1은 느낌표 뒤 부분만 이동합니다. 전체 열 A:A는 열 차원만 이동하고 다른 것은 이동하지 않습니다. 전체 행 1:1은 행 차원만 이동하고 다른 것은 이동하지 않습니다. $A:$A는 전혀 이동하지 않습니다. Table[A1] 같은 구조화된 표 참조는 손대지 않은 채 남습니다. 대괄호 안 부분은 좌표가 아니라 열 이름이기 때문입니다

Delphi에서 공유 수식 마스터 A1을 세 행 아래 A3까지 확장하는 다이어그램: B1과 1:1 같은 이동하는 토큰과, $C$1, 리터럴 A1, 전체 열, LOG10, 구조적 참조 같은 얼어붙는 토큰을 색으로 구분
시프터는 상대 부분을 델타만큼 옮기고 절대 참조, 문자열 리터럴, 통째 컬럼, 함수 이름, 구조화 참조는 얼립니다. 토큰 경계 검사가 LOG10이 LOG11이 되지 않게 지킵니다
// master 하나를 오른쪽으로 2 column, 아래로 0 row 확장
// Master D1: A1+A:A+$A:$A
// F1       : C1+C:C+$A:$A
//
// master 하나를 아래로 3 row, 가로로 0 column 확장
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3       : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// 두 번째 line에서 이동하지 않은 항목: absolute $C$1,
// string literal "A1", 순수 row delta 아래의 전체 column A:A
// function name LOG10, structured reference Table[A1]

함수 이름은 여기서 조용한 함정입니다. 문자 뒤에 숫자가 오는 것을 그냥 붙잡는 토큰 스캐너는 한 행 아래로 갈 때 LOG10을 LOG11로 기꺼이 재작성해 버릴 것입니다. HotXLS는 후보 토큰 앞뒤에 참조 경계가 있을 것을 요구하므로, 문자, 숫자, 밑줄, 점, 또는 여는 괄호로 이어지는 식별자는 셀 참조가 아닙니다. 다른 표기 체계로 작업 중이라면 같은 경계 문제가 다르게 나타나며, R1C1 표기법 글에서 두 모델이 어디서 갈라지는지 다룹니다

자체 종료 f 요소가 다음 값을 삼켜버리는 이유는 무엇인가

자체 종료 요소는 종료 요소 이벤트를 만들어내지 않기 때문입니다. 이것은 이 기능 전체에서 가장 값비싼 단일 버그이며, 특정 XML 파서에만 국한되지 않습니다. TXMLReader에서 <f t="shared" si="4"/>는 IsEmptyElement가 True로 설정된 채 정확히 하나의 Element 이벤트를 발생시키며, 짝을 이루는 EndElement는 결코 발생시키지 않습니다. 수식을 캡처하는 상태를 EndElement에서만 닫는 파서는 그래서 수식 안에 계속 머물게 되고, 다음에 보는 텍스트, 즉 <v> 안의 캐시된 결과값이 수식 버퍼에 덧붙여집니다. 더 나쁜 것은, 그 상태가 셀 경계를 넘어서까지 살아남으므로, 진짜 <f>를 가진 다음 셀의 수식 텍스트가 이전 셀에 흡수된다는 것입니다. 수정은 IsEmptyElement가 True일 때마다 Element 이벤트 자체에서 수식 상태를 종료시키고, 기다리는 대신 그 자리에서 전체 팔로워 해석을 실행하는 것입니다. 이는 속성으로부터 t, si, ref, aca, ca를 읽고, 공유 전개를 적용하고, 재계산 속성을 셀에 쓰고, 공유 상태를 지우는 것을 모두 빈 요소를 처리하는 분기 안에서 수행한다는 뜻입니다. 포맷은 <f t="shared" si="4"/>와 <f t="shared" si="4"></f> 두 가지 표기를 모두 허용하며, 두 번째 것은 실제로 EndElement를 발생시킨다는 점에 주목하십시오. 올바른 리더는 이 쌍을 동일하게 처리해야 하며, 그래서 HotXLS는 같은 회귀 테스트 파일에서 두 표기 모두를 다룹니다

희소하고 순서 없는 si 값과 대기 큐

si 속성은 파일이 제공하는 부호 없는 정수이지, 여러분이 통제하는 배열 위치가 아닙니다. 스키마의 어떤 것도 공유 인덱스가 조밀하거나, 0에서 시작하거나, 오름차순으로 나타날 것을 요구하지 않으며, 적대적이거나 그저 이상한 파일이 첫 번째 셀에 si="4294967290"을 쓰는 것을 막지 않습니다. 그래서 관찰된 가장 큰 si로 조회 배열의 크기를 정하는 것은 최적화가 아니라 메모리 고갈 원시 기법입니다. HotXLS는 대신 워크북을 여는 경로를 정렬된 희소 테이블 위에 유지합니다: 공유 그룹은 정렬된 TStringList에 정수 키로 등록되며, 이는 조회를 실제로 존재하는 그룹 수와 무관하게 이진 탐색으로 만듭니다. 순서는 문제의 두 번째 절반입니다. 마스터는 보통 문서 순서상 팔로워보다 먼저 오지만, 이는 규칙이 아니라 관례일 뿐이므로, 파싱되는 시점에 자신의 si를 해석할 수 없는 팔로워는 대기 큐로 들어갑니다. 시트가 끝나면 그 큐는 이제 완전해진 테이블에 대해 다시 재생되며, 늦게 온 마스터들이 자신의 고아들을 해석합니다. 결코 마스터를 찾지 못하는 셀은 빈 수식을 유지하는데, 이는 정의된 적 없는 그룹을 참조하는 파일에 대한 정직한 결과입니다

HotXLS: 파스 타임라인 다이어그램: si 마스터가 나중에 나타나는 팔로워 C5와 D1이 보류 큐에 들어가 시트 끝에 정렬된 스파스 테이블과 함께 재생되며, 최대 si로 배열 크기를 잡지 말라는 경고
마스터보다 먼저 파싱된 팔로워는 시트 끝에 재생되는 대기 큐에서 기다립니다. 정렬된 스파스 테이블이 조회 비용을 si의 숫자 크기가 아니라 그룹 수에 묶습니다

워크북을 로드하지 않고 공유 수식 전개하기

스트리밍 리더는 훨씬 빠듯한 메모리 예산 아래서 같은 요구사항에 직면하며, 워크시트 단위의 로컬 테이블로 그것을 해결합니다. TXLSDirectReader와 TXLSRowCursor 둘 다 자신의 경계 있는 메모리와 프로젝션 동작을 유지하면서 팔로워를 완전한 셀 단위 수식으로 전개하므로, 300MB 시트에 대한 전방향 단일 패스도 여전히 실제 수식 텍스트를 건네줍니다

var
  Reader: TXLSDirectReader;
  Cursor: TXLSRowCursor;
begin
  // Projection: row 2..3과 column A만 해당. master는 row 1에 있고
  // projection 밖이지만 follower 확인을 위해 여전히 parse됨
  Reader:= TXLSDirectReader.Create;
  try
    Reader.FirstRow:= 2;
    Reader.LastRow:= 3;
    Reader.IncludeColumn(1);
    Reader.OnCell:= HandleCell;   // 여기서는 Cell.Formula가 완전히 확장됨
    Reader.ReadFile(FileName);
  finally
    Reader.Free;
  end;

  // 앞으로만 row를 순회하며 동일하게 확장
  Cursor:= TXLSRowCursor.Create;
  try
    Cursor.Open(FileName);
    if Cursor.FindFirst then
      repeat
        if Cursor.CellCount > 0 then
          WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
      until not Cursor.FindNext;
  finally
    Cursor.Free;
  end;
end;

이 설계에서 두 가지 제약이 도출됩니다. 첫째, 프로젝션은 결코 마스터를 건너뛸 수 없습니다. FirstRow와 LastRow로 설정된 행 필터나 IncludeColumn으로 만든 열 필터는 마스터 셀을 콜백으로 방출하는 것은 건너뛸 수 있지만, 파서는 여전히 그 si, 앵커 좌표, 적용 범위, 수식 텍스트를 기록해야 합니다. 그렇지 않으면 프로젝션 안의 모든 팔로워가 아무것도 아닌 것으로 해석됩니다. 오직 팔로워 쪽 작업, 즉 이동과 값 디코딩만 안전하게 건너뛸 수 있습니다. 둘째, 테이블은 워크시트별로 존재하며 그 수명은 명시적으로 관리되어야 합니다: TXLSRowCursor는 시트 패스 동안 인스턴스 하나를 유지하며 재시작, 시트 전환, 파일 끝, 예외, 닫기 시점에 그것을 지우므로, 시트 1에서 정의된 그룹이 시트 2로 새어 들어갈 수 없습니다. 스트리밍 경로는 핫 루프이므로, 셀당 정수-문자열 변환을 피하기 위해 정렬된 문자열 테이블 대신 개방 주소 지정 정수 해시를 사용합니다

저장 시 무슨 일이 일어나며 경계는 어디에 있는가

일단 팔로워가 전개되면 그것은 평범한 수식이며, HotXLS는 그것을 t="shared"도 si도 없는 독립적인 <f> 요소로 다시 씁니다. 왕복은 안정적이며 캐시된 <v> 결과는 살아남지만, 출력은 대량으로 공유된 시트에서는 입력보다 커지며, Excel이 만든 그룹화는 저장 시 재구성되지 않습니다. 모든 셀에 실제 수식 텍스트를 갖는 것보다 공유 그룹의 바이트 수준 충실도가 여러분에게 더 중요하다면, 이것이 받아들이는 트레이드오프입니다. 참고로 XLS 쪽은 다릅니다: BIFF8의 SHRFMLA 레코드는 자체 인코딩과 자체 작성기를 가지며, 워크북에 공유 그룹 토글이 있습니다

같은 <f> 요소를 공유하지만 명시적으로 공유 수식이 아닌 관련 항목이 두 가지 있습니다. 레거시 CSE 배열 수식은 앵커된 범위를 커버하는 ref와 함께 t="array"를 사용하며, 동적 배열도 같은 t="array" 표기를 쓰지만 cellMetadata를 통해 XLDAPR 레코드로 이어지는 cm 속성으로 식별됩니다. 동적 배열 스필 셀을 공유 또는 CSE 팔로워로 취급하는 것은 진짜 정확성 버그이며, 그 구분은 동적 배열과 스필 수식에 관한 글에서 다룹니다. 이 세 가지 경우를 우연히 같은 태그 이름을 공유하는 세 개의 파서로 읽으면, 코드는 정직하게 유지됩니다

여기서 설명한 공유 수식 전개, 스트리밍 리더, 참조 변환기는 델파이와 C++Builder용 HotXLS Excel 컴포넌트의 일부로 제공됩니다. 제품 페이지에는 위에서 사용한 프로젝션 속성을 포함한 전체 수식 및 다이렉트 리드 API 레퍼런스가 있습니다