FileInputStream, FileOutputStream, Selenium, XSSF, XSSFCell, XSSFCellStyle, XSSFColor, XSSFRow, XSSFSheet, XSSFWorkbook

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.

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.

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.

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"";
}
    }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s