LoginSignup

This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

@changkejun(kejun chang)

Let's Graduate from POI

Posted at

Background

Have you ever used POI? If you've ever felt like a "Poor Obfuscation Implementation", then you've captured the essence ^_^.
https://ja.wikipedia.org/wiki/Apache_POI

If you think about the actions you take on Excel files, they can be divided into three types.
・Creating data tables
・Clean up the data table
・Things like adding a speech bubble or surrounding it with a frame

That is, "data work," "format work," and "graphic work." "Data work" is relatively easy, get or set. "Format work" and "shape work" are troublesome. There are many places where you can set the line color, type, shadow, etc., and when implementing it with POI, the program often becomes extremely long.

However, when we operate Excel manually, I don't think anyone feels that troublesome. This is because if you copy and paste, everything will be arranged neatly. It's even more convenient if you use a brush.
Wouldn't it be easier to use if we incorporated this method into the API?

Introduction to efw's Excel API

Overall Image

Type API
File Operations new save close getSheetNames
Sheet Operations createSheet removeSheet setSheetOrder setActiveSheet
getMaxRow setPrintArea showSheet hideSheet
Matrix Operations addRow delRow showRow hideRow
showCol hideCol
getArray getSingle getValue setCell setLink
Shape Operations isEncircled encircle addShape addShapeInRange replacePicture

Samples

We provide the following three types of samples.

EFW js event sample

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();
}

jSample of using efw's Excel API with java

<%@ 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());
%>

Original POI sample

Do you feel like it's a "poor and difficult to read implementation"?

<%@ 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;
	}
%>

Sheet copy maintains row height. The background and text style will be inherited by copying the cell format. By copying a shape, you can reproduce the same type, color, size, relative position within the cell, etc.
image.png

Get Jar

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

For jdk15 or higher, related jars are required.

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

This is the last important 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>

This sample can be downloaded from the link below. It also includes all tests for efw's Excel operation API. Please refer if you are interested.

The image below is a business flow diagram that our company created using this API.
image.png

0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin