Thursday, October 4, 2018

How to Write Excel File in Java Using Apache POI

In this post we’ll see how you can Write Excel sheet in Java using Apache POI library.

Apache POI is an open source library using which you can read and write Excel files from your Java program.


Maven Dependencies

You will need to include following maven dependencies for Apache POI in your pom.xml file.

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>4.0.0</version>
</dependency>  

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

Here the first dependency is required for working with older excel format having .xls extension. Second dependency is required for working with the OOXML based file format having .xlsx extension.

These dependencies add the following jars-

poi-4.0.0.jar
commons-codec-1.10.jar
commons-collections4-4.2.jar

poi-ooxml-4.0.0.jar
poi-ooxml-schemas-4.0.0.jar
xmlbeans-3.0.1.jar
commons-compress-1.18.jar
curvesapi-1.04.jar

Apache POI classes for working with Excel spreadsheets

Before getting into example for writing to excel in Java using Apache POI first let’s get some idea about the classes that are used in the code.

With in Apache POI there are two implementations for two types of spread sheets-

  • HSSF- It is the POI Project's pure Java implementation of the Excel '97(-2007) file format (.xls).
  • XSSF- It is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

Note that there is a component module that attempts to provide a common high level Java API to both OLE2 and OOXML document formats which is SS for Excel workbooks. So it's better to use SS package as much as possible so that one implementation can be replaced by another seamlessly.

The following interfaces from the SS model will be used in the example to write excel file in Java using Apache POI-

  • org.apache.poi.ss.usermodel.Workbook- High level representation of a Excel workbook. This is the first object most users will construct whether they are reading or writing a workbook. Implementing classes for the HSSF and XSSF respectively are HSSFWorkbook and XSSFWorkbook.
  • org.apache.poi.ss.usermodel.Sheet- High level representation of a Excel worksheet. Implementing classes for the HSSF and XSSF respectively are HSSFSheet and XSSFSheet.
  • org.apache.poi.ss.usermodel.Row- High level representation of a row of a spreadsheet. Implementing classes for the HSSF and XSSF respectively are HSSFRow and XSSFRow.
  • org.apache.poi.ss.usermodel.Cell- High level representation of a cell in a row of a spreadsheet. Cells can be numeric, formula-based or string-based (text). Implementing classes for the HSSF and XSSF respectively are HSSFCell and XSSFCell.

Writing excel file in Java using Apache POI example

The example shown here writes a List of object of type User to an Excel sheet using a Java program.

Steps you need to follow are as follows-

  1. First thing is to create a workbook instance of type XSSFWorkbook or HSSFWorkbook based on whether you want Excel with .xslx or .xls extension.
  2. Then create a sheet with in a workbook.
  3. Create row in a sheet and then cells with in a row to write data.
  4. You can also style cells by providing font, color, alignment, border etc. values.

Model bean (User.java)

public class User {

  private String firstName;
  private String lastName;
  private String email;
  private Date dob;

  public User() {
   
  }
  public User(String firstName, String lastName, String email, Date dob) {
   this.firstName = firstName;
   this.lastName = lastName;
   this.email = email;
   this.dob = dob;
  }
  
  public String getFirstName() {
   return firstName;
  }
  public void setFirstName(String firstName) {
   this.firstName = firstName;
  }
  public String getLastName() {
   return lastName;
  }
  public void setLastName(String lastName) {
   this.lastName = lastName;
  }
  public String getEmail() {
   return email;
  }
  public void setEmail(String email) {
   this.email = email;
  }
  public Date getDob() {
  return dob;
  }
  public void setDob(Date dob) {
  this.dob = dob;
  }
}
Class used for writing to excel sheet.
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.time.LocalDate;
import java.time.Month;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.BorderStyle;
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.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.netjs.Model.User;
public class WriteExcel {
  private static final String PATH_TO_EXCEL="G:\\Test\\user.xlsx";
  public static void main(String[] args) throws ParseException{
    WriteExcel writeExcel = new WriteExcel();
    List<User> users = writeExcel.getListOfUsers();
    writeExcel.writeExcel(PATH_TO_EXCEL, users);
  }
    
