More than 3 years have passed since last update.

Apache POI ハマりポイント一覧

Last updated at 2020-05-22Posted at 2019-07-15

どんな記事？

Apache POI はとても便利なのですが、多くのハマりポイントを抱えています。
本記事は、Excel ファイル比較ツール「方眼Diff」¹ を作成する過程ではまったポイントを記録したものです。誰かの参考になれば幸いです。

繰り返しますが、**Apache POI はとても便利です。**こんなものが無料で利用できるのですから、コントリビュータの方々には感謝感謝なのです。文句があるなら自分でコントリビュートしろ、せめて issue を立てろという話なのです²。つまり本記事はそれができない私自身の力不足を記録したものなのです。

なお、本記事には私の誤解や理解不足に基づく内容が含まれる可能性が大いにありますので、ご了承ください。それらにお気付きの方はコメントいただけますと有難いです。

本記事が前提とするバージョン

Apache POI 4.1.0～（下表に記載）
Excel for Office 365
OpenJDK 12.0.1

はまりポイント一覧

№	ハマりポイント	対象	POI ver.	記載日
1	シートの種類（ワークシート、グラフシート、マクロシート、ダイアログシート）を見分けられない	Sheet インタフェース	4.1.0	2019/7/15 初稿
2	クラス名の大文字／小文字がタイポ？	XSSFDialogsheet クラス	4.1.0	2019/7/15 初稿
3	一般的な命名則と異なるメソッド名に注意	HSSFSheet#getDialog() メソッド	4.1.0	2019/7/15 初稿
4	HSSFSheet#getDialog() が機能しない	HSSFSheet#getDialog() メソッド	4.1.0	2019/7/15 初稿
5	.xlsx/.xlsm 形式のダイアログシート／マクロシートは無視される	WorkbookFactory#create(File) メソッド	4.1.0	2019/7/15 初稿
6	.xls 形式のダイアログシートはイベントモデル API でも識別できない	WSBoolRecord#getDialog() メソッド	4.1.0	2019/7/15 初稿
7	ブック形式（.xls vs .xlsx/.xlsm）によって数式内の空白が再現されたりされなかったりする	Cell#getCellFormula() メソッド	4.1.0	2019/8/12 追加
8	斜めの罫線を操作するためのAPIが提供されていない	CellStyle インタフェース	4.1.0	2019/10/5 追加
9	空コメントに関する挙動がファイル形式によって異なる	Comment#getString() メソッド	4.1.2	2020/3/25 追加
10	XSSFComment#setVisible(boolean) が機能しない	XSSFComment#setVisible(boolean) メソッド	4.1.2	2020/3/25 追加
11

思い出ししだい、追記していきます。

個別説明

No.1 シートの種類（ワークシート、グラフシート、マクロシート、ダイアログシート）を見分けられない

説明

Excel のシートには次のような種類があります。

ワークシート
グラフシート
ダイアログシート
マクロシート

これらの種類、特にワークシートを見分けたいというケースがあると思いますが、どういうわけか Apache POI のユーザーモデル API はそのような機能を提供していません。

Excel シートを Apache POI で操作する際の最初の選択肢は、ユーザーモデル API と呼ばれる機能群に含まれ .xls/.xlsx/.xlsm 形式のファイルを透過的に扱うことのできる org.apache.poi.ss.usermodel.Sheet を利用することです。このインタフェースに Sheet#isWorksheet() というような API は提供されていません。

ではどうするか？
Sheet インタフェースは次の階層構造になっています。

①Sheet：.xls/.xlsx/.xlsm 形式のシート
　├─ ②HSSFSheet：.xls 形式のシート
　├─ ③XSSFSheet：.xlsx/.xlsm 形式のシート
　│　　├─ ④XSSFChartSheet：.xlsx/.xlsm 形式のグラフシート
　│　　└─ ⑤XSSFDialogsheet：.xlsx/.xlsm 形式のダイアログシート
　└─ ⑥SXSSFSheet：.xlsx形式のシート（ストリーミング方式）

