How to Append Data in Excel Using Java POI

Adding new data to an existing Excel file without wiping out what's already there is a common, and sometimes frustrating, programming task. Instead of manually copying and pasting rows, you can automate this entire process using Java and the powerful Apache POI library. This tutorial will walk you through exactly how to read an Excel file, find the last row with data, and seamlessly append new records to the end.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is Apache POI?

Apache POI is a popular open-source Java library created to manipulate Microsoft Office files, including Excel spreadsheets, Word documents, and PowerPoint presentations. For developers, it's the go-to tool for programmatically creating, reading, and modifying xls and xlsx files without needing to have Microsoft Office installed on the server or machine where the code is running.

You can use it for all sorts of data-heavy tasks, such as:

• Generating daily sales reports and emailing them automatically.
• Consolidating data from multiple CSV files into a single, formatted Excel workbook.
• Reading user data from an uploaded spreadsheet to import into an application database.
• Creating dynamic financial models or project dashboards based on real-time data.

In our case, we'll focus on one specific, highly practical use: appending data to a master report or log file.

Setting Up Your Java Project

Before writing any code, you need to add the Apache POI library to your project's dependencies. The easiest way to do this is with a build management tool like Maven or Gradle.

You'll need two main artifacts:

• poi: The core library for handling the older .xls file format (HSSF).
• poi-ooxml: An extension for handling the newer XML-based .xlsx file format (XSSF), which is what most people use today.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Maven Configuration

If you're using Maven, add the following dependencies to your pom.xml file. Always check Maven Central for the latest stable versions.

<dependencies>
    <!-- For reading and writing .xlsx files (Excel 2007+) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.5</version>
    </dependency>

    <!-- For reading and writing .xls files (Excel 97-2003) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.5</version>
    </dependency>
</dependencies>

Once you save the pom.xml file, your IDE (like IntelliJ or Eclipse) will automatically download and configure the required JAR files.

The Core Logic: How to Append Data Step-by-Step

The logic for appending data follows a simple sequence. First, you need to open the existing file and figure out where the data ends. Then, you can start adding new rows right after it.

1. Open an input stream to your Excel file: You need to read the existing file into your program. A FileInputStream is perfect for this.
2. Create a Workbook object: Using the input stream, you'll instantiate a XSSFWorkbook object (for .xlsx files) to represent the entire Excel file in memory.
3. Select the correct Worksheet: Access the specific sheet you want to modify, either by its name (e.g., getSheet("Sheet1")) or by its index (e.g., getSheetAt(0)).
4. Find the last row number: This is the most important step. The Sheet object has a method called getLastRowNum(), which returns the index of the last row containing data. Since row indices are zero-based, the next available row will be at getLastRowNum() + 1.
5. Create new Row and Cell objects: You'll loop through your new data, and for each new record, create a Row object at the next available index. Within that row, you'll create Cell objects for each column.
6. Set the cell values: Populate each cell with your data using the setCellValue() method.
7. Write the changes back to the file: To save your work, you need to open a FileOutputStream to the same file path and use the workbook.write() method. This will overwrite the original file with the newly updated workbook.
8. Close all resources: It's crucial to close the workbook and file streams to prevent memory leaks. The best way to do this is with a try-with-resources block.

Practical Example: Coding the Append Logic

Let's put the theory into practice. Imagine you have an Excel file named DailyReport.xlsx with the following data:

Initial State of DailyReport.xlsx:

Our goal is to add two new product sales to this report. Here's a complete Java class that accomplishes this.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Java Code

import org.apache.poi.ss.usermodel.*,
import org.apache.poi.xssf.usermodel.XSSFWorkbook,

import java.io.File,
import java.io.FileInputStream,
import java.io.FileOutputStream,
import java.io.IOException,
import java.util.Date,

public class ExcelAppender {

