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

기술 문서

Excel이 캐시된 #SPILL!과 #CALC! 에러를 무시하는 이유

HotXLS Delphi Component

Excel은 워크북이 완전한 모던 에러 계약을 갖추었을 때만 캐시된 #SPILL!이나 #CALC!를 믿습니다: XLRICHVALUE 확장 URI가 소문자 GUID인 Rich Data 메타데이터, 7의 fileVersion, 191029 이상의 calcId, 그리고 microsoft.com:RD 계산 기능입니다. 네이티브 Delphi XLSX 컴포넌트인 HotXLS는 저장된 셀이 그런 에러를 담고 있을 때마다 네 가지를 모두 내보냅니다

한 조각이라도 빠지면 실패는 좀처럼 요란하지 않습니다. 가끔은 Excel 16이 아예 파일 열기를 거부합니다. 더 잦은 것은 멀쩡히 열리는데 여러분의 계산 엔진이 #SPILL!을 낸 자리에 #VALUE!를 보여 주는 경우로, 계산 버그처럼 보여서 완벽하게 올바른 수식 코드를 뒤지게 만듭니다. F9를 누르면 올바른 에러가 나타나므로, 자동 계산을 끈 채 리포트를 여는 고객이 나타나기 전까지 문제는 "내 머신에서는 된다"로 닫히는 경향이 있습니다. 이 글은 각 조각이 하는 일, 있을 수 없어 보이는 GUID 캐이싱 함정, 그리고 정확히 같은 조용한 방식으로 실패하는 배열 텍스트 캐시 규칙을 짚어 갑니다

Excel은 캐시된 #SPILL! 대신 #VALUE!를 보여 주는 이유

Excel이 #VALUE!를 보여 주는 이유는 셀이 문자 그대로 그것을 저장하기 때문입니다. ECMA-376 SpreadsheetML의 레거시 에러 타입에는 에러 리터럴의 고정 목록이 있고, #SPILL!과 #CALC!는 그 목록에 없습니다. t="e"인 셀은 #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A만 말할 수 있으므로, 새 Excel 버전들은 하위 호환 폴백으로 #VALUE!를 쓰고 진짜 에러 타입을 값 메타데이터 인덱스에 매달아 둡니다

그 인덱스, 즉 vm 속성은 xl/metadata.xml을 가리키고, 이것은 실제 에러를 기술하는 Rich Data 값을 가리킵니다. 낡은 리더는 #VALUE!를 보고 지나갑니다. 요즘 Excel은 체인을 따라가 모던 에러를 표시하지만, 그 체인이 신뢰할 만하다고 판단할 때만 그렇게 합니다. 흘러넘쳤다가 막힌 수식이 디스크에서 어떤 모습인지는 다음과 같습니다:

<!-- sheet1.xml: 레거시 리터럴과 값 메타데이터 포인터 -->
<c r="A1" t="e" cm="1" vm="1">
  <f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
  <v>#VALUE!</v>
</c>

<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
  <k n="errorType" t="i"/>
  <k n="colOffset" t="i"/>
  <k n="rwOffset" t="i"/>
</s>

<!-- xl/richData/rdrichvalue.xml: errorType 8은 #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS는 #SPILL!에 대해 열과 행 오프셋을 곁들인 에러 타입 8을, #CALC!에 대해 오프셋 없는 에러 타입 13을 씁니다. 레거시 <v>는 두 경우 모두 의도적으로 #VALUE!로 남습니다. Rich Data를 무시하는 어떤 리더가 봐야 할 값이 바로 그것이기 때문입니다

Excel 16이 실제로 검사하는 소문자 GUID

XLRICHVALUE 퓨처 메타데이터 블록은 확장 URI {3e2802c4-a4d2-4d8b-9148-e3be6c30e623}를 글자 하나까지 소문자로 써야 합니다. 16진 글자를 대문자로 올리면 로컬 Excel 16 빌드는 모든 XML 파트가 파싱되고 모든 관계가 풀리고 모든 개수와 인덱스가 맞아떨어지는데도 워크북 열기를 정상적으로 거부했습니다

<futureMetadata name="XLRICHVALUE" count="1">
  <bk>
    <extLst>
      <ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
        <xlrd:rvb i="0"/>
      </ext>
    </extLst>
  </bk>
</futureMetadata>
<valueMetadata count="1">
  <bk><rc t="2" v="0"/></bk>
</valueMetadata>

