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는 깔끔한 함수 이름을 반환합니다
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, ERFC | future-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("-",TRUE,B1:D1)을 인쇄해야 합니다. 따옴표가 "으로 나오는 것은 루틴이 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 이스케이프됨: " & <
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를 다시 기록합니다
모든 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을 풉니다
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 호출을 얻습니다