Header Ads

HOW TO WRITE DATA TO EXCEL FILE (.XLSX) USING APACHE POI?

We can also write data to excel file using Apache POI library.
Here is the very basic program to explain How to write data to excel file using Apache POI library.




Create a testdata.xlsx file in the C drive with following details:

Search Text
hello selenium
abhishek yadav qa

Use below code to write data to excel file:

Java Source code:

package com.helloselenium.selenium.test;

import java.io.*;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class writeDataToExcelFile{

 public static void main(String[] args) { 

 try {
   
  FileInputStream file = new FileInputStream(new File("C:\\testdata.xlsx")); 
  XSSFWorkbook workbook = new XSSFWorkbook(file);

  XSSFSheet sheet = workbook.getSheetAt(0);
  
  Cell searchText3 = sheet.getRow(0).getCell(0);
  searchText3.setCellValue("Test Search Keyword");

  file.close();

  FileOutputStream outFile =new FileOutputStream(new File("C:\\testdata-result.xlsx"));
  workbook.write(outFile);
  outFile.close();

 } catch (FileNotFoundException fnfe) {
  fnfe.printStackTrace();
 } catch (IOException ioe) {
  ioe.printStackTrace();
 }
 }
}

Output:
Test Search Keyword
hello selenium
abhishek yadav qa

Detailed explanation for the above program is as follows:




Following code is the required packages for JAVA IO to make integration with excel file.
import java.io.*;
Following code is the required packages for Apache POI library.
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
Following code is to locate the path of excel file.
FileInputStream file = new FileInputStream(new File("C:\\testdata.xlsx"));
Following code is to initialize the excel file as a workbook.
XSSFWorkbook workbook = new XSSFWorkbook(file);
Following code is to initialize the excel sheet of the workbook. Here 0 (zero) refers to the first sheet of the workbook.
XSSFSheet sheet = workbook.getSheetAt(0);
Following code is to define the cell location where we have to update the data.
Cell searchText3 = sheet.getRow(0).getCell(0);
Following code is to update the value into defined cell.
searchText3.setCellValue("test search keyword");




Use the following code is to close the excel file.
file.close();
Following code is to define the path of output excel file.
FileOutputStream outFile =new FileOutputStream(new File("C:\\testdata-result.xlsx"));
Following code is to update the output file on defined location.
workbook.write(outFile);
Use the following code is to close the output excel file.
outFile.close();



2 comments:

  1. am getting null pointer exception at Cell searchText3 = sheet.getRow(0).getCell(0);

    ReplyDelete
    Replies
    1. @Selenium2015, Are you still getting this exception?

      Delete


What would you like to add in my list? I look forward to reading your comments below.



Powered by Blogger.