NPOIで外部のExcelワークブックを参照する数式が作れない話

結論から言おう。2015年初頭現在のNPOI(~~そして恐らく元になっているApache POIでも~~)では新規に外部のシートを参照する数式を追加することはできない。追加するといくつかの条件を満たさない限り#REF!に変換される。

**補遺:** Apache POIにおいては2014年12月にリリースされたバージョン3.11からlinkExternalWorkbookメソッドが実装されていた。NPOIにも遠からず実装されるだろう。
**補遺の補遺:** NPOIでも2.2からLinkExternalWorkbookメソッドが追加された。数式の件は直っていないように見えるが、いずれにせよ近いうちに検証しようと思う。

この挙動は全部HSSF(.xls)で確認したもので、XSSF(.xlsx)で発生するかどうかは定かではない。

以下は全部解説。

そもそもBIFF形式におけるセル参照において、シートの名前は式として保存されない。

例えばSheet1、Sheet2、Sheet3を含む典型的な初期状態のExcelシートで、Sheet1のセルA1に次のような式を書いたとする。
*=Sheet3!B1*
このセルをNPOIで読み込むと次のような式を持ったFORMULAレコードとして認識される。
*.sheetIx=2 ! B1*

すでになんとなく察しがつくと思うが、参照先がSheet2なら.sheetIxは1となる。このインデックスは最終的に**SUPBOOKレコードが保持するシート名の配列**と関連付けられており、セルの解釈が始まる前に参照先のブックの評価が始められる構造になっている。SUPBOOKレコードは参照先のブックごとに追加される。初期状態のブックに登録されているSUPBOOKレコードはそのファイル自身とその中のシート一覧のみとなっているが、外部のブックへのセル参照を書くと普通のスプレッドシートソフトなら自動的に裏でSUPBOOKレコードも追加していくようになっている。

ちなみにブック内のシートを参照する場合はシート名のリストが別になるため厳密には手順が違うが、今回の話の本質ではないので省略する。

## InternalWorkbookの問題

問題はNPOIでは今のところ外部のブックを参照するSUPBOOKレコードを追加する手段が著しく制限されているところにある。
NPOIにおいてSUPBOOKレコードのリストはNPOI.HSSF.Model名前空間のInternalWorkbookクラスが持っているLinkTableオブジェクトで管理されるが、LinkTableクラスには外部ファイル参照を追加するためのインターフェースがない。LinkTable.AddNameXPtgメソッドはユーザー定義関数を登録するためのもので、外部参照の追加には使用できない。

なお悪いことにLinkTableは内部的にキャッシュを持っており、InternalWorkbookが持っているRecordsプロパティからレコードに細工をしただけでは参照を追加できない。従ってLinkTableに外部参照を追加できるチャンスがあるのはコードで直接InternalWorkbookオブジェクトを生成する場合だけで、読み込ませるレコードのリスト内の適切な位置(具体的に言うと内部参照用のSUPBOOKレコードの直後)に外部参照のレコードを挿入しなければならない。新規作成の場合は初期状態のレコードの一覧を自力で再現する必要がある。

## FormulaParserの問題

このハードルを越えてもまだ落とし穴がある。実はNPOIの数式パーサーは以下のような式をExcelと同じように解釈できない。
*='C:\Users\User\Documents\\\[Hoge.xls\]Sheet1'!A1*
これはNPOI.SS.Formula.FormulaParserの挙動に由来するものであり、おそらくXSSFでも同様の症状となる。

この記事を書いている時点でのソースを読む限り、単一引用符でくくられた文字列はすべてシート名扱いになる。そしてシート名の参照に失敗して#REF!と化す。NPOIが現在解釈できる参照の書式は以下の四種類だ。

* 英数字(実際はアンダーバーも使えるが以下略)のみのシート名: Sheet_1!A1
* ファイル名と英数字のみのシート名: [ブック1.xls]Sheet_1!A1
* 英数字でないシート名: 'シート1 概要'!A1
* **ファイル名と英数字でないシート名: [ブック1.xls]'シート1 概要'!A1**

