Generate Excel Files with Apache POI

Apache POI is a useful API to work with MS Excel files using Java.

It is capable of reading and writing Excel files with formatted cells.

You can extract a new Excel file by reading data from an existing Java entity to a specified location.

If you are using Apache Maven as your dependency management tool then add POI as a dependency into your project's pom.xml file.

Save your pom.xml file and then Eclipse will immediately update related Maven dependencies to your local Maven repository.

 poi
 poi
 2.5.1

After adding the above dependency in your project, under Maven dependencies you will see poi.jar file.

Also, create a Java entity class to use as a data holder which is named with Employee.java in the same package.

package org.sample.apachepoi;

public class Employee {

 private String name;
 private String surName;
 private String telNumber;
 private Long salary;
 
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public String getSurName() {
  return surName;
 }
 public void setSurName(String surName) {
  this.surName = surName;
 }
 public String getTelNumber() {
  return telNumber;
 }
 public void setTelNumber(String telNumber) {
  this.telNumber = telNumber;
 }
 public Long getSalary() {
  return salary;
 }
 public void setSalary(Long salary) {
  this.salary = salary;
 } 
}

Create a service class "ApachePoiSample.java" which contains operations to write an Excel file.

Outline of the ApachePoiSample class is represented at above. "generateExcelFiles" method is used to write Excel files to a specified location.

After adding Apache Poi Maven dependency and creating a new Java Project, file structure is displayed in Eclipse IDE as in the image at above. 

You can change the location of the dependencies that are stored on your hard drive by modifying the settings.xml file for Maven.

To change the location of local Maven repository add these lines into your settings.xml file : 

Eclipse->Preferences->Maven->Installations->User Settings

clicking the Browse button will take you to the maven settings.xml file.

add your local repository in to the settings.xml file by adding "localrepository" node which contains the new repository location such as "/home/UserName/.m2/repository ""generateExcelFiles" method is used to write new Excel files.

public void generateExcelFiles(List<Employee> employee){
  
  HSSFWorkbook wb;
  HSSFSheet sheet;
  OutputStream out;
  String fileName,fileExtension,fileLocation;

  if(employee != null && employee.size()>0){
  
   wb = new HSSFWorkbook();
   sheet = wb.createSheet("EmployeeData");
   prepareWorkBook(wb, sheet);
   fileLocation = "/home/tufangorel/";
   fileName = "Employee WorkSheet";
   fileExtension = ".csv";
   for (int j = 0; j < employee.size(); j++) {
    HSSFRow newRow = sheet.createRow((short) j + 1);
    createCell(employee,j,newRow);
   }
   try {
    out = new FileOutputStream(fileLocation+fileName+fileExtension);
    wb.write(out);
    out.close();
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
 } 

In order to create an Excel document add HSSFWorkBook and HSSFSheet objects into your method. 

HSSFRow object represents each row in the Excel file. "createCell" method takes a single employee record and copies that data into a new HSSFRow object.

private void createCell(List<Employee> list,int j, HSSFRow newRow) {
  if (list.get(j).getName() != null && !list.get(j).getName().equals("")) {
 newRow.createCell((short) 0).setCellValue(list.get(j).getName());
  }
  if (list.get(j).getSurName() != null && !list.get(j).getSurName().equals("")) {
 newRow.createCell((short) 1).setCellValue(list.get(j).getSurName());
  }
  if (list.get(j).getTelNumber() != null && !list.get(j).getTelNumber().equals("")) {
 newRow.createCell((short) 2).setCellValue(list.get(j).getTelNumber());
  }
  if (list.get(j).getSalary() != null) {
 newRow.createCell((short) 3).setCellValue(list.get(j).getSalary());
  }
}

"prepareWorkBook" method creates the first line, header line for the Excel file.

HSSFCellStyle and HSSFFont objects are required to make style modifications to Excel sheet.

private void prepareWorkBook(HSSFWorkbook wb, HSSFSheet sheet) {
  HSSFRow row = sheet.createRow((short) 0); 
  HSSFFont font = wb.createFont();    
  font.setColor(HSSFFont.BOLDWEIGHT_BOLD);
  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  HSSFCellStyle cellStyle = wb.createCellStyle(); 
  cellStyle.setFont(font);
  createHeaderCellsFormatting(row, cellStyle); 
}

"createHeaderCellsFormatting" method writes the names of columns in your sheet and set cell style.

private void createHeaderCellsFormatting(HSSFRow row,HSSFCellStyle cellStyle) {
 row.createCell((short) 0).setCellValue("Name");
 HSSFCell cell0 = row.getCell((short) 0);
 cell0.setCellStyle(cellStyle);
 row.createCell((short) 1).setCellValue("SurName");
 HSSFCell cell1 = row.getCell((short) 1);
 cell1.setCellStyle(cellStyle);
 row.createCell((short) 2).setCellValue("Tel Number");
 HSSFCell cell2 = row.getCell((short) 2);
 cell2.setCellStyle(cellStyle);
 row.createCell((short) 3).setCellValue("Salary");
 HSSFCell cell3 = row.getCell((short) 3);
 cell3.setCellStyle(cellStyle);  
}

in order to test your ApachePoiSample class add a main method which creates Employee objects and calls "generateExcelFiles" method to see the extracted Excel at the specified location.

public static void main(String[] args) {
  
 List<Employee> employee = new ArrayList<Employee>();
 Employee e1 = new Employee();
 e1.setName("John");
 e1.setSalary(2000L);
 e1.setSurName("Blade");
 e1.setTelNumber("1233873");
 employee.add(e1);
 Employee e2 = new Employee();
 e2.setName("Mary");
 e2.setSalary(3000L);
 e2.setSurName("New");
 e2.setTelNumber("343222");
 employee.add(e2);  
 ApachePoiSample apachePoiSample = new ApachePoiSample();
 apachePoiSample.generateExcelFiles(employee);
}