Delphi XLS 저장이 수식을 조용히 재계산하지 않게 하기

네이티브 Delphi 및 C++Builder Excel 라이브러리인 HotXLS는 클래식 BIFF8 .xls 통합 문서를 캐시 우선으로 저장합니다. TXLSWorksheet.WriteFormula가 TXLSWorkbook.TryGetCachedFormulaValue에 Excel이 각 수식 옆에 저장해 둔 값을 물어보고, 그 캐시가 없거나 무효화된 경우에만 평가기를 호출합니다. 열어만 두고 건드리지 않은 통합 문서는 같은 숫자를 그대로 되쓰고, 새 결과가 필요하면 SaveAs의 숨은 부작용이 아니라 명시적인 Recalculate 호출 한 번으로 얻습니다

이 계약을 세상에 드러나게 만든 버그는 민망할 만큼 작았습니다. nested-subtotals.xls라는 코퍼스 파일에는 R2C4에 캐시 값 37인 총합계가 있습니다. HotXLS로 열고 그 셀에 TryGetCachedFormulaValue를 물으면 37입니다. 셀 하나도 바꾸지 않고 저장하고, 저장된 사본을 열어 같은 질문을 하면 67입니다. API에 계산을 요청한 적이 없는데 파일 안의 숫자가 정확히 30만큼 움직였고, 그 30은 총합계가 덮는 범위 안에 있는 두 그룹 소계 10과 20의 합입니다

XLS 파일을 저장하면 수식 값이 바뀌는 이유

37이 67이 되려면 독립적인 결함 두 개가 나란히 맞아떨어져야 했고, 어느 하나만 고쳤다면 다른 하나를 가렸을 것입니다. 첫 번째는 구조적이었습니다. 클래식 작성기가 저장할 때마다 모든 수식을 재계산했습니다. 두 번째는 디스크에서 로드한 수식에는 결코 참이 될 수 없는 타입 검사였고, 그 때문에 평가기가 중첩된 SUBTOTAL 셀을 두 번 세었습니다. 코퍼스 파일은 저장 시 재계산이 Excel과 다른 답을 내놓고 누군가 둘을 비교한 최초의 입력이었을 뿐입니다. 구조적 결함은 말하기 쉽습니다. v2.382.3 이전에는 TXLSWorksheet.WriteFormula와 그 공유 수식 형제인 WriteFormulaWithTExp가 모든 Formula 레코드의 8바이트 FormulaValue 필드를 TXLSWorkbook.GetFormulaValue, 즉 평가기를 호출해 얻었습니다. ParseFormula가 로드 시 소스 파일에서 공들여 디코드해 둔 캐시는 나가는 길에 전혀 참조되지 않았습니다. 사실상 저장할 때마다 통합 문서 수준 재계산 API를 우회한 전체 재계산이었으므로, 통합 문서에 무엇을 설정해도 막을 수 없었습니다. HotXLS 평가기가 Excel과 어긋나는 모든 지점 — 정당하게 지원하지 않는 함수든 단순한 버그든 — 이 저장 시 조용한 데이터 변경이 되었습니다

두 번째 결함은 평가기가 쓰는 중첩 소계 콜백에 있었습니다. Excel은 모든 SUBTOTAL 형태가 자기 수식이 또 다른 SUBTOTAL인 셀을 무시하도록 정의하므로, lxCalc.pas의 계산기는 집계 중에 FIgnoreSubtotalCells를 켜고 통합 문서에 TXLSWorkbook.GetClassicIsSubtotalCell로 범위의 각 셀이 그런지 물어봅니다. 그 콜백은 수식 텍스트를 Variant로 가져와 VarType(f) = varOleStr로 검사했습니다. 텍스트는 GetUnCompiledFormula에서 Delphi String으로 돌아오고, String을 Variant에 대입하면 varUString이며 결코 varOleStr이 아닙니다. 그 술어는 로드한 모든 파일의 모든 셀에 대해 거짓이었고, 그룹 소계가 총합계에 두 번 합산되었으며, 모든 것을 재계산하는 저장에서 10 + 20 + 7이 67이 되었습니다

// HotXLS 2.381 이전: String으로 만든 수식 Variant는
// varUString이므로 이 비교는 결코 성공하지 못함
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr은 varString, varOleStr, varUString을 받고,
// AGGREGATE도 Excel처럼 바깥 소계에서 제외됨
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

