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

In this article, you’ll learn how to create and write to an excel file in Java using Apache POI.

You can check out the previous article to learn about Apache POI’s high-level architecture and how to read excel files using Apache POI library.

Dependencies

You need to add the following dependencies to include Apache POI in your project.

Maven

Maven users can add the following to their pom.xml file -

<!-- Used to work with the older excel file format - `.xls` -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

<!-- Used to work with the newer excel file format - `.xlsx` -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

And gradle users can add the following to their build.gradle file -

Gradle

compile "org.apache.poi:poi:3.17"	     // For `.xls` files
compile "org.apache.poi:poi-ooxml:3.17"	 // For `.xlsx` files

Writing to an excel file using Apache POI

Let’s create a simple Employee class first. We’ll initialize a list of employees and write the list to the excel file that we’ll generate using Apache POI.

class Employee {
    private String name;
    private String email;
    private Date dateOfBirth;
    private double salary;

    public Employee(String name, String email, Date dateOfBirth, double salary) {
        this.name = name;
        this.email = email;
        this.dateOfBirth = dateOfBirth;
        this.salary = salary;
    }

	// Getters and Setters (Omitted for brevity)
}

Let’s now look at the program to create an excel file and then write data to it. Note that I’ll be using an XSSFWorkbook to create a Workbook 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 in the previous article to learn about Workbook, XSSFWorkbook, HSSFWorkbook and other Apache POI terminologies.

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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.Calendar;
import java.util.Date;
import java.util.List;

public class ExcelWriter {

    private static String[] columns = {"Name", "Email", "Date Of Birth", "Salary"};
    private static List<Employee> employees =  new ArrayList<>();

	// Initializing employees data to insert into the excel file
    static {
        Calendar dateOfBirth = Calendar.getInstance();
        dateOfBirth.set(1992, 7, 21);
        employees.add(new Employee("Rajeev Singh", "rajeev@example.com", 
                dateOfBirth.getTime(), 1200000.0));

        dateOfBirth.set(1965, 10, 15);
        employees.add(new Employee("Thomas cook", "thomas@example.com", 
                dateOfBirth.getTime(), 1500000.0));

        dateOfBirth.set(1987, 4, 18);
        employees.add(new Employee("Steve Maiden", "steve@example.com", 
                dateOfBirth.getTime(), 1800000.0));
    }

    public static void main(String[] args) throws IOException, InvalidFormatException {
        // Create a Workbook
        Workbook workbook = new XSSFWorkbook(); // new HSSFWorkbook() for generating `.xls` file

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

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

        // Create a Font for styling header cells
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 14);
        headerFont.setColor(IndexedColors.RED.getIndex());

        // Create a CellStyle with the font
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.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(headerCellStyle);
        }

        // Create Cell Style for formatting Date
        CellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MM-yyyy"));

        // Create Other rows and cells with employees data
        int rowNum = 1;
        for(Employee employee: employees) {
            Row row = sheet.createRow(rowNum++);

            row.createCell(0)
                    .setCellValue(employee.getName());

            row.createCell(1)
                    .setCellValue(employee.getEmail());

            Cell dateOfBirthCell = row.createCell(2);
            dateOfBirthCell.setCellValue(employee.getDateOfBirth());
            dateOfBirthCell.setCellStyle(dateCellStyle);

            row.createCell(3)
                    .setCellValue(employee.getSalary());
        }

		// 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 fileOut = new FileOutputStream("poi-generated-file.xlsx");
        workbook.write(fileOut);
        fileOut.close();

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

In the above program, we first created a workbook using the XSSFWorkbook class. Then we created a Sheet named “Employee”. Once we got a Sheet, we created the header row and columns. The header cells were styled using a different font.

After creating the header row, we created other rows and columns from the employees 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. Following is the file generated by running the above program -

Wow, that’s nice no? :)

Opening and Modifying an existing Excel file

The following method shows you how to open an existing excel file and update it -

private static void modifyExistingWorkbook() throws InvalidFormatException, IOException {
    // Obtain a workbook from the excel file
    Workbook workbook = WorkbookFactory.create(new File("existing-spreadsheet.xlsx"));

    // Get Sheet at index 0
    Sheet sheet = workbook.getSheetAt(0);

    // Get Row at index 1
    Row row = sheet.getRow(1);
    
    // Get the Cell at index 2 from the above row
    Cell cell = row.getCell(2);

    // Create the cell if it doesn't exist
    if (cell == null)
        cell = row.createCell(2);

    // Update the cell's value
    cell.setCellType(CellType.STRING);
    cell.setCellValue("Updated Value");

    // Write the output to the file
    FileOutputStream fileOut = new FileOutputStream("existing-spreadsheet.xlsx");
    workbook.write(fileOut);
    fileOut.close();

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

The above program is self-explanatory. We first obtain a Workbook using the WorkbookFactory.create() method, and then get the 3rd column in the 2nd row of the 1st sheet and update its value.

Finally, we write the updated output to the file.