Delphi が生成したワークブックは =GAMMA(5) と =XIRR(B2:B9,A2:A9) を抱えていて、Excel が開くと GAMMA セルには #NAME?、XIRR セルには正しい結果が出ます。HotXLS で、XLSX では _xlfn. 接頭辞を、BIFF8 .xls では name ベースの呼び出しを運ぶ関数は、Excel がそう保存するもの——MS-XLSX の文法から外れるリスト——です
GAMMA には #NAME? が出て XIRR には出ないのはなぜか
数式テキストは正しい、格納が違うのです。XLSX ワークシートの中では数式は <f> 要素の中の素のテキストで、Excel 2007 以降に追加された関数は _xlfn.NAME として書かれます。接頭辞は読み手に「これはまだ知らないかもしれない組み込み関数だ」と告げるので、古い Excel は呼び出しをユーザー定義名と取り違えることなく #NAME? を出し、現行 Excel は表示時に接頭辞を剥がしてユーザーに =GAMMA(5) を見せます。付けないままだと、現行 Excel は GAMMA という定義名を探し、見つからず、#NAME? を出します。XIRR は古株です。Analysis ToolPak と一緒に来て、Excel は裸で書き、接頭辞付きの _xlfn.XIRR は Excel が作るトークンとは別物です。GAMMA、GAUSS、PHI、SHEET、SHEETS、XOR はみな接頭辞が要り、Excel 2013 が工学と複素数のセットに加えた 8 関数(IMCOSH、IMCOT、IMCSC、IMCSCH、IMSEC、IMSECH、IMSINH、IMTAN)もそうです。古株の IMSIN、IMSQRT、IMSUM は裸のままです。ワークシート専用の配列関数は第 2 の接頭辞 _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 の後の閉じ括弧が接頭辞付きグループを終わらせるので、その後のすべては裸の名前です。2 つ目の生成規則、通常の組み込み関数リストは、SUM や VLOOKUP の隣に CONCAT、TEXTJOIN、IFS、MAXIFS、MINIFS、SWITCH を含みます。Excel は両方の点で文法に反します:
| 関数 | MS-XLSX の文法が置く場所 | Excel が書くもの |
|---|---|---|
| NETWORKDAYS.INTL、WORKDAY.INTL | _xlfn. グループの後、つまり裸 | _xlfn.NETWORKDAYS.INTL、_xlfn.WORKDAY.INTL |
| ISO.CEILING | _xlfn. グループの後、つまり裸 | _xlfn.ISO.CEILING |
| FORECAST.LINEAR と FORECAST.ETS ファミリー | _xlfn. グループの後、つまり裸 | 接頭辞付き、たとえば _xlfn.FORECAST.LINEAR |
| CONCAT、TEXTJOIN、IFS、MAXIFS、MINIFS、SWITCH | 通常の組み込み関数リスト | 接頭辞付き、たとえば _xlfn.TEXTJOIN |
| XIRR、XNPV、ERFC | future-function リストにない | 裸 |
だから文法から生成された書き手は接頭辞なしの NETWORKDAYS.INTL(…) を作り、Excel は公表済みの構文に適合するファイルの中で #NAME? で答えます。リストは保存済みの Excel ファイルから取り、ABNF は出発点として扱いましょう。HotXLS は計算するすべての関数についてそうしており、接頭辞付きの CONCAT も接頭辞付きの NETWORKDAYS.INTL も、Excel が書くとおりに出てきます。下のコードは同じ種類の数式を両方向へ保存し、1 つを読み返します:
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; // 日付シリアル
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)'; // アドインの 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 として開き、ワークシートパートの生の <f> テキストを列挙します。modern.xlsx なら、下のルーチンは EDATE(A1,1)、_xlfn.GAMMA(5)、_xlfn.NETWORKDAYS.INTL(A1,A1+30,11)、_xlfn.TEXTJOIN("-",TRUE,B1:D1) を印字するはずです。引用符は " として出ます。ルーチンが XML テキストをアンエスケープせずに印字するからで、診断には無害です。これはどの作成者のファイルにも効くので、サードパーティのワークブックが自分の Excel が対応している関数で #NAME? を出すときの最速のチェックになります
uses
SysUtils, Classes, Zip;
// 1 つのワークシートパートの全 <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>: セル数式ではない
P := Pos('>', Xml, P) + 1; // t="shared" などの属性をスキップ
if Xml[P - 2] = '/' then
Continue; // 共有数式の空の <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 の数式は関数名をまったく含みません。各組み込み関数は固定の関数テーブル(Ftab)の中の番号で、数式はその番号付きの ptgFunc か ptgFuncVar を格納します。新しい関数には番号がありません。MS-XLS はそういう呼び出しを Ftab エントリ 0x00FF「User Defined Function」へ迂回させます。関数インデックス 255 の ptgFuncVar で、最初の引数が本物の関数を指す name トークンです。トークンに格納される引数の数は、ユーザーが打った数より 1 つ多い。先頭の名前が引数として数えられるからです。Excel がどの name トークンを書くかは、関数のファミリーで決まります:
| 関数ファミリー | name トークン | name の指す先 |
|---|---|---|
| Analysis ToolPak 関数: EDATE、EOMONTH、WORKDAY、NETWORKDAYS、RANDBETWEEN、XIRR … | ptgNameX | ワークブックのアドイン SUPBOOK 内の EXTERNNAME レコード |
Excel が _xlfn. 付きで書く関数: XLOOKUP、ISOWEEKNUM、IFS、CONCAT … | ptgName | 定義が #NAME? である _xlfn.XLOOKUP(または _xlfn._xlws.FILTER)という隠し NAME レコード |
| 保存する Excel が認識しない関数 | ptgName | 空の定義を持つ通常のワークブック NAME レコード |
最初の行のアドイン SUPBOOK は、読み手が外部参照を分類するときに出会うのと同じレコードで、BIFF SUPBOOK レコードが外部参照を分類するしかけが、他の種類の間でそれがどこに座るかを示します。アドインに見えるものすべてがこの経路を行くわけではありません。DATEDIF、USDOLLAR、FINDB、ISPMT などはネイティブの Ftab 番号を持ち、素の関数呼び出しとして格納されます。3 行目が壊れやすい行です。保存した Excel より新しい関数、たとえば REGEXTEST と REGEXEXTRACT は、定義のない通常の name を通る呼び出しになります。空定義の name をゴミとして落とす読み手は、数式を、もう存在しない name インデックスへの参照のまま残し、Excel は書き戻したファイル全体を開くことを拒みます。HotXLS はそういう name を #NAME? の定義付きで保持し、保存時に元のレコードをもう一度書き出します
すべての name は最初のワークシートより前に存在しなければならない
順序が次の制約です。BIFF8 ファイルは、どんなワークシートサブストリームよりも先に、グローバル——NAME テーブルと EXTERNSHEET——を書くので、数式が指すすべての name は最初のセルが書かれる前に既知でなければなりません。シート 3 の途中で XLOOKUP に出会った書き手は、もう必要な隠し name を追加できません。自分で BIFF8 を作るなら、グローバルを書く前にすべての数式ソースをスキャンしましょう。セル数式、ワークブックとシートレベルの名前、データの入力規則、条件付き書式です。サイズはトークンから出ます。name 呼び出しは引数の上に 9 バイト(ptgName に 5、ptgFuncVar に 4)、アドイン呼び出しは 11 バイト(ptgNameX に 7、ptgFuncVar に 4)かかり、書く前に計算されるレコード長は、後から書かれるバイトと同じトークンの選択を使わなければなりません。HotXLS はこれらすべての name を、何かを書く前に 1 パスで解決します
オペランドクラスが Excel の計算結果を変える
BIFF8 の数式の各引数は、参照、値、配列のオペランドとしてタグ付けされ、Excel はタグを文字どおり受け取ります。AGGREGATE の第 3 引数を値としてタグ付けすれば Excel は #VALUE! を返します。SUM(SORTBY(…)) の内側の結果を値としてタグ付けすれば、Excel は暗黙のインターセクションを挿入し、SUM(@SORTBY(…)) を表示して単一セルを合計します。書き手はこれらの引数を Excel と同じようにタグ付けしなければなりません。関数のドキュメントが示唆するクラスとは限りません。アドイン関数の引数は参照として書かれ、動的配列関数は配列を返し、XLOOKUP、TAKE、DROP、IFS、SWITCH は参照が期待される場所では参照を返します。間違ったクラスはファイルを壊しません。答えを変えます
VBA 関数は XLSB ワークブックのバイナリを保つ
XLSB は 3 つ目の格納ケースを加えます。マクロワークブックは、セルから自分の VBA Function プロシージャを呼べます。バイナリ形式は各プロシージャを、空の数式を持つ procedure フラグ付きの name として宣言し、セルは BIFF8 と同じ ptgName + 関数 255 のパターンでそれを呼びます。XML 形式には等価物がありません。空の定義名はそちらでは正しくなく、#NAME? と定義された name は同名の VBA 関数を隠します。HotXLS はそういうワークブックを、宣言を無傷のまま XLSB へ保存し戻し、1 バイト書く前に他のどんな形式への変換も拒みます。変換は保存が失敗してからではなく、変換前に変換レポートを請求しましょう。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 から代入すれば、XLSX は Excel が期待する接頭辞を得て、ワークブックの .xls 保存は隠し name と関数 255 の呼び出しを得ます