Update / Modify XLS File in Java POI Example

Update Existing Excel Workbook in Java

If you have a requirement to programatically update an Excel spreadsheet in Java, then this post will help you. Here we throw a basic Java example that uses Apache POI library to update a XLS workbook. Apache POI is a very powerful language, that can be handy in manipulating Excel documents.This tutorial focusses on XLS documents (Office 97 - 2003). You can refer to the related posts below that will discusses updating XLSX documents also.

Input SpreadSheet

Let us frame a simple example so that we can discuss this better. Here is a sample spreadsheet:

Update Existing XLS Document in Java Using Apache POI

A simple example would be to update marks by adding 5 for Row #2 alone. (we will see more complex cases later). Step by Step guide to updating excel document using Java POI, is given below:

1: Read Spreadsheet into Java

The first step would be to read the XLS document as a FileInputStream into HSSFWorkbook object, and get the worksheet we want to update. A code fragement that explains how to do this, is provided below:

                //Read the spreadsheet that needs to be updated
                FileInputStream input_document = new FileInputStream(new File("C:\\UpdateXLS.xls"));
                //Access the workbook
                HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document); 
                //Access the worksheet, so that we can update / modify it.
                HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0); 

2.Access Cell to modify the value

In this step, we use the Cell object, to access the second row and second column, value, in order to modify / update it. Once we get the scope of the value into a Cell object, we can change it to a different value. As an example;

                // declare a Cell object
                Cell cell = null; 
                // Access the cell first to update the value
                cell = my_worksheet.getRow(1).getCell(1);
                // Get current value and then add 5 to it 
                cell.setCellValue(cell.getNumericCellValue() + 5);

Note that cells start with (0,0)..unlike in Microsoft Excel, so be careful when you refer to a cell.

3. Close Excel Document after update

ok, what we have done so far is to read an existing spread sheet as InputStream and modified it. We now have the logical Excel document in memory, that we can write to the output stream. For this to happen, we will have close the InputStream first.

                //Close the InputStream
                input_document.close();

4. Open XLS in OutputStream to Write Updates

We will now open the same spreadsheet in FileOutputStream form, and use the write method in HSSFWorkbook class to commit the changes we have done. This is explained through the code fragment below:

                //Open FileOutputStream to write updates
                FileOutputStream output_file =new FileOutputStream(new File("C:\\UpdateXLS.xls"));
                //write changes
                my_xls_workbook.write(output_file);
                //close the stream
                output_file.close();

Note that the output stream needs to be closed once the updates are done.

Complete Java Program Example

The complete Java program that updates an XLS document using Apache POI is provided below:

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import java.util.Iterator;
public class updatexls {  
        public static void main(String[] args) throws Exception{
                //Read the spreadsheet that needs to be updated
                FileInputStream input_document = new FileInputStream(new File("C:\\UpdateXLS.xls"));
                //Access the workbook
                HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document); 
                //Access the worksheet, so that we can update / modify it.
                HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
                // declare a Cell object
                Cell cell = null; 
                // Access the cell first to update the value
                cell = my_worksheet.getRow(1).getCell(1);
                // Get current value and then add 5 to it 
                cell.setCellValue(cell.getNumericCellValue() + 5);
                //Close the InputStream
                input_document.close();
                //Open FileOutputStream to write updates
                FileOutputStream output_file =new FileOutputStream(new File("C:\\UpdateXLS.xls"));
                //write changes
                my_xls_workbook.write(output_file);
                //close the stream
                output_file.close();            
        }
}

Updated XLS Document - Example

This program updates the document as per our expectations. A sample screen of the output is provided below:

Updated XLS Document in POI  / Java Output

You can define a simple For loop if you want to update all the cells in the spreadsheet. In the next tutorial, we will discuss how to update a XLSX spreadsheet using Apache POI. Note that you need poi-3.8.jar file to run this example.

This Tutorial: How to update an XLS Document in Java using Apache POI?

Keywords: Apache POI, Update Excel Document Example, Modify XLS in Java, Change Cell data in Excel using Java