Java

[Java x POI]Excel入出力はUtil作って超簡易化[書式コピー対応]

Javaを使ってExcelの実装を考えたとき、ほぼ一択でApache POIを使用します。

しかしこのPOIですが、個別機能でExcel入出力処理を実装していくと、ソースが非常に煩雑化します。
かなり、めんどくさいです。

そのため、POIを使う場合はいかに共通化Util化するかが非常に重要になってきます。

最近、POIを使ったExcel入出力をUtil化しました。
かなり使いやすい形で実装したので、他でも流用できるように書き連ねておきます。

モジュール

POI特有の型

実装を見る前に最低限、POIの型を頭の片隅に入れておいてください。
(見りゃわかるような型名ですが笑)

Workbook型 Excel自体を表す
Sheet型 シートを表す
Row型 行を表す
Cell型 セルを表す

 

Excel出力用Util

はい、今回の主役がいきなり登場です。

サンプルの実装を見ると使い方がピンと来るかと思います。

ExcelUtils.java
==============================
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ConditionalFormatting;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetConditionalFormatting;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelUtils {

	private ExcelUtils() {
	}

	/**
	 * Book取得
	 */
	public static Workbook workbook(String path) {
		try {
			return WorkbookFactory.create(new FileInputStream(path));
		} catch (Exception e) {
			// TODO エラー処理(nullを返してますが、各プロジェクトに適したExceptionのthrowでもOK)
			return null;
		}
	}

	/**
	 * シート取得
	 * 0番目のシートを取得
	 */
	public static Sheet sheet(Workbook workbook) {
		return workbook.getSheetAt(0);
	}

	/**
	 * シート取得
	 * n番目のシートを取得
	 */
	public static Sheet sheet(Workbook workbook, int n) {
		return workbook.getSheetAt(n);
	}

	/**
	 * シート取得
	 * シート名から取得
	 */
	public static Sheet sheet(Workbook workbook, String name) {
		return workbook.getSheet(name);
	}

	/**
	 * シート配列取得
	 * 指定したテンプレート名のシートをコピーして、指定した名称分のシートを生成
	 * テンプレートシートは削除
	 */
	public static Sheet[] sheets(Workbook workbook, String templateSheetName, String... sheetNames) {
		Sheet[] sheets = new Sheet[sheetNames.length];
		final PrintSetup templatePs = workbook.getSheet(templateSheetName).getPrintSetup();
		for (int i = 0; i < sheetNames.length; i++) {
			sheets[i] = workbook.cloneSheet(workbook.getSheetIndex(templateSheetName));
			workbook.setSheetName(workbook.getSheetIndex(sheets[i]), sheetNames[i]);

			// 印刷設定
			final PrintSetup ps = sheets[i].getPrintSetup();
			ps.setCopies(templatePs.getCopies());
			ps.setDraft(templatePs.getDraft());
			ps.setFitHeight(templatePs.getFitHeight());
			ps.setFitWidth(templatePs.getFitWidth());
			ps.setFooterMargin(templatePs.getFooterMargin());
			ps.setHeaderMargin(templatePs.getHeaderMargin());
			ps.setHResolution(templatePs.getHResolution());
			ps.setLandscape(templatePs.getLandscape());
			ps.setLeftToRight(templatePs.getLeftToRight());
			ps.setNoColor(templatePs.getNoColor());
			ps.setNoOrientation(templatePs.getNoOrientation());
			ps.setNotes(templatePs.getNotes());
			ps.setPaperSize(templatePs.getPaperSize());
			ps.setScale(templatePs.getScale());
			ps.setUsePage(templatePs.getUsePage());
			ps.setValidSettings(templatePs.getValidSettings());
			ps.setVResolution(templatePs.getVResolution());
		}
		return sheets;
	}

	/**
	 * シート取得
	 * 指定したテンプレート名のシートをコピーして、指定した名称のシートを生成
	 * テンプレートシートは削除
	 */
	public static Sheet sheet(Workbook workbook, String templateName, String sheetName) {
		return sheets(workbook, templateName, sheetName)[0];
	}

	/**
	 * シート削除
	 * 指定したシート名のシートを削除
	 */
	public static void removeSheet(Workbook workbook, String sheetName) {
		workbook.removeSheetAt(workbook.getSheetIndex(sheetName));
	}

	/**
	 * 行取得
	 * 行が存在しない場合は、前の行を指定列まで書式をコピーして生成
	 */
	public static Row row(Sheet sheet, int rowIndex, int firstCol, int lastCol) {
		Row row = sheet.getRow(rowIndex);
		if (row == null) {
			final Row srcRow = sheet.getRow(rowIndex - 1);
			row = sheet.createRow(rowIndex);

			row.setHeight(srcRow.getHeight());

			for (int i = firstCol; i <= lastCol; i++) {
				final Cell srcCell = srcRow.getCell(i);
				final Cell cell = row.createCell(i);

				cell.setCellStyle(srcCell.getCellStyle());
				cell.setCellType(srcCell.getCellTypeEnum());
			}
		}
		return row;
	}

	/**
	 * ワークブックからbyte配列取得
	 */
	public static byte[] byteArray(Workbook workbook) {
		try (final ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
			workbook.write(baos);
			return baos.toByteArray();
		} catch (Exception e) {
			// TODO エラー処理(nullを返してますが、各プロジェクトに適したExceptionのthrowでもOK)
			return null;
		}
	}

	/**
	 * シートの条件付き書式
	 */
	public static void conditionalFormat(Sheet sheet) {
		conditionalFormat(sheet, 0);
	}

	/**
	 * シートの条件付き書式
	 */
	public static void conditionalFormat(Sheet sheet, int rowIndexFrom) {
		final SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
		for (int i = 0; i < formatting.getNumConditionalFormattings(); i++) {
			final ConditionalFormatting format = formatting.getConditionalFormattingAt(i);
			final CellRangeAddress[] ranges = format.getFormattingRanges();
			if (rowIndexFrom <= ranges[0].getFirstRow()) {
				for (int j = 0; j < ranges.length; j++) {
					ranges[j].setLastRow(sheet.getLastRowNum());
				}
				format.setFormattingRanges(ranges);
			}
		}
	}

	/**
	 * Excelファイルを指定パスに出力
	 */
	public static void output(Workbook workbook, String path) {
		try (BufferedInputStream bis = new BufferedInputStream(new ByteArrayInputStream(byteArray(workbook))); BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(path))) {
			byte[] buff = new byte[4096];
			int len = 0;
			while ((len = bis.read(buff)) != -1) {
				bos.write(buff, 0, len);
			}
			bos.flush();
		} catch (Exception e) {
			// TODO 各プロジェクトに適したExceptionのthrow
		}
	}

}

