はじめに
この記事では、Apache POIを使用してExcelファイルからセルの値を取得する際に発生する予期せぬエラーについて説明します。セルの書式が「文字列」に設定しているにも関わらず、formatter.formatCellValue()で発生するエラーの原因とその解決策です。
対象読者: Apache POIを使ってExcelのセルの値を操作している開発者で、書式や型に関連する問題に直面している方
問題の背景
formatter.formatCellValue()の基本
DataFormatterクラスのformatCellValue()メソッドは、Excelのセルから値を取得し、その値をフォーマットして文字列として返します。通常は、どんな型式が入ってきても対応できるメソッドとなっています。
予期せぬエラーの発生
Excelでセルの書式を「文字列」に設定したにも関わらず、formatter.formatCellValue()を使って値を取得しようとすると、エラーが発生しました。
DataFormatter formatter = new DataFormatter();
String formattedValue = formatter.formatCellValue(cell); // エラー発生個所
エラーの内容は下記となっており、原因を特定していきました。
(e.getMessage()にはNullが入っていました。)
NotSupportedException@143 "com.monitorjbl.xlsx.exceptions.NotSupportedException"
原因の特定
デバッグによる確認
Excelではセルに「文字列」書式を適用しても、POIのgetCellType()メソッドで確認すると、セルのタイプが「NUMERIC」として認識されていました。このため、formatCellValue()を使用すると、数値型のセルとして扱われエラーとなっていた模様です。
cell.getCellType(); // CellType.NUMERICとなっている
取り込んだExcelデータの確認
今回の事象発生した理由は、書式が「標準」のものを「文字列」に変更したため発生したと考えられます。「標準」から「文字列」に変更した数字でも上記エラーが発生しないケースもあったため、内容を確認していきました。
①書式「標準」に数字を入力
→右詰め(数字)で表示される
②書式「標準」を「文字列」に変換
→左詰め(文字列)で表示される
③「F2」などで入力状態にする
→右詰め(数字)で表示される
④ ③の入力状態から元の状態に戻す
→左詰め(文字列)に戻り、セル左上に緑色の三角が出る
※以降入力状態にしても、③のように右詰めにはならない
それぞれのケースを動かして確認
①②④のデータで書式確認を用いてCelltypeの内容とエラーが発生するケースをそれぞれ確認していきます
・使用したExcelデータ
・使用したExcelデータの書式
| A | B | C | |
|---|---|---|---|
| 1 | ①標準 | ②文字列(入力時に数値型) | ④文字列 |
・使用したソースコード
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
private void checkCellType(Row excelRow){
Cell cellA = excelRow.getCell(0);
Cell cellB = excelRow.getCell(1);
Cell cellC = excelRow.getCell(2);
System.out.println(cellA.getCellType()); // 1：標準
System.out.println(cellB.getCellType()); // 2：文字列(？)
System.out.println(cellC.getCellType()); // 4：文字列
try{
formatter.formatCellValue(cellA);
}
catch(Exception e){
System.out.println("エラーA");
}
try{
formatter.formatCellValue(cellB);
}
catch(Exception e){
System.out.println("エラーB");
}
try{
formatter.formatCellValue(cellC);
}
catch(Exception e){
System.out.println("エラーC");
}
}
NUMERIC
NUMERIC
STRING
エラーA
エラーB
つまり②の状態だと、Javaでは書式が「標準」のままになっています。そのため、「文字列」のところに「数値」が入っておりエラーになっていたようです。ちなみに①の「標準」を「数値」にしてもエラーとなりました。
ただformatCellValue()は文字でも数字でも正常に値を取得できるはずなのでもう少し探っていきます。
ライブラリの確認
今回触っていたアプリケーションはMavenを使っていたためpom.xmlの中身を確認しました。
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
..略称
<packaging>pom</packaging>
</project>
もしやと思い確認したところ、最新は5.3.0だったようです(2024/11時点)
最新では上記事象は発生しないようです。
解決策
cell.getCellType()で型を確認
cell.getCellType()を使用して、セルの型がNUMERICであるかどうかを確認し、NUMERICの場合はformatter.formatCellValue()ではなく、cell.getNumericCellValue()を使用します。
getNumericCellValue()は常にdouble型を返すため、整数でも小数点以下が表示されてしまうことがあります。これらは、DecimalFormatを使って数値をフォーマットし、整数として表示することができます。
また、日付型の対処として、DateUtil.isCellDateFormatted()を用いて、日付型かの判断を行い、日付型の場合はフォーマットを指定して表示させます。
import java.text.SimpleDateFormat;
import java.math.BigDecimal;
if(cell.getCellType() == NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); // 取り込みたい日付形式
System.out.println(sdf.format(cell.getDateCellValue()));
} else {
double numericValue = cell.getNumericCellValue();
BigDecimal bigDecimalValue = new BigDecimal(numericValue); // 科学的記数法を避けてそのまま表示
System.out.println(bigDecimalValue.toPlainString());
}
}
else{
System.out.println(formatter.formatCellValue(cell));
}
上記コードの詳細説明
通貨型はNUMERICとして認識されます。Excel上で通貨型で表示されていたも、取り込んだ際は元の数値となります(例：¥1,000→1000)。ちなみに後で紹介しますが、cell.getStringCellValue()を使用すると、表示形式そのままの形(例：¥1,000)で取得できます。
日付型もNUMERICとして認識されますが、そのまま取得するとシリアル値で表示されてしまうのでフォーマットの指定が必要となります。
数値型の場合は、BigDecimalを使用することがおすすめです。理由としては、整数値を入れているのに小数(10．0など)表示される事象を回避できるからです。
色々検証してみたのですが、セルの型がBLANK、FORMULAの場合はエラーにならないようです。(それぞれ空文字や”SUM(A1:C1)”などが正常に出力されます)
検証：cell.getStringCellValue()ではダメなのか？
これだけ調べておいて後から分かったのですが、cell.getStringCellValue()を使うと数字も文字も取得できるみたいです。
全部cell.getStringCellValue()で取得すればよいのではないかと思い検証してみました。
検証①小数点を含む数字
| 表示 | 実際の値 | 型式 | cell.getStringCellValue() |
|---|---|---|---|
| 1234567890 | 1234567890.12345 | 文字列 | 1234567890 |
| 1234567890.12345 | 1234567890.12345 | 文字列 | 1234567890.12345 |
| 1234567890.12345 | 1234567890.12345 | ユーザー指定 | 1234567890.12345 |
| 1234567890.12345 | 1234567890.12345 | 数値 | 1234567890.12345 |
表示通り取れているようです(1列目のは上で記載している②のバグなのでどうしようもなさそうです…)
検証②大きい数字
| 表示 | 実際の値 | 型式 | cell.getStringCellValue() |
|---|---|---|---|
| 1.23457E+15 | 1234567890123450 | 標準 | 1.23457E+15 |
| 1234567890123450 | 1234567890123450 | 数値 | 1234567890123450 |
| ######### | 1234567890123450 | 数値 | 1234567890123450 |
| 1234567890123450 | 1234567890123450 | 文字列 | 1234567890123450 |
セルのサイズを超えていて出る「#########」(４列目)は問題ないですが、標準(１列目)だと科学的記数法で取得されるようです。
検証③その他書式(日付や通貨)
| 表示 | 実際の値 | 型式 | cell.getStringCellValue() |
|---|---|---|---|
| 2021/10/26 | 2021/10/26 | 日付 | 10/26/21 |
| 2021/10/26 | 2021/10/26 | ユーザー定義 | 2021/10/26 |
| 2021年10月26日 | 2021/10/26 | 日付 | Tuesday, October 26, 2021 |
| 2021年10月26日 | 2021/10/26 | ユーザー定義 | reserved-44495x1F |
| 2021-10-26 | 2021/10/26 | 日付 | 2021-10-26 |
| 20211026 | 2021/10/26 | ユーザー定義 | 20211026 |
| 0:00:00 | 0:00:00 | 時刻 | 12:00:00 AM |
| ¥1,110,301,080 | 1110301080 | 通貨 | ¥1,110,301,080 |
通常の日付指定(列1.3.7)の場合、標準的に出るものは、変わった形で出る用です。取得内容を確認すると、numericFormatが勝手に設定されるため起こっていると考えられます。書式を設定している場合(列2.5.6)だとExcelの表示通りに出るようです。
ただ、日本語は対応していないようです(列4)。ユーザー定義で無理やり年月日表示をするようにしたところ、”reserved-0x1F”と表示されます。
(”reserved-0x1F”はPOIが未対応のフォーマットコードや異常なセルスタイルの場合に出る表記です)
通貨型に関しては問題なく表示されますが、ユーザー定義でPOIが受け付けていない場合は上記と同じ表示がされるはずです。
まとめ
formatter.formatCellValueを使う際に生じたエラーについて記載しました。Apache POIのVerによってはformatCellValueで数値を取得しようとするとエラーが発生します。また、Excel側では文字列と設定しても、プログラム上では前の書式が残っているケースがあります。
解決策として、cell.getCellType()でセルの型を確認し、NUMERICの場合はgetNumericCellValue()を使用して表示する方法を紹介しました。