[Java] Handle Excel files with Apache POI

Overview

It is necessary to refer to and output (create) an Excel file from a Java program in business, and at that time, Apache POI was used to handle it, so the basic operations are summarized.

What is Apache POI?

A 100% Java library that can read and write Microsoft Office format files such as Word and Excel in one of the Apache projects. _ * The name POI is ironically called "Poor Obfuscation Implementation" because the format was intentionally and halfway obfuscated when reverse engineering the Office file format. It is derived from the acronym of Damono. _

Get Apache POI

Get the library from Apache POI official website. (The latest version as of March 2020 is 4.1.2, but this time it was the latest at the time of business support, implemented in 3.17)

Sample code

A sample that acquires a value from an existing Excel file, adds the contents, and outputs it to another Excel file.

code

ApachePoiSample.java

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ApachePoiSample {

	/**Input Excel file path*/
	public static final String IN_EXCEL_FILE_PATH = "excel/in/sample.xlsx";
	/**Output excel file path*/
	public static final String OUT_EXCEL_FILE_PATH = "excel/out/output.xlsx";

	public static void main(String[] args) {

		//----Open excel file----
		Workbook wb = null;
		try ( InputStream is = new FileInputStream(IN_EXCEL_FILE_PATH); ) {
			wb = WorkbookFactory.create(is);
		} catch (FileNotFoundException e) {
			//Occurs when the target Excel file does not exist
		    e.printStackTrace();
		} catch (IOException e) {
			//Occurs when reading the target Excel file fails
		    e.printStackTrace();
		} catch (EncryptedDocumentException e) {
			//Occurs when a password is set for the target Excel file
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			//Occurs when the target Excel file is in an invalid format
			e.printStackTrace();
		}

		//Select the target sheet
		Sheet sheet1 = wb.getSheet("Sheet1");

		//----Get the value from each cell in column A----
		System.out.println("<Input>");

		//Get a string
		Row row = sheet1.getRow(0);		//The first line
		Cell cell = row.getCell(0);		//Column A
		String a1 = cell.getStringCellValue();
		System.out.println("A1 : " + a1);

		//Get the number
		row = sheet1.getRow(1);			//2nd line
		cell = row.getCell(0);			//Column A
		double a2 = cell.getNumericCellValue();
		System.out.println("A2 : " + a2);

		//Get the formula
		row = sheet1.getRow(2);			//3rd line
		cell = row.getCell(0);			//Column A
		String a3 = cell.getCellFormula();
		System.out.println("A3 : " + a3);

		//----Add a value to column B for output (create a new cell because column B is empty)----
		System.out.println("<Output>");

		//Output a string
		row = sheet1.getRow(0);			//The first line
		cell = row.createCell(1);		//Creating column B
		cell.setCellValue(a1 + a1);
		System.out.println("B1 : " + cell.getStringCellValue());

		//Output numerical value
		row = sheet1.getRow(1);			//2nd line
		cell = row.createCell(1);		//Creating column B
		cell.setCellValue(a2 + a2);
		System.out.println("B2 : " + cell.getNumericCellValue());

		//Output formula
		row = sheet1.getRow(2);			//3rd line
		cell = row.createCell(1);		//Creating column B
		cell.setCellFormula("B2*2");
		System.out.println("A3 : " + cell.getCellFormula());

		//----Output to Excel file----
		try ( FileOutputStream os = new FileOutputStream(OUT_EXCEL_FILE_PATH); ) {
			wb.write(os);
		} catch (FileNotFoundException e) {
			//Occurs when the specified path does not exist in the output destination
			e.printStackTrace();
		} catch (IOException e) {
			//Occurs when the output of an Excel file fails
			e.printStackTrace();
		}
	}

}

Execution result

Input file (sample.xlsx)

A character string (Sample) is entered in cell A1, a numerical value (1.23) is entered in cell A2, and a formula (A2 * 2) is entered in cell A3.

Console log

Each value of column A of the input file is acquired. Also, set the values for the output file as follows.

<Input>
A1 : sample
A2 : 1.23
A3 : A2*2
<Output>
B1 : samplesample
B2 : 2.46
A3 : B2*2

Output file (output.xlsx)

Each value set in the program is output in column B.