Apache POIを使ってJavaからxlsxファイルを読み込み・生成するためのメモ

# この記事について
Apache POIを使ってみたい人が、コピペしながらExcelファイルを操作出来ることを目標としています。
ここで示す実装例は、あくまでも、Apache POIを動かすための最低限の実装であり、別途エラーハンドリングをする必要があります。
コードの動作を確認しているバージョンは4.1.0です。

# Apache POIの用意
こちらから用意できます。
https://poi.apache.org/download.html

# Apache POIにおけるオブジェクト
`org.apache.poi.ss.usermodel.Workbook`がxlsxファイルのブック全体を表します。

読み書き対象のxlsxファイルについて、`Workbook`オブジェクトを生成した後に、まず初めに対象のシートを指定します。これが`org.apache.poi.ss.usermodel.Sheet`です。

`Sheet`オブジェクトを`Workbook`オブジェクトから取得後、読み書き対象の行にを指定します。これが、`org.apache.poi.ss.usermodel.Row`です。

`Row`オブジェクトを`Sheet`オブジェクトから取得後、読み書き対象のセルを指定します。列を指定して、セルを指定します。これが`org.apache.poi.ss.usermodel.Cell`です。

`Cell`オブジェクトを`Row`オブジェクトから取得後、普段Excelで行っているような、セルに対する値の読み書きが可能になります。

# .xlsxファイルの読み込み
## Cellオブジェクトの取得
まず、ファイルパスから`Workbook`オブジェクトを取得します。

```java
Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
```

`Sheet`オブジェクトは、シート名及びindex(１番左のシートから0始まり)から取得できます。

```java
//シート名から取得
Sheet sheet = workbook.getSheet(name);

//indexから取得
Sheet sheet = workbook.getSheetAt(index);
```

`Sheet`オブジェクトから、`Row`オブジェクトを取得後、`Cell`オブジェクトを取得します。

```java
Row row = sheet.getRow(rowIndex);
Cell cell = row.getCell(columIndex);
```

基本的に`Row`オブジェクト、`Cell`オブジェクトの取得には、何行目、何列目といった、0スタートのindexを指定する必要がありますが、`CellReference`を使うことで、Excelで馴染みのある、A1形式で指定することも可能です。

```java
CellReference reference = new CellReference(a1Position);
Row row = sheet.getRow(reference.getRow());
Cell cell = row.getCell(reference.getCol());
```

## Cellの中身の取得

`Cell`オブジェクトを取得後、セルの中身を文字列として得る場合、`Cell.getCellType()`メソッドを実行して、セルの中に入ってる値の種類を取得し、それぞれに合わせて、いくつか個別に対応するメソッドを設計する必要があります。

セルの中身が文字列(`STRING`)の場合、標準の`Cell.getStringCellValue()`で読み取りが可能です。

数値(`NUMERIC`)の場合、日付も数値扱いなので、これに注意して、自前でメソッドを用意した方が良いです。
日付の場合、一般に、表示フォーマットのスタイルが適応された値が欲しい場合が殆どだと思うので、自前のメソッドでこれに対応します。

真理値(`BOOLEAN`)の場合、標準の`Cell.getBooleanCellValue()`で`boolean`の値を取得出来るので、文字列に変換して下さい。

関数値(`FORMULA`)の場合、関数の式を文字列として欲しいだけなら、`Cell.getCellFormula()`で取得可能です。
ただ、一般には、関数の計算結果が欲しい場合が殆どだと思うので、その場合は自前でメソッドを用意します。

値が入っていない(`BLANK`)の場合、往々にして結合されたセルであることが多いです。結合されたセルは結合領域の最も左上のセルに値が入っていることになっているので、結合領域の別のセルを読み取っても空白扱いになります。
結合されたセルに関しては、Excelの使用上特殊な保存のされ方をするので、別途メソッドを用意して、これに対応します。

