HotXLS로 Delphi에서 실시간 워크북 간 수식 사용하기

HotXLS는 =[Rates.xlsx]FX!B4*C10 같은 수식을, 오래된 캐시 값이 아니라 메모리에 열려 있는 다른 워크북을 대상으로 평가할 수 있습니다. RegisterExternalWorkbook으로 두 번째 워크북을 이름으로 등록하면, 이후 모든 재계산은 참조된 셀을 그 객체에서 직접 읽어옵니다. 등록을 해제하면 평가는 파일이 이미 담고 있는 외부 링크 캐시로 되돌아갑니다

이는 특정하고도 짜증스러운 공백을 메워 줍니다. Excel 워크북은 서로를 참조하는 일이 일상적입니다: 환율 테이블, 계정 과목표, 다른 팀이 관리하는 가격 목록 같은 것들입니다. 이런 파일을 Excel이 아니라 라이브러리가 열면, 그런 참조는 보통 Excel이 마지막으로 재계산했을 때 캐시된 값으로 해석되는데, 이는 몇 달 전 값일 수도 있으며, 아무리 재계산을 반복해도 데이터가 애초에 존재하지 않으므로 고쳐지지 않습니다

워크북 간 참조는 파일 내부에서 어떤 모습인가?

외부 참조는 수식 입력줄에 보이는 텍스트 그대로 저장되지 않습니다. 워크북은 참조 대상 워크북들의 테이블을 유지하고, 각 수식 토큰은 그 테이블의 인덱스를 가리키며, 별도의 캐시된 값 파트가 참조되는 각 셀의 마지막으로 알려진 결과를 담고 있습니다. 그래서 함께 있어야 할 파일 없이 열어도 숫자가 여전히 표시되는 것입니다

텍스트 형태를 파싱하는 데는 나름의 미묘함이 있습니다. 렉서는 대괄호로 묶인 워크북 이름을 하나의 식별자의 일부로 취급하므로, [Book.xlsx]Sheet!A1은 짝이 맞지 않는 대괄호 다음에 시트 참조가 오는 것이 아니라 하나의 토큰이 됩니다. 이름은 그런 다음 조회를 위해 정규화됩니다: 인용 부호, 경로 접두사, 파일 확장자, 숫자 인덱스 형태가 모두 대문자로 된 기본 이름 하나로 합쳐지므로, 'C:\Rates\[Rates.xlsx]FX'!B4와 [rates.xlsx]FX!B4는 동일하게 등록된 워크북으로 해석됩니다

워크북 등록과 재계산

등록은 이름으로 이루어지며, 그 이름은 참조하는 쪽 워크북의 수식에 실제로 나타나는 바로 그 이름입니다:

uses
  lxHandleX;

var
  Main, Rates: TXLSXWorkbook;
begin
  Main := TXLSXWorkbook.Create;
  Rates := TXLSXWorkbook.Create;
  try
    if (Main.Open('valuation.xlsx') <> 1) or
       (Rates.Open('rates.xlsx') <> 1) then
      Exit;

    // valuation.xlsx 안에서 [Rates.xlsx]...라고 되어 있는 수식은 이제
    // 캐시된 외부 값이 아니라 실시간 객체에서 값을 읽어옵니다
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));

    // 원본 값을 바꾸고 다시 재계산: 새 값이 그대로 반영됨
    Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
    Main.Recalculate;
    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
  finally
    Main.UnregisterExternalWorkbook('Rates.xlsx');
    Rates.Free;
    Main.Free;
  end;
end;

등록은 약한 참조를 유지합니다. HotXLS는 등록된 워크북의 소유권을 가져가지 않으며 이를 해제하지도 않으므로, 수명 관리는 사용자의 책임이고 순서가 중요합니다: 원본 워크북을 해제하기 전에 등록을 해제하거나, 최소한 해제한 뒤에는 재계산하지 마세요

워크북이 등록되지 않으면 어떻게 되는가?

평가는 파일에 저장된 외부 링크 캐시로 되돌아가는데, 이는 함께 있어야 할 파일 없이 열린 워크북의 동작과 정확히 같습니다. 이 폴백이 있기 때문에 등록 해제가 파괴적이지 않고 안전한 것입니다

등록 해제 이후의 동작은 사람들을 놀라게 하는 부분이므로 정확히 짚어 둘 가치가 있습니다. 참조가 등록된 워크북에서도 캐시에서도 해석될 수 없을 때, 평가는 오류를 만들어내지만 그 오류는 셀에 다시 기록되지 않습니다. 셀은 마지막으로 계산된 값을 유지합니다. 원본을 다시 등록하고 재계산하면 값은 다시 갱신됩니다

