技術記事

新 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 の文法から外れるリスト——です

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 はきれいな関数名を返します

Delphi 向け HotXLS における新 Excel 関数の格納経路。GAMMA は _xlfn 接頭辞を運び、ToolPak の XIRR は裸のまま
接頭辞は読み手がまだ知らないかもしれない組み込みの印なので、現行 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 の後の閉じ括弧が接頭辞付きグループを終わらせるので、その後のすべては裸の名前です。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、ERFCfuture-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(&quot;-&quot;,TRUE,B1:D1) を印字するはずです。引用符は &quot; として出ます。ルーチンが 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 エスケープ済み: &quot; &amp; &lt;
    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? の定義付きで保持し、保存時に元のレコードをもう一度書き出します

Delphi 向け HotXLS における BIFF8 の name 呼び出し経路。ToolPak 関数は ptgNameX に乗り、_xlfn 関数は隠し name レコードと関数 255 を通って呼ばれる
.xls ファイルの数式は番号を格納するので、新しい関数はユーザー定義のスロットを借り、ラウンドトリップを生き延びねばならない name レコードを指します

すべての name は最初のワークシートより前に存在しなければならない

順序が次の制約です。BIFF8 ファイルは、どんなワークシートサブストリームよりも先に、グローバル——NAME テーブルと EXTERNSHEET——を書くので、数式が指すすべての name は最初のセルが書かれる前に既知でなければなりません。シート 3 の途中で XLOOKUP に出会った書き手は、もう必要な隠し name を追加できません。自分で BIFF8 を作るなら、グローバルを書く前にすべての数式ソースをスキャンしましょう。セル数式、ワークブックとシートレベルの名前、データの入力規則、条件付き書式です。サイズはトークンから出ます。name 呼び出しは引数の上に 9 バイト(ptgName に 5、ptgFuncVar に 4)、アドイン呼び出しは 11 バイト(ptgNameX に 7、ptgFuncVar に 4)かかり、書く前に計算されるレコード長は、後から書かれるバイトと同じトークンの選択を使わなければなりません。HotXLS はこれらすべての name を、何かを書く前に 1 パスで解決します

Delphi 向け HotXLS における BIFF8 ファイルの書き込み順。グローバル内の name レコードはすべてのワークシートに先行するので、数式ソースは先にスキャンされる
シート 3 の途中で出会った 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 の呼び出しを得ます