【make it easy】POIから卒業しましょう

背景

POIを使ったことありますか?
もし「質の悪い難読な実装（Poor Obfuscation Implementation）」と感じたのであれば本質を捉えたといえますね^_^。
https://ja.wikipedia.org/wiki/Apache_POI

Excelファイルに対するアクションは3種類に分けられます。
・データ表の作成
・データ表の整理
・吹き出しを付ける、枠で囲むなど

つまり、「データ作業」、「書式作業」、「図形作業」に該当します。「データ作業」は比較的に簡単で、get or setで完結しますが、「書式作業」と「図形作業」は面倒です。線の色・型・影などいろいろ設定できる箇所があり、このようなことが原因でPOIで実装する場合、プログラムがとても長くなることもあります。

しかし、我々が手動でExcelを操作するとき、だれもその面倒臭さは感じていないはずです。なぜならばコピーや貼り付けをやれば何でも綺麗に揃えることができ、ブラシを使えばさらに良くすることができる為です。

ではこのやり方をAPIに取り込めば、使いやすくなるのではないでしょうか？

efwのExcel API の紹介

全体イメージ

種類	API
ファイル操作	new	save	close	getSheetNames
シート操作	createSheet	removeSheet	setSheetOrder	setActiveSheet
	getMaxRow	setPrintArea	showSheet	hideSheet
行列操作	addRow	delRow	showRow	hideRow
			showCol	hideCol
	getArray	getSingle	getValue	setCell	setLink
図形操作	isEncircled	encircle	addShape	addShapeInRange	replacePicture

サンプル

以下３種類のサンプルを紹介します。

efwのjsイベントのサンプル

var helloExcel_submit={};
helloExcel_submit.paramsFormat={};
helloExcel_submit.fire=function(params){
	//ファイル作成時、テンプレートをコピー作成
	new Excel("excel/IamExcelTemplate.xlsx")
	//シート作成時、テンプレートをコピー作成
	.createSheet("newSheet","templateSheet")
	//セルに値を代入とともに、書式のコピー元を指定
	.setCell("newSheet","B2","helloworld","templateSheet","A1")
	//テンプレートの図形をコピーして、新しい場所に貼り付ける
	.addShape("newSheet","C2","templateSheet","myCircle")
	//保存する
	.save("myExcel.xlsx")
	//閉じる
	.close();
	return new Result().attach("myExcel.xlsx").deleteAfterDownload();
}

javaでefwのExcel APIを利用するサンプル

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="efw.excel.ExcelManager"%>
<%@ page import="efw.excel.Excel"%>
<%
//ファイル作成時、テンプレートをコピー作成
Excel excel=ExcelManager.open("excel/IamExcelTemplate.xlsx", false);
//シート作成時、テンプレートをコピー作成
excel.createSheet("newSheet","templateSheet");
//セルに値を代入とともに、書式のコピー元を指定
excel.setCellStringValue("newSheet","B2","helloworld");
excel.setCellStyle("newSheet","B2","templateSheet","A1");
//テンプレートの図形をコピーして、新しい場所に貼り付ける
excel.addShapeInCell("newSheet","C2", "templateSheet","myCircle", "", 0, 0, 0, 0);
//保存する
excel.save("myExcel.xlsx", null);
//閉じる
ExcelManager.close(excel.getKey());
%>

オリジナルのPOIのサンプル