`Cell`オブジェクトを受け取って、`String`を返すメソッドの簡易的な実装例は次のようになります。自前のメソッドについては、以降で説明します。

```java
String readCell(Cell cell) {
    switch (cell.getCellType()) {
    //文字列(標準)
    case STRING:
    	return cell.getStringCellValue();
    //数値(自前)
    case NUMERIC:
    	return getStringNmericValue(cell);
    //真理値(標準)
    case BOOLEAN:
    	return Boolean.toString(cell.getBooleanCellValue());
    //関数値(自前)
    case FORMULA:
    	return getStringFormulaValue(cell);
    //空白(自前)
    case BLANK:
    	return getStringRangeValue(cell);
    default:
    	System.out.println("Unexpected Type: "+cell.getCellType());
    	return null;
    }
}
```

### 数値(`NUMERIC`)の自前メソッド
セルの中身の値が、単なる数値の場合はそのまま文字列に変換して返せば良いですが、日付の場合は、スタイルを適応させてから、文字列として取得します。

標準の日付のフォーマットの場合、`java.time`を使うことで文字列として取得出来ますが、ユーザー定義型の場合は、`org.apache.poi.ss.format.CellFormat`を使って、スタイルを適応させた後にそのテキストの値を取得します。

以下が簡単な実装例です。

```java
//日付を考慮して、数値の取得
String getStringNmericValue(Cell cell) {
	//標準の日付の場合
	if(DateUtil.isCellDateFormatted(cell)) {
		Date date = cell.getDateCellValue();
		//java.time.DateTimeFormatterで文字列化
		DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss.SSS");
		LocalDateTime localDateTime = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
		return dateTimeFormatter.format(localDateTime);
	}
	//ユーザー定義型で日付を設定している場合
	if(BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX <= cell.getCellStyle().getDataFormat()) {
		CellFormat cellFormat = CellFormat.getInstance(cell.getCellStyle().getDataFormatString());
		return cellFormat.apply(cell).text;
	}
	//日付じゃない場合は数値を返す。
	return Double.toString(cell.getNumericCellValue());
}
```

### 関数値(`FORMULA`)の自前メソッド
関数の計算結果を取得する場合、`FormulaEvaluator`オブジェクトに、対象の`Cell`オブジェクトを渡して計算結果を得ます。

ただし、計算結果は文字列ではなく、`CellValue`オブジェクトで返ってくるので、計算結果が文字列なのか、数値なのか、真理値なのか、再度`CellValue.getCellType()`で判定し、それぞれ、POIの標準のメソッドで値を読み込みます。

また、注意点として、`FormulaEvaluator`オブジェクトは対応していない関数がいくつか存在します。
現在対応している関数は最後に一覧を載せているので参考にして下さい。
対応していない関数を実行する場合、`EvaluationException`が投げられます。

ユーザー定義の関数を実行したい場合は、こちらを参考にして下さい。
https://poi.apache.org/components/spreadsheet/user-defined-functions.html

以下が`FORMULA`の自前メソッドの自前メソッドの簡単な実装例です。

```java
//関数を実行して読み取り
String getStringFormulaValue(Cell cell) {
	try {
        //関数の実行
		CreationHelper helper = workbook.getCreationHelper();
		FormulaEvaluator evaluator = helper.createFormulaEvaluator();
		CellValue value = evaluator.evaluate(cell);

        //計算結果の中身に合わせてメソッドの呼び出し先を変える。
		switch (value.getCellType()) {
		case STRING:
			return value.getStringValue();
		case NUMERIC:
			return Double.toString(value.getNumberValue());
		case BOOLEAN:
			return Boolean.toString(value.getBooleanValue());
		default:
			System.out.println("Unexpected Type: "+value.getCellType());
			return null;
		}
	}catch(Exception e) {
		return cell.getCellFormula();
	}
}
```

### 空白(`BLANK`)の自前メソッド
`BLANK`の場合、単なる空白セルなら読み込めなくても困りませんが、結合セルの場合は読み込めないと困るので、結合セルに対応させます。

