Update Excel File using Apache POI

Overview

In this post, we will go through how to update excel file and modify its cell values.

Steps to download and configure Apache POI in the Java project:

https://www.testingdocs.com/download-apache-poi/

Add Jar file to the project:

https://www.testingdocs.com/add-apache-poi-to-java-project/

Let’s take the same file TDocs.xlsx as an example. The file consists of sample table with movie names in two columns.

Program to Update Excel Cell

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteXL {
    String filename = null;
    File file = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null ;
    FileOutputStream fos = null ;
    
    public WriteXL(String filename)
    {
        this.filename=filename;
    }
    
    public void modifyCell(int rowNumber,int columnNumber,String sheetName, String valueToWrite) throws IOException{
        
    try {
            file = new File(filename);
            fis = new FileInputStream(file);
            workbook = new XSSFWorkbook(fis);
            sheet = workbook.getSheet(sheetName);
            Cell cell = sheet.getRow(rowNumber).getCell(columnNumber);
            XSSFRow row  = sheet.getRow(rowNumber);
            cell = row.getCell(columnNumber, Row.RETURN_BLANK_AS_NULL);

            if (cell != null) {
                cell.setCellValue(valueToWrite);
            } else {
                throw new Exception("Cell Exception.");
            }

            fos = new FileOutputStream(filename);
            workbook.write(fos);
            
        }catch (Exception e) {
            System.out.println("ERROR : Not able to update the cell");
            e.printStackTrace();
        }
        finally{
        if(fis!= null || fos != null)
        {
        fis.close();
        fos.flush();
        fos.close();
        }
        }
    }
    
    public static void main(String... args) throws IOException
    {
        WriteXL writeExcel = new WriteXL("TDocs.xlsx");
        writeExcel.modifyCell(2, 0, "MovieList", "Oz");
        writeExcel.modifyCell(2, 1, "MovieList", "The Wizard");
        
        ArrayList<String> consume = new ArrayList<String>();
        ReadXL readExcel = new ReadXL("TDocs.xlsx");
        consume = readExcel.getMyRow(2, 1, "MovieList");
        System.out.println("**********OutPut************");
        for(String s : consume)
        {
        System.out.println(s);
        }
        
    }

}