기술 문서

새 Excel 함수를 위한 _xlfn 접두사와 BIFF8 name 호출

Delphi가 만든 워크북은 =GAMMA(5)와 =XIRR(B2:B9,A2:A9)을 담고 있는데, Excel은 이를 열 때 GAMMA 셀에서는 #NAME?을, XIRR 셀에서는 올바른 결과를 보여 줍니다. HotXLS에서 XLSX에 _xlfn. 접두사를 달거나 BIFF8 .xls에서 name 기반 호출을 쓰는 함수들은 Excel이 그렇게 저장하는 것들이며, MS-XLSX 문법에서 벗어난 목록입니다

Excel은 GAMMA에는 #NAME?을 보여 주고 XIRR에는 그렇지 않은 이유

formula 텍스트는 맞고 저장이 틀렸습니다. XLSX 워크시트 안에서 formula는 <f> 요소의 평문 텍스트이며, Excel 2007 이후 추가된 함수는 _xlfn.NAME으로 적힙니다. 이 접두사는 reader에게 "아직 모를 수 있는 built-in 함수"라고 말해 주므로, 오래된 Excel은 호출을 사용자 정의 name으로 착각하지 않고 #NAME?을 보여 주고, 최신 Excel은 표시할 때 접두사를 벗겨 사용자가 =GAMMA(5)를 보게 합니다. 접두사를 빼면 최신 Excel은 GAMMA라는 정의된 name을 찾아 나서다 아무것도 못 찾고 #NAME?을 보여 줍니다. XIRR은 더 오래되었습니다. Analysis ToolPak과 함께 들어왔고 Excel은 맨몸으로 적으며, 접두사가 붙은 _xlfn.XIRR는 Excel이 만드는 것과 다른 토큰입니다. GAMMA, GAUSS, PHI, SHEET, SHEETS, XOR은 모두 접두사가 필요하고, Excel 2013이 engineering과 복소수 집합에 더한 여덟 함수(IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH, IMTAN)도 마찬가지입니다. 더 오래된 IMSIN, IMSQRT, IMSUM은 맨몸으로 남습니다. 워크시트 전용 배열 함수는 두 번째 접두사 _xlfn._xlws.를 달며, MS-XLSX §2.2.2가 FILTER, SORT, PY를 위해 예약한 것입니다. 읽을 때는 HotXLS가 두 접두사를 모두 제거하므로 Cell.Formula는 깔끔한 함수 이름을 반환합니다

Delphi용 HotXLS에서 새 Excel 함수의 저장 경로: GAMMA는 _xlfn 접두사를 달고 ToolPak XIRR은 맨몸으로 남습니다
접두사는 reader가 아직 모를 수 있는 built-in에 표식을 남기므로, 최신 Excel은 이를 벗기고 오래된 버전은 name 오류로 답합니다

MS-XLSX 문법은 맨몸으로 두고 Excel이 접두사를 다는 함수는 무엇인가

MS-XLSX §2.2.2는 그 목록을 ABNF로 future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …처럼 적습니다. 문자 그대로 읽으면 Z.TEST 뒤의 닫는 괄호가 접두사 붙은 group을 끝내므로, 그 뒤의 모든 것은 맨몸 이름입니다. 두 번째 production인 평범한 built-in 함수 목록은 SUM과 VLOOKUP 곁에 CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH를 포함합니다. Excel은 두 군데서 모두 다르게 굴지요:

함수MS-XLSX 문법이 놓는 위치Excel이 쓰는 것
NETWORKDAYS.INTL, WORKDAY.INTL_xlfn. group 뒤, 즉 맨몸_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILING_xlfn. group 뒤, 즉 맨몸_xlfn.ISO.CEILING
FORECAST.LINEAR와 FORECAST.ETS 가족_xlfn. group 뒤, 즉 맨몸접두사 있음, 예컨대 _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH평범한 built-in 함수 목록접두사 있음, 예컨대 _xlfn.TEXTJOIN
XIRR, XNPV, ERFCfuture-function 목록에 없음맨몸

