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

  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Print Triangle Pattern Flowchart
  • RAPTOR Editions
  • Flowgorithm Conditional Breakpoint Statement
  • Flowgorithm Read Numbers from File Example
  • Search Text File Flowchart Example
  • Flowgorithm Turtle Graphics Symbols
  • Draw Circle using Flowgorithm Turtle

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com

Go to mobile version