この記事について
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(1番左のシートから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が1番ドキュメントとして利用するという環境の人も多いと思うので、以下では基本的な装飾の方法を示します。
以降に示す装飾もRow
・Cell
のnullチェックをした方が良いことに変わりありません。
セルの結合
結合されたセルを読み込むときにSheet
オブジェクトから取得したように、今度は逆にSheet.addMergedRegion()
を使って、Sheet
オブジェクトに登録をします。
//topRow:1番左上のセルの行index
//topColum:1番左上のセルの列index
//bottomRow:1番右下のセルの行index
//bottomColum:1番右下のセルの列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.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