문법에서 생성된 writer는 따라서 접두사 없이 NETWORKDAYS.INTL(…)을 만들고, 공표된 문법에는 합격하는 파일에서 Excel은 #NAME?으로 답합니다. 목록은 저장된 Excel 파일에서 가져오고 ABNF는 출발점으로만 치세요. HotXLS는 계산하는 모든 함수에 그렇게 하므로 접두사 붙은 CONCAT과 접두사 붙은 NETWORKDAYS.INTL 모두 Excel이 쓰는 그대로 나옵니다. 다음 코드는 같은 종류의 formula를 두 방식으로 저장하고 하나를 다시 읽습니다:

uses
  SysUtils, lxCalc, lxHandle, lxHandleX;

procedure SaveModernFunctions;
var
  XBook, XCheck: TXLSXWorkbook;
  XSheet: TXLSXWorksheet;
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  // XLSX: 접두사는 저장 시 붙고 읽을 때 벗겨집니다
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // date serial 하나
    XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)';       // ToolPak: 접두사 없음
    XSheet.Cells[1, 3].Formula := 'GAMMA(5)';          // _xlfn.GAMMA
    XSheet.Cells[1, 4].Formula := 'NETWORKDAYS.INTL(A1,A1+30,11)';
    XSheet.Cells[1, 5].Formula := 'TEXTJOIN("-",TRUE,B1:D1)';
    if XBook.SaveAs('modern.xlsx', xlsxOpenXMLWorkbook) <> lxOk then
      raise Exception.Create('XLSX save failed');
  finally
    XBook.Free;
  end;

  XCheck := TXLSXWorkbook.Create;
  try
    if XCheck.Open('modern.xlsx') <> lxOk then
      raise Exception.Create('XLSX open failed');
    // 기대 결과: NETWORKDAYS.INTL(A1,A1+30,11), 접두사는 이미 제거됨
    Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
  finally
    XCheck.Free;
  end;

  // BIFF8: 같은 호출들이 name 토큰과 Ftab 항목 255가 됩니다
  Book := TXLSWorkbook.Create;
  try
    Sheet := Book.Sheets.Add;
    Sheet.Cells[1, 1].Value := 46000.0;
    Sheet.Cells[1, 2].Formula := '=EDATE(A1,1)';       // add-in EXTERNNAME
    Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)';    // 숨겨진 _xlfn NAME
    if Book.SaveAs('modern.xls') <> lxOk then          // 기본은 BIFF8
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

두 파일 모두 최신 Excel에서 모든 함수가 온전한 채로 열려야 합니다. 접두사를 보려면 패키지를 ZIP으로 열어 워크시트 part의 날 <f> 텍스트를 나열하세요. modern.xlsx라면 아래 루틴이 EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11), _xlfn.TEXTJOIN(&quot;-&quot;,TRUE,B1:D1)을 인쇄해야 합니다. 따옴표가 &quot;으로 나오는 것은 루틴이 XML 텍스트를 unescape하지 않고 인쇄하기 때문이며, 진단에는 무해합니다. 어떤 생산자의 파일에도 통하므로, 쓰고 있는 Excel이 지원하는 함수에서 서드파티 워크북이 #NAME?을 보여 줄 때 가장 빠른 검사입니다

uses
  SysUtils, Classes, Zip;

// 워크시트 part 하나의 모든 <f> 요소를 접두사까지 인쇄합니다
procedure DumpRawFormulas(const XlsxFile: string;
  const Part: string = 'xl/worksheets/sheet1.xml');
var
  Zip: TZipFile;
  Bytes: TBytes;
  Xml: string;
  P, StartTag, Close: Integer;
begin
  Zip := TZipFile.Create;
  try
    Zip.Open(XlsxFile, zmRead);
    Zip.Read(Part, Bytes);
  finally
    Zip.Free;
  end;
  Xml := TEncoding.UTF8.GetString(Bytes);
  P := 1;
  while True do
  begin
    StartTag := Pos('<f', Xml, P);
    if StartTag = 0 then
      Break;
    P := StartTag + 2;
    if not CharInSet(Xml[P], ['>', ' ', '/']) then
      Continue;                                // <formula>, <font>: 셀 formula가 아닙니다
    P := Pos('>', Xml, P) + 1;               // t="shared" 같은 attribute 건너뜀
    if Xml[P - 2] = '/' then
      Continue;                                // shared formula의 빈 <f .../>
    Close := Pos('</f>', Xml, P);
    if Close = 0 then
      Break;
    Writeln(Copy(Xml, P, Close - P));          // XML 이스케이프됨: &quot; &amp; &lt;
    P := Close + 4;
  end;
