Apache-POI:Writing and Reading to/from Excel file

In this article, we will explain about how to read and write excel file i.e (XLS Excel 2003 and earlier) and XLXS(2007 and later) using Apache-POI.

Apache-POI basics

  • HSSF: provides the mechanism to read or write excel sheets of 2003 and earlier.
  • XSSF: provides the mechanism to read or write excel sheets of 2007 and later.
  • Workbook: provides the mechanism to create the workbook i.e HSSFWorkbook(for 2003 and earlier) and XSSFWorkbook(2007 and later).
  • Sheet: provides the mechanism to excel work sheet(HSSFSheet and XSSFSheet).
  • Row: provides the mechanism to work with excel sheet row(HSSFRow and XSSFRow).
  • Cell: provides the mechanism to work with excel cell.

Examples

  • Write an Excel File
  • Read an Excel File

Technologies and Tools

  • poi-3.17.jar
  • JDK 1.8
  • Eclipse Oxygen
  • Maven 3.5.3

pom.xml

		  				<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
							xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
							<modelVersion>4.0.0</modelVersion>
							<groupId>ApachePOIExample</groupId>
							<artifactId>ApachePOIExample</artifactId>
							<version>0.0.1-SNAPSHOT</version>
							<packaging>war</packaging>
							<dependencies>
								<dependency>
									<groupId>org.apache.poi</groupId>
									<artifactId>poi-ooxml</artifactId>
									<version>3.17</version>
								</dependency>
							</dependencies>
							<build>
								<sourceDirectory>src</sourceDirectory>
								<plugins>
									<plugin>
										<artifactId>maven-compiler-plugin</artifactId>
										<version>3.7.0</version>
										<configuration>
											<source>1.8</source>
											<target>1.8</target>
										</configuration>
									</plugin>
									<plugin>
										<artifactId>maven-war-plugin</artifactId>
										<version>3.0.0</version>
										<configuration>
											<warSourceDirectory>WebContent</warSourceDirectory>
										</configuration>
									</plugin>
								</plugins>
							</build>
						</project>
		  			

Those who do not want to use maven, can download the following jars and do the apache-poi examples

  • poi-ooxml-3.17.jar
  • poi-3.17.jar
  • commons-codec-1.10.jar
  • commons-collections4-4.1.jar
  • poi-ooxml-schemas-3.17.jar
  • xmlbeans-2.6.0.jar
  • stax-api-1.0.1.jar
  • curvesapi-1.04.jar

Write to Excel sheet Example

Steps:

  • Create workbook using XSSFWorkbook(excel 2007 and later) or, HSSFWorkbook()
  • Create Sheet with the help of workbook.
  • Create the headerRow with the help of Sheet.
  • If you want to do some style use the CellStyle and sets the foregroundcolor, fillpattern etc.
  • Create the cell of the created Row and set the values of the header.
  • Create the rows with the help of Sheet and cell with the help of row.
  • Add the cell value in the created cells.
  • Once workbook done, create the FileOutputStream object and write the workbook in the fileOutputstream.

WriteToExcelSheetExample.java

		  				package com.javainfohub.application;

						import java.io.FileOutputStream;
						import java.io.IOException;
						import java.math.BigDecimal;
						import java.util.ArrayList;
						import java.util.List;
						
						import org.apache.poi.ss.usermodel.Cell;
						import org.apache.poi.ss.usermodel.CellStyle;
						import org.apache.poi.ss.usermodel.FillPatternType;
						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;
						
						import com.javainfohub.pojo.Employee;
						
						public class WriteToExcelSheetExample {
						
							public static void main(String[] args) throws IOException {
								List<Employee> employeeList = new ArrayList<>();
								Employee emp1 = new Employee(new BigDecimal("1"), "Nagen Kumar Sahu", new BigDecimal("40000"), "Angul");
								Employee emp2 = new Employee(new BigDecimal("2"), "Santosh Kumar Mohapatra", new BigDecimal("40000"), "Jatani");
								Employee emp3 = new Employee(new BigDecimal("3"), "Rajyalakshmi Kona", new BigDecimal("40000"), "Vizag");
								Employee emp4 = new Employee(new BigDecimal("4"), "Ajit Prasad rana", new BigDecimal("40000"), "Mayurbhanj");
								Employee emp5 = new Employee(new BigDecimal("5"), "Rekha Kanungo", new BigDecimal("40000"), "Kendrapara");
						
								employeeList.add(emp1);
								employeeList.add(emp2);
								employeeList.add(emp3);
								employeeList.add(emp4);
								employeeList.add(emp5);
								Workbook workbook = null;
								FileOutputStream fileOutput = null;
						
								try {
									workbook = new XSSFWorkbook();
									Sheet sheet = workbook.createSheet("first sheet");
						
									Row headerRow = sheet.createRow(0);
									CellStyle style = workbook.createCellStyle();
									style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
									style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
						
									Cell cell = headerRow.createCell(0);
									cell.setCellValue("EMP ID");
									cell.setCellStyle(style);
						
									cell = headerRow.createCell(1);
									cell.setCellValue("EMP NAME");
									cell.setCellStyle(style);
						
									cell = headerRow.createCell(2);
									cell.setCellValue("SALARY");
									cell.setCellStyle(style);
						
									cell = headerRow.createCell(3);
									cell.setCellValue("ADDRESS");
									cell.setCellStyle(style);
						
									for (int i = 1; i <= employeeList.size(); i++) {
										Employee emp = employeeList.get(i - 1);
						
										Row row = sheet.createRow(i);
						
										cell = row.createCell(0);
										cell.setCellValue(emp.getEmpId().toString());
						
										cell = row.createCell(1);
										cell.setCellValue(emp.getEmpName());
						
										cell = row.createCell(2);
										cell.setCellValue(emp.getSalary().toString());
						
										cell = row.createCell(3);
										cell.setCellValue(emp.getAddress());
									}
						
									fileOutput = new FileOutputStream("D://workbook.xlsx");
									workbook.write(fileOutput);
									System.out.println("Excel File write done...............");
						
								} finally {
									if (null != fileOutput) {
										fileOutput.close();
									}
									if (workbook != null) {
										workbook.close();
									}
								}
							}
						
						}
		  			

