Update / Modify XLSX Files in Java POI Example Program

How to modify XLSX documents using POI?

In the last post, we discussed how to update an Excel document (XLS format) using Apache POI in Java. In this post, we will discuss how to update Excel documents (2007, 2012, XLSX format) using POI. You will have to use different class files to modify XLSX files, and hence I thought I would provide a separate example for this. You will need POI v3.8 (though you can use versions below this) for the example to work.

Let us create a sample XLSX document..

For us to work on a example, we need an input file. So, let us create a simple Excel workbook. (name it simple.xlsx ). The rows you need to add to the workbook is provided below:

Sample XLSX Document - Update XLSX Example Tutorial

The row #3, I would like to subtract 5 from 44 and make it 39. We will see how to do this using POI. Power up your favourite Java code editor, and we are ready to go..Here is a step by step guide.

Open the XLSX Document in InputStream

In this step, we create a FileInputStream and open the document for editing. We cannot edit the file directly, as how you do in MS Office. You need logical POI objects to read the document. To help here, we will bring XSSFWorkbook object, that will hold a copy of the workbook. We will also read the worksheet we want to update into XSSFSheet object. You can think XSSFWorkbook as a container, that will hold the updated XLSX document, which we will write later to a workbook. A simple code fragment is shown below:

                //Read Excel document first
                FileInputStream input_document = new FileInputStream(new File("C:\\simple.xlsx"));
                // convert it into a POI object
                XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
                // Read excel sheet that needs to be updated
                XSSFSheet my_worksheet = my_xlsx_workbook.getSheetAt(0); 

This is the only change you need to make to update the document. Rest is simple.

Fetch Cell to Update - Cell Object

We will use Cell object, and to access the Cell. In POI, the starting point is (0,0).So, the data we need to update is in Row # 2 and Column Number #1. Here is how to access the Cell and update it to the value we want..

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

Write updates to File

We can now close the InputStream..Wait!..where are we writing the update, if we close the InputStream? It is important to note that updating documents works differently in POI. You read the document first, and take the workbook as an object. You then manipulate the object and modify / update it to support your needs. You have to close the document, and then "write" the update back to the same document. That is the reason, why you need to close the InputStream. Once this is done, you can create a new OutputStream and write the updates to it. The code fragment is given below:

                //important to close InputStream
                input_document.close();
                //Open FileOutputStream to write updates
                FileOutputStream output_file =new FileOutputStream(new File("C:\\simple.xlsx"));
                //write changes
                my_xlsx_workbook.write(output_file);
                //close the stream
                output_file.close(); 

Complete Code Example

Putting it all together, complete Java program that uses POI to update XLSX spreadsheet is provided below:

import java.io.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; //New imports to read XLSX format
import org.apache.poi.xssf.usermodel.XSSFSheet; //New imports to read XLSX format
import org.apache.poi.ss.usermodel.*;
import java.util.Iterator;
public class Updatexlsx {  
        public static void main(String[] args) throws Exception{
                //Read Excel document first
                FileInputStream input_document = new FileInputStream(new File("C:\\simple.xlsx"));
                // convert it into a POI object
                XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
                // Read excel sheet that needs to be updated
                XSSFSheet my_worksheet = my_xlsx_workbook.getSheetAt(0);
                // declare a Cell object
                Cell cell = null; 
                // Access the cell first to update the value
                cell = my_worksheet.getRow(2).getCell(1);
                // Get current value and reduce 5 from it
                cell.setCellValue(cell.getNumericCellValue() - 5);
                //important to close InputStream
                input_document.close();
                //Open FileOutputStream to write updates
                FileOutputStream output_file =new FileOutputStream(new File("C:\\simple.xlsx"));
                //write changes
                my_xlsx_workbook.write(output_file);
                //close the stream
                output_file.close(); 
                
        }
}

The output of this program is provided below..And our row is updated..exactly how we wanted to change.

Update XLSX document using Java / POI - Output Example

Here are some exceptions you may get:

Exception in thread "main" java.io.FileNotFoundException: C:\simple.xlsx (The process cannot access the file because it is being used by another process)
        at java.io.FileOutputStream.open(Native Method)
        at java.io.FileOutputStream.<init>(Unknown Source)
        at java.io.FileOutputStream.<init>(Unknown Source)
        at Updatexlsx.main(Updatexlsx.java:23)

This means your document is open in a different editor. You need to close that, so that POI can update it. As a last note ,you need different JAR files to compile and run this example. A sample compilation and run example is given below:

javac -classpath .;poi-3.8.jar;poi-ooxml-3.8.jar;poi-ooxml-schemas-3.8.jar Updatexlsx.java

java -classpath .;poi-3.8.jar;poi-ooxml-3.8.jar;poi-ooxml-schemas-3.8.jar;dom4j-1.6.1.jar;xmlbeans-2.3.0.jar;poi-excelant-3.8.jar Updatexlsx

Try updating your XLSX document based on these code. If you are stuck, show us where and we will solve it for you.