Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

この記事について

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番ドキュメントとして利用するという環境の人も多いと思うので、以下では基本的な装飾の方法を示します。
以降に示す装飾もRowCellの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

参考にしたサイト・記事

kkitam_465daa
検索して躓いたことを備忘録的にまとめています。 このアカウントによる投稿内容は私自身の見解であり、必ずしも所属組織の立場、戦略、意見を代表するものではありません。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away