Employee.java

		  				package com.javainfohub.pojo;

						import java.math.BigDecimal;
						
						public class Employee {
							private BigDecimal empId;
							private String empName;
							private BigDecimal salary;
							private String address;
						
							public Employee() {
								super();
							}
						
							public Employee(BigDecimal empId, String empName, BigDecimal salary, String address) {
								super();
								this.empId = empId;
								this.empName = empName;
								this.salary = salary;
								this.address = address;
							}
						
							public BigDecimal getEmpId() {
								return empId;
							}
						
							public void setEmpId(BigDecimal empId) {
								this.empId = empId;
							}
						
							public String getEmpName() {
								return empName;
							}
						
							public void setEmpName(String empName) {
								this.empName = empName;
							}
						
							public BigDecimal getSalary() {
								return salary;
							}
						
							public void setSalary(BigDecimal salary) {
								this.salary = salary;
							}
						
							public String getAddress() {
								return address;
							}
						
							public void setAddress(String address) {
								this.address = address;
							}
						
							@Override
							public String toString() {
								return "Employee [empId=" + empId + ", empName=" + empName + ", salary=" + salary + ", address=" + address
										+ "]";
							}
						}
		  			

Output

Read from Excel sheet Example

Steps:

  • Read the excel file using FileInputStream.
  • Create the workbook by passing the excel file in the XSSFWorkbook constructor.
  • Get the sheet of the workbook.
  • Get the rows from the sheet with the help of iterator.
  • Then get the cell value of each row.

ReadFromExcelSheetExample.java

			  			package com.javainfohub.application;
	
						import java.io.File;
						import java.io.FileInputStream;
						import java.io.IOException;
						import java.math.BigDecimal;
						import java.util.ArrayList;
						import java.util.Iterator;
						import java.util.List;
						
						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.xssf.usermodel.XSSFWorkbook;
						
						import com.javainfohub.pojo.Employee;
						
						public class ReadFromExcelSheetExample {
						
							public static void main(String[] args) throws IOException {
								Workbook workbook = null;
								FileInputStream excelFile = null;
								try {
									excelFile = new FileInputStream(new File("D://workbook.xlsx"));
									List<Employee> employeeList = new ArrayList<>();
						
									workbook = new XSSFWorkbook(excelFile);
									Sheet datatypeSheet = workbook.getSheetAt(0);
									Iterator<Row> iterator = datatypeSheet.iterator();
									boolean excludeFirstRow = true;
									while (iterator.hasNext()) {
										Row currentRow = null;
										if (excludeFirstRow) {
											excludeFirstRow = false;
											currentRow = iterator.next();
										} else {
											currentRow = iterator.next();
											Employee emp = new Employee();
											Cell empIdCell = currentRow.getCell(0);
											Cell empNameCell = currentRow.getCell(1);
											Cell salaryCell = currentRow.getCell(2);
											Cell addressCell = currentRow.getCell(3);
						
											emp.setEmpId(new BigDecimal(empIdCell.getStringCellValue().trim()));
											emp.setEmpName(empNameCell.getStringCellValue());
											emp.setSalary(new BigDecimal(salaryCell.getStringCellValue().trim()));
											emp.setAddress(addressCell.getStringCellValue());
						
											employeeList.add(emp);
										}
									}
									employeeList.forEach(emp -> System.out.println(emp));
								} finally {
									if (null != workbook) {
										workbook.close();
									}
									if (null != excelFile) {
										excelFile.close();
									}
								}
							}
						}
		  			

Employee.java

			  			package com.javainfohub.pojo;
	
						import java.math.BigDecimal;
						
						public class Employee {
							private BigDecimal empId;
							private String empName;
							private BigDecimal salary;
							private String address;
						
							public Employee() {
								super();
							}
						
							public Employee(BigDecimal empId, String empName, BigDecimal salary, String address) {
								super();
								this.empId = empId;
								this.empName = empName;
								this.salary = salary;
								this.address = address;
							}
						
							public BigDecimal getEmpId() {
								return empId;
							}
						
							public void setEmpId(BigDecimal empId) {
								this.empId = empId;
							}
						
							public String getEmpName() {
								return empName;
							}
						
							public void setEmpName(String empName) {
								this.empName = empName;
							}
						
							public BigDecimal getSalary() {
								return salary;
							}
						
							public void setSalary(BigDecimal salary) {
								this.salary = salary;
							}
						
							public String getAddress() {
								return address;
							}
						
							public void setAddress(String address) {
								this.address = address;
							}
						
							@Override
							public String toString() {
								return "Employee [empId=" + empId + ", empName=" + empName + ", salary=" + salary + ", address=" + address
										+ "]";
							}
						}
		  			

Input(workbook.xlsx)

Output

Employee [empId=1, empName=Nagen Kumar Sahu, salary=40000, address=Angul] Employee [empId=2, empName=Santosh Kumar Mohapatra, salary=40000, address=Jatani] Employee [empId=3, empName=Rajyalakshmi Kona, salary=40000, address=Vizag] Employee [empId=4, empName=Ajit Prasad rana, salary=40000, address=Mayurbhanj] Employee [empId=5, empName=Rekha Kanungo, salary=40000, address=Kendrapara]