[JAVA] Excel operation using Apache POI

Introduction

I tried to output from csv to Excel using Apache POI. I also output a bar graph. It is like this

template.xlsx output.xlsx
template.gif output.gif

Since I am a beginner, I think there are many points that I cannot reach, but I would appreciate your opinions.

github https://github.com/y012/POI_Sample.git

table of contents

  1. About Apache POI
  2. Usage environment
  3. Preparation
  4. Implementation
  5. Summary

1. About Apache POI

What is Apache POI in the first place? It started from.

When I looked it up, it seems that I can operate files in Microsoft Office format. (In addition to Excel, Word, PowerPoint, etc.)

wiki https://ja.wikipedia.org/wiki/Apache_POI API Document https://poi.apache.org/apidocs/dev/overview-summary.html

It seems that it is convenient to do various things, but it seems to be deeper by that amount ... This time, as the title suggests, we will operate Excel!

2. Usage environment

3. Preparation

-Download Apache POI.

Apache POI https://poi.apache.org/index.html

Download from the link → poi-bin- ～ .zip → Download from the link that can be obtained in the red frame This time I downloaded the second from the top.

poi_download_1.png poi_download_2.png

・ Unzip, build path Right-click on the project you want to add → New → Folder Then, create a lib folder and unzip the downloaded zip to the lib folder. This time I put the build path through all the jar files in the unzipped folder!

・ Input data (.csv) This time, the input data was prepared from the site below!

What a personal information http://kazina.com/dummy/

It seems interesting how to eat curry, but I declined this time.

・ Template creation (.xlsx) Setting fonts and styles with POI from 1 is difficult ~~ troublesome ~~, so make a template first!

4. Implementation

Preparations are over and it's finally implemented. Use XSSF or HSSF when working with Excel in Apache POI. There are roughly the following differences, and this time we will use XSSF.

HSSF ～Excel2003(.xls)
XSSF Excel2007(.xlsx)～

For the time being, read the template file and output

FileInputStream fis = null;
FileOutputStream fos = null;
Workbook wb = null;

try{
	//Get template file
	fis = new FileInputStream("Template file path");
	wb = (XSSFWorkbook)WorkbookFactory.create(fis);
	//Default font settings
	wb.createFont().setFontName("Font name");

	// (1)start
	//Duplicate the sheet from the template and set the sheet name
	//The duplicate is added to the last sheet in the workbook
	Sheet sheet = wb.cloneSheet(wb.getSheetIndex("Template sheet name"));
	wb.setSheetName(wb.getSheetIndex(sheet), "Output sheet name");
	
	//Get rows, cells(Cannot be obtained on a column-by-column basis)
	//Create because the row or cell will be null in the default state
	int rowIndex = 0;
	Row row = sheet.getRow(rowIndex);
	if(row == null){
		row = sheet.createRow(rowIndex);
	}
	int columnIndex = 0;
	Cell cell = row.getCell(columnIndex);
	if(cell == null){
		cell = row.createCell(columnIndex);
	}
	
	//Set to A1
	cell.setCellValue("Hello World!!");
	
	// (1)end
	
	//Output to output file
	fos = new FileOutputStream("Output file path");
	wb.write(fos);
}catch(Exception e) {
	e.printStackTrace();
}finally {
	if(fis != null) {
		try {
			fis.close();
		}catch(IOException e) {
		}
	}
	if(fos != null) {
		try {
			fos.close();
		}catch(IOException e) {
		}
	}
	if(wb != null) {
		try {
			wb.close();
		}catch(IOException e) {
		}
	}
}

With this, you can output without changing the template file, but since the value is set for each cell, it is necessary to repeat the range of (1) many times. Therefore, I created a Manager class that summarizes move processing and input processing.

Manager class creation

ExcelManager.java


package poi.common.util;

import static poi.common.constant.ExcelConstants.*;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xddf.usermodel.chart.XDDFChart;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;

public class ExcelManager {

