Overview

This article introduces a very basic implementation method when using Java's ** Apache POI ** as an option when you want to perform some mechanical operation on Excel, but writing macros is a hassle. I will. In the field of system development, many design documents are often written in Excel, and I think there are situations where it is difficult to check a large number of table definitions and screen item definitions at once. In such a case, as a method other than VBA (macro), the library of "Apache POI" makes it easy to create tools with Java, which you are familiar with.

What is Apache POI?

Apache POI (Apache Poi or PIO) is a project of the Apache Software Foundation and is provided as a 100% Java library that can read and write Microsoft Office format files such as Word and Excel. https://ja.wikipedia.org/wiki/Apache_POI

Initial setting

Create a Maven project and add "** poi " and " poi-ooxml **" to the dependency of the pom.xml file. (See below) You can also use OOXML format files by adding "poi-ooxml". In other words, POI can be used to read and write 2007 format files with extensions such as "xlsx" and "docx".

`pom.xml`


<dependencies>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>[Specify version]</version>
  </dependency>
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>[Specify version]</version>
  </dependency>
</dependencies>

The latest version seems to be "** 4.0.1 **". (As of February 1, 2019) https://mvnrepository.com/artifact/org.apache.poi/poi/4.0.1 https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml/4.0.1

Basic implementation

Read Excel file

Open file

Workbook workbook = WorkbookFactory.create(new File("File Path"));

Open the sheet

//If you know the sheet name
Sheet sheet = workbook.getSheet("Sheet name");

//If you know the number of the sheet you want to get
//Sheet number is zero-based
Sheet sheet = workbook.getSheetAt(0);

//When processing all sheets repeatedly
Iterator<Sheet> sheets = workbook.sheetIterator();
while(sheets.hasNext()) {
  Sheet sheet = sheets.next();
}

//Get the sheet name
String sheetName = inputSheet.getSheetName();

Get cell value

//Get row
//Line numbers are zero-based
Row row = sheet.getRow("line number");

//Get cell
//Column numbers are zero-based
Cell cell = row.getCell("Column index");

//Get cell type
int cellType = cell.getCellType();

//Get the value with a getter according to the type
// String
cell.getStringCellValue();
// Boolean
cell.getBooleanCellValue()
// Formula
cell.getCellFormula();
// Numeric
cell.getNumericCellValue();
// etc

Excel file creation and output

Create file

//File format up to Excel 2003
Workbook outputWorkbook = new HSSFWorkbook();

//OOXML in Excel 2007(Office Open XML)Format file format
Workbook outputWorkbook = new XSSFWorkbook();

Create a sheet

Sheet outputSheet = outputWorkbook.createSheet();

Set value in cell

//Create row
//Line numbers are zero-based
Row outputRow = outputSheet.createRow("line number");

//Create cell
//Column numbers are zero-based
Cell outputCell = outputRow.createCell("Column index");

//Set value in cell
outputCell.setCellValue("Value you want to set");

Output to file

//Prepare a stream for output
FileOutputStream out = new FileOutputStream("Output file path");
//Output to file
outputWorkbook.write(out);

reference

The tools I personally made this time are as follows. It's a fairly crude implementation, but it cuts out the necessary parts from a large number of Excel files and merges them into one file. https://github.com/yhayashi30/ExcelMergeTool

Manipulate Excel with Apache POI