TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

Apache POI

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

}

 


Output of the program:

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

File After running the program:

Update Excel File

 

—

Apache POI Tutorials:

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

More Information on Apache POI API:

https://poi.apache.org/

Related Posts

ApachePoi_ReadExcelRow

Apache POI /

Read Excel File using Apache POI

Configure Build Path

Apache POI /

Add Apache POI To Java Project

Apache POI Download

Apache POI /

Download Apache POI

Excel_Sheet_Movies_Names

Apache POI /

Getting number of Rows count from an Excel sheet

MS Excel Java Program

Apache POI /

Write to an MS Excel File using Apache POI

‹ Getting number of Rows count from an Excel sheet› Download Apache POI

Recent Posts

  • MS Access Data Types
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows
  • Activate CLion IDE
  • CLion IDE for C/C++ Development

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com

Go to mobile version