.xlsx/.xlsm 形式のシートについては、④⑤のインスタンスでないか調べることにより、シートの種類を確認することができます³。
しかし .xls 形式のシートについては、②がダイアログシートか否かを返すメソッドを提供しています⁴が、グラフシートか否かを見分けるメソッドは提供されていません。
ここで行き止まりとなります。

解決策

より低レイヤのソリューションを利用することで解決できます。

.xls 形式のシートについては、Apache POI が提供するイベントモデル API を利用することでシートの種類を見分けることができます⁵。
.xlsx/.xlsm 形式のシートについては、SAX (Simple API for XML) を利用することでシートの種類を見分けることができます。

詳細はこちらの記事で紹介しています。

No.2 クラス名の大文字／小文字がタイポ？

説明

No.1 で紹介した Sheet インタフェースの派生階層を再掲します。

org.apache.poi.ss.usermodel.Sheet
　├─ org.apache.poi.hssf.usermodel.HSSFSheet
　├─ org.apache.poi.xssf.usermodel.XSSFSheet
　│　　├─ org.apache.poi.xssf.usermodel.XSSFChartSheet
　│　　└─ org.apache.poi.xssf.usermodel.XSSFDialogsheet
　└─ org.apache.poi.xssf.streaming.SXSSFSheet

お分かりいただけたでしょうか？ XSSFDialogsheet だけ "s" が小文字なのです。意図的なもの？いや、タイポでしょ。

解決策

実装者がそう名付けたなら、それが正しいクラス名です。思い込みに基づくいちゃもんは何の解決にもなりません。IDE の補完機能やサジェスチョンを活用しましょう。

No.3 一般的な命名則と異なるメソッド名に注意

説明

No.1 の説明の中で、②HSSFSheet インタフェースがダイアログシートか否かを返すメソッドを提供していると書きました。次のメソッドがそれです。（Apache POI が提供する API ドキュメントから転載）

getDialog
　　public boolean getDialog()
get whether sheet is a dialog sheet or not
Returns:
　　isDialog or not

get すると boolean が返される。良いじゃないですか。
isDialogSheet() という名前じゃなきゃいけないなんて決まりはないのです。

解決策

何らかのメソッドを探すとき、そのメソッド名を思い込みで決めつけてはいけません。boolean を返すメソッドが欲しいなら、戻り値型が boolean であるメソッドをつぶさに当たるべきなのです。それこそ真実です。勝手な思い込みを排して API ドキュメントを上から下まで読み込み、目的の機能が提供されていないか探すべきです。

No.4 HSSFSheet#getDialog() が機能しない

説明

No.3 で紹介した HSSFSheet#getDialog() メソッドは機能しないので注意が必要です。具体的には、

グラフシートに対してこのメソッドを実行すると、NullPointerException が発生します。
少なくとも Excel for Office 365 で作成したダイアログシートに対してこのメソッドを実行すると、true ではなく false が返されます。

実演してみます。

1) テスト用 Excel ファイル（.xls 形式）の作成

シート名のタブを右クリックして [挿入...] を選択すると、次の種類のシートを作成できることが分かります。

ワークシート
「グラフ」シート
「Excel 4.0 マクロ」シート
「MS Excel 5.0 ダイアログ」シート

それぞれのシートを作成し、適当な内容を記載します。

これを .xls 形式のExcelブックとして保存します。

2) テスト用コードの作成

たとえば次のようなテストコードを作成します。

package mypackage;

