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);
        }
        
    }

}

 

Output of the program:

**********OutPut************
Oz
The Wizard

File After running the program:

 

—

Apache POI Tutorials:

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

More Information on Apache POI API:

https://poi.apache.org/