end;

BIFF8 .xls 파일은 번호가 없는 함수를 어떻게 호출하는가

BIFF8 formula에는 함수 이름이 전혀 없습니다. 각 built-in 함수는 고정 함수 표(Ftab)의 번호이고, formula는 그 번호와 함께 ptgFunc나 ptgFuncVar를 저장합니다. 더 새로운 함수에는 번호가 없습니다. MS-XLS는 그런 호출을 Ftab 항목 0x00FF, "User Defined Function"으로 돌립니다. 함수 인덱스 255의 ptgFuncVar로, 첫 인자가 진짜 함수를 가리키는 name 토큰이죠. 토큰에 저장된 인자 개수는 사용자가 타이핑한 것보다 하나 많습니다. 선행하는 name도 인자로 세어 지니니까요. Excel이 어느 name 토큰을 쓰는지는 함수의 가족에 달려 있습니다:

함수 가족name 토큰name이 가리키는 것
Analysis ToolPak 함수: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameX워크북의 add-in SUPBOOK 안의 EXTERNNAME record
Excel이 _xlfn.으로 쓰는 함수: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgName_xlfn.XLOOKUP(또는 _xlfn._xlws.FILTER)이라 불리는 숨겨진 NAME record, 정의는 #NAME?
저장하는 Excel이 인식하지 못하는 함수ptgName빈 정의를 지닌 평범한 워크북 NAME record

첫 행의 add-in SUPBOOK은 reader가 외부 참조를 분류할 때 만나는 record와 같은 것이고, BIFF SUPBOOK record가 외부 참조를 분류하는 방식이 다른 종류들 사이에서 그것이 어디에 앉는지 보여 줍니다. add-in처럼 보이는 모든 것이 이 길로 가는 것은 아닙니다. DATEDIF, USDOLLAR, FINDB, ISPMT 등은 고유의 Ftab 번호를 지녀 평범한 함수 호출로 저장됩니다. 세 번째 행이 깨지기 쉬운 행입니다. 파일을 저장한 Excel보다 너무 새로운 함수, 예컨대 REGEXTEST와 REGEXEXTRACT는 정의 없는 평범한 name을 통한 호출이 됩니다. 빈 정의 name을 잡동사니로 버리는 reader는 formula가 더 이상 존재하지 않는 name 인덱스를 가리키게 두고, Excel은 그것이 다시 쓴 파일 전체를 열기를 거부합니다. HotXLS는 그런 name을 #NAME? 정의와 함께 지키고, 저장할 때 원래 record를 다시 기록합니다

Delphi용 HotXLS의 BIFF8 name 호출 경로: ToolPak 함수는 ptgNameX를 타고, _xlfn 함수는 숨겨진 name record와 함수 255를 거쳐 호출됩니다
.xls 파일의 formula는 번호를 저장하므로, 새 함수는 user-defined 슬롯을 빌려 왕복에서 살아남아야 하는 name record를 가리킵니다

모든 name은 첫 워크시트보다 먼저 존재해야 합니다

순서가 다음 제약입니다. BIFF8 파일은 globals — NAME 표와 EXTERNSHEET — 를 어떤 워크시트 substream보다 먼저 적으므로, formula가 가리킬 모든 name은 첫 셀이 적히기 전에 알려져 있어야 합니다. 세 번째 시트 중반에서 XLOOKUP을 만난 writer는 필요한 숨겨진 name을 더 이상 추가할 수 없습니다. BIFF8을 직접 만든다면 globals를 쓰기 전에 모든 formula 소스를 훑으세요. 셀 formula, 워크북과 시트 수준 name, data validation 규칙, conditional formatting이죠. 크기는 토큰에서 따라 옵니다. name 호출은 인자 위에 9바이트(ptgName 5, ptgFuncVar 4), add-in 호출은 11바이트(ptgNameX 7, ptgFuncVar 4)를 드며, 쓰기 전에 계산된 record 길이는 이후 적히는 바이트와 같은 토큰 선택을 써야 합니다. HotXLS는 무엇이든 쓰기 전에 한 패스로 이 모든 name을 풉니다

