Java Add Rows to Existing Excel File using Apache POI

In this Java Apache POI tutorial we learn how to add more rows to an existing Excel file using the Apache POI library.

Table of contents

Add Apache POI dependencies to Java project
Example Excel File to Add More Records
Add New Entity Java Class
How to add rows to existing Excel file in Java
How to use the ExcelFileService class

Add Apache POI dependencies to 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>

Example Excel File to Add More Records

For example, we have an existing Excel file located at D:\SimpleSolution\Data\Employee.xlsx which store employee records as the screenshot below. In next steps of this tutorial we learn how to add more employee row to this Excel file.

Java Add Rows to Existing Excel File using Apache POI

Add New Entity Java Class

At this step we create a new Java class named Employee which represents an employee record as the Java code below.

Employee.java

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

    public Employee(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;
    }

}

How to add rows to existing Excel file in Java

At this step we implement a new Java class named ExcelFileService and implement method to add new rows to existing Excel sheet follow these steps.

Step 1: open the Excel Workbook from InputStream using WorkbookFactory.create(InputStream inp) method.
Step 2: get the existing Excel Sheet from the workbook using Workbook.getSheetAt(int index) method.
Step 3: get the last row number on the sheet using the Sheet.getLastRowNum() method.
Step 4: populate the new row index and use the Sheet.createRow(rowIndex) method to create a new Row object.
Step 5: create a new Excel Cell using Row.createCell(int column) method and set specified value to the Cell object.
Step 6: write the changes to Excel file using FileOutputStream and Workbook.write(OutputStream stream) method.

ExcelFileService.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.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.ss.usermodel.WorkbookFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

/**
 * The Excel service class to add more rows to the Excel file
 */
public class ExcelFileService {

    /**
     * Add the list of Employee objects as rows to Excel file
     * @param filePath the Excel file path to add more rows
     * @param employees the list of Employee objects
     */
    public void addRows(String filePath, List<Employee> employees) {
        InputStream inputStream = null;
        Workbook workbook = null;
        OutputStream outputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
            workbook = WorkbookFactory.create(inputStream);

            // Get first sheet of Excel file
            Sheet sheet = workbook.getSheetAt(0);
            // get latest index number
            int rowIndex = sheet.getLastRowNum();
            for (Employee employee : employees) {
                // Populate the index value of new row
                rowIndex = rowIndex + 1;
                createNewRow(workbook, sheet, rowIndex, employee);
            }
            // Write updated Excel file
            outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
                workbook.close();
                outputStream.close();
            }catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * Add new row to Excel sheet
     * @param workbook the Excel workbook
     * @param sheet the Excel sheet
     * @param rowIndex the index to add row
     * @param employee the Employee record to add to Excel row
     */
    private void createNewRow(Workbook workbook, Sheet sheet, int rowIndex, Employee employee) {
        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(employee.getFirstName());
        cell.setCellStyle(cellStyle);

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

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

How to use the ExcelFileService class

The the following Java code, we show how to use the ExcelFileService class from previous step to update the example Excel file by adding more employee rows and save it.

AddRowsToExcelFileExample1.java

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

public class AddRowsToExcelFileExample1 {
    public static void main(String... args) {
        // Existing Excel file to add more rows
        String filePath = "D:\\SimpleSolution\\Data\\Employee.xlsx";

        // List of Employee objects to add to Excel file
        List<Employee> employees = new ArrayList<>();
        employees.add(new Employee("Sohail", "Petty", "sohail@simplesolution.dev"));
        employees.add(new Employee("Sienna", "Fowler", "sienna@simplesolution.dev"));
        employees.add(new Employee("Bertie", "Naylor", "bertie@simplesolution.dev"));

        // Add more rows to Excel file
        ExcelFileService excelFileService = new ExcelFileService();
        excelFileService.addRows(filePath, employees);
    }
}