結合されたセルは、シートの特殊な領域に一括で保存されており、読み取り対象のセルが結合されたセルかどうかを判定するには、結合されたセルの領域の一覧を取り出して、読み取り対象のセルが含まれるような、結合された領域が存在するかどうかを判定します。

読み取り対象のセルが領域に含まれるような、結合された領域が存在する場合、その領域の左上を指定して、値を読み込みます。

この際、セルの中身がどの種類かは、`Cell.getCellType()`を使わないと判定できないため、改めて、`Cell`から`String`を返すメソッドに、左上のセルを渡します。(上の実装例ではreadCellメソッド)

以下が簡単な実装例です。

```java
//結合セルの読み取り
String getStringRangeValue(Cell cell) {
    //読み取り対象のセルのindexを取得
	int rowIndex = cell.getRowIndex();
	int columnIndex = cell.getColumnIndex();

    //結合された領域に含まれるかをしらみつぶしに探す。
	int size = sheet.getNumMergedRegions();
	for (int i = 0; i < size; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
        //含まれる領域があった場合
		if (range.isInRange(rowIndex, columnIndex)) {
			//左上のcellの取得
			Cell firstCell = sheet.getRow(range.getFirstRow()).getCell(range.getFirstColumn());
            //Cell->Stringのメソッドに投げる。
			return readCell(firstCell);
		}
	}
	return null;
}
```


# .xlsxファイルの書き込み

## `Workbook`,`Sheet`,`Row`,`Cell`オブジェクトの生成
xlsx形式のブックを新規作成する場合、`org.apache.poi.xssf.usermodel.XSSFWorkbook`のインスタンスを生成して、`Workbook`型の変数で受け取ります。

`Sheet`オブジェクトを生成する場合は、一度`WorkbookUtil.createSafeSheetName()`でシートに使える名前に変換してから、生成するとベターです。

`Row`オブジェクト、`Cell`オブジェクトについて、初期状態の`Sheet`オブジェクトでは、どの行・セルも`null`となっているため、セルへの書き込みを行う前に一度、それぞれのオブジェクトを生成する必要があります。
A1形式で行・列を指定したい場合は、上述の読み込みで行ったときと同様に、`CellReference`を使用します。

```java
//Workbookオブジェクトの生成
Workbook workbook = new XSSFWorkbook();

//Sheetオブジェクトの生成
String safeName = WorkbookUtil.createSafeSheetName(name);
Sheet sheet = workbook.createSheet(safeName);

//A1形式からindexへの変換
CellReference reference = new CellReference(a1Position);
int rowIndex = reference.getRow();
int columIndex = reference.getCol();

//Rowオブジェクトの生成
Row row = sheet.createRow(rowIndex);

//Cellオブジェクトの生成
Cell cell = row.createCell(columIndex);
```

## ファイル出力
以降で示す、様々な変更を`Workbook`オブジェクトに加えた後、最終的にxlsxファイルを書き出すには`FileOutputStream`を使用します。
(簡潔化のために省略して載せていますが、実際のコードでは、適切にtry-catchを記述して下さい。)

```java
FileOutputStream fileOut = new FileOutputStream(filePath);
workbook.write(fileOut);
fileOut.close();
```

## セルへの文字列の書き込み
文字列の書き込みは非常に単純で、`Cell.setvalue()`を呼び出すだけです。
ただし、セルをindex指定して書きこむようなメソッドを作成する場合、その位置の`Row`オブジェクトや`Cell`オブジェクトのnullチェックをした方がベターです。

簡単な実装例を示します。

```java
void writeCellAt(int rowIndex, int columIndex, String value) {
    //Row,Cellのnullチェック
	Row row = sheet.getRow(rowIndex);
    if(row == null) {
        row = sheet.createRow(rowIndex);
    }
	Cell cell = row.getCell(columIndex);
    if(cell == null) {
		cell = row.createCell(columIndex);
	}

    //文字列の書き込み
	cell.setCellValue(value);
}
```