import java.io.File;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Main {
    
    public static void main(String[] args) {
        
        try (Workbook wb = WorkbookFactory.create(
                new File("C:\\Users\\xxx\\No.4テスト.xls"))) {
            
            System.out.println(wb.getNumberOfSheets());
            
            wb.sheetIterator().forEachRemaining(s -> {
                System.out.print(String.format(
                        "%s : %s : ",
                        s.getSheetName(),
                        s.getClass().getName()));
                
                if (s instanceof HSSFSheet) {
                    HSSFSheet sheet = (HSSFSheet) s;
                    try {
                        System.out.println(sheet.getDialog());
                    } catch (NullPointerException e) {
                        System.out.println("★NPE発生★");
                    }
                } else {
                    System.out.println();
                }
            });
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3) テスト実行

上記のテストコードを実行すると、次の結果が得られます。

4
A_ワークシート : org.apache.poi.hssf.usermodel.HSSFSheet : false
B_グラフシート : org.apache.poi.hssf.usermodel.HSSFSheet : ★NPE発生★
C_マクロシート : org.apache.poi.hssf.usermodel.HSSFSheet : false
D_ダイアログシート : org.apache.poi.hssf.usermodel.HSSFSheet : false

「B_グラフシート」で NullPointerException が発生し、「D_ダイアログシート」で true ではなく false が返されていることが分かります。

解決策

HSSFSheet#getDialog() メソッドは使用しない方がよいと思います。

No.5 .xlsx/.xlsm 形式のダイアログシート／マクロシートは無視される

説明

少なくとも Excel for Office 365 で作成した .xlsx/.xlsm 形式のファイルについて、ダイアログシートおよびマクロシートを Apache POI のユーザーモデル API で読み込むことはできないようです。
実演してみます。

1) テスト用 Excel ファイル（.xlsm 形式）の作成

No.4 で使用したテスト用ファイルを .xlsm 形式で保存し直します。
※マクロシートを含むため、マクロ有効ブックとして保存します。

2) テスト用コードの作成

No.4 と同じテストコードを使います。
※ファイル名指定部分だけを変更します。

3) テスト実行

テストコードを実行すると、次の結果が得られます。

2
A_ワークシート : org.apache.poi.xssf.usermodel.XSSFSheet : 
B_グラフシート : org.apache.poi.xssf.usermodel.XSSFChartSheet :

「C_マクロシート」と「D_ダイアログシート」は読み込まれていないことが分かります。

解決策

.xlsx/.xlsm 形式のExcelファイルからマクロシートおよびダイアログシートを読み込みたい場合は、POI のユーザーモデル API の利用を諦め、イベントモデル API か SAX (Simple API for XML) を利用するとよいのではないかと思います（未検証）。

No.6 .xls 形式のダイアログシートはイベントモデル API でも識別できない

説明

No.1 で紹介したように、Apache POI のユーザーモデル API では .xls 形式のワークシートの種類を見分けることができません。イベントモデル API と呼ばれる機能が次の選択肢となります。

.xls 形式のExcelファイル形式はバイナリ交換ファイル形式（BIFF）と呼ばれ、その内容はBIFFレコードの集合体です。
BIFFの概要はこちらのブログが分かりやすく、詳細仕様が Micorosoft により公開されています。

BIFFレコードの内容を Apache POI のイベントモデル API で読むことができますが、どうやら WSBoolRecord#getDialog() メソッドが正しく機能していないらしく、やはりダイアログシートを正しく見分けることができません。
以下に実演してみます。

1) テスト用 Excel ファイル（.xls 形式）の用意

No.4 で使用したのと同じファイルを使います。

2) テスト用コード

たとえば次のようなテストコードを作成します。

package mypackage;

import java.io.FileInputStream;

import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Main {
    
    private static class TestListener implements HSSFListener {
        
        @Override
        public void processRecord(Record record) {
            System.out.println(record);
        }
    }
    
    public static void main(String[] args) throws Exception {
        
        try (FileInputStream fin = new FileInputStream(
                "C:\\Users\\xxx\\No.6テスト.xls");
                POIFSFileSystem poifs = new POIFSFileSystem(fin)) {
            
            HSSFRequest req = new HSSFRequest();
            TestListener listener = new TestListener();
            req.addListenerForAllRecords(listener);
            HSSFEventFactory factory = new HSSFEventFactory();
            factory.abortableProcessWorkbookEvents(req, poifs);
        }
    }
}