COM이나 레지스트리를 다뤄 본 사람이라면 GUID가 대소문자 구분 없이 비교된다는 걸 알고, 바로 그 습관이 여기서 물어 뜯습니다. 우리는 느린 방식으로 고립시켰습니다: Excel이 거부하는 최소 패키지를 만들고 한 번에 하나씩 바꿔 본 것이죠. 콘텐츠 타입을 바꿔도 고쳐지지 않았습니다. 리치 값 타입 파트를 더해도 고쳐지지 않았습니다. 네임스페이스 접두어를 바꾸거나 리치 값 데이터를 다시 써도 고쳐지지 않았습니다. 그 단 하나의 uri 속성을 소문자로 바꾸니 고쳐졌고, 같은 패키지가 이어서 열려 #CALC!와 #SPILL!을 모두 표시했습니다. 확장 URI는 불투명한 문자열 리터럴로 취급하고, 포맷 정의에서 복사하고, 자기 케이싱을 골라 대는 GUID 포매터에 절대 돌리지 마세요

진단 패키지를 손으로 쓰는 사람에게 실용적인 따름정리가 있습니다. 직렬화된 XML에 걸친 전역 텍스트 치환이 아니라 XML 파서로 편집하세요. 하나의 토큰을 겨냥한 일괄 치환은 파트의 다른 곳에 있는 &gt; 같은 엔티티를 망가뜨릴 수 있고, 그러면 원래의 질문 대신 망가진 프로브를 디버깅하게 됩니다

Excel이 캐시된 모던 에러를 읽는 데 그 외에 필요한 것

메타데이터 파트와 그 관계들은 필요하지만 충분하지 않습니다. 완벽하게 유효한 Rich Data 체인이 갖춰져 있어도, 워크북 파트가 자신이 모던 계산 엔진에서 왔다고 선언하기 전까지는 Excel이 여전히 캐시된 에러를 무시했습니다. HotXLS는 저장된 셀이 #SPILL!이나 #CALC!를 담고 있을 때마다 세 가지 선언을 workbook.xml에 기록합니다:

<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
  <ext uri="...">
    <calcFeatures>
      <feature name="microsoft.com:RD"/>
    </calcFeatures>
  </ext>
</extLst>

fileVersion 요소는 평범한 ECMA-376 Part 1 워크북 메타데이터이고, 7의 lastEdited / lowestEdited는 파일이 Rich Data를 이해하는 애플리케이션 세대에 의해 마지막으로 저장되었음을 Excel에게 말해 줍니다. calcPr의 calcId는 계산 엔진 버전을 식별하며, 191029는 우리가 찾은 이 캐시들에 대해 Excel이 받아들이는 최솟값입니다. http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures 네임스페이스의 계산 기능 확장 안의 microsoft.com:RD 항목은 캐시된 값들을 만든 엔진이 Rich Data 의미론으로 평가했음을 선언합니다. 서브타입만, 타입 파트만, 또는 다른 접두어와 관계 경로만 제공하는 것은 결과를 바꾸지 못했습니다. 세 선언이 함께 있어야 했습니다

이 값들을 직접 설정한다면 HotXLS가 적용하는 방식의 세 디테일이 알아 둘 가치가 있습니다:

  • TXLSXWorkbook.CalcId는 덮어쓰이지 않습니다. 작성기는 내보내는 값만 출력에서 191029로 올리므로, 더 높은 CalcId를 설정한 호출자는 그것을 유지하고, 메모리 내 속성은 여러분이 할당한 그대로입니다
  • 열린 파일과 함께 들어온 기존 계산 기능과 무관한 워크북 확장은 교체되지 않고 보존됩니다
  • 작성기는 추가하기 전에 기존 microsoft.com:RD 기능이 있는지 확인하므로, 같은 워크북을 반복 저장해도 중복 선언이 쌓이지 않습니다
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1은 3행으로 흘러넘침; A2가 점유되어 결과는 #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // 더 높은 CalcId는 살아남음; 작성기는 더 낮을 때만 191029로 올림
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // 리치 에러 파트는 자동 기록
  finally
    Book.Free;
  end;
end;

Rich Data 파트는 직접 만들 일이 없습니다. 쓰기 전에 TXLSXWorkbook은 모든 셀을 훑어 #SPILL!이나 #CALC! 값을 찾고, xl/metadata.xml과 두 리치 값 파트를 만들거나 확장하며, 에러가 그사이 바뀌었거나 지워진 셀에서 낡은 값 메타데이터 링크를 떼어 냅니다. 위의 명시적 CalcId 할당은 선택 사항이며, 존중된다는 점을 보이기 위해 보여 준 것뿐입니다

배열 텍스트 캐시는 공유 문자열이 아니라 t="str"에 속합니다

같은 조용한 실패가 배열 안의 텍스트 결과에서도 나타납니다. HotXLS는 수식 셀의 캐시된 텍스트, 그리고 CSE나 동적 배열 범위의 모든 추종 셀의 텍스트를 <v> 안의 리터럴 문자열과 함께 t="str"로 저장합니다. 그 캐시들을 t="s"의 공유 문자열로 쓰면 Excel이 캐시된 파일을 읽을 때 배열 셀에 #VALUE!를 표시합니다

