Apache POI チートシート

準備

dependencies {
    implementation("org.apache.poi:poi:5.x.x")
    implementation("org.apache.poi:poi-ooxml:5.x.x")
}

ワークブック(Workbook)

ワークブックの新規生成

Workbook workbook = new XSSFWorkbook();
// do someting
try (OutputStream os = new FileOutputStream("workbook.xlsx")) {
    workbook.write(os);
}

ワークブックを開く

Workbook workbook = WorkbookFactory.create(new File("sample.xlsx"));
Workbook workbook = WorkbookFactory.create(new FileInputStream("sample.xlsx"));

ワークブックを保存する

try (OutputStream out = new FileOutputStream(path)) {
    workbook.write(out);
}

シート(Sheet)

シート名を指定してシートを取得する

Sheet sheet = workbook.getSheet(sheetName);

シート番号を指定してシートを取得する

Sheet sheet = workbook.getSheetAt(index);
String sheetName = sheet.getSheetName();

アクティブなシートを取得する

int index = workbook.getActiveSheetIndex();
Sheet sheet = workbook.getSheetAt(index);

全てのシート名を取得する

for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
    Sheet sheet = workbook.getSheetAt(i);
    System.out.println(sheet.getSheetName());
}

新規シートを作成する

Sheet sheet = workbook.createSheet();
String safeName = WorkbookUtil.createSafeSheetName("sheetName");
workbook.setSheetName(workbook.getSheetIndex(sheet), safeName);

WorkbookUtil.createSafeSheetName() を使うとExcelで使えない文字を変換して安全な名前に変更できる。

シートを削除する

for (int i = book.getNumberOfSheets() - 1; i >= 0; i--) {
    Sheet sheet = workbook.getSheetAt(i);
    if (!sheet.getSheetName().equals("sheetName")) {
        book.removeSheetAt(i);
    }
}

シートインデックスが更新されるため末尾から処理する

シートを選択する

Sheet sheet = wb.createSheet("sheetName");
sheet.setSelected(true);

シートのセル幅を自動調整する

int lastColumn = row.getLastCellNum();
for (int i = 0; i < lastColumn; i++) {
    sheet.autoSizeColumn(i);
}

ウインドウ枠を固定する

// 先頭行の固定
sheet.createFreezePane( 0, 1, 0, 1 );

// 先頭列の固定
sheet.createFreezePane( 1, 0, 1, 0 );

// ウィンドウ枠の固定
sheet.createFreezePane( 2, 2 );

ウインドウを分割する

sheet.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );

ピクセル単位で分割点を指定し4分割される。最後のパラメータでどのペインにフォーカスがあるかを指定する。

オートフィルタを指定する

sheet.setAutoFilter(CellRangeAddress.valueOf("C5:F200"));

ズームを指定する

sheet.setZoom(75); // 75%

行(Row)

行を取得する

Row row = sheet.getRow(rowIndex);
if (row != null) {
    row = sheet.createRow(rowIndex);
}

未編集の空行の場合 null が返却される。上記は CellUtil を使って以下と同様。

Row row = CellUtil.getRow(rowIndex, sheet);

行を挿入する

int rowIndex = 10;
worksheet.shiftRows(rowIndex, worksheet.getLastRowNum(), 1);
Row newRow = worksheet.createRow(rowIndex);

1行下にシフト

カラムの場合は以下を使う。

sheet.shiftColumns(int startColumn, int endColumn, int n);

行を削除する

int rowIndex = 10;
Row row = sheet.getRow(rowIndex);
if (row != null) {
    sheet.removeRow(row);

    int lastRowNum = sheet.getLastRowNum();
    if (rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }
}

削除して1段上にシフト

カラムの場合は以下を使う。

sheet.shiftColumns(int startColumn, int endColumn, int n);

行を非表示/表示する

非表示は行の高さをゼロとすることと同意。

Row row = CellUtil.getRow(rowIndex, sheet);
row.setZeroHeight();

全ての非表示行を表示

Iterator<Row> rowIter = sheet.iterator();
while (rowIter.hasNext()) {
    Row row = rowIter.next();
    if (row.getZeroHeight()) {
        row.setZeroHeight(false);
    }
}

行をグルーピングする

int startRow = 10;
int endRow   = 15;
sheet.groupRow(startRow, endRow);

行をコピー挿入する

XSSFSheet.copyRows(List<? extends Row> srcRows, int destStartRow, CellCopyPolicy policy) を使う。このメソッドは @Beta とマークされている。

古いバージョンの場合は以下のように処理できる。

    public static Row copyRow(Sheet worksheet, int rowNum, int destNum) {

        Row sourceRow = worksheet.getRow(rowNum);

        // Save the text of any formula before they are altered by row shifting
        String[] formulasArray = new String[sourceRow.getLastCellNum()];
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
                formulasArray[i] = sourceRow.getCell(i).getCellFormula();
        }

        Row nextRow = worksheet.getRow(destNum);
        if (nextRow != null) {
            worksheet.shiftRows(destNum, worksheet.getLastRowNum(), 1);
        }
        Row newRow = worksheet.createRow(destNum);

        // Loop through source columns to add to new row
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            // Grab a copy of the old/new cell
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell;

            // If the old cell is null jump to next cell
            if (oldCell == null) {
                continue;
            } else {
                newCell = newRow.createCell(i);
            }

            // Apply to cell style
            newCell.setCellStyle(oldCell.getCellStyle());

            // If there is a cell comment, copy
            if (oldCell.getCellComment() != null) {
                newCell.setCellComment(oldCell.getCellComment());
            }

            // If there is a cell hyperlink, copy
            if (oldCell.getHyperlink() != null) {
                newCell.setHyperlink(oldCell.getHyperlink());
            }

            // Set the cell data type
            newCell.setCellType(oldCell.getCellType());

            // Set the cell data value
            switch (oldCell.getCellType()) {
                case Cell.CELL_TYPE_BLANK:
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    newCell.setCellValue(oldCell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_ERROR:
                    newCell.setCellErrorValue(oldCell.getErrorCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    newCell.setCellFormula(formulasArray[i]);
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    newCell.setCellValue(oldCell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    newCell.setCellValue(oldCell.getRichStringCellValue());
                    break;
                default:
                    break;
            }
        }

        // If there are any merged regions in the source row, copy to new row
        for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(
                        newRow.getRowNum(),
                        newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()),
                        cellRangeAddress.getFirstColumn(),
                        cellRangeAddress.getLastColumn());
                worksheet.addMergedRegion(newCellRangeAddress);
            }
        }
        return newRow;
    }

コピー元に設定されている計算式は、コピー先にコピーしても自動的に参照はインクリメントされないので注意。 FormulaParser.parse() を使って泥臭い処理が必要となる。

セルスタイルを使いまわしたくない場合は、以下のようにスタイルをクローンして設定する。クローンした場合、セルスタイルのインスタンスがExcelの上限を超えてファイルを開けなくなることがあるため注意(最近のExcelでは、自動でスタイルがマージされるが、少し古いExcelではファイルオープン時に修復が行われてスタイルが削除される)。

// Copy style from old cell and apply to new cell
CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
newCell.setCellStyle(newCellStyle);

編集済みの行範囲を取得する

int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();

セル(Cell)

セルを取得する

Cell cell = row.getCell(index);
if (cell == null) {
    cell = row.createCell(index)
}

getCell() は未利用のセルで null を返す。null時には createCell() を行うユーティリティが提供されている。

Cell cell = CellUtil.getCell(row, colIndex);

セルに値を設定する

// 数値
row.createCell(1).setCellValue(1.2);
// 論理値
row.createCell(2).setCellValue(true);

CreationHelper createHelper = workbook.getCreationHelper();

// RichText
row.createCell(3).setCellValue(
     createHelper.createRichTextString("This is a string"));

// 日時
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(
    createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(4);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);

RichTextは文字列の途中などで色が違うなどのテキストを指す。

ハイパーリンクを取得する

Hyperlink link = cell.getHyperlink();
if (link != null) {
    System.out.println(link.getAddress());
}

コメントを取得する

Comment comment = cell.getCellComment();
if (comment != null) {
  RichTextString str = comment.getString();
  String author = comment.getAuthor();
}

Comment comment = sheet.getCellComment(3, 1); // row, column

全てのセルを巡る

for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) {

    Row r = sheet.getRow(i);
    if (row == null) continue;

    for (int j = 0; j < row.getLastCellNum(); j++) {
        Cell cell = row.getCell(j, Row.RETURN_BLANK_AS_NULL);
        if (cell == null) continue;
        // do something
   }
}

セルの値を取得する

セルのタイプによりそれぞれの処理が必要となるため面倒。

private static Object getRawValue(Cell cell) {
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:   return null;
        case Cell.CELL_TYPE_ERROR:   return null; //cell.getErrorCellValue()
        case Cell.CELL_TYPE_STRING:  return cell.getRichStringCellValue().getString();
        case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue();
        case Cell.CELL_TYPE_NUMERIC: 
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                double n = cell.getNumericCellValue();
                return (n == ((double)(int) n)) ? (int) n : n;
            }
        case Cell.CELL_TYPE_FORMULA:
            FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
            CellValue val = evaluator.evaluate(cell);
            switch (val.getCellType()) {
                case Cell.CELL_TYPE_STRING:  return val.getStringValue();
                case Cell.CELL_TYPE_BOOLEAN: return val.getBooleanValue();
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        return DateUtil.getJavaDate(val.getNumberValue());
                    } else {
                        double n = val.getNumberValue();
                        return (n == ((double)(int) n)) ? (int) n : n;
                    }
                default: return null;
            }
        default: return null;
    }
}