	private Sheet sheet = null;
	private Row row = null;
	private Cell cell = null;
	private int offset;
	private int rowIndex;
	private int columnIndex;
	private Map<Integer,CellStyle> styleMap;

	//Setting process

	/**
	 *Set the sheet and set the reference cell position to "A1"
	 * @param sheet
	 */
	public void setSheet(Sheet sheet) {
		this.sheet = sheet;
		setPosition();
		this.styleMap = new HashMap<>();
		this.offset = 1;
		this.rowIndex = 0;
		this.columnIndex = 0;
	}

	/**
	 *Copy the template print settings
	 * @param printSetup
	 */
	public void setPrintSetup(PrintSetup printSetup) {
		PrintSetup newSetup = this.sheet.getPrintSetup();

		//Number of copies
		newSetup.setCopies(printSetup.getCopies());
		//Draft mode
		//newSetup.setDraft(printSetup.getDraft());
		//Number of pages high enough to fit on the sheet
		newSetup.setFitHeight(printSetup.getFitHeight());
		//Number of pages wide enough to fit the sheet
		newSetup.setFitWidth(printSetup.getFitWidth());
		//Footer margin
		//newSetup.setFooterMargin(printSetup.getFooterMargin());
		//Header margin
		//newSetup.setHeaderMargin(printSetup.getHeaderMargin());
		//Horizontal resolution
		//newSetup.setHResolution(printSetup.getHResolution());
		//Landscape mode
		newSetup.setLandscape(printSetup.getLandscape());
		//Print order from left to right
		//newSetup.setLeftToRight(printSetup.getLeftToRight());
		//Black and white
		newSetup.setNoColor(printSetup.getNoColor());
		//direction
		newSetup.setNoOrientation(printSetup.getNoOrientation());
		//Print memo
		//newSetup.setNotes(printSetup.getNotes());
		//Start of page
		//newSetup.setPageStart(printSetup.getPageStart());
		//size of paper
		newSetup.setPaperSize(printSetup.getPaperSize());
		//scale
		newSetup.setScale(printSetup.getScale());
		//Page number used
		//newSetup.setUsePage(printSetup.getUsePage());
		//Valid settings
		//newSetup.setValidSettings(printSetup.getValidSettings());
		//Vertical resolution
		//newSetup.setVResolution(printSetup.getVResolution());

	}

	/**
	 *Get print range
	 * @return
	 */
	public String getPrintArea() {
		int firstRow = this.sheet.getFirstRowNum();
		int lastRow = this.rowIndex;
		int firstColumn = this.sheet.getRow(firstRow).getFirstCellNum();
		int lastColumn = this.sheet.getRow(lastRow).getLastCellNum()-1;
		String printArea = "$" + getColumnAlphabet(firstColumn)
							+ "$" + String.valueOf(firstRow+1)
							+ ":$" + getColumnAlphabet(lastColumn)
							+ "$" + String.valueOf(lastRow);
		return printArea;
	}

	//This is the end of the setting process

	//Move processing

	/**
	 *Set the line position to refer to
	 */
	private void setRow() {
		if((this.row = sheet.getRow(this.rowIndex)) == null) {
			this.row = sheet.createRow(this.rowIndex);
		}
	}

	/**
	 *Set the cell position to refer to
	 */
	private void setCell() {
		if((this.cell = row.getCell(this.columnIndex)) == null) {
			this.cell = row.createCell(this.columnIndex);
		}
	}

	/**
	 *Set the offset (movement amount)
	 ** The default is "1"
	 * @param offset
	 */
	public void setOffset(int offset) {
		this.offset = offset;
	}

	/**
	 *Set the row position and cell position to refer to
	 */
	private void setPosition() {
		setRow();
		setCell();
	}

	/**
	 *Set the cell position to be referenced by specifying the row and column
	 * @param rowIndex
	 *Line position(0 base)
	 * @param columnIndex
	 *Column position(0 base)
	 */
	public void setPosition(int rowIndex, int columnIndex) {
		this.rowIndex = rowIndex;
		this.columnIndex = columnIndex;
		setPosition();
	}