基本的に文字列さえ書き込めれば、複雑な処理は、Javaの機能やライブラリを使って実装可能だと思います。

単純な表形式であればcsvファイルで可能ですが、わざわざxlsxを使う理由としていくつかのセルの装飾があると思います。
PDFやdocxよりも、xlsxが１番ドキュメントとして利用するという環境の人も多いと思うので、以下では基本的な装飾の方法を示します。
以降に示す装飾も`Row`・`Cell`のnullチェックをした方が良いことに変わりありません。

## セルの結合
結合されたセルを読み込むときに`Sheet`オブジェクトから取得したように、今度は逆に`Sheet.addMergedRegion()`を使って、`Sheet`オブジェクトに登録をします。

```java
//topRow：１番左上のセルの行index
//topColum：１番左上のセルの列index
//bottomRow：１番右下のセルの行index
//bottomColum：１番右下のセルの列index
CellRangeAddress cellRangeAddress = new CellRangeAddress(topRow, bottomRow, topColum, bottomColum);
sheet.addMergedRegion(cellRangeAddress);
```

## 行の高さ変更
`Row.setHeightInPoints()`で行の高さを変えることが可能です。具体的な高さを指定することが難しい場合は、デフォルト値の倍数で指定することも可能です。

```java
//倍数
float mult = 2.0f;
//デフォルト値の2倍に設定
row.setHeightInPoints(mult*sheet.getDefaultRowHeightInPoints());
```

折り返して全体を表示するには、対象の`Cell`オブジェクトに対して、`CellStyle`を使って次のようにします。

```java
CellStyle style = workbook.createCellStyle();
style.setWrapText(true);
cell.setCellStyle(style);
```

これで高さが上手く動的にフィットする値にならない場合は、こちらを参考にして下さい。
https://stackoverflow.com/questions/19145628/auto-size-height-for-rows-in-apache-poi

また、高さ調整が上手くいかない場合は、改行などをカウントして、倍数で設定する方法もあるようです。
https://stackoverflow.com/questions/45387206/how-to-increase-the-height-of-the-excel-row-using-apache-poi-having-merged-cell/45388037

## 罫線の描画
罫線を描画するには、以下のように、対象の`Cell`オブジェクトに対して、`CellStyle`を使って、上下左右それぞれ個別に指定します。
太さは、`BorderStyle`で指定します。紛らわしい名前なのですが、`BorderStyle.THIN`が一般的な太さの罫線を表し、`BorderStyle.MEDIUM`が一般的には太めの罫線を表します。

```java
CellStyle style = workbook.createCellStyle();
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);

cell.setCellStyle(style);
```

## フォント色の変更
フォントの色も対象の`Cell`オブジェクトに対して、`CellStyle`で変更が可能です。

色に関しては、標準のカラーパレットが予め変数として用意されています。
色の見本は、こちらなどを参考にして下さい。
https://lincolnminto.wordpress.com/2014/04/07/apache-poi-referencia-para-estilos-de-celulas-apache-poi-cellstyles-reference/

```java
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setColor(IndexedColors.WHITE.getIndex());
style.setFont(font);
cell.setCellStyle(style);
```

## セルの塗りつぶし
セルの塗りつぶしもまた、`Cell`オブジェクトに対して、`CellStyle`で変更が可能です。

これもまた、紛らわしいですが、`CellStyle.setFillBackgroundColor()`ではなく、`CellStyle.setFillForegroundColor()`を使用します。

```java
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.CORAL.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);
```

正しく動作するか確かめていませんが、フォントの色やセルの塗りつぶしの色について、次のようにRGB指定する方法もあるようです。

```java
IndexedColorMap colorMap = workbook.getStylesSource().getIndexedColors();
XSSFColor grey = new XSSFColor(new java.awt.Color(192,192,192), colorMap);
cellStyle.setFillForegroundColor(grey);
```