3) テスト実行

テストコードを実行すると、BIFFレコードの内容が出力されます。
※左端の行番号は説明のために記載したものであり、実際には出力されません。

詳しく見ると長くなるのでかいつまんで説明します。
次の BOF レコードはダイアログシートの定義開始を告げるものです。何故そうしたのか理解に苦しむのですが、ダイアログシートの BOF レコードの .type 属性はワークシートと同じ 0x0010 と定義されています。（これに対して、グラフシートは 0x0020、マクロシートは 0x0040 と定義されており、見分けることができます。）

...
5201: 
5202: [BOF RECORD]
5203:     .version  = 0x0600
5204:     .type     = 0x0010 (worksheet)
5205:     .build    = 0x4F5A
5206:     .buildyear= 1997
5207:     .history  = 0x000200C9
5208:     .reqver   = 0x00000806
5209: [/BOF RECORD]
5210: 
...

ワークシートとダイアログシートは、後続の WSBOOL レコードの .dialog 属性で見分けられることになっていますが、どういう訳か、実際にはダイアログシートにも関わらず .dialog = false と出力されます。

...
5263: 
5264: [WSBOOL]
5265:     .wsbool1        = 4
5266:         .autobreaks = false
5267:         .dialog     = false
5268:         .rowsumsbelw= false
5269:         .rowsumsrigt= false
5270:     .wsbool2        = ffffffd1
5271:         .fittopage  = true
5272:         .displayguts= false
5273:         .alternateex= true
5274:         .alternatefo= true
5275: [/WSBOOL]
5276: 
...

解決策

WSBoolRecord クラスの実装に問題があるのではないかと思っています。
こちらのBIFF仕様と Apahe が公開しているソースコードをにらめっこすれば原因と対策が分かるかもしれません。

あるいは、やや邪道ですが ROW レコードを持たないシートをダイアログシートと看做せるのかもしれません。（未検証）

No.7 ブック形式（.xls vs .xlsx/.xlsm）によって数式内の空白が再現されたりされなかったりする

説明

Apache POI のユーザーモデルAPIでは、数式内の空白が保持されません。このことは、Apache POI のドキュメントに明記されています。

保持されないならされないで、常に空白が削除された状態になってくれればよいのですが、元のExcelファイルの形式によって挙動が異なります。

具体的には、あるセルに = 1 + 2 と記入されており、これを Cell#getCellFormula() メソッドで読み取ったとき、

元のExcelファイルが .xls 形式の場合、1+2 という文字列が取得されます。つまり、空白が削除されます。
元のExcelファイルが .xlsx/.xlsm 形式の場合、 ``1 + 2 という文字列が取得されます。つまり、数式内の空白が再現されます。

これは、異なる形式のExcelファイル内の数式を比較したい場合などに問題となります。

解決策

Cell#getCellFormula() メソッドで取得した数式文字列内から不要なスペースを削除する関数を自作して数式文字列を標準化するというのは一つのアプローチです。この際、注意しなければならない点があります。

一点目は、文字列リテラル内の空白を削除してはならないことです。= "Hello, World !!" という数式と = "Hello,World!!" という数式では数式としての意味が変わってしまうからです。ただ、この程度の問題であれば対処するのはさほど難しくはないでしょう。

注意すべきことの二点目は、Excelにおいて空白（半角スペース）は「参照演算子」としての役割も持っていることです。
例えばセルに = MAX(A1:C3 B2:E5) という数式が入力されているとき、これは「セル領域A1:C3とセル領域B2:E5の共有部分、すなわちセル領域B2:C3の中の最大値」を表します。
Excelはセル領域に名前を付けることができます。従って、= 領域A 領域B という文字列も立派な数式になり得ます。（「領域A」と「領域B」共有部分が複数のセルであれば「#VALUE!」というエラーになりますし、単一のセルであればそのセルの値が計算結果となります。）
当然ながら、このような「参照演算子」としての空白を削除してはなりません。