出力サンプル実装

テンプレートのExcelファイル

今回のExcel出力サンプルに使用するテンプレートファイルです。
試す場合は「C:\\tmp\\template.xlsx」となるように配置してください。
template.xlsxをダウンロード

 

こんな感じになってます。
template.xlsxの画像

 
入力想定のセルと設定予定値の情報は以下の通りです。

入力想定セル Javaでセットする型 書式
C2 String 標準
A4~のA列 Integer 数値
B4~のB列 Date 日付
C4~のC列 String 条件付き書式 =ISBLANK(C4)で背景色赤

出力モジュールの実装

Excelの行を構成するBeanです。定義が面倒なので、コンストラクタを切ってます。

Bean.java


==============================
import java.util.Date;

public class Bean {

	public Bean(Integer test1, Date test2, String test3) {
		this.test1 = test1;
		this.test2 = test2;
		this.test3 = test3;
	}

	private Integer test1;
	private Date test2;
	private String test3;

	/** 以下各項目のsetter/getter */

	public Integer getTest1() {
		return test1;
	}

	public void setTest1(Integer test1) {
		this.test1 = test1;
	}

	public Date getTest2() {
		return test2;
	}

	public void setTest2(Date test2) {
		this.test2 = test2;
	}

	public String getTest3() {
		return test3;
	}

