델파이 HotXLS 수식 엔진과 사용자 정의 함수

수식 문자열만 저장하는 스프레드시트 라이브러리와 제대로 동작하는 수식 엔진을 갖춘 라이브러리는 서로 다른 제품인데, 둘 중 하나에 숫자를 물어보는 순간까지는 똑같아 보입니다. 대부분의 델파이 스프레드시트 코드는 그 틈을 알아채지 못합니다. Excel이 덮어 주기 때문입니다. 셀에 SUM(B2:B501)을 쓰고 저장하면, 사람이 파일을 여는 즉시 Excel이 합계를 다시 계산합니다. 그 사람을 고리에서 빼고 같은 통합 문서를 CSV로 바로 내보내는 서버 파이프라인에 태우면, 그 차이는 더 이상 학술적인 것이 아니게 됩니다. 숫자가 있어야 할 자리에 CSV가 =SUM(B2:B501)이라는 글자 그대로의 텍스트를 담습니다. 어느 지점에서도 실제로 수식을 평가한 것이 없었기 때문입니다

HotXLS는 그 경계선의 옳은 쪽에 서 있습니다. HotXLS는 파일 형식이 그러하듯 수식을 저장된 텍스트에 선택적 캐시 결과가 딸린 것으로 다루므로, 맨 CSV 내보내기는 요리가 아니라 조리법을 재현합니다. 그러면서 직접 호출할 수 있는 계산 엔진도 함께 싣고 있는데, XLS 파사드와 XLSX 파사드 양쪽에서 같은 엔진이며, 엔진이 들어 본 적 없는 함수 이름을 해결하는 훅도 딸려 있습니다. HotXLS는 Excel 자동화 없이 델파이와 C++Builder에서 XLS와 XLSX를 읽고 쓰는 네이티브 Object Pascal 라이브러리이고, 그 중 계산 절반이 저장된 수식을 필요할 때 값으로 되돌리는 부분입니다

수식은 저장되며, 미리 평가되지 않습니다

셀에 수식을 쓴다고 무엇이 계산되지는 않습니다. 저장 시점에 통합 문서는 수식 텍스트를 기록합니다. XLS 쪽에서는 RecalcOnSave가 지배하는 플래그도 함께 기록하는데, 이 속성은 기본값이 True이며 Excel에게 열 때 재계산하라고 알립니다. 그 모델은 Excel로 갈 파일에는 맞고, 셀 값을 직접 소비하는 파이프라인에는 틀립니다. CSV 내보내기든 HTML 내보내기든 셀을 되읽는 여러분 자신의 코드든 마찬가지입니다. 그런 경우에는 Calculate로 명시적으로 평가하십시오. 이 메서드는 네 곳의 진입점에 있습니다. TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook, TXLSXWorksheet가 모두 function Calculate(const Formula: WideString): Variant를 드러냅니다

// 프로세스 안에서 평가한 다음, 조리법이 아니라 값을 보냅니다
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // 이제 CSV가 숫자를 담습니다

Calculate에 건네는 식은 평범한 Excel 수식 텍스트입니다. 시트 간 참조, 정의된 이름, 중첩 함수가 모두 현재 메모리 안 통합 문서에 대해 해결되므로, 이 호출은 CSV 내보내기를 땜질하는 것을 훨씬 넘어 쓸모가 있습니다. 이것을 단언 장치로 다루십시오. 방금 상세 행 오백 개를 쓴 생성기가 통합 문서에게 그 자신의 총합계를 물어보고, Pascal에서 독립적으로 계산한 수치와 비교하면, 고객사 감사인이 알아채기 전에 하나 어긋난 범위 오류를 잡을 수 있습니다

이것은 수식이 많은 출력에 맞는 테스트 전략도 잡아 줍니다. Excel은 여전히 수식 언어의 참조 구현이므로, 사업적 결과가 걸린 소수의 수식에 대해서는 기대값을 Excel 자신이 만들어 낸 승인된 고정 파일을 보관하고, 빌드 파이프라인이 생성된 통합 문서의 수식을 Calculate로 평가해 그 고정값과 맞춰 보게 하십시오. 그러면 차이가 두 보고서를 비교하던 고객이 발견하는 불일치가 아니라 델파이에서 실패하는 테스트로 떠오릅니다

