Java – How to read/write Excel file with Apache POI

In this tutorial, we’re gonna look at examples that read and write Excel file using Apache POI.

I. Dependency

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

II. Write Data to Excel File

– Simple POJO Customer (id, name, address, age):

package com.javasampleapproach.excelpoi;

public class Customer {
	private String id;
	private String name;
	private String address;
	private int age;

	public Customer() {
	}

	public Customer(String id, String name, String address, int age) {
		this.id = id;
		this.name = name;
		this.address = address;
		this.age = age;
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", address=" + address + ", age=" + age + "]";
	}

}

– Write to Excel file:

package com.javasampleapproach.excelpoi;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.xssf.usermodel.XSSFWorkbook;

public class WriteExcel {
	
	private static String[] COLUMNs = {"Id", "Name", "Address", "Age"};
    private static List customers = Arrays.asList(
			new Customer("1", "Jack Smith", "Massachusetts", 23),
			new Customer("2", "Adam Johnson", "New York", 27),
			new Customer("3", "Katherin Carter", "Washington DC", 26),
			new Customer("4", "Jack London", "Nevada", 33), 
			new Customer("5", "Jason Bourne", "California", 36));
    
	public static void main(String[] args) throws IOException {
		
		Workbook workbook = new XSSFWorkbook();

		CreationHelper createHelper = workbook.getCreationHelper();

		Sheet sheet = workbook.createSheet("Customers");

		Font headerFont = workbook.createFont();
		headerFont.setBold(true);
		headerFont.setColor(IndexedColors.BLUE.getIndex());

		CellStyle headerCellStyle = workbook.createCellStyle();
		headerCellStyle.setFont(headerFont);

		// Row for Header
		Row headerRow = sheet.createRow(0);

		// Header
		for (int col = 0; col < COLUMNs.length; col++) {
			Cell cell = headerRow.createCell(col);
			cell.setCellValue(COLUMNs[col]);
			cell.setCellStyle(headerCellStyle);
		}

		// CellStyle for Age
		CellStyle ageCellStyle = workbook.createCellStyle();
		ageCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));

		int rowIdx = 1;
		for (Customer customer : customers) {
			Row row = sheet.createRow(rowIdx++);

			row.createCell(0).setCellValue(customer.getId());
			row.createCell(1).setCellValue(customer.getName());
			row.createCell(2).setCellValue(customer.getAddress());

			Cell ageCell = row.createCell(3);
			ageCell.setCellValue(customer.getAge());
			ageCell.setCellStyle(ageCellStyle);
		}

		FileOutputStream fileOut = new FileOutputStream("customers.xlsx");
		workbook.write(fileOut);
		fileOut.close();
		workbook.close();
	}
}

- Check results in customers.xlsx:

III. Read Data from Excel File

package com.javasampleapproach.excelpoi;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {

	public static void main(String[] args) throws IOException {

		FileInputStream excelFile = new FileInputStream(new File("customers.xlsx"));
		Workbook workbook = new XSSFWorkbook(excelFile);

		Sheet sheet = workbook.getSheet("Customers");
		Iterator rows = sheet.iterator();

		while (rows.hasNext()) {
			Row currentRow = rows.next();
			Iterator cellsInRow = currentRow.iterator();

			while (cellsInRow.hasNext()) {

				Cell currentCell = cellsInRow.next();

				if (currentCell.getCellTypeEnum() == CellType.STRING) {
					System.out.print(currentCell.getStringCellValue() + " | ");
				} else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
					System.out.print(currentCell.getNumericCellValue() + "(numeric)");
				}
			}
			
			System.out.println();
		}
		
		workbook.close();
		excelFile.close();
	}
}

- Check Result in Console:

Id | Name | Address | Age | 
1 | Jack Smith | Massachusetts | 23.0(numeric)
2 | Adam Johnson | New York | 27.0(numeric)
3 | Katherin Carter | Washington DC | 26.0(numeric)
4 | Jack London | Nevada | 33.0(numeric)
5 | Jason Bourne | California | 36.0(numeric)