v2.382.0은 VarIsStr 수정을 내보냈고, 같은 함수에 있는 김에 콜백이 AGGREGATE 셀도 바깥 소계에서 제외된다는 것을 배우게 했습니다. 그것만으로 코퍼스 단언이 통과했는데, 재계산된 37이 로드한 37과 이제 일치했기 때문입니다. 하지만 그것이 라이브러리를 정직하게 만들지는 않았습니다. 저장은 여전히 재계산하고 있었고, 테스트는 평가기가 그 특정 파일에서 우연히 Excel과 일치했기 때문에 초록이었을 뿐입니다. 숨김 행을 포함해 SUBTOTAL과 AGGREGATE가 어떤 셀을 건너뛰는지에 대한 규칙은 SUBTOTAL과 AGGREGATE 숨김 행 글에서 다룹니다. 여기서 중요한 것은 계산을 요청하지 않은 파일에 대해 어떤 평가기도 발언권을 가져서는 안 된다는 것입니다

저장 시 캐시 값에 대해 Excel이 보장하는 것

Excel은 저장을 계산 이벤트가 아니라 스냅숏으로 취급합니다. Formula 레코드의 FormulaValue 필드([MS-XLS] §2.4.127, 레이아웃은 §2.5.133)에 쓰이는 값은 그 셀이 현재 표시하는 값이며, 수동 계산 모드에서는 몇 년 전 값일 수도 있는데도 Excel은 그것을 충실히 기록합니다. 재계산은 자체 트리거를 가진 별개 연산입니다. 이제 HotXLS도 클래식 저장에 같은 규칙을 따릅니다. WriteFormula와 WriteFormulaWithTExp가 TryGetCachedFormulaValue를 먼저 호출해 상태가 xlfcsLoaded 또는 xlfcsCalculated면 CacheInfo.Value를 취하고, xlfcsMissing과 xlfcsInvalidated에 대해서만 GetFormulaValue로 넘어갑니다. 각 상태의 의미와 캐시된 빈 값이나 False가 왜 여전히 값으로 세어지는지를 포함한 이 계약의 읽기 쪽 절반은 재계산 없이 Delphi에서 Excel 캐시 수식 값 읽기에 설명되어 있습니다

HotXLS의 모든 클래식 XLS 저장이 내리는 캐시 우선 결정: WriteFormula와 WriteFormulaWithTExp가 TryGetCachedFormulaValue를 호출하고, 상태가 xlfcsLoaded나 xlfcsCalculated면 CacheInfo.Value를 그대로 쓰며, xlfcsMissing이나 xlfcsInvalidated면 GetFormulaValue 평가기로 폴백하고, 평가기마저 실패하면 fAlwaysCalc를 설정한 0 페이로드를 써서 Excel이 열 때 다시 계산하게 합니다 — 세션에서 할당한 수식은 캐시 없이 도착하고 교체한 수식은 무효화되므로 둘 다 저장 시 평가되어 생성한 통합 문서가 숫자와 함께 열리며, 열어만 두고 건드리지 않은 파일은 Excel이 저장한 값을 유지합니다

폴백 경로는 제거되지 않고 의도적으로 남아 있습니다. 이 세션에서 Cells[Row, Col].Formula로 할당한 수식은 캐시 없이 도착하고, 로드한 셀의 수식을 교체하면 _SetCompiledFormula가 xlfcsInvalidated로 표시합니다. 둘 다 예전처럼 저장 시 평가되므로 생성한 통합 문서는 여전히 숫자를 담은 채 Excel에서 열립니다. 평가기조차 값을 만들지 못하면 작성기는 0 페이로드를 내보내고 fAlwaysCalc(§2.4.127의 grbit 비트 0)를 설정해, Excel이 그 자리표시자를 믿는 대신 열 때 셀을 다시 계산하게 합니다

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // 시트, 행, 열은 1 기반: 첫 시트의 R2C4
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // 캐시된 셀에는 평가기가 관여하지 않음
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // nested-subtotals.xls에서 Before.Value = After.Value = 37
    // 재계산하는 저장이었다면 여기에 67을 썼을 것
  finally
    Book.Free;
  end;
end;

BIFF 공유 수식 루트는 캐시 값을 어디에 둘까