	public void setTest3(String test3) {
		this.test3 = test3;
	}
}

 
そして以下が、Excelを出力するモジュールです。
テンプレートファイル「C:\\tmp\\template.xlsx」を読み込んで
結果を「C:\\tmp\\output.xlsx」に出力します。

Output.java
==============================
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class Output {

	/** セル制御 */
	private static class Cell {
		/** 行 */
		private static class Row {
			private static final int HEADER = 1;
			private static final int LIST_INIT = 3;
		}

		/** 列 */
		private static class Col {
			/** ヘッダ */
			private static class Header {
				private static final int HEAD = 2;
			}

			/** 表 */
			private static class List {
				private static final int TEST1 = 0;
				private static final int TEST2 = 1;
				private static final int TEST3 = 2;
				private static final int LIST_MIN = TEST1;
				private static final int LIST_MAX = TEST3;
			}
		}
	}

	/** テンプレートパス */
	private static final String BOOK_TEMPLATE_PATH = "C:\\tmp\\template.xlsx";

	/** 出力パス */
	private static final String BOOK_OUTPUT_PATH = "C:\\tmp\\output.xlsx";

	/** テンプレートシート名 */
	private static final String SHEET_TEMPLATE = "テンプレートシート";

	public static void main(String[] args) {

		// -------------------
		// パラメータ用意用(この辺はどうでもよいです。)
		// -------------------
		List<Bean> list = new ArrayList<Bean>();
		try {
			list.add(new Bean(100, new SimpleDateFormat("yyyy/MM/dd").parse("2018/12/01"), "aaa"));
			list.add(new Bean(40, new SimpleDateFormat("yyyy/MM/dd").parse("2017/01/01"), "bbb"));
			list.add(new Bean(4000, new SimpleDateFormat("yyyy/MM/dd").parse("2017/01/01"), ""));
		} catch (Exception e) {
			// 日付のフォーマットに失敗でエラーなる可能性があるが、今回は説明のためにベタで正しい日付を入れているため握りつぶす
		}

		// -------------------
		// ★★★以下が重要★★★
		// -------------------
		// AutoCloseableは必ず使用
		try (Workbook workbook = ExcelUtils.workbook(BOOK_TEMPLATE_PATH)) {

			// シート生成
			Sheet sheet = ExcelUtils.sheet(workbook, SHEET_TEMPLATE, "JAVA入力シート名");

			// テンプレート削除
			ExcelUtils.removeSheet(workbook, SHEET_TEMPLATE);

			// タイトル行の開始終了位置設定
			sheet.setRepeatingRows(new CellRangeAddress(0, Cell.Row.LIST_INIT - 1, -1, -1));

			// ヘッダー値設定
			Row headerInfoRow = sheet.getRow(Cell.Row.HEADER);
			headerInfoRow.getCell(Cell.Col.Header.HEAD).setCellValue("JAVA入力ヘッダー値");

			// リスト値設定
			for (int i = 0; i < list.size(); i++) {
				Bean info = list.get(i);
				final Row row = ExcelUtils.row(sheet, Cell.Row.LIST_INIT + i, Cell.Col.List.LIST_MIN, Cell.Col.List.LIST_MAX);

				row.getCell(Cell.Col.List.TEST1).setCellValue(info.getTest1());
				row.getCell(Cell.Col.List.TEST2).setCellValue(info.getTest2());
				row.getCell(Cell.Col.List.TEST3).setCellValue(info.getTest3());
			}

			// 条件付き書式の設定
			ExcelUtils.conditionalFormat(sheet, Cell.Row.LIST_INIT);

			// ファイル出力
			ExcelUtils.output(workbook, BOOK_OUTPUT_PATH);
		} catch (Exception e) {
			// TODO 各プロジェクトに適したExceptionのthrow
		}
	}
}

実行結果

正しく値が入りました。
書式もリスト上でコピーされているのがわかりますね。
output.xlsxの画像

 

補足説明

書式の実装について

書式は一度値すべての行に適用した後に以下で、コピーしていってます。
(値と一緒に設定しようとして大ハマりした記憶があります。)
 