「質の悪い難読な実装」と感じていますか。

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="efw.file.FileManager"%>
<%@ page import="java.io.File"%>
<%@ page import="java.io.FileOutputStream"%>
<%@ page import="java.io.IOException"%>
<%@ page import="org.apache.poi.EncryptedDocumentException"%>
<%@ page import="org.apache.poi.ss.usermodel.Cell"%>
<%@ page import="org.apache.poi.ss.usermodel.PrintSetup"%>
<%@ page import="org.apache.poi.ss.usermodel.Row"%>
<%@ page import="org.apache.poi.ss.usermodel.Sheet"%>
<%@ page import="org.apache.poi.ss.usermodel.Workbook"%>
<%@ page import="org.apache.poi.ss.usermodel.WorkbookFactory"%>
<%@ page import="org.apache.poi.ss.util.CellReference"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFDrawing"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFShape"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFSheet"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFClientAnchor"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFSimpleShape"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFTextParagraph"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFTextRun"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFPicture"%>
<%@ page import="org.apache.poi.xssf.usermodel.XSSFPictureData"%>
<%@ page import="org.apache.poi.openxml4j.opc.PackagePart"%>
<%@ page import="org.apache.poi.ss.usermodel.Font"%>
<%@ page import="java.util.List"%>



<%
//テンプレートを一時ファイルにコピーする
File file=FileManager.get("excel/IamExcelTemplate.xlsx");
File tempFile=File.createTempFile("efw", "");
FileManager.duplicate(file, tempFile);
Workbook workbook=WorkbookFactory.create(tempFile);
//新しいシートを作成する
Sheet sheet = workbook.cloneSheet(workbook.getSheetIndex("templateSheet"));
workbook.setSheetName(workbook.getSheetIndex(sheet.getSheetName()), "newSheet");
Sheet tempSheet = workbook.getSheet("templateSheet");
//B2セルに値を設定する
CellReference reference = new CellReference("B2");
Row row = sheet.getRow(reference.getRow());
Cell cell = null;
if (row == null) {
	row = sheet.createRow(reference.getRow());
	row.setHeightInPoints(sheet.getDefaultRowHeightInPoints());//初期高さを設定
}
cell = row.getCell(reference.getCol());
if (cell==null){
	cell=row.createCell(reference.getCol());
}
cell.setCellValue("helloworld");
//B2セルにA1の書式を利用する
CellReference templateReference = new CellReference("A1");
Row templateRow = tempSheet.getRow(templateReference.getRow());
if (templateRow != null) {
	Cell templateCell=templateRow.getCell(templateReference.getCol());
	cell.setCellStyle(templateCell.getCellStyle());
}
//図形を作成する
CellReference shapeReference = new CellReference("C2");
int cellrow=shapeReference.getRow();
int cellcol=shapeReference.getCol();

XSSFSheet xsheet = (XSSFSheet) workbook.getSheet("newSheet");
XSSFSheet xtemplateSheet=(XSSFSheet) workbook.getSheet("templateSheet");