<!-- 올바름: 배열 추종 셀의 리터럴 텍스트 캐시 -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- 틀림: 공유 문자열 인덱스, Excel이 캐시에서 #VALUE!를 표시 -->
<c r="B3" t="s"><v>17</v></c>

이것은 틀리기 쉽습니다. t="s"가 평범한 값 셀에는 옳은 선택이고 대부분의 작성기는 기본으로 모든 문자열을 공유 문자열 테이블로 보내기 때문입니다. 함정은 검증에 있습니다. Excel에서 재계산하면 배열 셀은 올바른 텍스트를 보여 주는데, 이는 수식이 멀쩡하다는 증거일 뿐 여러분이 쓴 캐시에 대해서는 아무것도 증명하지 않습니다. 사용자가 수동 계산으로 리포트를 열거나, 하류 도구가 계산 없이 캐시된 값을 읽는다면 공유 문자열 버전은 그냥 깨진 것입니다

수식 저장 문법: 선행 등호, 쉼표, _xlws

같은 작성기에 관련 정리가 하나 들어갑니다: OOXML로 들어가는 수식은 사용자가 입력한 문법이 아니라 파일 문법으로 저장됩니다. HotXLS는 여러분이 Formula에 할당한 텍스트를 왕복 표시용으로 그대로 유지하지만, 직렬화된 수식은 선행 =를 떼고, 함수 인수 사이에 쉼표를 쓰며, 배열 상수 행 구분자와 따옴표 텍스트는 그대로 둡니다. FILTER와 SORT는 그 워크시트 전용 함수에 대해 Excel이 기대하는 _xlfn._xlws. 접두어로 저장되고, SEQUENCE 같은 다른 퓨처 함수는 평범한 _xlfn. 접두어를 받습니다

// 세미콜론 구분자로 입력, 다음과 같이 저장
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // 1000 초과 행이 없으면 #CALC!, 리치 데이터와 함께 저장

작성기 안에서는 순서가 중요합니다. XML 이스케이핑은 문법 정규화 뒤에, 절대 앞에 오지 않습니다. 진단 패키지에 파서가 필요한 것과 같은 이유로, 이미 이스케이프된 텍스트에 적용된 구분자 재작성은 비교 연산자의 &gt; 같은 엔티티를 망가뜨립니다

스스로를 속이지 않고 캐시된 에러 검증하기

믿을 만한 유일한 테스트는 계산이 이미 수동으로 설정된 상태로 Excel에서 파일을 열고, 셀을 읽고, 그다음에야 재계산하는 것입니다. Excel은 세션에서 처음 연 워크북에서 계산 모드를 물려받으므로, 우리의 인수 하니스는 먼저 자기 시드 워크북을 만들고 수동으로 전환한 뒤 테스트 대상 파일을 엽니다. 시드 단계를 건너뛴 프로브는 파일이 열리고 올바르게 계산된다는 것만 증명했습니다. 캐시라고 부른 것들은 이미 재계산되어 있었죠

그 구분이 이 글 전체의 축소판입니다. 모던 에러 캐시는 어떤 계산도 돌기 전에 Excel이 표시할 때 올바른 것이고, 소문자 GUID부터 t="str" 텍스트 추종 셀까지 위의 모든 요구 사항은 재계산된 상태가 아니라 그 상태를 확인해서 발견되었습니다. HotXLS가 애초에 스플 범위를 어떻게 크기 정하고 소유하는지에 대한 배경은 HotXLS와 함께 Delphi에서 동적 배열 스플 수식을 참고하세요. 증상이 잘못된 에러가 아니라 복구 프롬프트라면 Excel이 정상적인 XLSX를 복구하는 이유의 패키지 수준 규칙이 더 나은 출발점입니다

FAQ

#SPILL!과 #CALC!를 올바르게 저장하려면 HotXLS에 어떤 설정이 필요한가요?

아니요. HotXLS는 저장 시점에 #SPILL!과 #CALC! 값을 감지해서 Rich Data 파트, fileVersion, 최소 calcId, microsoft.com:RD 기능을 자동으로 기록합니다

셀 XML에 여전히 #VALUE!가 들어 있는 이유는 무엇인가요?

레거시 SpreadsheetML 에러 타입에는 #SPILL!이나 #CALC!에 대한 리터럴이 없으므로, #VALUE!가 하위 호환 폴백입니다. 요즘 Excel은 vm 값 메타데이터 링크를 통해 진짜 에러를 읽습니다

HotXLS는 내가 직접 설정한 CalcId를 낮추나요?

아니요. TXLSXWorkbook.CalcId는 모던 에러가 있을 때 기록된 파일에서 191029로만 올라가고, 여러분이 설정한 더 높은 값은 그대로 유지됩니다

HotXLS는 자체 계산 엔진으로, 동적 배열을 포함해 Delphi와 C++Builder에서 XLSX, XLSM, 레거시 XLS를 읽고 씁니다. 세부 사항과 체험판 다운로드는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 있습니다