How to write to an Excel file in Java using Apache POI

Excel is the popular file format created by Microsoft and we will learn how to write data to an excel file using Apache POI.

How to write to an Excel file in Java using Apache POI
Excel Writer

Excel spreadsheets are widely used around the world for various tasks that are related to analysis, storage of tabular data, calculation, visualization etc. Although Excel file is not an opened file format, Java applications can still read and write Excel files using the Apache POI - the Java API for Microsoft Documents, because the development team uses reverse-engineering to understand the Excel file format. Hence the name POI stands for Poor Obfuscation Implementation.

Since Excel files are so common, developers often encounter their uses on reading data or to generate a report in excel file format.

In this blog post, we will see how to write data to an excel file in Java using Apache POI's library. We will use Mavan for this project.

Getting Apache POI library:

Apache POI is the pure Java API for reading and writing Excel files in both formats XLS (Excel 2003 and earlier) and XLSX (Excel 2007 and later). To use Apache POI in your Java project:

Add the following dependencies to pom.xml file:

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>5.0.0</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>5.0.0</version>
</dependency>
  • The first dependency poi is used to work with the old Microsoft's binary file format for excel. These file formats have .xls extension.
  • The second dependency poi-ooxml is used to work with the newer xml based file format. These file formats have .xlsx extension.

Writing to an excel file using Apache POI:

We will first create Person class and list of persons to write the list to the excel file.

public class Person {

    private String name;

    private String username;

    private String email;

    public Person(String name, String username, String email) {
        this.name = name;
        this.username = username;
        this.email = email;
    }
   
    // Getters and Setters goes here
}

To write data to an excel file, we will be using an XSSFWorkbook instance. This will generate the newer XML based excel file (.xlsx). You may choose to use HSSFWorkbook if you want to generate the older binary excel format (.xls).

Check out the Apache POI terminologies section here.

import com.velocitybytes.model.Person;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelWriter {

    private static final String[] columns = {"Name", "Username", "Email"};

    private static final List<Person> persons = new ArrayList<>();

    static {
        persons.add(new Person("Srivastava", "srivastava", "srivastava@gmail.com"));
        persons.add(new Person("VelocityBytes", "velocitybytes", "srivastava@gmail.com"));
        persons.add(new Person("Killer Frost", "killerfrost", "killerfrost@gmail.com"));
    }

    public void excelWriter(String file) throws IOException {
        // Create a workbook - for .xlsx files
        Workbook workbook = new XSSFWorkbook(); // HSSFWorkbook() for .xls files

        /* CreationHelper helps us create instances of various things like DataFormat,
           Hyperlink, RichTextString etc., in a format (HSSF, XSSF) independent way
        */
        CreationHelper creationHelper = workbook.getCreationHelper();

        // Create a Sheet
        Sheet sheet = workbook.createSheet("Person");

        // Create a Font for styling header cells
        Font headerFont = workbook.createFont();
        headerFont.setBold(false);
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setColor(IndexedColors.GREEN.getIndex());

        // Create CellStyle with font
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(headerFont);

        // Create a Row
        Row headerRow = sheet.createRow(0);

        // Create cells
        for (int i = 0; i < columns.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(columns[i]);
            cell.setCellStyle(cellStyle);
        }

        int rowNum = 1;

        for (Person person: persons) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(person.getName());
            row.createCell(1).setCellValue(person.getUsername());
            row.createCell(2).setCellValue(person.getEmail());
        }

        // Resize all columns to fit the content size
        for (int i = 0; i < columns.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // Write the output to a file
        FileOutputStream outputStream = new FileOutputStream(file);
        workbook.write(outputStream);
        outputStream.close();

        // Close the workbook
        workbook.close();
    }
}

In the above program, we created a workbook using XSSFWorkbook class. Then we created a Sheet named "Person", once sheet is obtained, we created the header row and columns. With font helpers we have set fonts and colors. After creating rows, we created other rows and columns from persons list. Next, we used sheet.autoSizeColumn() method to resize all the columns to fit the content size. Finally, we wrote the output to a file.

You can find the source code at Github.

Thanks for reading!