Delphi용 HotXLS에서 BIFF8 파일의 쓰기 순서: globals의 name record가 모든 워크시트에 앞서므로 formula 소스를 먼저 훑습니다
세 번째 시트 중반에서 만난 name은 globals에 합류할 수 없으므로, 첫 바이트가 적히기 전에 모든 formula 소스를 훑습니다

operand 클래스가 Excel의 계산을 바꿉니다

BIFF8 formula의 각 인자는 reference, value, array operand로 태그되고, Excel은 태그를 문자 그대로 받아들입니다. AGGREGATE의 셋째 인자를 value로 태그하면 Excel은 #VALUE!를 반환합니다. SUM(SORTBY(…))의 안쪽 결과를 value로 태그하면 Excel은 implicit intersection을 끼워 넣어 SUM(@SORTBY(…))을 보여 주고 셀 하나를 합산합니다. writer는 이런 인자를 Excel이 하는 식으로 태그해야 하는데, 이는 함수 문서가 암시하는 클래스와 늘 같지는 않습니다. add-in 함수 인자는 reference로 적히고, dynamic-array 함수는 배열을 반환하며, XLOOKUP, TAKE, DROP, IFS, SWITCH는 reference가 기대되는 자리에 앉으면 reference를 반환합니다. 잘못된 클래스는 파일을 깨지 않습니다. 답을 바꿉니다

VBA 함수는 XLSB 워크북 binary를 유지합니다

XLSB는 세 번째 저장 사례를 더합니다. 매크로 워크북은 자기 VBA Function 프로시저를 셀에서 호출할 수 있고, binary 형식은 각각을 빈 formula와 함께 procedure로 표시된 name으로 선언합니다. 그런 뒤 셀은 BIFF8과 같은 ptgName + 함수 255 패턴으로 호출하죠. XML 형식에는 동등물이 없습니다. 빈 defined name은 거기서 유효하지 않고, #NAME?으로 정의된 name은 같은 이름의 VBA 함수를 가려 버립니다. HotXLS는 그런 워크북을 선언을 온전히 지킨 채 XLSB로 되저장하고, 바이트 하나 쓰기 전에 다른 어떤 형식으로의 변환도 거부합니다. 저장이 실패한 뒤가 아니라 변환 전에 conversion report를 요청하세요. TXLSXWorkbook.GetConversionReport는 그런 워크북에 대해 unsupported로 표시된 VBAFunctionNames 항목을 반환하고, 코드는 이를 기준으로 분기할 수 있습니다

uses
  SysUtils, lxCalc, lxDiagnostics, lxHandleX;

procedure ConvertOrKeepBinary(const XlsbFile: string);
var
  Book: TXLSXWorkbook;
  Report: TXLSConversionReport;
  I: Integer;
  Blocked: Boolean;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(XlsbFile) <> lxOk then
      raise Exception.Create('Open failed');
    Report := Book.GetConversionReport(xlsConversionOpenXmlWorkbook);
    try
      Blocked := False;
      for I := 0 to Report.Count - 1 do
        if Report[I].Fidelity = xlsConversionUnsupported then
        begin
          Blocked := True;
          Writeln(string(Report[I].Feature), ': ', string(Report[I].Message));
        end;
    finally
      Report.Free;
    end;
    if Blocked then                               // 예: VBAFunctionNames
      Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
        xlsxExcelBinaryWorkbook)
    else
      Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
  finally
    Book.Free;
  end;
end;

다른 도구에서 나온 워크북이 Excel이 명백히 아는 함수에서 #NAME?을 보여 준다면 무엇보다 먼저 DumpRawFormulas를 돌리세요. <f> 텍스트의 맨몸 NETWORKDAYS.INTL이나 TEXTJOIN이 진단 전부입니다. HotXLS Delphi Excel component의 Cell.Formula로 같은 formula 텍스트를 할당하면 XLSX는 Excel이 기대하는 접두사를 얻고, 워크북의 .xls 저장은 숨겨진 name과 함수 255 호출을 얻습니다