List<XSSFShape> templateShapes=((XSSFDrawing) xtemplateSheet.getDrawingPatriarch()).getShapes();
for (XSSFShape templateShape : templateShapes) {
	if ("myCircle".equals(templateShape.getShapeName())) {
		XSSFDrawing patriarch=xsheet.getDrawingPatriarch();
		if(patriarch==null) patriarch = xsheet.createDrawingPatriarch();
		XSSFShape shape=cloneShape(workbook,patriarch,templateShape,"");
		XSSFClientAnchor anchor=(XSSFClientAnchor)(shape.getAnchor());
		int x=0;
		int y=0;
		int dx1=0;
		int dy1=0;
		int dx2=0;
		int dy2=0;
		int width=0;
		int height=0;
		if(x==0){
			dx1=anchor.getDx1();
		}
		if(y==0){
			dy1=anchor.getDy1();
		}
		if(width==0){
			width=anchor.getDx2()-anchor.getDx1();
		}
		if(height==0){
			height=anchor.getDy2()-anchor.getDy1();
		}
		dx2=width+dx1;
		dy2=height+dy1;
		anchor.setRow1(cellrow);
		anchor.setRow2(cellrow);
		anchor.setCol1(cellcol);
		anchor.setCol2(cellcol);
		anchor.setDx1(dx1);
		anchor.setDy1(dy1);
		anchor.setDx2(dx2);
		anchor.setDy2(dy2);
		break;
	}
}
//目的ファイルとして保存する
FileOutputStream fileOutputStream = new FileOutputStream(FileManager.get("myExcel.xlsx"));
workbook.setForceFormulaRecalculation(true);
workbook.write(fileOutputStream);
fileOutputStream.close();
workbook.close();
//一時ファイルを削除する
tempFile.delete();
%>
<!-- 以下はclone関数 -->
<%!
	/**
	 * XSSFのShapeをコピーする
	 * @param patriarch
	 * @param templateShape
	 * @param value
	 * @return　作成されたshape
	 */
	XSSFShape cloneShape(Workbook workbook,XSSFDrawing patriarch,XSSFShape templateShape,String value){
		String clsNm=templateShape.getClass().getSimpleName();
		if ("XSSFSimpleShape".equals(clsNm)) {
			XSSFSimpleShape orgSimpleShape=(XSSFSimpleShape)templateShape;
			XSSFSimpleShape simpleShape = patriarch.createSimpleShape((XSSFClientAnchor)orgSimpleShape.getAnchor());
			simpleShape.getCTShape().set(orgSimpleShape.getCTShape().copy());
			if(orgSimpleShape.getTextParagraphs().size()>0){
				XSSFTextParagraph tempParagraph=orgSimpleShape.getTextParagraphs().get(0);
				if(tempParagraph.getTextRuns().size()>0){
					XSSFTextRun tempRun=tempParagraph.getTextRuns().get(0);
					simpleShape.setText(tempRun.getText());
					if(simpleShape.getTextParagraphs().size()>0){
						XSSFTextParagraph paragraph=simpleShape.getTextParagraphs().get(0);
						if(paragraph.getTextRuns().size()>0){
							XSSFTextRun textRun= paragraph.getTextRuns().get(0);
							if (value!=null) {
								textRun.setText(value);
							}else {
								textRun.setText(tempRun.getText());
							}
							textRun.setFontSize(tempRun.getFontSize());
							textRun.setCharacterSpacing(tempRun.getCharacterSpacing());
							textRun.setFontColor(tempRun.getFontColor());
							textRun.setFontFamily(tempRun.getFontFamily(), Font.DEFAULT_CHARSET, tempRun.getPitchAndFamily(), false);
							paragraph.setTextAlign(tempParagraph.getTextAlign());
							paragraph.setTextFontAlign(tempParagraph.getTextFontAlign());
						}
					}
				}
			}
			return (XSSFShape)simpleShape;
		}else if ("XSSFPicture".equals(clsNm)) {
			XSSFPicture orgPicture=(XSSFPicture)templateShape;
			PackagePart orgPackagePart= orgPicture.getPictureData().getPackagePart();
			@SuppressWarnings("unchecked")
			List<XSSFPictureData> allpictures=(List<XSSFPictureData>) workbook.getAllPictures();
			int pictureIndex=-1;
			for(int index=0;index<allpictures.size();index++) {
				PackagePart packagePart=allpictures.get(index).getPackagePart();
				if (packagePart.equals(orgPackagePart)) {
					pictureIndex=index;
					break;
				}
			}
			XSSFPicture picture = patriarch.createPicture((XSSFClientAnchor)orgPicture.getAnchor(),pictureIndex);
			return (XSSFShape)picture;
		}
		return null;
	}
%>

シートのコピーにより、行の高さを設定します。
セルの書式コピーにより背景と文字スタイルは継承します。図形のコピーにより種類・色・サイズ・セル内の相対位置などを同じく再現することができます。

jarの入手

<dependency>
    <groupId>io.github.efwgrp</groupId>
    <artifactId>efw</artifactId>
    <version>4.07.000</version>
</dependency>

jdk15以上の場合、関連jarが必要です。

<dependency>
    <groupId>org.openjdk.nashorn</groupId>
    <artifactId>nashorn-core</artifactId>
    <version>15.4</version>
</dependency>

最後は肝心なPOIですね。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

今回のサンプルは以下のリンクからダウンロードできます。efwのExcel操作APIの全部のテストを含んでいますので、興味があればご参考にしてください。

以下の画像はうちの会社はこのAPI利用して作った業務フロー図です。