Working with Microsoft Excel in Selenium

Excel provides huge number of capabilities, we can make multiple use out of it like storing data, maintenance, calculations, retrieval, etc. We can use the same capabilities of Excel in Selenium with the help of programming language like Java.

In this post I will be sharing how we can use excel with the help of a class example which can be directly used in any project as a Utility. I will be using XSSFWorkbook, XSSFSheet, XSSFCell,  XSSFRow, XSSFCellStyle, XSSFColor, FileInputStream, FileOutputStream to achieve this.

Code : Where to start from.

static FileInputStream fis; static FileOutputStream fileOut; static XSSFWorkbook wb; static XSSFSheet sh; static XSSFCell cell; static XSSFRow row; static XSSFCellStyle cellstyle; static XSSFColor mycolor;

We will make two functions in ExcelReader class.

Methods to add:-

setExcelFile method will accept two parameters while calling i.e. ExcelPath and SheetName of type String.

getCellData method will accept two parameters while calling i.e. rowNumber and columnNumber of type integer.

Class : how will it look like?

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());
        }
    }
 

    public static String getCellData(int rowNumber, int columnNumber) throws Exception {
        try
{
            cell = sh.getRow(rowNumber).getCell(columnNumber);
            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"";
}
    }

#FileInputStream #XSSFCell #XSSFColor #FileOutputStream #XSSFWorkbook #XSSFRow #XSSFSheet #XSSFCellStyle