Overview

In this post, we will go through how to update/ modify an existing excel file and modify its cell values. Let’s take the same file TDocs.xlsx as an example. We will modify the Movie name “Harry Potter” to “Oz the Wizard”.

 

 

Sample program to update/modify an existing excel cell below:

 

package com.testingdocs.excel.examples.apachepoi;

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: