Apache POI 4.X チートシート

はじめに

増強版として以下にまとめ直しました。

blog1.mammb.com

Apache POI のよく使う API の簡易チートシートです。

3 系、4系 で API 使い方が変わってきています。 ここでは 4系 を対象とします。

依存

拡張子 xlsx 形式を扱う場合は poi-ooxml が必要

implementation 'org.apache.poi:poi:4.1.2'
implementation 'org.apache.poi:poi-ooxml:4.1.2'

ワークブックの新規作成

Workbook workbook = new XSSFWorkbook();
// SpreadsheetVersion.EXCEL97
// Workbook workbook = new HSSFWorkbook();

• XSSF(XML SpreadSheet Format) : Excel 2007 以降の拡張子 xlsx 形式
• HSSF(Horrible SpreadSheet Format) : 拡張子 xls 形式

HSSF を使うことはほとんど無いため XSSFWorkbook workbook = new XSSFWorkbook(); と割り切るのも良い

既存ワークブックのオープン

try {
    Path path = Paths.get("sample.xlsx");
    Workbook workbook = WorkbookFactory.create(path.toFile());
} catch (IOException e) {
    throw new RuntimeException(e);
}

ブックの保存

try (OutputStream os = new FileOutputStream("sample.xlsx")) {
    workbook.write(os);
} catch (IOException e) {
    throw new RuntimeException(e);
}

シートの作成

デフォルト名でシート作成

Sheet sheet = workbook.createSheet();

シート名を指定して作成

Sheet sheet = workbook.createSheet("sheetA");

シート名には使えない文字がある

使えない文字は createSafeSheetName で空白に変換可能

String safeSheetName = WorkbookUtil.createSafeSheetName("sheet?");
Sheet sheet = workbook.createSheet(safeSheetName);

シートの取得

シート名で取得

String sheetName = "sheetA";
Sheet sheet = workbook.getSheet(sheetName);

アクティブシートの取得

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

シート名の変更

workbook.setSheetName(workbook.getSheetIndex(sheet), "newName");

行の取得

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

または

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

行と列の移動

startRow から endRow までを n 行移動する

sheet.shiftRows(int startRow, int endRow, int n,
        boolean copyRowHeight, boolean resetOriginalRowHeight);

移動先の内容は上書きされる

列の移動

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

セルの取得

Row row = CellUtil.getRow(0, sheet);
Cell cell = CellUtil.getCell(row, 0);

セルへの値設定

cell.setCellValue("Hello");
cell.setCellValue(123);
cell.setCellValue(LocalDate.now());

セル値の文字列取得

DataFormatter formatter = new DataFormatter();
String value = formatter.formatCellValue(cell);

Formula がある場合に計算後の値を取得するには FormulaEvaluator を使う

FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
DataFormatter formatter = new DataFormatter();
String strValue = formatter.formatCellValue(cell, evaluator);

行とセルの走査

for (Row row : sheet) {
  for (Cell cell : row) {
    System.out.println(cell.getCellType());
  }
}

値が設定されている範囲の取得

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

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

罫線の設定

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

cell.setCellStyle(border);

同じスタイルの場合は同じインスタンスを使い回すこと(xlsxのスタイル上限は64000個)

セルスタイルプロパティ

cell.getCellStyle() で取得した CellStyle に対して変更を加えた場合、同じスタイルが適用された全てのセルに対して変更が反映される。

CellUtil.setCellStyleProperties() を使えば、対象のセルの一部のスタイルを変更でき、加えて変更後の同じスタイルが定義されていた場合には定義済みのスタイルを適用できる(同じスタイルが定義されていなければ新規スタイルが作成される)。

final Map<String, Object> properties = new HashMap<>();
properties.put(CellUtil.BORDER_TOP, BorderStyle.MEDIUM);
properties.put(CellUtil.BORDER_BOTTOM, BorderStyle.DOUBLE);

CellUtil.setCellStyleProperties(cell, properties);

フォントの設定

Font font = workbook.createFont();
font.setBold(true);
font.setColor(IndexedColors.BLUE.getIndex());
CellUtil.setFont(cell, font);

またはセルスタイルプロパティを使って以下のようにすることもできる

Font font = workbook.createFont();
// ...
final Map<String, Object> properties = new HashMap<>();
properties.put(CellUtil.Font, font.getIndex());

CellUtil.setCellStyleProperties(cell, properties);

背景色の設定

IndexedColorMap map = ((XSSFWorkbook) workbook).getStylesSource().getIndexedColors();
XSSFColor grey = new XSSFColor(new java.awt.Color(128, 128, 128), map);

XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
style.setFillForegroundColor(grey);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

cell.setCellStyle(style);

XSSF では無い場合は以下

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

入力規則(プルダウンリスト)の設定

DataValidationHelper helper = sheet.getDataValidationHelper();

String[] selections = {"yes", "no"};
DataValidationConstraint constraint = helper.createExplicitListConstraint(selections);

CellRangeAddressList address = new CellRangeAddressList(
  cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());

DataValidation validation = helper.createValidation(constraint, address);
sheet.addValidationData(validation);

helper.createFormulaListConstraint("Sheet1!A:A"); のようにすればシートの値から設定可能

式の設定

cell.setCellFormula("ROW()-1");
workbook.getCreationHelper().createFormulaEvaluator().evaluate(cell);

evaluateAll() で全て評価

sheet.setForceFormulaRecalculation(true); や workbook.setForceFormulaRecalculation(true); でファイルを開いたタイミングで再計算するように設定できる

カラム幅の自動調整

sheet.autoSizeColumn(columnIndex);

Apache POI入門

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