Apache POI: Append Rows to an Existing Excel File

Learn to append new rows in an excel sheet in Java using the Apache POI. In this example, we will learn to prepare the data (to write) and then append the new rows after the last row in an existing sheet.

1. Maven

Add the latest version of org.apache.poi:poi and org.apache.poi:poi-ooxml in the application, if not added already.

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

2. Core Classes and Methods

The POI uses the following classes to read or write data:

• XSSFWorkbook: represents an excel workbook.
• Sheet: represents a sheet in the workbook.
• Row: represents a row in the sheet.
• Cell: represents a cell in any row.

To write or append records to the sheet, we need to use the following methods:

• workbook.getSheetAt(index) or workbook.getSheet(name) gets the sheet by its index or name. Index starts at 0.
• sheet.getLastRowNum() returns the last row number in a sheet. It helps in the case of appending new rows.
• sheet.createRow(num) creates a new row in the given sheet.
• row.createCell(num) creates a new cell in the given row.
• cell.setCellValue(val) sets a value in the given cell.

See also: Read and Write Excel File in Java

3. Appending Rows to an Existing Sheet

Using the above-discussed methods, we will be appending the new rows in the following Excel sheet.

The data can be in any form. To keep things simple, we have the following model containing the data.

public class BusinessEntity {

  private String firstName;
  private String lastName;
  private String age;

  //constructors, getters and setters
}

Although we can write logic as required, here we are creating a generic Object[] which we will pass to a utility function. The values in the object array are mapped to cells in the row in index order.

private static Map<Integer, Object[]> prepareData(int rowNum, List<BusinessEntity> recordsToWrite) {

    Map<Integer, Object[]> data = new HashMap<>();
    for (BusinessEntity entity : recordsToWrite) {
      rowNum++;
      data.put(rowNum, new Object[]{
      	rowNum, 
      	entity.getFirstName(),
      	entity.getLastName(), 
      	entity.getAge()});
    }
    return data;
}

The following is the utility function to write the records in Excel.

• It takes the file path and the records as method parameters.
• Then, it calls prepareData() to create the object array.
• Finally, the object array is iterated, and cell values are populated based on the indices in the array.
• The correct row to append the data is calculated with the function sheet.getLastRowNum() and adding 1 to it.

Note that the function checks the data type for the values in the object array so we can call a corresponding setCellValue() method.

public static void appendRows(List<BusinessEntity> recordsToWrite, File file)
  throws IOException, InvalidFormatException {

	XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
	Sheet sheet = workbook.getSheetAt(0);
	int rowNum = sheet.getLastRowNum() + 1;

	Map<Integer, Object[]> data = prepareData(rowNum, recordsToWrite);

	Set<Integer> keySet = data.keySet();
	for (Integer key : keySet) {
	  Row row = sheet.createRow(rowNum++);
	  Object[] objArr = data.get(key);
	  int cellNum = 0;
	  for (Object obj : objArr) {
	  Cell cell = row.createCell(cellNum++);
	  if (obj instanceof String)
	      cell.setCellValue((String) obj);
	  else if (obj instanceof Integer)
	      cell.setCellValue((Integer) obj);
	  }
	}
	try {
	  FileOutputStream out = new FileOutputStream(file);
	  workbook.write(out);
	  out.close();
	} catch (Exception e) {
	  e.printStackTrace();
	}
}

4. Demo

To run the demo, we will create a few records and pass them to the utility function appendRows() along with the file path.

public static void main(String[] args) {

    File file = new File("C:\\temp\\data.xlsx");

    List<BusinessEntity> recordsToWrite = List.of(
        new BusinessEntity("Charles", "Babej", 60),
        new BusinessEntity("John", "Doe", 70),
        new BusinessEntity("Loreum", "Ipsum", 80)
    );

    try {
      appendRows(recordsToWrite, file);
    } catch (IOException | InvalidFormatException e) {
      e.printStackTrace();
    }
}

Verify the appended rows in the Excel file. Note that formatting cell values are out of the scope of this tutorial.

5. Conclusion

In this Apache POI tutorial, we learned to append new rows in an existing Excel sheet by creating a few utility functions. I will suggest you play with the code for better understanding.

Happy Learning !!

Source Code on Github