Java Example to Update Existing Excel Files Using Apache POI

This Java Excel tutorial shows you how to update an existing Microsoft Excel file using the Apache POI library. Here are the steps for updating an Excel file:

• Read the Excel file to an InputStreamand get the Workbook from this stream.
• Update new data to an existing Sheet or create a new Sheet.
• Close the InputStream.
• Write the workbook to an OutputStream. This will overwrite the existing file with updated data.

Suppose that we have an Excel 2003 file (JavaBooks.xls) looks like this:

Now, we are going to write Java code to update this Excel file by this manner: append 4 more books to the list.

1. Java Code Example to Update an Excel file

This program reads the above Excel file and updates it by adding 4 more books to the list:

package net.codejava.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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;

/**
 * This program illustrates how to update an existing Microsoft Excel document.
 * Append new rows to an existing sheet.
 * 
 * @author www.codejava.net
 *
 */
public class ExcelFileUpdateExample1 {


	public static void main(String[] args) {
		String excelFilePath = "JavaBooks.xls";
		
		try {
			FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
			Workbook workbook = WorkbookFactory.create(inputStream);

			Sheet sheet = workbook.getSheetAt(0);

			Object[][] bookData = {
					{"The Passionate Programmer", "Chad Fowler", 16},
					{"Software Craftmanship", "Pete McBreen", 26},
					{"The Art of Agile Development", "James Shore", 32},
					{"Continuous Delivery", "Jez Humble", 41},
			};

			int rowCount = sheet.getLastRowNum();

			for (Object[] aBook : bookData) {
				Row row = sheet.createRow(++rowCount);

				int columnCount = 0;
				
				Cell cell = row.createCell(columnCount);
				cell.setCellValue(rowCount);
				
				for (Object field : aBook) {
					cell = row.createCell(++columnCount);
					if (field instanceof String) {
						cell.setCellValue((String) field);
					} else if (field instanceof Integer) {
						cell.setCellValue((Integer) field);
					}
				}

			}

			inputStream.close();

			FileOutputStream outputStream = new FileOutputStream("JavaBooks.xls");
			workbook.write(outputStream);
			workbook.close();
			outputStream.close();
			
		} catch (IOException | EncryptedDocumentException
				| InvalidFormatException ex) {
			ex.printStackTrace();
		}
	}

}

Running this program will update the Excel file looks like this:

NOTE: Pay attention to the row index to make sure you append data, not overwrite. In the above program, the row index is calculated to continue from the last row in the document:

int rowCount = sheet.getLastRowNum();

And the above example program works with both types XLS and XLSX as we use the WorkbookFactory utility class that automatically detects the corresponding Excel file format.

Next examples show you other kinds of update.

2. Update a specific cell in the Excel file

The following code updates price of the book ‘Head First Java’ in the above Excel document. That means we need to update the cell at row 0 and column 3:

Sheet sheet = workbook.getSheetAt(0);
Cell cell2Update = sheet.getRow(1).getCell(3);
cell2Update.setCellValue(49);

3. Rename a sheet in the Excel file

The following code renames the first sheet in the workbook to ‘Programming Books’:

workbook.setSheetName(0, "Programming Books");

4. Create a new sheet in the Excel file

The following program creates a new sheet in the above Excel document:

package net.codejava.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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;

/**
 * This program illustrates how to update an existing Microsoft Excel document.
 * Create a new sheet.
 * 
 * @author www.codejava.net
 *
 */
public class ExcelFileUpdateExample4 {


	public static void main(String[] args) {
		String excelFilePath = "JavaBooks.xls";
		
		try {
			FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
			Workbook workbook = WorkbookFactory.create(inputStream);

	        Sheet newSheet = workbook.createSheet("Comments");
	        Object[][] bookComments = {
	                {"Head First Java", "Funny and Exciting"},
	                {"Effective Java", "Insightful tips and advices"},
	                {"Clean Code", "Write Readable Code"},
	                {"Thinking in Java", "Classic"},
	        };
	 
	        int rowCount = 0;
	         
	        for (Object[] aBook : bookComments) {
	            Row row = newSheet.createRow(++rowCount);
	             
	            int columnCount = 0;
	             
	            for (Object field : aBook) {
	                Cell cell = row.createCell(++columnCount);
	                if (field instanceof String) {
	                    cell.setCellValue((String) field);
	                } else if (field instanceof Integer) {
	                    cell.setCellValue((Integer) field);
	                }
	            }
	             
	        }        

			FileOutputStream outputStream = new FileOutputStream("JavaBooks.xls");
			workbook.write(outputStream);
			workbook.close();
			outputStream.close();
			
		} catch (IOException | EncryptedDocumentException
				| InvalidFormatException ex) {
			ex.printStackTrace();
		}
	}

}

Result:

5. Remove a sheet in the Excel file

The following program removes the 2^nd sheet in the above Excel document:

package net.codejava.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * This program illustrates how to update an existing Microsoft Excel document.
 * Remove an existing sheet.
 * 
 * @author www.codejava.net
 *
 */
public class ExcelFileUpdateExample5 {


	public static void main(String[] args) {
		String excelFilePath = "JavaBooks.xls";
		
		try {
			FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
			Workbook workbook = WorkbookFactory.create(inputStream);

	        workbook.removeSheetAt(1);	        

			FileOutputStream outputStream = new FileOutputStream("JavaBooks.xls");
			workbook.write(outputStream);
			workbook.close();
			outputStream.close();
			
		} catch (IOException | EncryptedDocumentException
				| InvalidFormatException ex) {
			ex.printStackTrace();
		}
	}

}

Maven dependency for Apache POI:

If you are using Maven, add the following XML snippet to project’s pom.xml file (3.15 is the latest version of Apache POI when this article is being written):

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>  

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.15</version>
</dependency>

Otherwise you need to add the poi-VERSION.jar and poi-ooxml-VERSION.jar to the project’s classpath.

You can download the full source code under the Attachments section below.

That's some Java code examples to update an existing Excel file programmatically: add new rows, update a specific cell, rename a sheet, create a new sheet and remove a sheet. If you have time, check this Java developer course to learn all topics in Java Core.

Related Java Excel Tutorials:

• How to Read Excel Files in Java using Apache POI
• How to Write Excel Files in Java using Apache POI
• Java Example to Read Password-protected Excel Files Using Apache POI
• Working with Formula Cells in Excel using Apache POI

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:

JavaUpdateExcelFileExamples.zip

[Java Code Examples to Update Excel Files]

4 kB