この挙動、とくに四番目は[明らかにExcelの仕様に反している](http://office.microsoft.com/ja-jp/excel-help/HP010102338.aspx)が、現在のパーサーは外部参照が[から始まる場合以外は他ブックへの参照とみなさず、さらにブック名の書式は厳密に検証しないので上記の式でないと通用しない。さらに言うと**[]内に記入するブック名はSUPBOOKレコードで追加したExcelファイルのURLと同じにする必要がある**。

ただしシート参照は最終的に.sheetIxに変換されるため、この問題はInternalWorkbookのそれほど致命的ではない。はっきり言えることは**Excelでテストした外部参照入りの数式をそのままコピーしても機能しない**という事だけである。

## 結論

ご覧のとおり外部参照を含む式を作成する場合、NPOIは実用に耐えない場合が多い。ひとつのブック内で完結しないExcelファイルの編集にNPOIを使うべきではない。

## 補遺

以上は配布されているNPOIを使用する場合の話である。NPOIはOSSであり、自力で機能を追加することもできる。例えばこんな風に。

```csharp:HSSF/Model/LinkTable.cs#392行目
public void AddExternalSheets(string url, string[] sheetNames)
{
	var record = SupBookRecord.CreateExternalReferences(url, sheetNames);
	var temp = new ExternalBookBlock[_externalBookBlocks.Length + 1];
	_externalBookBlocks.CopyTo(temp, 0);
	temp[_externalBookBlocks.Length] = new ExternalBookBlock(new RecordStream(new[] { record }, 0));
	var bookIx = 0;
	bool found = false;
	for (var e = _workbookRecordList.Records.GetEnumerator(); e.MoveNext(); bookIx++) {
		var _rec = e.Current as SupBookRecord;
		if (_rec != null && !_rec.IsAddInFunctions) {
			found = true;
		}
		else if (found) {
			break;
		}
	}
	_workbookRecordList.Add(bookIx, record);
	for (int sheetIx = 0; sheetIx < sheetNames.Length; sheetIx++) {
		_externSheetRecord.AddRef(_externalBookBlocks.Length, sheetIx, sheetIx);
	}
	_externalBookBlocks = temp;
}
```

```csharp:SS/Formula/FormulaParser.cs#111行目
private class SheetIdentifier
{

    private String _path;
    private String _bookName;
    private Identifier _sheetIdentifier;

    public SheetIdentifier(String path, String bookName, Identifier sheetIdentifier)
    {
        _path = path;
        _bookName = bookName;
        _sheetIdentifier = sheetIdentifier;
    }
    public String Path //←ディレクトリ名を保持するために追加
    {
        get
        {
            return _path;
        }
    }
    public String BookName
    {
        get
        {
            return _bookName;
        }
    }
    public String URL //←ブックのフルネームを取るために追加
    {
        get
        {
            return _path != null ? (_path + _bookName) : _bookName;
        }
    }
    public Identifier SheetID
    {
        get
        {
            return _sheetIdentifier;
        }
    }
    public override String ToString()
    {
        StringBuilder sb = new StringBuilder(64);
        sb.Append(this.GetType().Name);
        sb.Append(" [");
        if (_path != null) {
            sb.Append(_path);
        }
        if (_bookName != null)
        {
            sb.Append(" [").Append(_bookName).Append("]");
        }
        if (_sheetIdentifier.IsQuoted)
        {
            sb.Append("'").Append(_sheetIdentifier.Name).Append("'");
        }
        else
        {
            sb.Append(_sheetIdentifier.Name);
        }
        sb.Append("]");
        return sb.ToString();
    }
}
```

```csharp:SS/Formula/FormulaParser.cs#783行目
private ParseNode CreateAreaRefParseNode(SheetIdentifier sheetIden, SimpleRangePart part1,
        SimpleRangePart part2)
{

    int extIx;
    if (sheetIden == null)
    {
        extIx = Int32.MinValue;
    }
    else
    {
        String sName = sheetIden.SheetID.Name;
        if (sheetIden.BookName == null)
        {
            extIx = _book.GetExternalSheetIndex(sName);
        }
        else
        {
            extIx = _book.GetExternalSheetIndex(sheetIden.URL, sName); //←ここをBookNameからURLに変更した
        }
    }
```

```csharp:SS/Formula/FormulaParser.cs#1080行目
private SheetIdentifier ParseSheetName()
{
    String path, bookName;
    path = null;
    bookName = ParseBookName(); //←ここの処理をメソッドとして抽出する

    if (look == '\'')
    {
        StringBuilder sb = new StringBuilder();

        Match('\'');
        bool done = look == '\'';
        while (!done)
        {
            sb.Append(look);
            GetChar();
            if (look == '\'')
            {
                Match('\'');
                done = look != '\'';
            }
            else if (look == '[') { //←このブロックを追加してディレクトリとブック名を分離する
                path = sb.ToString();
                bookName = ParseBookName();
                sb.Clear();
            }
        }

        Identifier iden = new Identifier(sb.ToString(), true);
        // quoted identifier - can't concatenate anything more
        SkipWhite();
        if (look == '!')
        {
            GetChar();
            return new SheetIdentifier(path, bookName, iden);
        }
        return null;
    }

    // unquoted sheet names must start with underscore or a letter
    if (look == '_' || Char.IsLetter(look))
    {
        StringBuilder sb = new StringBuilder();
        // can concatenate idens with dots
        while (IsUnquotedSheetNameChar(look))
        {
            sb.Append(look);
            GetChar();
        }
        SkipWhite();
        if (look == '!')
        {
            GetChar();
            return new SheetIdentifier(path, bookName, new Identifier(sb.ToString(), false));
        }
        return null;
    }
    return null;
}

private string ParseBookName()
{
    String bookName;
    if (look == '[') {
        StringBuilder sb = new StringBuilder();
        GetChar();
        while (look != ']') {
            sb.Append(look);
            GetChar();
        }
        GetChar();
        bookName = sb.ToString();
    }
    else {
        bookName = null;
    }
    return bookName;
}
```

注:上記のコードは[2014年12月26日にGitHubにコミットされたmaster](https://github.com/tonyqus/npoi/commit/3b5599dccfc3bb5ea97a5b9792a537d1969dc044)に対して個人的に書き加えたものの要約であり、他の環境・条件で実行した場合の動作を保証できる物ではない。