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

5 comments:

  1. thanks very explanatory. thanks for sharing
    lets say if in column 1 I have CustName and in next column the details about him. So if we have to locate those rows/cell how we can do that ? any suggestion

    ReplyDelete
  2. Hi Team, I need help,
    I have an excel sheet which is having lots of columns ,I want to add filter against two column. suppose
    column name "COLLAGE" having values A-clg, B-clg, c-clg, D-clg, E-clg etc
    we have another column ie. "UNIVERCITY" having values Pune, Mumbai,,Nagpur,Nashik,Aurangbad etc

    Now I want to automatically apply filter, If "COLLAGE" column having values A-clg, B-clg, c-clg, D-clg then column "UNIVERCITY" values will be "pune" if others values are present.(replace all with Pune againest "A-clg, B-clg, c-clg, D-clg" this logic will Not for "E-clg")

    Thanks in advance

    @for more - you can rich out to me.9028989533

    ReplyDelete
  3. Hi Team, I need help,
    I have an excel sheet which is having lots of columns ,I want to add filter against two column. suppose
    column name "COLLAGE" having values A-clg, B-clg, c-clg, D-clg, E-clg etc
    we have another column ie. "UNIVERCITY" having values Pune, Mumbai,,Nagpur,Nashik,Aurangbad etc

    Now I want to automatically apply filter, If "COLLAGE" column having values A-clg, B-clg, c-clg, D-clg then column "UNIVERCITY" values will be "pune" if others values are present.(replace all with Pune againest "A-clg, B-clg, c-clg, D-clg" this logic will Not for "E-clg")

    Thanks in advance

    @for more - you can rich out to me.9028989533

    ReplyDelete