Java Create Excel File .xlsx using Apache POI

In this Java Apache POI tutorial we learn step by step to implement a Java program to export data from Java objects to an Excel file using the Apache POI library.

Table of contents

Add Apache POI dependencies to Java project
Create New Entity Class
Implement Excel File Exporter Class
How to use ExcelFileExporter class to export Excel file

Add Apache POI dependencies to Java project

First step, we add the Apache POI dependencies to the Java project.

If you use Gradle build project, add the following dependencies to the build.gradle file.

implementation group: 'org.apache.poi', name: 'poi', version: '5.2.2'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.2'

If you use Maven build project, add the following dependencies to the pom.xml file.

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

Create New Entity Class

For example, we want to implement a Java application to export a list of contacts data to an Excel file. At this step we implement a new Java class named Contact to represent the contact information which needs to be exported.

Contact.java

public class Contact {
    private String firstName;
    private String lastName;
    private String email;

    public Contact(String firstName, String lastName, String email) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    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;
    }
}

Implement Excel File Exporter Class

At this step we implement a new Java class named ExcelFileExporter with the following steps to export an Excel file from given data.

Create a new Workbook object which represent an Excel workbook.
Create a new Sheet object which represent an Excel worksheet.
Create a new header Row from given header texts. At this step we use CellStyle class to define header foreground color and border.
Create new data rows from given list of Contact objects. At this step we use CellStyle class to define cell border for data rows.
Using Sheet.autoSizeColumn(int column) method to adjusts column width to fit the contents for three first columns.
Using the Workbook.write(OutputStream stream) method to write Excel file with given name.

ExcelFileExporter.java

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.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 java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * Excel File Exporter
 */
public class ExcelFileExporter {

    public void exportExcelFile(List<Contact> contacts, String[] headers, String fileName) {
        // create a new Workbook
        Workbook workbook = new XSSFWorkbook();

        // Create a new Sheet named "Contacts"
        Sheet sheet = workbook.createSheet("Contacts");

        // Create header row
        createHeaderRow(workbook, sheet, headers);

        // Create rows
        for(int i = 0; i < contacts.size(); i++) {
            // row index equals i + 1 because the first row of Excel file is the header row.
            int rowIndex = i + 1;
            createNewRow(workbook, sheet, rowIndex, contacts.get(i));
        }

        // Adjusts 3 columns to set the width to fit the contents.
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);

        // Write to file
        try (OutputStream outputStream = new FileOutputStream(fileName)) {
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Create header row
     * @param workbook the Workbook object
     * @param sheet the Sheet object
     * @param headers the headers text
     */
    private void createHeaderRow(Workbook workbook, Sheet sheet, String[] headers) {
        Row headerRow = sheet.createRow(0);
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerCellStyle.setBorderTop(BorderStyle.THIN);
        headerCellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        headerCellStyle.setBorderRight(BorderStyle.THIN);
        headerCellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        headerCellStyle.setBorderBottom(BorderStyle.THIN);
        headerCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        headerCellStyle.setBorderLeft(BorderStyle.THIN);
        headerCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);

        for(int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerCellStyle);
        }
    }

    /**
     * Create a new row
     * @param workbook the Workbook object
     * @param sheet the Sheet object
     * @param rowIndex the index of row to create
     * @param contact the Contact object which represent information to write to row.
     */
    private void createNewRow(Workbook workbook, Sheet sheet, int rowIndex, Contact contact) {
        Row row = sheet.createRow(rowIndex);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);

        Cell cell = row.createCell(0);
        cell.setCellValue(contact.getFirstName());
        cell.setCellStyle(cellStyle);

        cell = row.createCell(1);
        cell.setCellValue(contact.getLastName());
        cell.setCellStyle(cellStyle);

        cell = row.createCell(2);
        cell.setCellValue(contact.getEmail());
        cell.setCellStyle(cellStyle);
    }

}

How to use ExcelFileExporter class to export Excel file

At this final step we implement a Main class and use the ExcelFileExporter above to export Excel file named contacts.xlsx as the following Java code.

Main.java

import java.util.ArrayList;
import java.util.List;

public class Main {
    public static void main(String... args) {
        // List of contacts to export to Excel file.
        List<Contact> contacts = new ArrayList<>();
        contacts.add(new Contact("Rayhan", "Harmon", "rayhan@simplesolution.dev"));
        contacts.add(new Contact("Paddy", "Sheridan", "paddy@simplesolution.dev"));
        contacts.add(new Contact("Clara", "Callaghan", "clara@simplesolution.dev"));
        contacts.add(new Contact("Josephine", "Amos", "josephine@simplesolution.dev"));
        contacts.add(new Contact("Sheikh", "Tucker", "sheikh@simplesolution.dev"));

        // Header text
        String[] headers = new String[] {"First Name", "Last Name", "Email"};

        // File name
        String fileName = "contacts.xlsx";

        // Export Excel file
        ExcelFileExporter excelFileExporter = new ExcelFileExporter();
        excelFileExporter.exportExcelFile(contacts, headers, fileName);
    }
}