이 실패 시 값 유지 규칙은 의도적인 것입니다. 원본을 사용할 수 없게 된 순간 모든 종속 셀에 오류 값을 기록하는 대안은, 일시적으로 누락된 환율 테이블을 나중에 성공한 재계산이 고쳐야 할 오류 표시로 가득 찬 워크북으로 바꿔 버릴 것입니다. 마지막으로 유효했던 값을 유지하면 중단된 파이프라인이라도 여전히 말이 되는 문서가 남습니다

더티 전파는 워크북 경계에서 멈춘다

등록된 원본에서 셀을 편집해도 메인 워크북의 종속 셀이 더티로 표시되지는 않습니다. 증분 재계산은 하나의 워크북 안에서만 의존성을 추적하며, 경계를 넘어서까지 감시하지는 않습니다. Excel도 똑같이 동작하는데, 이 때문에 Excel의 외부 참조는 계속 갱신되는 것이 아니라 열 때나 명시적인 새로 고침 시에만 갱신되는 것입니다

실무적으로 그 결과는, 원본 값을 바꾸려면 위 예제처럼 참조하는 워크북을 명시적으로 재계산해야 한다는 것입니다. 애플리케이션이 반복문 안에서 두 워크북을 모두 편집한다면, 편집할 때마다가 아니라 마지막에 한 번만 재계산하세요. 증분 재계산과 의존성 그래프에서 설명하는 증분 엔진은 그러면 각 워크북 안에서 최소한의 작업만 수행합니다

이 기능이 들어맞는 세 가지 형태

통합이 가장 명백한 경우입니다. 상위 워크북이 열두 개의 월별 파일을 참조합니다. 열두 개 모두를 등록하고 한 번 재계산한 다음, Excel을 한 번도 열지 않고 통합된 수치를 읽으면 됩니다. 각각의 등록 이름은 상위 워크북의 수식이 실제로 말하는 이름과 일치해야 하며, 이는 파일 이름과 일치할 것이라고 가정하기보다 수식 텍스트를 직접 확인해 볼 가치가 있습니다

시나리오 분석이 두 번째입니다. 원본을 등록하고 그 안에 일련의 가정을 기록한 다음, 종속 모델을 재계산하고 결과를 기록하고 이를 반복합니다. 원본이 실시간 객체이므로 반복 사이에 파일이 전혀 기록되지 않으며, 100개의 시나리오는 100번의 저장 후 재로드 주기가 아니라 100번의 재계산만 필요로 합니다

인도 전 검증이 세 번째이자 가장 눈에 띄지 않는 경우입니다. 워크북을 열고, 함께 있어야 할 파일들을 등록하고, 재계산한 다음, 그 결과를 파일이 이미 담고 있던 값과 비교합니다. 불일치는 캐시된 값이 오래되었다는 뜻이며, 이는 고객에게 파일이 전달되기 전에 잡아낼 가치가 있는 결함입니다. 이 비교는 두 Excel 워크북 비교하기에서 설명하는 워크북 diff와 자연스럽게 짝을 이룹니다

의존하기 전에 참조를 정리하라

먼저 수식 분석을 한 번 실행하세요. 이는 누락된 함수, 외부 참조, 순환 의존성 구성 요소를 한 번의 순회로 보고해 주므로, 파일을 찾아 나서기 전에 문서가 정확히 어떤 워크북 이름을 기대하는지 알 수 있습니다:

var
  Issues: TArray<TXLSXFormulaIssue>;
  I: Integer;
begin
  Issues := Main.AnalyzeFormulaIssues;
  for I := 0 to High(Issues) do
    if Issues[I].Kind = xlsxFormulaIssueExternalReference then
      Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
        [Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
         Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;

그 밖에 시간을 절약해 주는 습관이 두 가지 있습니다. 등록 이름은 표준 형태로 유지하세요 — 일반 파일 이름으로 등록하고, 수식 안의 인용 부호나 경로 접두사가 붙은 변형은 정규화가 처리하도록 두면 됩니다. 그리고 공급할 수 없는 파일을 참조하는 워크북은 읽기 전용 산출물로 취급하세요: 그것을 재계산해도 캐시된 값은 보존되지만, 종속 셀을 바꾸는 어떤 편집이든 일부 값은 최신이고 일부는 과거인 상태로 문서를 남기며, 어느 쪽이 어느 것인지 파일 안에는 아무 표시도 없습니다. 워크북 간에 수식을 복사하는 것은 자체 재바인딩 규칙을 가진 별개의 작업이며, 워크북 간 복사와 수식 재바인딩에서 다루고, 이름 정의된 범위는 정의된 이름과 시트 간 수식에서 설명하는 또 다른 층을 더합니다

실시간 워크북 간 평가, 증분 재계산, 수식 분석은 Delphi와 C++Builder를 위한 동일한 엔진에서 동작합니다. 전체 기능 목록은 HotXLS Delphi 스프레드시트 컴포넌트 페이지에서 확인할 수 있습니다