参考：https://stackoverflow.com/questions/10912578/apache-poi-xssfcolor-from-hex-code

## 画像の貼り付け
画像の貼り付けは、`Workbook`オブジェクトに対して行います。

`IOUtils.toByteArray()`でバイト列に変換した後、`Workbook.addPicture()`でブックに登録した後、画像のindexを返り値として取得します。このときの指定出来るファイルフォーマットの一部を次に示します。

| 定数              | フォーマット |
| :---------------- | :----------: |
| PICTURE_TYPE_DIB  |     bmp      |
| PICTURE_TYPE_JPEG |     jpeg     |
| PICTURE_TYPE_PNG  |     png      |

画像の位置や大きさは、`ClientAnchor`を使って指定します。
左上のセルと、右下のセルを指定して、位置と大きさを調整します。ただし、右下のセルを指定する`ClientAnchor.setCol2()`と`ClientAnchor.setRow2()`については、指定したindex-1の位置(一つ左上のセル)まで引き伸ばされるので、実際には引き伸ばしたい範囲の右下のセルのindex+1の値を指定します。

アンカータイプは、Excelで言うところの、図形の書式設定のプロパティ欄で、セルが移動したりサイズ変更したりしたとき、画像にも変更を加えるかどうかを指定できます。定数と設定の一覧を次に示します。

| 定数                 | 説明                                                             |
| :------------------- | :--------------------------------------------------------------- |
| DONT_MOVE_AND_RESIZE | 行・列のリサイズが行われても、画像の移動・リサイズを行わない。   |
| DONT_MOVE_DO_RESIZE  | 画像の移動は行わないが、アンカーのセルに合わせてリサイズは行う。 |
| MOVE_AND_RESIZE      | 移動もリサイズも行う。                                           |
| MOVE_DONT_RESIZE     | アンカーのセルに合わせて移動を行うが                             | 、リサイズは行わない |

最終的に、`Drawing`オブジェクトを使って、指定したアンカーの位置に、指定したindexの画像を貼り付けます。
以下が簡単な実装例です。(簡潔化のために省略して載せていますが、実際のコードでは、適切にtry-catchを記述して下さい。)

```java
//バイト列へ変換
InputStream in = new FileInputStream(filePath);
byte[] bytes = IOUtils.toByteArray(in);
in.close();

//画像の登録、indexの取得
int pictureIndex = workbook.addPicture(byteWorkbook.PICTURE_TYPE_PNG);

//アンカーで位置・大きさを指定
ClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
anchor.setCol1(topColum);
anchor.setRow1(topRow);
anchor.setCol2(bottomColum+1);
anchor.setRow2(bottomRow+1);
anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);

//指定したアンカーの位置に、指定したindexの画像を貼り付け
Drawing patriarch = sheet.createDrawingPatriarch();
patriarch.createPicture(anchor, pictureIndex);
```

# 【Appendix】 POIが対応しているビルトイン関数
参考：https://poi.apache.org/components/spreadsheet/eval-devguide.html

