Write to Excel File in Java Using Apache POI

In this article we’ll see how to write to Excel file in Java using Apache POI library. If you have to read an Excel file in Java you can check this post- Read Excel File in Java Using Apache POI

Table of contents

1. Apache POI
2. Maven dependencies for Apache POI
3. Apache POI classes for writing excel files
4. Write excel file in Java using Apache POI example

Apache POI

Apache POI is a Java API for Microsoft documents. Using Apache POI you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java.

Apache POI provides support for reading both OLE2 files and Office Open XML standards (OOXML) files.

• OLE2 files include most Microsoft Office files such as XLS, DOC, and PPT as well as MFC serialization API based file formats.
• Office OpenXML Format is the new standards based XML file format found in Microsoft Office 2007 and 2008. This includes XLSX, DOCX and PPTX.

Which means Apache POI supports writing excel files having .xls format as well as excel files with .xlsx format.

Maven dependencies for Apache POI

To write to excel file using Apache POI you need to add the following dependency.

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

Here note that poi-x.jar has classes for working with old excel format (.xls) and poi-ooxml-x.jar has classes for working with newer excel format (.xlsx).

Apache POI classes for writing excel files

Following section gives an overview of classes that are used for writing to excel file in Java using Apache POI.

Apache POI library provides two implementations for two excel spread sheet formats-

• HSSF- It is the pure Java implementation for earlier excel format (.xls). Classes in this implementation will usually have HSSF prefix like HSSFWorkBook, HSSFSheet.
• XSSF- It is the pure Java implementation for xslx file format (OOXML). Classes in this implementation will usually have XSSF prefix like XSSFWorkBook, XSSFSheet.

SS- It is a package built on top of HSSF and XSSF that provides common support for both formats with a common API. You should try to use classes from this package for better compatibility.

While working with excel file the usual progression is-

There are interfaces corresponding to this progression-

• 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. It is also the top level object for creating new sheets.
• org.apache.poi.ss.usermodel.Sheet- High level representation of a Excel worksheet. Sheets are the central structures within a workbook.
• org.apache.poi.ss.usermodel.Row- High level representation of a row of a spreadsheet.
• 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).

For creating a Workbook, WorkbookFactory class is used.

• org.apache.poi.ss.usermodel.WorkbookFactory- Factory for creating the appropriate kind of Workbook (be it HSSFWorkbook or XSSFWorkbook), by auto-detecting from the supplied input.

Write excel file in Java using Apache POI example

For the example there is a class Employee with fields firstName, lastName, department and DOB. Using these fields data is written to excel file in 4 columns per row. One extra column is added to show how to write a formula to excel sheet using Apache POI.

Employee class

import java.time.LocalDate;

public class Employee {
  private String firstName;
  private String lastName;
  private String department;
  private LocalDate dob;
  Employee(){}
  public Employee(String firstName, String lastName, String department, LocalDate dob) {
    this.firstName = firstName;
    this.lastName = lastName;
    this.department = department;
    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 getDepartment() {
    return department;
  }
  public void setDepartment(String department) {
    this.department = department;
  }
  public LocalDate getDob() {
    return dob;
  }
  public void setDob(LocalDate dob) {
    this.dob = dob;
  }
  @Override
  public String toString() {
    return "Employee [firstName=" + firstName + ", lastName=" + lastName + ", department=" + department + ", dob="
        + dob + "]";
  }
}

Following Java class retrieve data from fields of Employee objects and write to excel sheet.

import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
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;

public class WriteExcel {
  private static final String EXCEL_FILE_PATH="D:\\knpcode\\Employee1.xlsx";
  public static void main(String[] args) {
    WriteExcel writeExcel = new WriteExcel();    
    writeExcel.writeExcelFile(EXCEL_FILE_PATH, WriteExcel.getEmployees());
  }
    
  public void writeExcelFile(String excelFilePath, List<Employee> employees) {
    final String[] header= {"First Name", "Last Name", "Department", "DOB", "Current Date"};
    Workbook workbook = null;
    // Excel with .xslx extension
    workbook = new XSSFWorkbook();
    // For .xls extension HSSF workbook can be created
    //workbook = new HSSFWorkbook();

    // Creating sheet with in the workbook
    Sheet sheet = workbook.createSheet("Employees");
    /*Font and style For Header*/
    Font font = workbook.createFont();
    font.setFontName("VERDANA");
    font.setColor(IndexedColors.CORNFLOWER_BLUE.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.LIGHT_TURQUOISE.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);
    // Writing header to excel
    for(int i = 0; i < header.length; i++) {
       // each column 20 characters wide
       sheet.setColumnWidth(i, 20*256);
       Cell cell = row.createCell(i);
       cell.setCellValue(header[i]);
       cell.setCellStyle(style);
    }   
    // Header styling ends
    //Preparing column data for each row
    CellStyle dateStyle = workbook.createCellStyle();
    // Setting format For the date column
    dateStyle.setDataFormat(workbook.getCreationHelper()
                                    .createDataFormat().getFormat("dd-mm-yyyy"));
    int rowNum = 1;
    for(Employee emp : employees) {
      // create new row
      row = sheet.createRow(rowNum++);
      row.createCell(0).setCellValue(emp.getFirstName());
      row.createCell(1).setCellValue(emp.getLastName());
      row.createCell(2).setCellValue(emp.getDepartment());    
      // Writing date with date formatting
      Cell cell = row.createCell(3);
      cell.setCellStyle(dateStyle);
      cell.setCellValue(emp.getDob());
      // Writing formula with date formatting
      // as formula shows current date
      cell = row.createCell(4);
      cell.setCellStyle(dateStyle);
      cell.setCellFormula("Today()");
    }
          
    FileOutputStream outputStream = null;
    try {
     outputStream = new FileOutputStream(excelFilePath);
     // Writing to excel sheet
     workbook.write(outputStream);
    } catch (IOException exp) {
     // TODO Auto-generated catch block
     exp.printStackTrace();
    }finally {
      if(outputStream != null) {
        try {
          outputStream.close();
          workbook.close();
        } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }   
  }
    
  // Method to get list of employees
  private static List<Employee> getEmployees(){
    List<Employee> employees = new ArrayList<Employee>();
    //Adding 3 employee objects
    employees.add(new Employee("John", "Emerson", "Technology", LocalDate.of(1990,10,12)));
    employees.add(new Employee("Shelly", "Mcarthy", "HR", LocalDate.of(1960, 04, 07)));
    employees.add(new Employee("Tom", "Cevor", "Finance", LocalDate.of(1992, 02, 03)));
    return employees;
  }
}

Running this program creates excel sheet as shown below.

Points to note here-

1. For header in excel sheet some styling is done using CellStyle class.
2. For the date column, date formatting is done. To create a date format you can use CreationHelper class in Apache POI.

     workbook.getCreationHelper().createDataFormat().getFormat("dd-mm-yyyy")
     

3. To write a formula, setCellFormula() method is used.

That's all for the topic Write to Excel File in Java Using Apache POI. If something is missing or you have something to share about the topic please write a comment.

---

You may also like

• How to Convert File to Byte Array in Java
• Write a File Asynchronously in Java
• Java Program to Reverse Each Word in a String
• Serialization Proxy Pattern -readResolve() and writeReplace()
• Interface Vs Abstract Class in Java
• HDFS Replica Placement Policy
• Spring Boot Stand Alone (non web) Application Example
• Advantages and Disadvantages of Autowiring in Spring
• How to Delete File and Directory in Python
• HTTP POST Method in React - fetch, Axios