第二引数はリストの開始位置です。

Output.java
=====================
// 条件付き書式の設定
ExcelUtils.conditionalFormat(sheet, Cell.Row.LIST_INIT);

 
最初のfor文でシート内の書式をすべて拾って回してます。
次のfor文最終行を割り出して、最後に書式設定をしています。

ExcelUtils.java
======================
/**
 * シートの条件付き書式
 */
public static void conditionalFormat(Sheet sheet, int rowIndexFrom) {
	final SheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
	for (int i = 0; i < formatting.getNumConditionalFormattings(); i++) {
		final ConditionalFormatting format = formatting.getConditionalFormattingAt(i);
		final CellRangeAddress[] ranges = format.getFormattingRanges();
		if (rowIndexFrom <= ranges[0].getFirstRow()) {
			for (int j = 0; j < ranges.length; j++) {
				ranges[j].setLastRow(sheet.getLastRowNum());
			}
			format.setFormattingRanges(ranges);
		}
	}
}

 

注意事項

テンプレートのExcelで使わない行は一度削除する

何度かハマりましたが、POIはセルが変更されているか判定しているようです。

リスト処理では、行の終端をセルの修正があるかで判定する箇所があるので、

意図していないのに、高さが変わってたりすると変な動きをする場合があります。

テンプレート作成後は、一度不要行を行削除、不要列を列削除したほうが良いです。
 

null値に注意

Stringとかは大丈夫ですが、nullを許容しない型があります。(Integerなど)

出力中に、ファイルを開いてると落ちます

当たり前ですが笑
テンプレートファイルは開いたままでも動作します。
 

入力サンプル実装

取り込むExcelファイル

先ほど出力したExcelを取り込みます。
ここから始める方は以下よりダウンロードして、「C:\tmp\output.xlsx」となるように配置してください。
output.xlsxをダウンロード

入力モジュールの実装

Input.java
=========================
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Input {

	/** セル制御 */
	private static class Cell {
		/** 行 */
		private static class Row {
			private static final int HEADER = 1;
			private static final int LIST_INIT = 3;
		}

		/** 列 */
		private static class Col {
			/** ヘッダ */
			private static class Header {
				private static final int HEAD = 2;
			}

			/** 表 */
			private static class List {
				private static final int TEST1 = 0;
				private static final int TEST2 = 1;
				private static final int TEST3 = 2;
				private static final int LIST_MIN = TEST1;
				private static final int LIST_MAX = TEST3;
			}
		}
	}

	/** 出力パス */
	private static final String BOOK_OUTPUT_PATH = "C:\\tmp\\output.xlsx";

	public static void main(String[] args) {

		try (final Workbook workbook = ExcelUtils.workbook(BOOK_OUTPUT_PATH)) {
			final Sheet sheet = ExcelUtils.sheet(workbook);

			final Iterator<Row> rows = sheet.rowIterator();
			while (rows.hasNext()) {
				Row row = rows.next();
				// 表の開始位置まで処理をスキップ
				if (row.getRowNum() < Cell.Row.LIST_INIT) {
					continue;
				}

				System.out.println(row.getCell(Cell.Col.List.TEST1).getNumericCellValue());
				System.out.println(row.getCell(Cell.Col.List.TEST2).getNumericCellValue());
				System.out.println(row.getCell(Cell.Col.List.TEST3).getStringCellValue());
				System.out.println("--------------------");

			}

		} catch (Exception e) {
			// TODO 各プロジェクトに適したExceptionのthrow
		}
	}
}

【実行結果】
100.0
43435.0
aaa
--------------------
40.0
42736.0
bbb
--------------------
4000.0
42736.0

--------------------

期待通りになりました。

最後に

ExcelUtilsの作成と、
Excel出力とExcel入力のサンプル実装をおこないました。

これで簡単にExcel出力系の業務に対応できるかと思われます。

ご意見、ご感想等ございましたら、↓よりコメントお願いします。励みになります。

以上です。今後ともよろしくお願いします。

-Java

Copyright© 婿入りエンジニア、ブログ書く , 2019 All Rights Reserved.