evaluator.evaluate(cell); を evaluator.evaluateInCell(cell); とすると、計算式の計算結果がセルに設定される。

編集済みのセル範囲を取得する

Row row = sheet.getRow(sheet.getFirstRowNum();
int firstCellIndex = row.getFirstCellNum();
int lastCellIndex = row.getLastCellNum();

セルの書式設定(CellStyle)

CellStyle

CellStyle はワークブックに定義されたスタイルの定義であり、同じインスタンスを使い、複数のセルに設定する。セル毎にインスタンスを生成せずに、同じスタイルは同じCellStyleインスタンスを使いまわす必要がある。これは後述のFontインスタンスについても同じ。

表示形式を指定する

DataFormat format = workbook.createDataFormat();

CellStyle style = workbook.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.0000"));
cell.setCellStyle(style);

文字の配置を指定する

CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
cell.setCellStyle(cellStyle);

罫線を指定する

CellStyle style = workbook.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(style);

CellStyleProperty を使うこともできる。

Map<String, Object> properties = new HashMap<String, Object>();
properties.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM);
properties.put(CellUtil.TOP_BORDER_COLOR, IndexedColors.RED.getIndex());

CellUtil.setCellStyleProperties(cell, properties);

罫線をまとめて指定する

PropertyTemplate pt = new PropertyTemplate();

pt.drawBorders(new CellRangeAddress(1, 3, 1, 3),
        BorderStyle.MEDIUM, BorderExtent.ALL);

pt.drawBorders(new CellRangeAddress(9, 11, 1, 3),
        BorderStyle.MEDIUM, IndexedColors.RED.getIndex(),
        BorderExtent.OUTSIDE);
pt.drawBorders(new CellRangeAddress(10, 10, 2, 2),
        BorderStyle.NONE,
        BorderExtent.ALL);

pt.applyBorders(sheet);

背景と前景を指定する

CellStyle style = workbook.createCellStyle();
style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(FillPatternType.BIG_SPOTS);
cell.setCellStyle(style);

style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);

XSSF を直接利用時には以下のように細かな色設定が可能。

XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128), new DefaultIndexedColorMap()));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

フォントを指定する

Font font = workbook.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);

CellStyle style = workbook.createCellStyle();
style.setFont(font);
cell.setCellStyle(style);

文字の折り返しを指定する

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

row.setHeightInPoints(2 * sheet.getDefaultRowHeightInPoints());

既存スタイルを流用する

CellStyle を変更すると、同じスタイルが設定されているセルのスタイルが全て変わる。既存を流用するにはコピーして変更する

CellStyle cellStyle = row.getSheet().getWorkbook().createCellStyle();
cellStyle.cloneStyleFrom(cell.getCellStyle());

セルの参照

CellReference
- 特定のセル位置を表す
- sheetName rowIndex colIndex 及び絶対参照か相対参照かを持つ
CellRangeAddress
- セル範囲を表す
- int _firstRow int _firstCol int _lastRow int _lastCol で範囲を管理
- シート名は持たない
- 絶対相対の区別も無い
- 単に A1:B2 のような範囲だけの表現
- 複数の CellRangeAddress は、 CellRangeAddressList で表現する
AreaReference
- セル範囲を表す
- CellReference _firstCell CellReference _lastCell で範囲を管理
- 主に名前定義の範囲を操作する際に利用

CellReference

CellReference cellReference = new CellReference("A1");
String cellRef = cellReference.formatAsString(); -> A1

new CellReference(0, 0) -> A1
new CellReference(1, 0, false, true) -> $A2
new CellReference("sheet", 1, 0, false, true) -> sheet!$A2

CellReference の equals はシート名は考慮されていない。セルのインデックスと絶対相対が同じ場合は true と判定される。

文字列 <--> インデックスの変換メソッドがある。

CellReference.convertColStringToIndex("A"); -> 0
CellReference.convertNumToColString(0); -> A

CellRangeAddress

CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn);
CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");

CellRangeAddressを使いセルを結合

CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");
sheet.addMergedRegion(region);

CellRangeAddressを使いセルのスタイル設定

CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");

RegionUtil.setBorderTop(BorderStyle.MEDIUM_DASHED, region, sheet, workbook);
RegionUtil.setTopBorderColor(IndexedColors.AQUA.getIndex(), region, sheet, workbook);

CellRangeAddressList で複数範囲を扱う

CellRangeAddressList list = new CellRangeAddressList();
list.addCellRangeAddress(CellRangeAddress.valueOf("B1:C3"));
for (int i = 0; i < list.countRanges(); i++) {
    CellRangeAddress range = list.getCellRangeAddress(i);
}