다른 모든 수식 셀처럼 자기 Formula 레코드에 두며, 바로 그 점 때문에 공유 그룹의 루트 셀이 캐시 우선 저장이 여전히 지던 유일한 자리가 되었습니다. BIFF8의 공유 수식은 왼쪽 위 셀의 Formula 레코드를 뒤따르는 ShrFmla 레코드([MS-XLS] §2.4.260)로 저장되며, 루트를 포함한 모든 구성 셀이 단일 PtgExp 토큰(§2.5.198)으로 이루어진 rgce를 지닙니다. 파싱된 식의 첫 바이트가 $01이고 그 뒤에 루트 셀의 행과 열이 옵니다. 팔로워 셀들은 자기완결적입니다. HotXLS가 각자의 FormulaValue를 읽고 루트의 컴파일된 수식을 조회해 식을 해석합니다. 루트 셀은 다릅니다. Formula 레코드를 파싱할 때 식이 아직 존재하지 않고, 한 레코드 뒤에 도착하기 때문입니다

그 한 레코드의 틈에서 캐시가 사라졌습니다. TXLSReader.ParseFormula는 캐시 값을 디코드하고, 좌표가 셀 자신과 같은 PtgExp를 보면 그 셀을 FSharedFormulaRow와 FSharedFormulaCol에 기억하고 캐시를 셀에 게시합니다. ShrFmla 레코드($04BC)가 도착하면 ParseSharedFormula가 식을 컴파일해 _SetCompiledFormula로 설치하고, _SetCompiledFormula는 수식 변경이면 마땅히 해야 할 일을 합니다. FCachedFormulaValue를 지우고 상태를 xlfcsMissing으로 되돌립니다. 그래서 루트가 로드한 37은 아무도 읽기 전에 버려졌고, TryGetCachedFormulaValue는 루트를 캐시 없음으로 보고했으며, 캐시 우선 작성기는 모두가 쳐다보는 바로 그 셀에 대해 평가기로 충실히 폴백했습니다. Array 레코드(§2.4.4)도 같은 순서를 공유하며 같은 구멍이 있었습니다

v2.382.3의 수정은 대기 중인 루트 좌표 옆에 세 번째 필드 FSharedFormulaCachedValue를 추가합니다. ParseFormula는 루트를 인식하면 디코드한 캐시를 거기에 넣어 두고, ParseSharedFormula와 ParseArrayFormula가 컴파일된 식을 설치한 직후 _SetCellCachedFormulaValue로 그것을 재생한 뒤 보관 값을 Unassigned로 되돌립니다. 캐시의 String 변형은 이 모든 것에 영향받지 않습니다. 페이로드가 별도의 String 레코드로 도착하고 레코드 순서가 아니라 셀 좌표로 라우팅되기 때문입니다. 같은 개념의 OOXML 쪽을 다룬다면, XLSX 공유 수식 si 확장 글에서 패키지 형식에는 이 순서 문제가 없는 대신 나름의 확장 함정이 있는 이유를 설명합니다

HotXLS에서 BIFF 공유 수식의 루트 셀이 캐시된 37을 잃은 이유: Formula 레코드가 PtgExp 토큰과 디코드된 캐시를 지니고, ShrFmla 식이 한 레코드 뒤에 도착하며, _SetCompiledFormula로 설치하면서 상태가 xlfcsMissing으로 되돌아갔고, v2.382.3이 FSharedFormulaCachedValue를 보관해 _SetCellCachedFormulaValue로 재생하기 시작했습니다 — Array 레코드도 같은 한 레코드 틈을 가졌고 ParseArrayFormula가 같은 방식으로 보관 값을 재생하며, String 캐시 변형은 셀 좌표로 라우팅되어 애초에 레코드 순서에 의존한 적이 없습니다

공유 수식 팔로워에 상대 이동이 필요한 이유

