Excel이 37로 보여 주는 전체 합계가 프로그램이 .xls 파일을 열고 셀 하나도 건드리지 않고 저장한 뒤에는 67로 돌아올 수 있습니다. 늘어난 30은 무작위가 아닙니다. 전체 합계 범위 안에 들어 있는 두 그룹 소계 10과 20의 합과 정확히 같습니다. 이 숫자가 움직이려면 두 가지가 동시에 틀어져야 합니다. 저장이 나가는 길에 모든 수식을 재계산하는 것, 그리고 재계산하는 주체가 SUBTOTAL은 다른 SUBTOTAL 셀을 건너뛴다는 사실을 잊는 것입니다. 하나만 제거해도 파일은 무사합니다. 그래서 첫 번째 문제가 두 번째 뒤에 숨은 채 드러나지 않을 수 있습니다
XLS 파일을 저장하면 왜 수식 값이 바뀔까
저장이 수식 값을 바꾸는 경우는 작성기가 각 수식 옆에 저장할 숫자를 로드한 것에서 복사하는 대신 직접 계산할 때입니다. 모든 BIFF8 Formula 레코드는 8바이트 FormulaValue 필드([MS-XLS] §2.4.127, 값 레이아웃은 §2.5.133)를 갖고, 그곳에는 Excel이 그 셀에 대해 마지막으로 보여 준 결과가 들어 있습니다. 작성기는 저 여덟 바이트에 무언가를 넣어야 하고, 가장 짧은 코드 경로는 모든 수식을 계산 엔진에 넘기는 것입니다. 일단 그렇게 되면 저장은 아무도 요청하지 않은 전체 재계산이 되고, 그 엔진이 Excel과 다른 지점마다 사용자가 손대지 않았다고 믿는 파일에서 숫자가 바뀝니다
SUBTOTAL 불일치가 좋은 예입니다. 스프레드시트 규칙은 단순한데 함정이 Delphi의 Variant 타이핑에 있기 때문입니다. Excel은 모든 SUBTOTAL 함수 번호를, 자체 수식이 또 다른 SUBTOTAL인 셀을 무시하도록 정의합니다. 상세 행을 모두 더하면 37이 되는 시트(두 그룹이 각각 10과 20으로 소계되는 행들에 어느 그룹에도 속하지 않는 7이 더해진 구성)에서 전체 합계는 37입니다. 중첩 소계를 알아채지 못하는 엔진은 10과 20을 한 번 더 세어 67에 도달합니다. 알아채는 일은 보통 수식 텍스트에 대한 짧은 검사로 처리되는데, 자연스러워 보이는 Delphi 버전은 컴파일도 되고 실행도 되면서 아무것도 결코 매치하지 못합니다
// 직접 작성하는 헬퍼입니다. 예를 들어 시트를 훑으며
// 바깥 소계가 건너뛰어야 할 수식을 찾을 때 씁니다
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// 끌리는 검사는 Unicode Delphi에서 결코 성공하지 않습니다. Variant에
// 대입된 String은 varOleStr이 아니라 varUString입니다
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // varString, varOleStr, varUString을 모두 받습니다
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(');
end;
// HotXLS와 함께 쓰는 예(시트, 행, 열은 1부터):
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...HotXLS 평가기가 같은 질문에 적용하는 검사가 VarIsStr이고, 이 검사는 Excel이 그러하듯 AGGREGATE 셀도 바깥 소계에서 제외 대상으로 취급합니다. 다만 술어를 고치는 것이 이야기의 전부는 아닙니다. 올바른 검사를 갖춰도 재계산하는 저장이 37을 쓰는 것은 엔진이 이 특정 파일에서 우연히 Excel과 같기 때문입니다. 엔진이 조금이라도 다르게 처리하는 다음 수식은 또 움직입니다. SUBTOTAL과 AGGREGATE가 숨겨진 행을 포함해 어떤 셀을 건너뛰는지의 규칙은 SUBTOTAL과 AGGREGATE 숨겨진 행 글에 정리되어 있습니다. 캐시 우선 저장에서는 계산을 요청한 적 없는 파일이 엔진이 뭐라 판단했든 셀에 37을 담은 채 디스크로 돌아갑니다
클래식 XLS 저장이 각 Formula 레코드에 쓰는 것
HotXLS는 BIFF8 워크북을 캐시 우선으로 저장합니다. 각 수식 셀에 대해 TXLSWorkbook.SaveAs는 TryGetCachedFormulaValue가 값을 보고해 주면 그 값을 쓰고, 보고하지 않을 때만 평가기를 돌립니다. 기준은 캐시 상태입니다. xlfcsLoaded(값이 소스 파일에서 왔음)나 xlfcsCalculated(HotXLS가 이번 세션에서 계산함) 상태의 셀은 CacheInfo.Value가 바이트 그대로 기록됩니다. xlfcsMissing이나 xlfcsInvalidated 상태의 셀은 저장 시점에 평가됩니다. Excel이 저장을 다루는 방식과 같습니다. 각 셀이 보여 주는 것의 스냅샷이며, 수동 계산 모드에서는 크게 낡았을 수 있어도 충실히 그대로 씁니다
평가 경로가 남아 있는 데는 이유가 있습니다. 코드에서 Cells[Row, Col].Formula로 대입한 수식에는 아직 캐시가 없고, 로드된 셀에 새 수식을 대입하면 그 셀은 xlfcsInvalidated로 옮겨 갑니다. 낡은 결과는 낡은 식의 것이기 때문입니다. 둘 다 저장 중에 계산되므로 처음부터 생성한 워크북은 Excel에서 0이 아니라 계산된 숫자를 보이며 열립니다. 평가기조차 값을 만들지 못할 때(지원하지 않는 함수, 깨진 외부 링크)는 작성기가 0을 저장하고 §2.4.127의 레코드 플래그 비트 0인 fAlwaysCalc를 켜서, Excel이 자리표시자를 믿는 대신 열 때 셀을 재계산하게 합니다
관련 있어 보이지만 그렇지 않은 속성이 하나 있습니다. TXLSWorkbook.RecalcOnSave는 CalcSaveRecalc 레코드([MS-XLS] §2.4.37)의 Excel "저장하기 전에 통합 문서 다시 계산" 옵션을 반영합니다. 이것은 Excel에만, 그것도 수동 계산 모드에서만 의미가 있고, HotXLS는 그저 파일에 써 넣을 뿐입니다. SaveAs는 FormulaValue에 무엇이 들어갈지 정할 때 이 속성을 보지 않습니다. 아래 왕복이 자기 파일 하나에서 이 계약을 확인하는 가장 빠른 길입니다
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// 시트, 행, 열은 1부터 시작합니다
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // 캐시된 셀은 저장된 그대로 기록됩니다
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// 기대: After.Value가 Before.Value와 같고 After.State는
// 다시 xlfcsLoaded입니다. 저장된 파일에서 왔기 때문입니다
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;이 계약의 읽는 쪽, 즉 각 상태가 무엇을 뜻하는지 그리고 캐시된 빈 값이나 False도 여전히 값으로 치는 이유는 Read Excel Cached Formula Values in Delphi Without Recalc에 설명되어 있습니다. 위 검사를 중첩 소계가 있는 워크북의 전체 합계 셀에 돌리면 두 줄이 같은 숫자를 인쇄합니다
BIFF 공유 수식 루트는 캐시 값을 어디에 두나
다른 모든 수식 셀과 마찬가지로 자기 Formula 레코드에 들어 있습니다. 다만 식이 한 레코드 뒤에 도착하고, 무심한 리더가 캐시를 잃는 지점이 바로 이 간격입니다. BIFF8은 공유 수식을 그룹의 왼쪽 위 셀 Formula 레코드 바로 뒤에 놓이는 ShrFmla 레코드([MS-XLS] §2.4.260)로 저장합니다. 루트를 포함한 모든 멤버 셀은 PtgExp 토큰 하나(§2.5.198)로 이루어진 rgce를 갖습니다. 바이트 $01 뒤에 루트의 행과 열이 붙는 형태입니다. 팔로워 셀은 쉽습니다. 나타나는 시점에는 루트의 식이 이미 존재하고 각자의 FormulaValue는 자기 레코드에 들어 있기 때문입니다. 루트는 쉽지 않습니다. Formula 레코드를 읽는 시점에 루트는 캐시 값 37을 갖고 있지만 아직 식이 없습니다
셀에 식을 장착하면 캐시가 지워지고, 그것은 참으로 남아야 합니다. 교체된 수식은 실제로 낡은 결과를 무의미하게 만들기 때문입니다. 그러나 같은 규칙을 ShrFmla 레코드에 무심하게 적용하면 식이 도착하는 순간 루트의 37이 버려지고, TryGetCachedFormulaValue는 루트를 xlfcsMissing으로 보고하며, 저장은 사용자가 가장 먼저 들여다볼 바로 그 셀에 대해 평가기로 폴백합니다. HotXLS 리더는 ShrFmla 레코드를 기다리는 동안 루트의 디코딩된 값을 들고 있다가, 식을 장착한 뒤 상태 xlfcsLoaded로 로드된 값을 되돌려 놓습니다. 배열 수식(Array 레코드, §2.4.4)도 같은 레코드 순서를 따르므로 같은 처리를 받습니다. String 형태의 캐시는 애초에 이 문제가 없습니다. 그 텍스트는 별도의 String 레코드로 도착하고 행과 열로 셀에 매칭되므로, 스트림에서의 위치가 중요하지 않기 때문입니다
OOXML 쪽도 다룬다면 XLSX 공유 수식 si 확장 글이 패키지 형식에는 레코드 순서 간격이 없지만 자체적인 확장 함정을 안고 온다는 점을 설명합니다
공유 수식 팔로워는 식의 시프트된 사본을 소유합니다
ShrFmla 레코드의 식은 루트 셀 기준으로 쓰여 있으므로, 각 팔로워는 루트와의 거리만큼 displaced된 자기 사본이 필요합니다. 축자 복사하면 B1에 뿌리를 둔 =A1*3 그룹이 B2와 B3에게 똑같은 =A1*3을 쥐여 줍니다. HotXLS는 각 팔로워에게 상대 참조가 팔로워의 행·열 거리만큼 밀린 사본을 주므로, B2는 진짜 =A2*3을, B3는 =A3*3을 소유합니다
이것이 평범한 왕복으로는 팔로워 식이 올바른지 알 수 없는 이유이기도 합니다. 팔로워는 로드된 값을 저장을 통해 유지하므로 축자 복사도 통과합니다. 무언가를 증명하는 검사는 입력을 바꾸고 재계산해야 합니다. 아래 루틴은 UseSharedFormulas를 켠 채(기본값은 False이며, 셀마다 Formula 레코드 하나를 씁니다) 공유 그룹을 쓰고, 파일을 다시 열고, A2를 편집하고 B2를 지켜 봅니다
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3이 하나의 ShrFmla 그룹이 됩니다
Book.SaveAs(FileName); // 아직 캐시 없음: 6, 12, 18로 평가됩니다
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // 팔로워의 입력을 편집
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // 기대: 12, 아직 로드됨
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // 기대: 30, =A2*3에서
finally
Book.Free;
end;
end;=A1*3을 재사용한 팔로워였다면 두 번째 줄에서 6을 인쇄했을 것입니다. 첫 번째 줄도 똑같이 중요합니다. 리터럴 편집은 무언가 재계산하기 전까지 종속 수식의 로드된 값을 제자리에 둔다는 것이 보입니다
캐시 우선 저장이 사용자에게 맡기는 것
캐시 우선 저장은 로드된 것을 보존합니다. 로드된 것이 여전히 참인지는 추적하지 않습니다. 수식이 의존하는 셀을 바꾸면 다음 재계산을 위한 의존성 그래프가 표시되지만, 종속 셀은 xlfcsLoaded 값을 그대로 갖고 있고, 먼저 Recalculate를 호출하거나 셀의 Value를 읽지 않는 한(읽으면 수식이 계산되고 상태가 xlfcsCalculated로 옮겨갑니다) SaveAs는 그 낡은 숫자를 씁니다. Recalculate는 평가하지 못한 수식 셀의 개수를 반환하므로, 0이 아닌 결과는 저장 전에 로그로 남을 가치가 있습니다
흔한 파이프라인은 프로그램이 남이 만든 워크북을 열고 레이블 몇 개나 헤더를 채워 저장합니다. 그 파일에서 값이 바뀌어야 할 것은 아무것도 없고, 캐시 우선 저장에서는 실제로 아무것도 바뀌지 않습니다. 입력을 편집하는 프로그램이 대신 재계산 단계를 소유하며, 그 단계는 편집 곁에, 코드를 읽는 누구나 볼 수 있는 Recalculate 호출 하나에 있어야 합니다. Excel도 수동 계산 모드에서 같은 절충을 합니다. XLSX 작성기에는 자체 정책이 있습니다. XLSX 워크북 클래스에 설정하는 RecalcBeforeSave의 xrmManual, xrmIfDirty, xrmAlways 모드이며, 위의 모든 것과 독립적입니다
이 설계에서 두 가지 경계가 따라 나옵니다. 캐시 경로는 xlfcsLoaded나 xlfcsCalculated 상태의 셀에만 도움이 되므로, 수천 개의 새 수식을 쓰면서 평가하지 않는 생성기는 저장 중에 셀당 한 번의 평가를 여전히 지불합니다. 그리고 이 계약은 저장이 쓰는 것을 다룰 뿐, 명시적인 재계산이 계산하는 것을 다루지 않습니다. 손대지 않은 파일은 Excel이 저장한 숫자를 유지하고, Recalculate는 HotXLS 평가기의 결과를 씁니다. 파이프라인을 재계산 저장으로 전환하기 전에 몇 개의 대표적인 워크북에서 둘을 비교해 보세요
HotXLS Delphi Spreadsheet Component는 Excel이나 OLE 자동화 설치 없이 Delphi와 C++Builder에서 클래식 BIFF8과 XLSX 워크북을 읽고 씁니다. 제품 페이지에 이 예제들이 쓰는 워크북, 캐시 값, 재계산 호출의 API 레퍼런스가 있습니다