  private void writeExcel(String pathToExcel, List<User> users) {
    final String[] header= {"First Name", "Last Name", "Email", "DOB"};
    Workbook workbook = null;
    try {
      workbook = new XSSFWorkbook();
      // for HSSF (.xls extension) 
      //workbook = new HSSFWorkbook();
      // Creating sheet with in the workbook
      Sheet sheet = workbook.createSheet("Users");
      /*For Header*/
      Font font = workbook.createFont();
      font.setFontName("TIMES_ROMAN");
      font.setColor(IndexedColors.WHITE.getIndex());
      font.setBold(true);
            
      CellStyle style = workbook.createCellStyle();
      style.setFont(font);
      style.setWrapText(true);
      style.setAlignment(HorizontalAlignment.CENTER);
      style.setVerticalAlignment(VerticalAlignment.CENTER);
      style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
      style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      style.setBorderRight(BorderStyle.THIN);
      style.setRightBorderColor(IndexedColors.BLACK.getIndex());
      style.setBorderLeft(BorderStyle.THIN);
      style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
      style.setBorderTop(BorderStyle.THIN);
      style.setTopBorderColor(IndexedColors.BLACK.getIndex());
      style.setBorderBottom(BorderStyle.THIN);
      style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            
      Row row = sheet.createRow(0);
     
      for(int i = 0; i < header.length; i++) {
        // each column 12 characters wide
        sheet.setColumnWidth(i, 12*256);
        Cell cell = row.createCell(i);
        cell.setCellValue(header[i]);
        cell.setCellStyle(style);
      }
      /* Header ends*/
      /** Rows in the sheet **/
      CellStyle dateStyle = workbook.createCellStyle();
      // Setting format For the date column
      dateStyle.setDataFormat(workbook.getCreationHelper()
               .createDataFormat()
               .getFormat("dd/MM/yyyy"));
      int rowNum = 1;
      for(User user : users) {
        // create new row
        row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(user.getFirstName());
        row.createCell(1).setCellValue(user.getLastName());
        row.createCell(2).setCellValue(user.getEmail());
        Cell cell = row.createCell(3);
        cell.setCellValue(user.getDob());
        cell.setCellStyle(dateStyle);
      }
      // Writing sheet data
      FileOutputStream outputStream = new FileOutputStream(pathToExcel);
      workbook.write(outputStream);
    }catch (EncryptedDocumentException | IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally {
      try {
        if(workbook != null)
          workbook.close();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }
    
  // Dummy method for adding List of Users
  private List<User> getListOfUsers() throws ParseException {
    List<User> users = new ArrayList<User>();
    Calendar dob = Calendar.getInstance();
    dob.set(1975,6,12);
    users.add(new User("Jack", "Reacher", "abc@xyz.com", dob.getTime()));
    // Using LocalDate from new time&date API 
    LocalDate date = LocalDate.of(2016, Month.APRIL, 28);
    users.add(new User("Remington", "Steele", "rs@cbd.com",
       Date.from(date.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant())));
    dob.set(1965,12,6);
    users.add(new User("Jonathan", "Raven", "jr@sn.com", dob.getTime()));
    return users;
  }
}
The sheet you get by running this code is as below.
writing excel in Java using Apache POI

That's all for this topic How to Write Excel File in Java Using Apache POI. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Programs Page


Related Topics

  1. Spring MVC Excel Generation Example
  2. How to Create PDF From XML Using Apache FOP
  3. How to Create Password Protected Zip File in Java
  4. How to Find Last Modified Date of a File in Java
  5. Reading File in Java 8

You may also like-

  1. Difference Between Two Dates - Java Program
  2. Java Lambda Expression Comparator Example
  3. How to Find All The Permutations of The Given String
  4. How to Create Deadlock in Java Multi-Threading - Java Program
  5. Invoking Getters And Setters Using Reflection - Java Program
  6. Heap Memory Allocation in Java
  7. TypeWrapper Classes in Java
  8. Spring Web MVC Example With Annotations