Manipulate Excel with Apache POI

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

Recommended Posts

Manipulate Excel with Apache POI
[Apache POI] Corresponding Excel version
Excel operation using Apache POI
Apache POI Excel in Kotlin
Excel output using Apache POI!
Replace text in Excel file autoshapes with Apache POI
Create an excel file with poi
[Java] Creating an Excel file using Apache POI
Convert large XLSX files to CSV with Apache POI
Repeated sample with Apache Freemarker
Apache POI Addictive Point List
Start Apache Solr with Embedded.
Let's operate Excel with Java! !!
CSV output with Apache Commons CSV
Access Apache Kafka with Micronaut
apache POI personal notes crossfish21
How to use Apache POI
Output Excel with formulas with XlsMapper
Circuit Breaker Pattern with Apache Camel
common export to excel using poi
Download large files with Apache JMeter
Easy Pub/Sub messaging with Apache Kafka
[Apache POI] Judgment of unnecessary cells
EXCEL file update sample with JAVA
Restart apache with docker php-apache image
FileUpload with Rest on Apache Wicket
[Linux] Start Apache container with Docker