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!