OnUserFunction으로 업무 함수 추가하기

엔진이 알아보지 못하는 함수 이름을 만나면 곧장 실패하는 대신 이벤트를 일으킵니다. 어느 통합 문서 클래스에서든 OnUserFunction을 할당하면 그 호출을 직접 해결할 수 있습니다:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args는 Variant 배열로 도착합니다
    Handled := True;
  end;
end;

// 연결과 사용
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

세 가지 세부 사항이 눈길을 받을 만합니다. 첫째, 이름을 실제로 알아본 때에만 Handled := True로 설정하십시오. False로 두면 엔진이 평소의 알 수 없는 함수 처리를 이어 가므로, 처리기 하나가 지나가는 모든 것을 자기 것이라 주장하지 않으면서 여러 통합 문서를 섬길 수 있습니다. 둘째, 수식 작성자가 discount(와 DISCOUNT(를 가리지 않고 치므로 이름은 SameText로 대소문자를 무시해 비교하십시오. 셋째, 인수는 미리 평가되어 도착합니다. DISCOUNT(A1)은 참조가 아니라 A1의 값을 건네므로, 함수는 자기 입력이 어디서 왔는지 알 수 없습니다. 그 마지막 항목이 다음 절이 다룰 한계를 예고합니다

처리기 본문은 다른 어떤 외부 진입점과 똑같이 방어적으로 다루십시오. Args 배열은 수식 작성자가 친 것을 그대로 반영하므로, 인덱싱하기 전에 인수 개수와 형을 검증하고, 잘못된 호출이 무엇을 돌려줄지를 미리 정하십시오. Variant 오류 값인지, 아니면 일으킨 예외인지 말입니다. 처리기 안에서 던진 예외는 평가를 촉발한 Calculate 호출을 거쳐 밖으로 전파되므로 이 선택은 중요합니다. 빡빡하게 통제되는 생성기에서는 받아들일 만하고, 사용자가 작성한 통합 문서를 평가하는 서비스에서는 무례합니다. 후자에서는 나쁜 수식 하나가 요청 전체를 무너뜨립니다. 그런 환경에서는 처리기 안에서 잡고, 둘러싼 작업 흐름이 알아보고 기록할 수 있는 표식 값을 돌려주십시오

위치를 아는 함수에는 Ex 변형이 필요합니다

어떤 함수는 어디에서 평가되는지에 정당하게 의존합니다. 시트마다 다른 요율, 행에 상대적인 조회, 지역 시트에서만 적용되는 지역별 승수. 이 중 어느 것도 인수 값만으로는 답할 수 없습니다. 평범한 이벤트는 그것을 표현하지 못하므로, 엔진은 매개변수 하나만 더 붙었을 뿐 나머지는 똑같은 OnUserFunctionEx를 제공합니다:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // 같은 수식이 지역 시트마다 다른 요율을 냅니다
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext는 평가 중인 셀의 SheetIndex, Row, Col을 실어 나릅니다. 어떤 함수의 결과가 조금이라도 자기 위치에 의존한다면 처음부터 Ex 이벤트를 연결하십시오. 이미 수식 서른 개가 부르고 있는 처리기에 문맥을 뒤늦게 끼워 넣는 일은 첫날에 맞는 시그니처를 고르는 것보다 훨씬 지저분하고, 두 이벤트는 그 밖에는 너무 비슷해서 더 좁은 쪽으로 시작할 이유가 별로 없습니다

사용자 정의 함수는 Excel까지 따라가지 않습니다

사용자 정의 함수는 전적으로 여러분의 프로세스 안에서만 삽니다. DISCOUNT라는 이름은 여러분의 델파이 코드와 그 이벤트 처리기가 돌고 있는 동안에만 무엇인가를 뜻합니다. 저장된 파일을 Excel에서 열면 DISCOUNT는 그저 알아보지 못하는 이름이고, 사용자 컴퓨터에 마침 짝이 맞는 VBA 함수나 추가 기능이 있지 않은 한 셀은 #NAME?을 보여 줍니다. 이것이 데모와 출하 가능한 제품을 가르는 설계상의 사실이며, 나중에 발견하는 대신 일부러 내려야 할 선택을 강요합니다

셀 단위로, 두 계약 중 어느 쪽을 출하하는지 정하십시오. 사용자가 Excel 안에서 재계산되는 것을 보아야 할 셀은 Excel 자신의 함수 어휘만으로 지어야 합니다. 논리가 독점적인 셀은 Calculate로 프로세스 안에서 평가해 평범한 값으로 저장해야 하며, 그러면 사용자 정의 함수는 파일 내용이 아니라 내부 계산 규칙으로 행동합니다. 어김없이 지원 문의를 만들어 내는 실패 유형은 그 중간 지대입니다. 사용자 정의 함수 수식을 저장해 두고 Excel이 그것을 존중해 주기를 기대하는 것 말입니다

값만 담는 계약에는 조용한 이점이 하나 있습니다. 지적 재산을 보호한다는 것입니다. 여러분의 델파이 프로세스에서 평가되어 숫자로 출하된 가격 규칙은 보이는 수식처럼 역공학으로 파헤칠 수 없고, 사용자가 중간 셀을 고쳐서 망가뜨릴 수도 없습니다. 청구서 생성기, 수수료 명세서, 요율표는 거의 언제나 이쪽 진영에 속합니다. 살아 있는 수식이 정말로 필요한 경우는 대화형 what-if 모델입니다. 고객이 입력을 바꾸고 합계가 움직이는 것을 보리라 기대되는 경우이며, 그런 것은 Excel 자신의 어휘에 정의된 이름을 더해 지어야 합니다

계산 모드, 반복, R1C1: XLS 파사드의 다이얼

XLS 파사드는 Excel이 파일에서 읽는 BIFF 수준 계산 설정을 드러냅니다. CalculationMode는 xlCalcManual, xlCalcAutomatic(기본값), xlCalcAutomaticExceptTables를 받으며, 파일이 열린 뒤 Excel이 어떻게 행동할지를 정합니다. 수식이 수천 개인 모델 통합 문서는 수동 모드로 전달하는 편이 흔히 더 친절합니다. 재계산 폭풍이 언제 일어날지를 받는 사람이 정하기 때문입니다. EnableIteration(기본값 False)은 MaxIterations(기본값 100), MaxIterationChange(기본값 0.001)와 함께, 일부 금융 모델에 나타나는 반복 수렴 종류의 의도적인 순환 참조를 풀어 줍니다. ReferenceStyle은 A1 표시와 R1C1 표시를 오가고, UseFullPrecision은 Excel의 표시된 정밀도 사용 옵션을 반영합니다

이 속성들이 XLS 파사드에 있는 것은 BIFF 레코드에 대응하기 때문입니다. .xlsx를 생성할 때는 수식이 반복 설정에 의존하지 않도록 계획하거나, 수렴된 값을 델파이에서 계산해 결과를 쓰십시오

배열 수식: 공개 진입점은 XLSX입니다

예전 CSE 방식 배열 수식은 TXLSXRange.SetArrayFormula로 만듭니다:

// A2:A4에 걸친 배열 수식 하나
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

같은 메서드가 XLS 클래스 계층에도 있지만 private 구역에 앉아 있어서, .xls 파일에 새 배열 수식을 작성할 지원되는 방법은 없습니다. 열린 파일에 이미 있던 것은 온전히 왕복하며, 못 하는 것은 새로 만드는 일입니다. 여기서 따라 나오는 규칙은 충분히 간단합니다. 배열 의미론이 요구 사항의 일부라면 .xlsx를 목표로 삼으십시오. 예전 .xls 산출물이 정말로 배열 동작을 필요로 한다면, 실용적인 경로는 배열 결과를 델파이에서 계산해 개별 값을 셀에 쓰는 것입니다

이 사이트에서 관련해 읽을 것 두 가지: 정의된 이름과 시트 간 수식은 엔진이 수행하는 이름 해결을 다루고, CSV·TSV 내보내기 글은 명시적 계산을 필요하게 만드는 내보내기 동작을 자세히 설명합니다. 지원되는 함수 집합을 포함한 전체 엔진 참조 문서는 HotXLS Delphi Component와 함께 제공됩니다