	/**
	 *Move the referenced cell position according to the set offset
	 *Current column position+offset
	 */
	public void nextCell() {
		moveCell(this.offset);
	}

	/**
	 *Moves the referenced cell position according to the specified offset
	 *Current column position+offset
	 * @param columnOffset
	 */
	public void moveCell(int columnOffset) {
		move(0, columnOffset);
	}

	/**
	 *Move the referenced line position according to the set offset
	 *Current line position+offset
	 ** The column position is 0 (A) column.
	 */
	public void nextRow() {
		nextRow(0);
	}

	/**
	 *Moves the referenced row position according to the set offset and moves to the specified column position.
	 *Current line position+offset
	 * @param columnIndex
	 */
	public void nextRow(int columnIndex) {
		this.columnIndex = columnIndex;
		moveRow(this.offset);
	}

	/**
	 *Moves the referenced row position according to the specified offset
	 *Current line position+offset
	 ** The row position does not change
	 * @param rowOffset
	 */
	public void moveRow(int rowOffset) {
		move(rowOffset, 0);
	}

	/**
	 *Moves the referenced row position and column position according to the specified row offset and column offset.
	 *Current line position+Row offset
	 *Current column position+Column offset
	 * @param rowOffset
	 * @param columnOffset
	 */
	public void move(int rowOffset, int columnOffset) {
		this.rowIndex += rowOffset;
		this.columnIndex += columnOffset;
		setPosition();
	}

	/**
	 *Shifts subsequent lines down one line from the current line position (insert line)
	 ** Inherit the style and height of the upper row
	 */
	public void shiftRows() {
		int lastRowNum = this.sheet.getLastRowNum();
		int lastCellNum = this.sheet.getRow(this.rowIndex-1).getLastCellNum();

		this.sheet.shiftRows(this.rowIndex, lastRowNum+1, 1);
		Row newRow = this.sheet.getRow(this.rowIndex);
		if(newRow == null) {
			newRow = this.sheet.createRow(this.rowIndex);
		}
		Row oldRow = this.sheet.getRow(this.rowIndex-1);
		for(int i = 0; i < lastCellNum-1; i++) {
			Cell newCell = newRow.createCell(i);
			Cell oldCell = oldRow.getCell(i);
			//Style settings if oldCell is not null
			//Since there is an upper limit to the cell style that can be created in wrokbook, pack it in map
			if(oldCell != null) {
				if(!styleMap.containsKey(i)) {
					CellStyle newStyle = this.sheet.getWorkbook().createCellStyle();
					newStyle.cloneStyleFrom(oldCell.getCellStyle());
					newStyle.setBorderTop(BorderStyle.DOTTED);
					styleMap.put(i, newStyle);
				}
				newCell.setCellStyle(styleMap.get(i));
			}
		}
		newRow.setHeightInPoints(oldRow.getHeightInPoints());
		setPosition();
	}

	//This is the end of the move process

	//Input processing

