この記事について
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オブジェクトを取得します。
Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
Sheetオブジェクトは、シート名及びindex(１番左のシートから0始まり)から取得できます。
//シート名から取得
Sheet sheet = workbook.getSheet(name);
//indexから取得
Sheet sheet = workbook.getSheetAt(index);
Sheetオブジェクトから、Rowオブジェクトを取得後、Cellオブジェクトを取得します。
Row row = sheet.getRow(rowIndex);
Cell cell = row.getCell(columIndex);
基本的にRowオブジェクト、Cellオブジェクトの取得には、何行目、何列目といった、0スタートのindexを指定する必要がありますが、CellReferenceを使うことで、Excelで馴染みのある、A1形式で指定することも可能です。
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を返すメソッドの簡易的な実装例は次のようになります。自前のメソッドについては、以降で説明します。
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を使って、スタイルを適応させた後にそのテキストの値を取得します。
以下が簡単な実装例です。
//日付を考慮して、数値の取得
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の自前メソッドの自前メソッドの簡単な実装例です。
//関数を実行して読み取り
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メソッド)
以下が簡単な実装例です。
//結合セルの読み取り
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を使用します。
//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を記述して下さい。)
FileOutputStream fileOut = new FileOutputStream(filePath);
workbook.write(fileOut);
fileOut.close();
セルへの文字列の書き込み
文字列の書き込みは非常に単純で、Cell.setvalue()を呼び出すだけです。
ただし、セルをindex指定して書きこむようなメソッドを作成する場合、その位置のRowオブジェクトやCellオブジェクトのnullチェックをした方がベターです。
簡単な実装例を示します。
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オブジェクトに登録をします。
//topRow：１番左上のセルの行index
//topColum：１番左上のセルの列index
//bottomRow：１番右下のセルの行index
//bottomColum：１番右下のセルの列index
CellRangeAddress cellRangeAddress = new CellRangeAddress(topRow, bottomRow, topColum, bottomColum);
sheet.addMergedRegion(cellRangeAddress);
行の高さ変更
Row.setHeightInPoints()で行の高さを変えることが可能です。具体的な高さを指定することが難しい場合は、デフォルト値の倍数で指定することも可能です。
//倍数
float mult = 2.0f;
//デフォルト値の2倍に設定
row.setHeightInPoints(mult*sheet.getDefaultRowHeightInPoints());
折り返して全体を表示するには、対象のCellオブジェクトに対して、CellStyleを使って次のようにします。
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が一般的には太めの罫線を表します。
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/
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()を使用します。
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.CORAL.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);
正しく動作するか確かめていませんが、フォントの色やセルの塗りつぶしの色について、次のようにRGB指定する方法もあるようです。
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を記述して下さい。)
//バイト列へ変換
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.htmlQiita Apache POIの覚え書き Excel読み取り編
https://qiita.com/panage/items/4476ff91d1fcec67f525stackoverflow Auto size height for rows in Apache POI
https://stackoverflow.com/questions/19145628/auto-size-height-for-rows-in-apache-poistackoverflow 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/45388037stackoverflow Apache POI background color style seems not working
https://stackoverflow.com/questions/43212386/apache-poi-background-color-style-seems-not-working/43213843stackoverflow Apache POI XSSFColor from hex code
https://stackoverflow.com/questions/10912578/apache-poi-xssfcolor-from-hex-code