How to write data into Excel in Selenium

Write data to any external source in selenium is very important when we are working with framework.
Data can be write in any format like excel, csv, text etc.

There are many methods for write data into excel files. easiest way to read data from excel file is using apache poi.

first of all you have to import below packages for apache poi.

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Now you need to create separate function for write data into excel. I have created below two functions for writing data into excel file.
First function is use for write whole table (two dimensional array) into excel.
Second function is use for write single row into excel.

Both functions can be used for write data into excel at the end of file. If file is not there at specific path then code will create first file the write data into it.

First function : 

public void mWriteDataInExcel(String vXlPath, String vSheetName, String[][] vData)
 {    
  try
  {
   File XLfile = new File(vXlPath);
   
   if(!XLfile.exists())
   {
    XLfile.createNewFile();
    FileOutputStream vOutputStream = new FileOutputStream(XLfile);
    XSSFWorkbook vWb = new XSSFWorkbook();
    XSSFSheet vSheet = vWb.createSheet(vSheetName);   
    vWb.write(vOutputStream);
    vOutputStream.close();
   }
   
   FileInputStream vInputStream = new FileInputStream(XLfile);   
   XSSFWorkbook vWorkbook = new XSSFWorkbook(vInputStream);
      
   XSSFSheet vSheet = vWorkbook.getSheetAt(0);
   Boolean bSheet = false;
      
   if(vWorkbook.getNumberOfSheets() != 0)
   {
    for(int i=0; i<vWorkbook.getNumberOfSheets(); i++)
    {
     if(vWorkbook.getSheetName(i).equals(vSheetName))
     {
      System.out.println(vSheetName + " is exists. No need to create sheet.");
      bSheet = true;      
     }     
    }
    
    if(bSheet)
    {
     vSheet = vWorkbook.getSheet(vSheetName);
    }
    else
    {
     vSheet = vWorkbook.createSheet(vSheetName);
    }
   }
   else
   {
    vSheet = vWorkbook.createSheet(vSheetName);
   }
      
   // Get the current count of rows in excel file   
   int vRowCount = 0;      
   
   if(vSheet.getLastRowNum()==0)
   {
    vRowCount = vSheet.getLastRowNum();
    System.out.println("Before add Row Number : "+vSheet.getLastRowNum());
   }
   else
   {
    vRowCount = vSheet.getLastRowNum()+1;
    System.out.println("After add Row Count : "+vRowCount);
   }
   
   for(int i=0;i<vData.length;i++)
   {       
    // Create a new row and append it at last of sheet
    Row vNewRow = vSheet.createRow(vRowCount++);
    for(int j=0;j<vData[0].length;j++)
    {
     Cell vCell2 = vNewRow.createCell(j);
     vCell2.setCellValue(vData[i][j]);
     System.out.println(vCell2);
    }    
   }   
   System.out.println("After add Row Count : "+(vSheet.getLastRowNum() + 1));
   
   vInputStream.close();
   
   FileOutputStream vOutputStream = new FileOutputStream(XLfile);   
   vWorkbook.write(vOutputStream);   
   vOutputStream.close();     
   
  }
  catch(Exception E)
  {
   System.out.println("Error in write data into excel file.");
   System.out.println(E.getMessage());
  }
     
 }

Second Function :

public void mWriteDataInExcel(String vXlPath, String vSheetName, String[] vData)
 {  
  try
  {
   File XLfile = new File(vXlPath);
   
   if(!XLfile.exists())
   {
    XLfile.createNewFile();
    FileOutputStream vOutputStream = new FileOutputStream(XLfile);
    XSSFWorkbook vWb = new XSSFWorkbook();
    XSSFSheet vSheet = vWb.createSheet(vSheetName);   
    vWb.write(vOutputStream);
    vOutputStream.close();
   }
   
   FileInputStream vInputStream = new FileInputStream(XLfile);   
   XSSFWorkbook vWorkbook = new XSSFWorkbook(vInputStream);
      
   XSSFSheet vSheet = vWorkbook.getSheetAt(0);
   Boolean bSheet = false;
      
   if(vWorkbook.getNumberOfSheets() != 0)
   {
    for(int i=0; i<vWorkbook.getNumberOfSheets(); i++)
    {
     if(vWorkbook.getSheetName(i).equals(vSheetName))
     {
      System.out.println(vSheetName + " is exists. No need to create sheet.");
      bSheet = true;      
     }     
    }
    
    if(bSheet)
    {
     vSheet = vWorkbook.getSheet(vSheetName);
    }
    else
    {
     System.out.println(vSheetName + " is not exists. So need to first create sheet.");
     vSheet = vWorkbook.createSheet(vSheetName);
    }
   }
   else
   {
    System.out.println(vSheetName + " is not exists. So need to first create sheet.");
    vSheet = vWorkbook.createSheet(vSheetName);
   }
   
   // Get the current count of rows in excel file   
   int vRowCount = 0;   
   
   if(vSheet.getLastRowNum()==0)
   {
    vRowCount = vSheet.getLastRowNum();
    System.out.println("Before add Row Count : "+vSheet.getLastRowNum());
   }
   else
   {
    vRowCount = vSheet.getLastRowNum()+1;
    System.out.println("Before add Row Count : "+vRowCount);
   }   
   
   Row vNewRow = vSheet.createRow(vRowCount);
   
   for(int i=0;i<vData.length;i++)
   {    
    Cell vCell2 = vNewRow.createCell(i);
    vCell2.setCellValue(vData[i]);
    System.out.println(vCell2);    
   }   
   System.out.println("After add Row Count : "+(vSheet.getLastRowNum() + 1));
   
   vInputStream.close();
   
   FileOutputStream vOutputStream = new FileOutputStream(XLfile);   
   vWorkbook.write(vOutputStream);   
   vOutputStream.close();     
   
  }
  catch(Exception E)
  {
   System.out.println("Error in write data into excel file.");
   System.out.println(E.getMessage());
  }
 }

Please add comment if you have any question regarding this post.