|             |            |             |            |             |
| :---------: | :--------: | :---------: | :--------: | :---------: |
|     ABS     |    ACOS    |    ACOSH    |  ADDRESS   |     AND     |
|    ASIN     |   ASINH    |    ATAN     |   ATAN2    |    ATANH    |
|   AVEDEV    |  AVERAGE   |   BIN2DEC   |  CEILING   |    CHAR     |
|   CHOOSE    |   CLEAN    |    CODE     |   COLUMN   |   COLUMNS   |
|   COMBIN    |  COMPLEX   | CONCATENATE |    COS     |    COSH     |
|    COUNT    |   COUNTA   | COUNTBLANK  |  COUNTIF   |  COUNTIFS   |
|    DATE     |    DAY     |   DAYS360   |  DEC2BIN   |   DEC2HEX   |
|   DEGREES   |   DELTA    |    DEVSQ    |    DGET    |    DMIN     |
|   DOLLAR    |   EDATE    |   EOMONTH   | ERROR.TYPE |    EVEN     |
|    EXACT    |    EXP     |    FACT     | FACTDOUBLE |    FALSE    |
|    FIND     |   FIXED    |    FLOOR    |     FV     |   HEX2DEC   |
|   HLOOKUP   |    HOUR    |  HYPERLINK  |     IF     |   IFERROR   |
|  IMAGINARY  |   IMREAL   |    INDEX    |  INDIRECT  |     INT     |
|  INTERCEPT  |    IPMT    |     IRR     |  ISBLANK   |    ISERR    |
|   ISERROR   |   ISEVEN   |  ISLOGICAL  |    ISNA    |  ISNONTEXT  |
|  ISNUMBER   |   ISODD    |    ISREF    |   ISTEXT   |    LARGE    |
|    LEFT     |    LEN     |     LN      |    LOG     |    LOG10    |
|   LOOKUP    |   LOWER    |    MATCH    |    MAX     |    MAXA     |
|   MDETERM   |   MEDIAN   |     MID     |    MIN     |    MINA     |
|   MINUTE    |  MINVERSE  |    MIRR     |   MMULT    |     MOD     |
|    MODE     |   MONTH    |   MROUND    |     NA     | NETWORKDAYS |
|     NOT     |    NOW     |    NPER     |    NPV     |   OCT2DEC   |
|     ODD     |   OFFSET   |     OR      | PERCENTILE |     PI      |
|     PMT     |  POISSON   |    POWER    |    PPMT    |   PRODUCT   |
|   PROPER    |     PV     |  QUOTIENT   |  RADIANS   |    RAND     |
| RANDBETWEEN |    RANK    |    RATE     |  REPLACE   |    REPT     |
|    RIGHT    |   ROMAN    |    ROUND    | ROUNDDOWN  |   ROUNDUP   |
|     ROW     |    ROWS    |   SEARCH    |   SECOND   |    SIGN     |
|     SIN     |    SINH    |    SLOPE    |   SMALL    |    SQRT     |
|    STDEV    | SUBSTITUTE |  SUBTOTAL   |    SUM     |    SUMIF    |
|   SUMIFS    | SUMPRODUCT |    SUMSQ    |  SUMX2MY2  |  SUMX2PY2   |
|   SUMXMY2   |     T      |     TAN     |    TANH    |    TEXT     |
|    TIME     |   TODAY    |  TRANSPOSE  |    TRIM    |    TRUE     |
|    TRUNC    |   UPPER    |    VALUE    |    VAR     |    VARP     |
|   VLOOKUP   |  WEEKDAY   |   WEEKNUM   |  WORKDAY   |    YEAR     |
|  YEARFRAC   |

# 参考にしたサイト・記事
- ひしだま's ホームページ Apache POI
http://www.ne.jp/asahi/hishidama/home/tech/apache/poi/

- Let's プログラミング Apache POIでExcelを操作
https://www.javadrive.jp/poi/

- スケ郎のお話 POIでEXCELに画像を挿入する
https://www.sukerou.com/2019/03/poiexcel.html

- Qiita Apache POIの覚え書き Excel読み取り編
https://qiita.com/panage/items/4476ff91d1fcec67f525

- stackoverflow Auto size height for rows in Apache POI
https://stackoverflow.com/questions/19145628/auto-size-height-for-rows-in-apache-poi

- stackoverflow How to increase the height of the excel row using Apache-POI having merged cell value greater than the cell width?
https://stackoverflow.com/questions/45387206/how-to-increase-the-height-of-the-excel-row-using-apache-poi-having-merged-cell/45388037

- stackoverflow Apache POI background color style seems not working
https://stackoverflow.com/questions/43212386/apache-poi-background-color-style-seems-not-working/43213843

- stackoverflow Apache POI XSSFColor from hex code
https://stackoverflow.com/questions/10912578/apache-poi-xssfcolor-from-hex-code