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

기술 문서

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

HotXLS Component

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

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

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

포맷이 의도적으로 수식을 한 번만 저장하기 때문입니다. 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, trimmed to the interesting cells
  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)
    // Follower one row down: relative row moves, absolute row frozen,
    // the mixed A$1 keeps its row, and the literal stays a 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] 같은 구조화된 표 참조는 손대지 않은 채 남습니다. 대괄호 안 부분은 좌표가 아니라 열 이름이기 때문입니다

// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1       : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// 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
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the 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를 해석할 수 없는 팔로워는 대기 큐로 들어갑니다. 시트가 끝나면 그 큐는 이제 완전해진 테이블에 대해 다시 재생되며, 늦게 온 마스터들이 자신의 고아들을 해석합니다. 결코 마스터를 찾지 못하는 셀은 빈 수식을 유지하는데, 이는 정의된 적 없는 그룹을 참조하는 파일에 대한 정직한 결과입니다

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

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

var
  Reader: TXLSDirectReader;
  Cursor: TXLSRowCursor;
begin
  // Projection: only rows 2..3, only column A. The master lives in row 1,
  // outside the projection, and is still parsed so the followers resolve
  Reader:= TXLSDirectReader.Create;
  try
    Reader.FirstRow:= 2;
    Reader.LastRow:= 3;
    Reader.IncludeColumn(1);
    Reader.OnCell:= HandleCell;   // Cell.Formula is fully expanded here
    Reader.ReadFile(FileName);
  finally
    Reader.Free;
  end;

  // Forward-only row traversal, same expansion
  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 레퍼런스가 있습니다