To append rows to an existing Excel file with Apache POI, we load the workbook into memory, create new rows after sheet.getLastRowNum(), and write the whole workbook back to disk. POI has no append mode for Excel files, so every change, even one new row, means reading the complete file and writing a complete new one.
We append rows whenever a Java program adds data to a spreadsheet that people already use, such as a daily expense log, an order export that grows every night or a test report that collects results from several runs.
The following example appends one expense to expenses.xlsx, which has a header row and two expense rows. The new row copies the date format of the row before it, and the workbook goes to a temporary file first, which replaces the original only after a successful write.
Path temp = file.resolveSibling("expenses.xlsx.tmp");
try (InputStream in = Files.newInputStream(file);
Workbook workbook = WorkbookFactory.create(in)) {
Sheet sheet = workbook.getSheetAt(0);
int nextRow = sheet.getLastRowNum() + 1; // 3 (rows 0 to 2 hold data)
CellStyle dateStyle = sheet.getRow(2).getCell(0).getCellStyle(); // yyyy-mm-dd
Row row = sheet.createRow(nextRow);
row.createCell(0).setCellValue(LocalDate.of(2026, 10, 3));
row.getCell(0).setCellStyle(dateStyle);
row.createCell(1).setCellValue("Fuel");
row.createCell(2).setCellValue(45.00);
try (OutputStream out = Files.newOutputStream(temp)) {
workbook.write(out); // the whole workbook goes to the temp file
}
}
Files.move(temp, file, StandardCopyOption.REPLACE_EXISTING); // expenses.xlsx has 4 rowsNotice that getLastRowNum() returns the zero-based index of the last row, so the next free row is that index plus one. Also notice that the input stream is closed before Files.move() replaces the file.
Next, we look at how POI reads and writes a workbook, how to find the right row when a sheet is empty or has formatted blank rows, and how to keep the formats of the existing rows. After that, we add rows before a totals row and append thousands of rows with SXSSFWorkbook.
1. How Appending Rows Works in Apache POI
An .xlsx file is a ZIP archive with XML files inside, and an .xls file is a binary file. Neither format lets a program add bytes at the end of the file the way we append lines to a log file. So POI always works on the whole workbook.
- WorkbookFactory.create() reads the complete file into a Workbook object in memory.
- We pick a Sheet, find the first free row index and call createRow() and createCell() for the new data.
- Workbook.write() writes the complete workbook, old rows and new rows, to an output stream.
For example, an expense tracker app exports the day’s expenses to a shared spreadsheet every night. The export job opens expenses.xlsx, adds the new rows at the end of the October sheet and saves the file, so the people who use the spreadsheet see the new expenses in the morning.
The four POI spreadsheet interfaces in the org.apache.poi.ss.usermodel package cover both file formats. WorkbookFactory picks the implementation from the file content, which is XSSFWorkbook for .xlsx and HSSFWorkbook for .xls.
| Interface | Represents | Methods we use for appending |
|---|---|---|
| Workbook | the whole Excel file | getSheetAt(0), getSheet(“October”), write(out) |
| Sheet | one worksheet | getLastRowNum(), getRow(index), createRow(index) |
| Row | one row of a sheet | createCell(column), getCell(column) |
| Cell | one cell of a row | setCellValue(value), setCellStyle(style) |
If you are new to POI, the Excel read and write tutorial creates a workbook from scratch and reads every cell type.
2. Maven Dependency
The poi-ooxml artifact supports .xlsx files and pulls in the poi artifact for .xls files, so one dependency covers both formats. The examples use Apache POI 5.5.1 on Java 25.
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.5.1</version>
</dependency>3. Finding the First Free Row
The method Sheet.getLastRowNum() returns the zero-based index of the last row that exists in the sheet. When the sheet has no rows at all, the method returns -1, so getLastRowNum() + 1 gives the correct index 0 for an empty sheet too.
The method getPhysicalNumberOfRows() looks similar, but it counts only the rows that exist. When a sheet has a gap, such as rows 0, 1 and 5, the count is 3 while the next free index is 6, so the count is the wrong value for appending.
| Sheet content | getLastRowNum() | getPhysicalNumberOfRows() | Next free row |
|---|---|---|---|
| no rows | -1 | 0 | 0 |
| one row at index 0 | 0 | 1 | 1 |
| rows 0, 1 and 5 | 5 | 3 | 6 |
| data in rows 0, 1 and 5, a formatted blank row at index 20 | 20 | 4 | 6 |
The last row of the table is a common surprise with files that people edit in Excel. When someone formats a whole block of rows, for example gives rows 7 to 21 a date format for future entries, Excel saves those rows even though they hold no data. So getLastRowNum() returns 20, and our new expenses land far below the real data.
The helper lastDataRowIndex() walks back from the last row and stops at the first row that has a non-blank cell.
public static int lastDataRowIndex(Sheet sheet) {
for (int i = sheet.getLastRowNum(); i >= 0; i--) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
for (Cell cell : row) {
if (cell.getCellType() != CellType.BLANK) {
return i;
}
}
}
return -1;
}int lastRow = sheet.getLastRowNum(); // 20 (formatted blank row)
int lastData = lastDataRowIndex(sheet); // 5
int nextRow = lastDataRowIndex(sheet) + 1; // 6Because a formatted blank row already exists, we call sheet.getRow(index) first and create the row only when getRow() returns null, as the example in section 4 does.
4. Appending Rows to an Existing Excel File
The following example appends a list of expenses to the first sheet of expenses.xlsx. Each expense is a record with a date, a category and an amount, and each expense becomes one row with three cells. Before the run, the sheet has a header row and two expenses, with a yyyy-mm-dd format on the date column and a #,##0.00 format on the amount column.
public record Expense(LocalDate date, String category, double amount) {
}The method appendRows() combines the steps from section 1 with the helper from section 3. It returns the index of the first new row, so the caller can log where the data went.
public static int appendRows(Path file, List<Expense> expenses) throws IOException {
Path temp = file.resolveSibling(file.getFileName() + ".tmp");
int firstNewRow;
try (InputStream in = Files.newInputStream(file);
Workbook workbook = WorkbookFactory.create(in)) {
Sheet sheet = workbook.getSheetAt(0);
int lastDataRow = lastDataRowIndex(sheet); // -1 for an empty sheet
firstNewRow = lastDataRow + 1;
CellStyle dateStyle = styleOrNew(sheet, lastDataRow, 0, workbook, "yyyy-mm-dd");
CellStyle amountStyle = styleOrNew(sheet, lastDataRow, 2, workbook, "#,##0.00");
int rowIndex = firstNewRow;
for (Expense expense : expenses) {
Row row = sheet.getRow(rowIndex); // a blank, formatted row can exist
if (row == null) {
row = sheet.createRow(rowIndex);
}
Cell date = row.createCell(0);
date.setCellValue(expense.date());
date.setCellStyle(dateStyle);
row.createCell(1).setCellValue(expense.category());
Cell amount = row.createCell(2);
amount.setCellValue(expense.amount());
amount.setCellStyle(amountStyle);
rowIndex++;
}
try (OutputStream out = Files.newOutputStream(temp)) {
workbook.write(out);
}
} catch (IOException | RuntimeException e) {
Files.deleteIfExists(temp);
throw e;
}
Files.move(temp, file, StandardCopyOption.REPLACE_EXISTING);
return firstNewRow;
}The try-with-resources block closes the input stream and the workbook before Files.move() runs. When reading or writing fails, the catch block deletes the half-written temp file and rethrows the exception, and the original file stays as it was.
We call the method with two new expenses and print the sheet before and after the call.
List<Expense> newExpenses = List.of(
new Expense(LocalDate.of(2026, 10, 3), "Fuel", 45.00),
new Expense(LocalDate.of(2026, 10, 4), "Groceries", 63.80)
);
int firstNewRow = ExcelAppender.appendRows(Path.of("expenses.xlsx"), newExpenses); // 30: Date Category Amount
1: 2026-10-01 Groceries 52.40
2: 2026-10-02 Rent 1,200.00
Appended from row index 3
0: Date Category Amount
1: 2026-10-01 Groceries 52.40
2: 2026-10-02 Rent 1,200.00
3: 2026-10-03 Fuel 45.00
4: 2026-10-04 Groceries 63.80We can see that the new rows start at index 3, right after the last expense, and that they show the same date and amount formats as the existing rows.
4.1. Keeping the Date and Number Formats
Excel stores a date as a number of days counted from 1900, and the cell style decides whether the cell shows a date or that number. So setCellValue(LocalDate) without a date style shows 46298 instead of 2026-10-03 in Excel.
The method styleOrNew() takes the style from the same column of the last data row, so the new rows look like the rows the user already has. When the sheet has only a header row or no rows, there is no data row to copy from, and the method creates a style with the given format.
private static CellStyle styleOrNew(Sheet sheet, int rowIndex, int column, Workbook workbook,
String format) {
if (rowIndex > 0) {
Row row = sheet.getRow(rowIndex);
Cell cell = row == null ? null : row.getCell(column);
if (cell != null) {
return cell.getCellStyle();
}
}
CellStyle style = workbook.createCellStyle();
style.setDataFormat(workbook.createDataFormat().getFormat(format));
return style;
}We create or look up each style once, outside the loop, and never call createCellStyle() for every cell. An .xlsx workbook holds at most 64,000 cell styles, and a loop that creates one style per cell reaches that limit after a few thousand rows.
5. Why We Write to a Temporary File
A common first attempt opens the file with new XSSFWorkbook(file) and writes back to the same file. The constructor that takes a File reads the ZIP entries lazily from the open file. FileOutputStream truncates the file to zero bytes, so POI can no longer read the parts it still needs.
try (XSSFWorkbook workbook = new XSSFWorkbook(file)) {
workbook.getSheetAt(0).createRow(3).createCell(0).setCellValue("Fuel");
try (OutputStream out = new FileOutputStream(file)) {
workbook.write(out); // POIXMLException: java.io.EOFException: Unexpected end of ZLIB input stream
}
}After the exception, expenses.xlsx is 0 bytes long, and the next WorkbookFactory.create() throws an EmptyFileException. All the old rows are gone.
The safe order has two rules, and appendRows() in section 4 follows both.
- Read the workbook from an InputStream that we close before we replace the file, so no open handle points to the original file.
- Write to a temp file in the same folder, and replace the original with Files.move() only after write() returns without an exception.
The temp file also protects the data when the JVM stops halfway through write(), for example during a deployment. In that case, only the temp file is damaged, and the next run overwrites it.
6. Adding Rows Before a Totals Row
Many sheets end with a totals row, such as Total with SUM(C2:C3) in the amount column. Appending after the last row puts the new expenses below the total, so the sum does not include them.
The method Sheet.shiftRows(startRow, endRow, n) moves the rows from startRow to endRow down by n rows, and we create the new rows in the gap. POI updates formula references that point into the moved rows, but a range that ends before the moved rows stays the same. For example, SUM(C2:C3) is still SUM(C2:C3) after shiftRows(3, 3, 2), so we write the formula again with the new last row.
Sheet sheet = workbook.getSheetAt(0);
int totalsRow = sheet.getLastRowNum(); // 3
int count = expenses.size(); // 2
sheet.shiftRows(totalsRow, totalsRow, count); // totals row moves to index 5
for (int i = 0; i < count; i++) {
Expense expense = expenses.get(i);
Row row = sheet.createRow(totalsRow + i);
row.createCell(0).setCellValue(expense.date());
row.createCell(1).setCellValue(expense.category());
row.createCell(2).setCellValue(expense.amount());
}
int newTotalsRow = totalsRow + count; // 5
Cell sum = sheet.getRow(newTotalsRow).getCell(2);
sum.setCellFormula("SUM(C2:C" + newTotalsRow + ")"); // SUM(C2:C5), evaluates to 1361.2
workbook.setForceFormulaRecalculation(true);The formula uses Excel row numbers, which start at 1, so the zero-based index 5 of the totals row is also the Excel number of the last expense row. The call setForceFormulaRecalculation(true) tells Excel to recalculate all formulas when it opens the file, because POI does not update the cached result of a formula cell.
7. Apache POI Append Rows FAQs
7.1. How Do I Append Thousands of Rows Without Running Out of Memory?
We wrap the loaded workbook in an SXSSFWorkbook, which keeps only a window of new rows in memory and writes older rows to a temp file. The existing rows still load fully into memory, so SXSSFWorkbook helps when the new data is large, not when the existing file is large.
try (InputStream in = Files.newInputStream(source);
XSSFWorkbook template = new XSSFWorkbook(in);
SXSSFWorkbook workbook = new SXSSFWorkbook(template, 100)) {
int nextRow = template.getSheetAt(0).getLastRowNum() + 1; // 3 (ask the XSSF sheet)
Sheet sheet = workbook.getSheetAt(0);
for (int i = 0; i < 100_000; i++) {
Row row = sheet.createRow(nextRow + i);
row.createCell(0).setCellValue(LocalDate.of(2026, 10, 5));
row.createCell(1).setCellValue("Coffee");
row.createCell(2).setCellValue(3.50);
}
try (OutputStream out = Files.newOutputStream(target)) {
workbook.write(out); // last row index 100002
}
}Notice that we read the last row from the XSSFWorkbook sheet. The SXSSFSheet knows only the rows it created, so its getLastRowNum() returns -1, and createRow(0) throws IllegalArgumentException: Attempting to write a row[0] in the range [0,4] that is already written to disk.
7.2. Why Does getLastRowNum() Return 0 for a Sheet With One Row?
The method returns an index, not a count. A sheet with one row has that row at index 0, so getLastRowNum() returns 0, and an empty sheet returns -1. In both cases, getLastRowNum() + 1 is the index of the next free row.
7.3. Can I Append Rows to an .xls File?
Yes. WorkbookFactory.create() returns an HSSFWorkbook for an .xls file, and the Workbook, Sheet, Row and Cell interfaces work the same way, so appendRows() from section 4 handles both formats without changes. An .xls sheet holds at most 65,536 rows, whereas an .xlsx sheet holds 1,048,576 rows.
7.4. How Do I Append Rows to a Sheet by Name?
We call workbook.getSheet(name), which returns null when the workbook has no sheet with that name. So we check for null and create the sheet, for example when the first expense of a new month arrives.
Sheet november = workbook.getSheet("November"); // null, no such sheet
if (november == null) {
november = workbook.createSheet("November");
}To read the appended data back into an app, for example to load it into a database, see importing Excel data with Spring Boot.
8. Conclusion
Apache POI appends rows by loading the complete workbook, creating rows after the last data row and writing the complete workbook again. The next free index is getLastRowNum() + 1, and for files edited by people, a helper that skips formatted blank rows gives the correct index.
New rows look like the old ones when we reuse the cell styles of the last data row, and we create each style only once. For a sheet with a totals row, shiftRows() makes room for the new rows, but we rewrite the SUM formula ourselves.
Most data loss comes from the save step. We read the file through an InputStream, write the workbook to a temp file and replace the original with Files.move(), so a failed write never leaves an empty spreadsheet behind.
9. References
- Apache POI Spreadsheet (HSSF and XSSF)
- Sheet JavaDoc (Apache POI)
- WorkbookFactory JavaDoc (Apache POI)
- SXSSFWorkbook JavaDoc (Apache POI)
- Busy Developers’ Guide to HSSF and XSSF Features
Happy Learning !!
