How To Set Cell Value In Excel Using Java

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:

                                               FileInputStream input_document                =                new                FileInputStream                (                new                File                (                "                C:\\UpdateXLS.xls                "                )                );                                  HSSFWorkbook my_xls_workbook                =                new                HSSFWorkbook                (input_document);                                   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;

                                               Cell cell                =                null;                                   cell                =                my_worksheet.getRow                (1                ).getCell                (1                );                                  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.

                                               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:

                                               FileOutputStream output_file                =                new                FileOutputStream                (                new                File                (                "                C:\\UpdateXLS.xls                "                )                );                                  my_xls_workbook.write                (output_file);                                  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{                               FileInputStream input_document              =              new              FileInputStream              (              new              File              (              "              C:\\UpdateXLS.xls              "              )              );                                  HSSFWorkbook my_xls_workbook              =              new              HSSFWorkbook              (input_document);                                   HSSFSheet my_worksheet              =              my_xls_workbook.getSheetAt              (              0              );                                  Cell cell              =              null;                                   cell              =              my_worksheet.getRow              (              1              ).getCell              (              1              );                                  cell.setCellValue              (cell.getNumericCellValue              (              )              +              5              );                                  input_document.close              (              );                                  FileOutputStream output_file              =              new              FileOutputStream              (              new              File              (              "              C:\\UpdateXLS.xls              "              )              );                                  my_xls_workbook.write              (output_file);                                  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