ShrFmla에 저장된 식이 루트 셀을 기준으로 쓰이므로, 그것을 그대로 재사용하는 팔로워는 자기 참조가 아니라 루트의 참조를 계산하기 때문입니다. 예전 리더는 각 팔로워에 Value.GetCopy()를 설치했는데, 변위가 없는 깊은 복사본이라 B1을 루트로 하고 =A1*3인 그룹은 모든 팔로워도 =A1*3이 되었습니다. 캐시 우선 저장이 로드한 파일에서는 이 문제를 가려 주었습니다. 팔로워가 자기 FormulaValue를 지니고 있어 저장을 올바르게 하는 데 식이 필요하지 않았기 때문입니다. 무언가가 재계산하는 순간 드러났습니다. 이제 리더는 TXLSCompiledFormula.GetCopy(row - srow, col - scol)를 설치하며, 구문 트리를 훑어 모든 상대 참조를 루트로부터의 팔로워 거리만큼 옮기므로 B2의 팔로워는 진짜 =A2*3을 갖습니다

두 동작을 모두 고정하는 회귀 테스트는 우연이 통과하는 것을 허용하지 않으므로 읽어 볼 가치가 있습니다. 입력 2와 4에 대해 =A1*3과 =A2*3이 있는 통합 문서를 만들고, _SetCellCachedFormulaValue로 일부러 틀린 캐시 999와 888을 심는데, UseSharedFormulas를 켠 경우와 끈 경우에 한 번씩 합니다. 저장하고 다시 로드한 뒤에도 두 셀은 여전히 999와 888을 보고해야 하며, 이는 저장이 루트와 팔로워 캐시 어느 쪽도 건드리지 않았다는 증거입니다. 명시적 Recalculate 뒤에야 6과 12가 되어야 하며, 이는 팔로워의 이동된 식이 올바르다는 증거입니다. 참값을 심는 테스트는 예전 작성기에서도 통과했을 것이고, 그래서 일부러 틀린 값을 심는 것입니다

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // 입력을 변경

    // 종속 수식의 로드된 캐시는 리터럴 편집으로 무효화되지
    // 않으므로 그냥 SaveAs하면 예전 숫자가 유지됨
    // 새 결과가 정말 필요할 때 재계산을 요청할 것:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

캐시 우선 계약이 해 주지 않는 것

캐시 우선 저장은 로드한 것을 보존할 뿐, 그것이 아직 참인지는 추적하지 않습니다. 수식이 의존하는 리터럴을 바꾸면 평가기를 위해 의존성 그래프가 더러워지지만, 종속 셀의 xlfcsLoaded 캐시는 그대로 남고, 클래식 작성기는 Recalculate를 호출하거나 셀의 Value를 먼저 읽어 계산하고 상태를 xlfcsCalculated로 옮기지 않는 한 그 오래된 값을 기꺼이 씁니다. Excel이 수동 계산 모드에서 하는 것과 같은 맞바꿈이고, 서드파티 파일을 열어 라벨 몇 개를 고치고 저장하는 파이프라인에는 올바른 선택입니다. 다만 입력을 편집하는 통합 문서는 재계산 단계를 스스로 책임져야 합니다. XLSX 작성기의 RecalcBeforeSave 정책은 이번 작업으로 바뀌지 않았고, 같은 정신으로 캐시를 보존하는 자체 수동 모드를 갖습니다. 여기서 작은 경계 두 개가 따라옵니다. 캐시 우선 경로는 상태가 xlfcsLoaded나 xlfcsCalculated인 셀에만 도움이 되며, 수식을 쓰고 평가한 적 없는 생성기는 예전과 똑같이 저장 시 셀마다 한 번의 평가 비용을 냅니다. 그리고 중첩 소계 수정은 평가기가 건너뛰는 셀을 바로잡는 것이지 평가기가 구현한 모든 함수를 바로잡는 것이 아닙니다. HotXLS가 Excel과 동일하게 계산할 수 없는 수식이 있는 파일도 이제 건드리지 않고 왕복하는 것은 안전하지만, 그 파일에 대해 의도적으로 Recalculate를 하면 라이브러리의 답이 나오고, 재계산한 저장을 믿기 전에 둘을 비교해야 합니다

캐시 우선 클래식 저장과 복원된 공유 및 배열 수식 루트 캐시, 공유 팔로워의 상대 참조 이동, 바로잡힌 SUBTOTAL과 AGGREGATE 중첩 규칙은 모두 Delphi와 C++Builder용 표준 HotXLS Delphi Spreadsheet Component에 실려 있으며, Excel이나 어떤 OLE 자동화 서버에도 의존하지 않습니다. 제품 페이지에 여기서 사용한 통합 문서, 캐시 리더, 재계산 진입점의 전체 API 레퍼런스가 있습니다