これらの点を考慮して標準化関数を作成するのは一つの手です。

もしくは、こちらのPDFファイルなどを参照してExcelのファイル形式を理解し、パーサーを自作することもできるかもしれません。

いずれにせよ、骨の折れる作業です。

No.8 斜めの罫線を操作するためのAPIが提供されていない

説明

CellStyle インタフェースおよびその派生インタフェースである HSSFCellStyle, XSSFCellStyle インタフェースには、罫線を操作するためのいくつかのAPIが用意されています。

CellStyle
- 罫線スタイルの取得
  - getBorderBottom(): BorderStyle
  - getBorderLeft(): BorderStyle
  - getBorderRight(): BorderStyle
  - getBorderTop(): BorderStyle
- 罫線色の取得
  - getBottomBorderColor(): short
  - getLeftBorderColor(): short
  - getRightBorderColor(): short
  - getTopBorderColor(): short
- 罫線スタイルの設定
  - setBorderBottom(BorderStyle): void
  - setBorderLeft(BorderStyle): void
  - setBorderRight(BorderStyle): void
  - setBorderTop(BorderStyle): void
- 罫線色の設定
  - setBottomBorderColor(short): void
  - setLeftBorderColor(short): void
  - setRightBorderColor(short): void
  - setTopBorderColor(short): void
HSSFCellStyle
- （略）
XSSFCellStyle
- （略）

御覧の通り、縦（Left, Right）横（Top, Bottom）の罫線に関するAPIのみが提供されており、斜めの罫線に関するAPIは提供されていません。

解決策

こちらのブログ（プログラム備忘録 etc - Java Apache POI 斜線の罫線を取得する xlsファイル限定）などで公開されている情報を参考にして、斜めの罫線に関する処理を自分で実装する必要があります。
その際には、POI内部のクラス構成などに対する理解が必要となります。

No.9 空コメントに関する挙動がファイル形式によって異なる

説明

空のコメント（セルにコメントは付されているが、そのコメントの中に何も記載されていないもの）について、xls 形式のブックからは空文字列 "" が返される一方で、xlsx/xlsm 形式のブックからは null が返されます。

Cell cell = (何らかの方法でセルオブジェクトを取得)
Comment comment = cell.getCellComment();  // コメントが無い場合はnullが返される
RichTextString richText = comment.getString();  // 空コメントの場合も非null値が返される
String str = richText.getString();  // ★ブック形式によって空コメントの場合の挙動が異なる★

解決策

例えば次のような方法で空文字列 "" に正規化するのがよいのではないでしょうか。

String str = Optional.ofNullable(richText.getString).orElse("");

No.10 XSSFComment#setVisible(boolean) が機能しない

説明

表示されているセルコメントに対して setVisible(false) を実行しても非表示になりませんし、非表示のセルコメントに対して setVisible(true) を実行しても表示されません。
もしかして true と false が逆なのかもと思って試してみましたが、やはり効果なしでした。

解決策

XSSFComment#setVisible(boolean) メソッドは使えません。
代替方法も見つけられていません。

中締め

ちょっと疲れたので、一旦ここまでとします。

しつこいようですが、Apache POI はとても便利です。
とても便利なのですが、以上に挙げたように、使い倒そうとすると段々気が狂いそうになってくるのも事実なのであります。

いつの日か自分自身でコントリビュートしたいと思いつつ、精神衛生を保つためしばらく離れることにしたいと思います。

とても便利なので使ってやってくださいませ。こちらで公開していますし、記事も書いています。 ↩
いつかOSSにコントリビュートするというのが、アマチュアプログラマとしての次の目標なのであります。 ↩
ただし実際には、No.5 で紹介する落とし穴があります。 ↩
ただし実際には、No.4 で紹介するように、このメソッドは機能しません。 ↩
ただし実際には、これにも No.6 で紹介する落とし穴があります。 ↩

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up