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

    • ChatGPT Plans Free and PlusChatGPT Subscription Plans
    • Stellar Converter for Database ToolStellar Converter for Database
    • Stellar MySQL Log AnalyzerStellar Log Analyzer for MySQL
    • Stellar Repair for MySQLStellar Repair for MySQL
    • ChatGPT IntroductionChatGPT Capabilities
    • How to secure your SQL Database: Tips and Tricks
    • ChatGPT4 Conversational AI FeaturesChatGPT4 Conversational AI Features
    • Trends in Software EngineeringShaping the Future of Development: Exploring Key Trends in Software Engineering
    • Java PerformanceImproving Java Performance with Multithreading
    • QDrant Vector DatabaseOpen-source Vector Databases
    • Difference between PHP and JavaScript?
    • Bing AI Browser Web ContentBing Conversation Styles
    • ChatGPT PreviewChatGPT Introduction
    • Open Source AI Frameworks TensorFlowOpen Source AI Frameworks
    • Artificial Intelligence Tools

    Back to Top

    Links

    • Contact
    • Privacy Policy
    • Cookie Policy

    www.TestingDocs.com