	/**
	 *Set a char type value in the cell and move the referenced column position according to the specified offset
	 *Set BLANK if there is no value
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(char value, int columnOffset) {
		setCellValue(String.valueOf(value), columnOffset);
	}
	/**
	 *Set a char type value in the cell and move the referenced column position according to the set offset
	 *Set BLANK if there is no value
	 * @param value
	 */
	public void setCellValue(char value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a String type value in the cell and move the referenced column position according to the specified offset
	 *Set BLANK if there is no value
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(String value, int columnOffset) {
		if(value.trim().length() == 0) {
			this.cell.setBlank();
		}else {
			this.cell.setCellValue(value);
		}
		moveCell(columnOffset);
	}

	/**
	 *Set a String type value in the cell and move the referenced column position according to the set offset
	 *Set BLANK if there is no value
	 * @param value
	 */
	public void setCellValue(String value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a value of type RichTextString to the cell and move the referenced column position according to the specified offset
	 *Set BLANK if there is no value
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(RichTextString value, int columnOffset) {
		if(value.getString().trim().length() == 0) {
			this.cell.setBlank();
		}else {
			this.cell.setCellValue(value);
		}
		moveCell(columnOffset);
	}

	/**
	 *Set a RichTextString type value in the cell and move the referenced column position according to the set offset
	 *Set BLANK if there is no value
	 * @param value
	 */
	public void setCellValue(RichTextString value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a byte type value in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(byte value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a byte type value in the cell and move the referenced column position according to the set offset.
	 * @param value
	 */
	public void setCellValue(byte value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a short type value in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(short value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a short type value in the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellValue(short value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set an int type value to the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(int value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set an int type value in the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellValue(int value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a long value in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(long value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a long type value in the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellValue(long value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a double type value in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(double value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a double type value in the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellValue(double value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a float type value in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(float value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a float type value in the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellValue(float value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a boolean value in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(boolean value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a boolean value in the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellValue(boolean value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a Calendar type value in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(Calendar value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a Calendar type value in the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellValue(Calendar value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a Date type value in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(Date value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a Date type value in the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellValue(Date value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a value of type LocalDate in the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(LocalDate value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a value of type LocalDate in the cell and move the referenced column position according to the set offset.
	 * @param value
	 */
	public void setCellValue(LocalDate value) {
		setCellValue(value, this.offset);
	}

	/**
	 *Set a value of type LocalDateTime to the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellValue(LocalDateTime value, int columnOffset) {
		this.cell.setCellValue(value);
		moveCell(columnOffset);
	}

	/**
	 *Set a value of type LocalDateTime in the cell and move the referenced column position according to the set offset.
	 * @param value
	 */
	public void setCellValue(LocalDateTime value) {
		setCellValue(value, this.offset);
	}

	/**
	 *String type calculation formula("="I don't need)To the cell and move the referenced column position according to the specified offset
	 * @param value
	 * @param columnOffset
	 */
	public void setCellFormula(String value, int columnOffset) {
		this.cell.setCellFormula(value);
		moveCell(columnOffset);
	}

	/**
	 *String type calculation formula("="I don't need)To the cell and move the referenced column position according to the set offset
	 * @param value
	 */
	public void setCellFormula(String value) {
		setCellFormula(value, this.offset);
	}

	/**
	 *Set a String type comment in the cell(Display area is fixed value)
	 ** The row position does not move
	 * @param commentStr
	 */
	public void setCellComment(String commentStr) {
		CreationHelper helper = sheet.getWorkbook().getCreationHelper();
		XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
		//Value shifted from the position of row1(Pixel unit)
		int dx1 = 0;
		//Value to shift from the position of col1(Pixel unit)
		int dy1 = 0;
		//Value to shift from the position of row2(Pixel unit)
		int dx2 = 0;
		//Value to shift from the position of col2(Pixel unit)
		int dy2 = 0;
		//Upper left column position in the comment display area(Cell unit)
		int col1 = this.columnIndex + 1;
		//Top left line position in the comment display area(Cell unit)
		int row1 = this.rowIndex;
		//Lower right column position in the comment display area(Cell unit)
		int col2 = this.columnIndex + 4;
		//Lower right line position in the comment display area(Cell unit)
		int row2 = this.rowIndex + 3;
		ClientAnchor anchor = drawing.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
		Comment comment = drawing.createCellComment(anchor);
		//comment.setAuthor("master");
		comment.setString(helper.createRichTextString(commentStr));
		this.cell.setCellComment(comment);
	}

	/**
	 *Insert bar chart from current cell position(The size is a fixed value)
	 */
	public void setBarChart() {
		XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
		int dx1 = 0;
		int dy1 = 0;
		int dx2 = 0;
		int dy2 = 0;
		int col1 = this.columnIndex;
		int row1 = this.rowIndex;
		move(16, 10);
		int col2 = this.columnIndex;
		int row2 = this.rowIndex;
		ClientAnchor anchor = drawing.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
		XDDFChart chart = (XDDFChart)drawing.createChart(anchor);

        CTChart ctChart = ((XSSFChart)chart).getCTChart();
        CTPlotArea ctPlotArea = ctChart.getPlotArea();
        CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
        CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
        ctBoolean.setVal(true);
        ctBarChart.addNewBarDir().setVal(STBarDir.COL);

        for (int r = 4; r < 8; r++) {
           CTBarSer ctBarSer = ctBarChart.addNewSer();
           CTSerTx ctSerTx = ctBarSer.addNewTx();
           CTStrRef ctStrRef = ctSerTx.addNewStrRef();
           //Usage Guide
           ctStrRef.setF("Aggregate!$B$" + r);
           ctBarSer.addNewIdx().setVal(r-4);
           CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
           ctStrRef = cttAxDataSource.addNewStrRef();
           //item
           ctStrRef.setF("Aggregate!$C$3:$I$3");
           CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
           CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
           //Data area
           ctNumRef.setF("Aggregate!$C$" + r + ":$I$" + r);

           ctBarSer.addNewSpPr().addNewLn().addNewSolidFill()
                                .addNewSrgbClr().setVal(new byte[] {0,0,0});

        }

        int catId = 100;
        int valId = 200;
        ctBarChart.addNewAxId().setVal(catId);
        ctBarChart.addNewAxId().setVal(valId);

        CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
        ctCatAx.addNewAxId().setVal(catId);
        CTScaling ctScaling = ctCatAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctCatAx.addNewDelete().setVal(false);
        ctCatAx.addNewAxPos().setVal(STAxPos.B);
        ctCatAx.addNewCrossAx().setVal(valId);
        ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        CTValAx ctValAx = ctPlotArea.addNewValAx();
        ctValAx.addNewAxId().setVal(valId);
        ctScaling = ctValAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctValAx.addNewDelete().setVal(false);
        ctValAx.addNewAxPos().setVal(STAxPos.L);
        ctValAx.addNewCrossAx().setVal(catId);
        ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        CTLegend ctLegend = ctChart.addNewLegend();
        ctLegend.addNewLegendPos().setVal(STLegendPos.B);
        ctLegend.addNewOverlay().setVal(false);
	}

	//Input processing ends here

	//Conversion process

	/**
	 *Converts the specified column number to A1 notation
	 * @param ascii
	 * @return
	 */
	public String getColumnAlphabet(int ascii) {
		String alphabet = "";
		if(ascii < 26) {
			alphabet = String.valueOf((char)(ASCII+ascii));
		}else {
			int div = ascii / 26;
			int mod = ascii % 26;

			alphabet = getColumnAlphabet(div-1) + getColumnAlphabet(mod);
		}
		return alphabet;
	}

	/**
	 *Change the current column number to A1 notation
	 * @return
	 */
	public String getColumnAlphabet() {
		return getColumnAlphabet(this.columnIndex);
	}

	//This is the end of the conversion process

}

To explain in a nutshell -NextCell () moves to the next cell, nextRow () moves to the next row, and the cell moves to column 0 (column A). -Set a value in a cell with setCellValue () and move to the next cell. -Since setBarChart () prepared a cell to put a value in the template, the range setting is set to a fixed value. I'm cramming this and that into a god class, so I thought I had to divide it, but once with this (laugh)

Completed version of Main class

PoiSample.java


package poi.main;

import static poi.common.constant.ExcelConstants.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import poi.common.dao.CSVInputDao;
import poi.common.dto.CSVDto;
import poi.common.util.ExcelManager;

public class PoiSample {


	public static void main(String[] args) {

		System.out.println("start");

		FileInputStream fis = null;
		FileOutputStream fos = null;
		Workbook wb = null;
		ExcelManager manager = new ExcelManager();
		int rowIndex = 0;
		CSVInputDao csvInDao = new CSVInputDao();
		CSVDto csvDto = new CSVDto();
		Map<Integer, List<CSVDto>> agesMap = new TreeMap<>();
		SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy/MM/dd");

		csvInDao.setFile(FILE_PATH_INPUT);


		try {

			fis = new FileInputStream(FILE_PATH_TEMPLATE);
			wb = (XSSFWorkbook)WorkbookFactory.create(fis);

			wb.createFont().setFontName(FONT);
			//Get by specifying the sheet name
			Sheet sheet = wb.cloneSheet(wb.getSheetIndex(SHEET_NAME_TEMPLATE));

			//Sheet name setting
			wb.setSheetName(wb.getSheetIndex(sheet), SHEET_NAME_ALL_DATA);

			System.out.println("createSheet:" + sheet.getSheetName());

			manager.setSheet(sheet);

			csvInDao.open();

			//All data output
			while((csvDto = csvInDao.read()) != null) {
				if(rowIndex != 0){
					if(rowIndex >= 2) {
						manager.shiftRows();
					}

					int age = Integer.parseInt(csvDto.getAge());
					String comment = "phone:" + csvDto.getTelephone() + LINE_SEPARATOR
							+ "Mobile phone:" + csvDto.getMobile() + LINE_SEPARATOR
							+ "Email:" + csvDto.getMail();
					manager.setCellComment(comment);
					manager.setCellValue(csvDto.getName());
					manager.setCellValue(csvDto.getFurigana());
					manager.setCellValue(csvDto.getSex());
					manager.setCellValue(age);
					manager.setCellValue(sdFormat.parse(csvDto.getBirthDay()));
					manager.setCellValue(csvDto.getMarriage());
					manager.setCellValue(csvDto.getBloodType());
					manager.setCellValue(csvDto.getBirthPlace());
					manager.setCellValue(csvDto.getCareer());
					//Age acquisition ex)23→20
					age = (int)(age / 10) * 10;
					if(agesMap.containsKey(age)) {
						agesMap.get(age).add(csvDto);
					}else {
						List<CSVDto> dtoList = new ArrayList<>();
						dtoList.add(csvDto);
						agesMap.put(age, dtoList);
					}

				}
				rowIndex++;
				manager.nextRow();
			}
			//Print settings, print range
			manager.setPrintSetup(wb.getSheetAt(wb.getSheetIndex(SHEET_NAME_TEMPLATE)).getPrintSetup());
			wb.setPrintArea(wb.getSheetIndex(sheet), manager.getPrintArea());


			//Output by dividing into sheets for each age
			List<String> sheetNames = new ArrayList<>();
			for(Map.Entry<Integer, List<CSVDto>> ageMap : agesMap.entrySet()) {
				String sheetName = String.valueOf(ageMap.getKey()) + "Substitute";
				if((sheet = wb.getSheet(sheetName)) == null) {
					sheet = wb.cloneSheet(wb.getSheetIndex(SHEET_NAME_TEMPLATE));
					wb.setSheetName(wb.getSheetIndex(sheet), sheetName);
				}
				sheetNames.add(sheet.getSheetName());
				System.out.println("createSheet:" + sheet.getSheetName());
				manager.setSheet(sheet);
				rowIndex = 1;
				manager.nextRow();
				for(CSVDto nextDto: ageMap.getValue()) {
					if(rowIndex >= 2) {
						manager.shiftRows();
					}
					String comment = "phone:" + nextDto.getTelephone() + LINE_SEPARATOR
							+ "Mobile phone:" + nextDto.getMobile() + LINE_SEPARATOR
							+ "Email:" + nextDto.getMail();
					manager.setCellComment(comment);
					manager.setCellValue(nextDto.getName());
					manager.setCellValue(nextDto.getFurigana());
					manager.setCellValue(nextDto.getSex());
					manager.setCellValue(Integer.parseInt(nextDto.getAge()));
					manager.setCellValue(sdFormat.parse(nextDto.getBirthDay()));
					manager.setCellValue(nextDto.getMarriage());
					manager.setCellValue(nextDto.getBloodType());
					manager.setCellValue(nextDto.getBirthPlace());
					manager.setCellValue(nextDto.getCareer());
					rowIndex++;
					manager.nextRow();
				}
				//Print settings, print range
				manager.setPrintSetup(wb.getSheetAt(wb.getSheetIndex(SHEET_NAME_TEMPLATE)).getPrintSetup());
				wb.setPrintArea(wb.getSheetIndex(sheet), manager.getPrintArea());
			}


			//Aggregate output
			sheet = wb.cloneSheet(wb.getSheetIndex(SHEET_NAME_GRAPH_TEMPLATE));
			wb.setSheetName(wb.getSheetIndex(sheet), SHEET_NAME_AGGREGATED);

			System.out.println("createSheet:" + sheet.getSheetName());
			manager.setSheet(sheet);

			String formula = "";
			for(rowIndex = 3; 7 >= rowIndex; rowIndex++) {
				manager.setPosition(rowIndex, 2);
				for(String sheetName: sheetNames) {
					if(rowIndex == 7) {
						formula = "SUM($" + manager.getColumnAlphabet() + "$4:$"
								+ manager.getColumnAlphabet() +"$7)";
					}else {
						formula = "COUNTIF(\'" + sheetName + "\'!$I:$I,$B$" + String.valueOf(rowIndex+1) + ")";
					}
					manager.setCellFormula(formula);
				}
			}
			//The formula is not calculated just by setting it, so recalculate
			wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
			rowIndex++;
			manager.setPosition(rowIndex, 1);
			//Bar chart settings
			manager.setBarChart();

			//Print settings, print range
			manager.setPrintSetup(wb.getSheetAt(wb.getSheetIndex(SHEET_NAME_GRAPH_TEMPLATE)).getPrintSetup());
			wb.setPrintArea(wb.getSheetIndex(sheet), manager.getPrintArea());

			//Delete unnecessary templates
			wb.removeSheetAt(wb.getSheetIndex(SHEET_NAME_TEMPLATE));
			wb.removeSheetAt(wb.getSheetIndex(SHEET_NAME_GRAPH_TEMPLATE));

			//File output
			fos = new FileOutputStream(FILE_PATH_OUTPUT);
			wb.write(fos);

			System.out.println("End");

		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			if(fis != null) {
				try {
					fis.close();
				}catch(IOException e) {

				}
			}
			if(fos != null) {
				try {
					fos.close();
				}catch(IOException e) {

				}
			}
			if(wb != null) {
				try {
					wb.close();
				}catch(IOException e) {

				}
			}
		}

	}
}

5. Summary

There were many things I didn't understand, so I made a trial and error. However, I managed to read the csv file, output all the data, output by age, and output the total! I personally created the Manager class, which made it easier to implement, but I think I should have used inheritance by dividing the class according to the processing content because it was packed too much. I'm not good at inheriting, so I have to study.

Thank you for watching until the end! Finally, I will publish the github link again!

github https://github.com/y012/POI_Sample.git

Recommended Posts

Excel operation using Apache POI
Excel output using Apache POI!
[Java] Creating an Excel file using Apache POI
[Apache POI] Corresponding Excel version
Manipulate Excel with Apache POI
Apache POI Excel in Kotlin
common export to excel using poi
[Java] Handle Excel files with Apache POI
[Java] Text extraction from PowerPoint (ppt) using Apache POI
Replace text in Excel file autoshapes with Apache POI
Apache POI Addictive Point List
Data processing using Apache Flink
apache POI personal notes crossfish21
Elasticsearch Operation via REST API using Apache HttpClient in Java
Sign XML using Apache Santuario
I tried using Apache Wicket
How to use Apache POI
[De-Github desktop! ] Git operation using terminal
Create an excel file with poi
[Apache POI] Judgment of unnecessary cells
Notes for reading and generating xlsx files from Java using Apache POI