    public static void main(String[] args) {
        String filePath = "DailyReport.xlsx",

        // Let's define the new data we want to add
        Object[][] newData = {
                {"Product D", "East", 1200.00},
                {"Product E", "South", 750.50}
        },

        try (
            // Use FileInputStream to read the existing file
            FileInputStream inputStream = new FileInputStream(new File(filePath)),
            // Create a Workbook object from the input stream
            Workbook workbook = new XSSFWorkbook(inputStream),
        ) {
            // Get the first sheet from the workbook
            Sheet sheet = workbook.getSheetAt(0),
            // Find the last row number
            int lastRowNum = sheet.getLastRowNum(),
            System.out.println("Last row number is: " + lastRowNum),

            // Loop through our new data and append it
            for (Object[] rowData : newData) {
                // Create a new row at the end of the sheet
                Row newRow = sheet.createRow(++lastRowNum),
                int cellNum = 0,
                for (Object cellData : rowData) {
                    Cell newCell = newRow.createCell(cellNum++),
                    // Set cell value based on its type
                    if (cellData instanceof String) {
                        newCell.setCellValue((String) cellData),
                    } else if (cellData instanceof Double) {
                        newCell.setCellValue((Double) cellData),
                    }
                }
            }

            // Use FileOutputStream to write the changes back to the original file
            try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
                workbook.write(outputStream),
            }

            System.out.println("Data successfully appended to the Excel file."),

        } catch (IOException ex) {
            System.out.println("Error updating the file: " + ex.getMessage()),
            ex.printStackTrace(),
        }
    }
}

Code Walkthrough

1. We specify the file path and define our newData as a 2D array. This structure makes it easy to manage a list of new rows.
2. We open the file with a FileInputStream and create an XSSFWorkbook.
3. The key line is int lastRowNum = sheet.getLastRowNum(),. If our sheet has a header (row 0) and records at rows 1 and 2, this will return 2.
4. We loop through the newData. In the first iteration, we call sheet.createRow(++lastRowNum). The pre-increment ++ makes lastRowNum become 3, which is the correct index for our first new row.
5. Inside the inner loop, we create cells and set their values, casting them to the appropriate String or Double.
6. Finally, we open a FileOutputStream and write the workbook back to the same file path, then close the streams to ensure changes are saved properly.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Handling Different Data Types

The real world involves more than just strings and numbers. You often have to deal with dates, booleans, and formulas. Here's how you can handle them.

Writing Dates

To write dates correctly, it's best to create a CellStyle to format the cell as a date in Excel.

CellStyle dateCellStyle = workbook.createCellStyle(),
CreationHelper createHelper = workbook.getCreationHelper(),
dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")),

Cell dateCell = newRow.createCell(cellIndex),
dateCell.setCellValue(new Date()), // Sets the current date
dateCell.setCellStyle(dateCellStyle),

Writing Formulas

You can also write formulas directly into cells. Apache POI will not evaluate the formula, but Excel will do it when the user opens the workbook.

Cell formulaCell = row.createCell(4),
// Assuming columns C and D are a Quantity and a Price
formulaCell.setCellFormula("C5*D5"),

Common Issues and Best Practices

• FileNotFoundException: Ensure your file path is correct and accessible.
• NullPointerException: Verify the sheet exists before operating on it.
• File Corruption: Always close resources properly, use try-with-resources.
• Excel is still Open: Close Excel files before running the program, otherwise, you'll get an IOException.

Final Thoughts

Learning to programmatically append data to Excel files with Java POI is an incredibly valuable skill for automating repetitive reporting and data consolidation tasks. By opening the file, identifying the last row, and carefully writing new data, you can build robust systems that save hours of manual effort.

While coding these solutions gives you ultimate control, we know firsthand that manually managing data exports, scripts, and updates is time-consuming. We built Graphed to completely automate this process. Instead of writing Java code to append rows to a report, you can connect your data sources (like databases, Shopify, or Google Ads) directly. We then instantly build and refresh your dashboards in real-time, so you can focus on analyzing insights, not wrestling with data.