数式

セルに数式を設定する

cell.setCellFormula("SUM(A4:F4)");

= は不要。

数式を取得する

if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
    System.out.println(cell.getCellFormula()); // SUM(A4:F4)
}

数式の計算結果を取得する

Workbook workbook = cell.getSheet().getWorkbook();
CreationHelper helper = workbook.getCreationHelper();
FormulaEvaluator evaluator = helper.createFormulaEvaluator();
CellValue val = evaluator.evaluate(cell);

CellValue は以下をそのままフィールドのみ持つ値

 private final int _cellType;
 private final double _numberValue;
 private final boolean _booleanValue;
 private final String _textValue;
 private final int _errorCode;

以下のように場合分けして結果を取得できる。

switch (val.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC: ...
    case Cell.CELL_TYPE_STRING:  ...
    case Cell.CELL_TYPE_BOOLEAN: ...
    case Cell.CELL_TYPE_ERROR:   ...
}

セルに数式の計算結果を反映する

Workbook workbook = cell.getSheet().getWorkbook();
CreationHelper helper = workbook.getCreationHelper();
FormulaEvaluator evaluator = helper.createFormulaEvaluator();
evaluator.evaluateInCell(cell);

セル数式の自動計算を有効にする

sheet.setForceFormulaRecalculation(true);

workbook.setForceFormulaRecalculation(true);

以下ではPOI上で数式結果を作成。

workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

データの入力規則(DataValidation)

固定リストのプルダウンを作成する

DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"10", "20"});
CellRangeAddressList regions = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = helper.createValidation(constraint, regions);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

セルの参照からプルダウンを作成する

String formula = String.format("INDIRECT(`%s!$A$1:$A$10`)".replace('`', '"'), refSheetName);
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);
CellRangeAddressList regions = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = helper.createValidation(constraint, regions);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

条件付き書式(ConditionalFormatting)

SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0");

FontFormatting fontFmt = rule1.createFontFormatting();
fontFmt.setFontStyle(true, false);
fontFmt.setFontColorIndex(IndexedColors.DARK_RED.index);

PatternFormatting patternFmt = rule1.createPatternFormatting();
patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);

ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "-10", "10");
ConditionalFormattingRule[] cfRules = { rule1, rule2 };
CellRangeAddress[] regions = { CellRangeAddress.valueOf("A3:A5") };
sheetCF.addConditionalFormatting(regions, cfRules);

名前定義(Name)

名前定義を取得する

Name name = workbook.getName("name");

int namedCellIdx = workbook.getNameIndex("name");
Name name = workbook.getNameAt(namedCellIdx);

セルが削除されても、名前定義は削除されない。存在しなくなったセルを指す名前付き範囲が含まれる可能性があるため以下でチェックする必要がある。

if (!name.isDeleted()) {
    AreaReference ref = new AreaReference(name.getRefersToFormula());
}

名前定義からセル参照を取得する

Name name = workbook.getName("name");
AreaReference areaReference = new AreaReference(name.getRefersToFormula());
CellReference firstCell = areaReference.getFirstCell();
CellReference lastCell = areaReference.getLastCell();

CellReference[] referencedCells = areaReference.getAllReferencedCells();
for (int i = 0; i < referencedCells.length; i++) {
    Sheet sheet = workbook.getSheet(crefs[i].getSheetName());
    Row row = sheet.getRow(referencedCells[i].getRow());
    Cell cell = row.getCell(referencedCells[i].getCol());
}

定義された名前が複数の領域に分割されている場合は以下を使う

AreaReference[] arefs = AreaReference.generateContiguous(name.getRefersToFormula());

名前定義を作成する

Name name = workbook.createName();
name.setNameName("name");
name.setRefersToFormula(sheet.getSheetName() + "!A1:A1");

名前定義を削除する

for (int i = workbook.getNumberOfNames() - 1; i >= 0; i--) {
    workbook.removeName(i);
}

その他

画像を追加する(PNG/JPG/DIB)

byte[] bytes = ...
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);

Drawing drawing = sheet.createDrawingPatriarch();

CreationHelper helper = workbook.getCreationHelper();
ClientAnchor anchor = helper.createClientAnchor();
anchor.setCol1(3);
anchor.setRow1(2);
Picture pict = drawing.createPicture(anchor, pictureIdx);
pict.resize();

ピボットテーブルを作成する

XSSFPivotTable pivotTable = sheet.createPivotTable(
    new AreaReference("A1:D4"), new CellReference("H5"));

pivotTable.addRowLabel(0);
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
pivotTable.addReportFilter(3);

Apache POI入門

作者:丸岡孝司
ラトルズ

Amazon

Apache POI Handbook: Java + Apache POI tutorial (English Edition)

作者:Serdiuk, Vitalii

Amazon