Selenium: Excel Read and Write using java Apache POI library


We will see the use of apache POI library on excel read and write operations.

Download basic required apache POI jar files from here.



Add the below jar files along with selenium related to Eclipse: 
poi-x.xx-*.jar (x-xx* for latest releases)
poi-ooxml-x.xx*.jar
xmlbeans-x.x.x.jar
poi-ooxml-schemas-x.xx-*.jar

Unlike jexcel api, apacke POI handles both the types of excel file, .xls and .xlsx file as well.

For handling .xls files, we use HSSF (Horrible SpreadSheet Format) related POI classes.
For handling .xlsx files, we use XSSF (XML SpreadSheet Format) related POI classes.

If you are using XSSF classes, then you can deal with both the excel format files, so no worries!

We will be only discussing the operations that are needed for our selenium framework and the operations are as listed below.
  • Create an excel file if it's not existed.
  • Enter (update) data in a cell.
  • Read data from particular cell.
  • Highlight particular cell.


1) Create an excel file and sheet if it's not existed

Here in this below code, I am importing all the required libraries for the rest of the code snippets, so it will be easy for reference.

package pkg.poi;

import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class excelutil {
 
 private static FileInputStream fis;
 private static FileOutputStream fileOut;
 private static XSSFWorkbook wb;
 private static XSSFSheet sh;
 private static XSSFCell cell;
        private static XSSFRow row;
        private static XSSFCellStyle cellstyle;
        private static XSSFColor mycolor;
    
 public static void setExcelFile(String ExcelPath,String SheetName) throws Exception
 {  
    try{
       File f = new File(ExcelPath);
       if(!f.exists())
       {
          f.createNewFile();
          System.out.println("File doesn't exist, so created!");
        }  
        fis=new FileInputStream(ExcelPath);
        wb=new XSSFWorkbook(fis);
        sh = wb.getSheet(SheetName);
        //sh = wb.getSheetAt(0); //0 - index of 1st sheet
        if (sh == null)
        {
            sh = wb.createSheet(SheetName);
        }  
     }catch (Exception e){System.out.println(e.getMessage());}
 }

setExcelFile method takes 2 argument, path of excel file and sheet name, that we will be working on, we are checking if the excel file/sheet exist, if not, create it.

2) Enter(update) data in a cell

setCellData method takes 3 arguments, string text we want to enter into cell with rownum and colnum, this method enters data into an empty cell and also updates if data already existed.

public static void setCellData(String text, int rownum, int colnum) throws Exception
{
 try{   
    row  = sh.getRow(rownum);
    if(row ==null)
    {
       row = sh.createRow(rownum);
    }
    cell = row.getCell(colnum);
   if (cell != null) 
    {
        cell.setCellValue(text);
    } 
    else 
    {
         cell = row.createCell(colnum);
         cell.setCellValue(text);  
    }
    fileOut = new FileOutputStream(ExcelPath);
    wb.write(fileOut);
    fileOut.flush();
    fileOut.close();
 }catch(Exception e){throw (e);} }

3) Read data from particular cell:

getCellData method takes the rownum and colnum to fetch the data from excel sheet, 
excel path and sheet name are already taken care by the setExcelFile method.
There are different ways to fetch the data from cell, like we need to fetch the cell_type 
1st and then fetch the data accordingly.


public static String getCellData(int rownum, int colnum) throws Exception
 {
  try{
     cell = sh.getRow(rownum).getCell(colnum);
     String CellData = null;         
     switch (cell.getCellType()){
     case Cell.CELL_TYPE_STRING:
          CellData = cell.getStringCellValue();
          break;
     case Cell.CELL_TYPE_NUMERIC:
          if (DateUtil.isCellDateFormatted(cell))
          {
             CellData = cell.getDateCellValue().toString();
          }
          else
          {  
             CellData = Double.toString(cell.getNumericCellValue());
             if (CellData.contains(".0"))//removing the extra .0
              {
               CellData = CellData.substring(0, CellData.length()-2);
              }
           }
           break;
     case Cell.CELL_TYPE_BLANK:
           CellData = "";
           break;
     case Cell.CELL_TYPE_BOOLEAN:
           CellData = Boolean.toString(cell.getBooleanCellValue());
           break;
     }      
        return CellData;
        }catch (Exception e){return"";}
 }

4) Highlight particular cell.

highlightCell method takes 3 argument, which color to highlight the cell(row and col).



public static void highlightCell(String color, int rownum, int colnum)throws Exception
 {
  try{
  cell = sh.getRow(rownum).getCell(colnum,Row.RETURN_BLANK_AS_NULL);
   }catch(Exception e){System.out.println("cell is null");}
  if (cell == null) 
    {
   cell = row.createCell(colnum);
    }
  cellstyle = wb.createCellStyle();
  color = color.toUpperCase();
  
  switch(color)
  {
  case "GREEN":
   mycolor = new XSSFColor(Color.GREEN);
   break;
  case "RED":
   mycolor = new XSSFColor(Color.RED);
  break;
  default:
   mycolor = new XSSFColor(Color.BLACK);
  break;
  }
  cellstyle.setFillForegroundColor(mycolor); 
  cellstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
  cell.setCellStyle(cellstyle);
  FileOutputStream fileOut = new FileOutputStream(ExcelPath);
                wb.write(fileOut);
                fileOut.flush();
  fileOut.close();
  System.out.print("color done");  
 }


You can call the above 4 methods based on your requirement from the main() method of the same class or can import the class into another class(import pkg.poi.excelutil) and call these methods.

Let me know of any other requirements that needs to be added in this post, so everyone can leverage the code, Thanks!

25 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hey, Your post is very informative and helpful for us.
    In fact i am looking this type of article from some days.
    Thanks a lot to share this informative article.
    Excel Training in Hyderabad

    ReplyDelete
  3. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.As always, we appreciate your confidence and trust in us
    java training in omr

    java training in annanagar | java training in chennai

    java training in marathahalli | java training in btm layout

    java training in rajaji nagar | java training in jayanagar

    ReplyDelete
  4. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
    python training in omr

    python training in annanagar | python training in chennai

    python training in marathahalli | python training in btm layout

    python training in rajaji nagar | python training in jayanagar

    ReplyDelete
  5. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.

    Best Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies

    Selenium Training in Bangalore | Best Selenium Training in Bangalore

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    ReplyDelete
  6. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
    devops online training

    aws online training

    data science with python online training

    data science online training

    rpa online training

    ReplyDelete
  7. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Microsoft Azure online training
    Selenium online training
    Java online training
    uipath online training
    Python online training


    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. If you want a number of users to add information to your spreadsheet, you don't want them to leave out any relevant fields of information. custom excel spreadsheets

    ReplyDelete
  10. That was great to read. Keep it up with your writing skills.
    Java Course in Pune

    ReplyDelete