0

Here is my excel utils class:

package utility;
import java.io.FileInputStream;
import java.io.FileOutputStream;
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;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.CellType;
public class ExcelUtils {
    private static XSSFSheet ExcelWSheet;
    private static XSSFWorkbook ExcelWBook;
    private static XSSFCell cell;
    private static XSSFRow row;
    //This method is to set the File path and to open the Excel 
    file, Pass Excel Path and Sheetname as Arguments to this method
    public static void setExcelFile(String Path, String SheetName) throws Exception {
        try {
            // Open the Excel file
            FileInputStream ExcelFile = new FileInputStream(Path);
            // Access the required test data sheet
            ExcelWBook = new XSSFWorkbook(ExcelFile);
            ExcelWSheet = ExcelWBook.getSheet(SheetName);
        } catch (Exception e) {
            throw (e);
        }
    }
    //This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num
    public static String getCellData(int RowNum, int ColNum) throws Exception {
        try {
            String cellData = "";
            cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
            cell.setCellType(CellType.STRING);
            cellData = cell.getStringCellValue();
            return cellData;
        } catch (Exception e) {
            return "undefined";
        }
    }
    //This method is to write in the Excel cell, Row num and Col num are the parameters
    public static void setCellData(String Result, int RowNum, int ColNum) throws Exception {
        try {
            row = ExcelWSheet.getRow(RowNum);
            cell = row.getCell(ColNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
            if (cell == null) {
                cell = row.createCell(ColNum);
                cell.setCellValue(Result);
            } else {
                cell.setCellValue(Result);
            }
            // Constant variables Test Data path and Test Data file name
            FileOutputStream fileOut = new FileOutputStream(Constants.Path_TestData + Constants.File_TestData);
            ExcelWBook.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (Exception e) {
            throw (e);
        }
    }
}

Here is the script where I'm calling the getCellData to get the values from Excel:

String cellData = ExcelUtils.getCellData(1, 1);
System.out.println("CellData :" + cellData);

Here is the excel file format:

TestCaseName | Username | Password 
TC_01        | TestData |          

Output:

   Exception in thread "main" java.lang.NullPointerException
    at utility.ExcelUtils.getCellData(ExcelUtils.java:63)
    at testScripts.Category_creation.main(Category_creation.java:47)

Here is the excel I'am using. Not able to fetch data from excel file. I'm using Page object framework, hence the excel utils file contains only the code and in fetching the data in testScript by passing row and column number.

  • Can you recheck the code String cellData = ExcelUtils.getCellData(1, 1); .Why are you passing the value (1,1) in getCellData? – shanila Aug 22 '18 at 17:10
  • @shanila: I want to fetch the data from 1th row and 1th column of excel sheet. The problem is it's returning blank. But the excel has the data in the specified cell. – Kaustubh Aug 22 '18 at 17:12
  • In your code you have mentioned as (int RowNum, int ColNum). But from where the RowNum and ColNum will be passed? You haven't mentioned the RowNum and ColNum anywhere.So Its returning blank . – shanila Aug 22 '18 at 17:22
  • I'm passing value for RowNum and ColNum from my test script using this code --String cellData = ExcelUtils.getCellData(1, 1) – Kaustubh Aug 22 '18 at 17:25
0

I ran your excel file with the simple changes of your code it worked successfully

I removed cell.setCellType(CellType.STRING); code line

Full excel read code is below

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;


public class ReadExcelConfig {
    XSSFWorkbook wb;
    XSSFSheet sheet1;

    public ReadExcelConfig(String excelpath) {
        try {
            File src = new File(excelpath);
            FileInputStream fis = new FileInputStream(src);
            wb = new XSSFWorkbook(fis);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getData(int sheetNumber, int row, int column) {
        sheet1 = wb.getSheetAt(sheetNumber);
        String data = sheet1.getRow(row).getCell(column).getStringCellValue();
        return data;
    }

    public int getRowCount(int SheetIndex) {
        int row = wb.getSheetAt(SheetIndex).getLastRowNum();
        row = row + 1;
        return row;
    }

}

To call this method

First, I call readExcelConfig() method and pass the excel sheet path

then I call getData() method from my test class by passing sheet number, row and column numbers

ReadExcelConfig excel = new ReadExcelConfig("PATH TO EXCEL");
System.out.println(excel.getData(0,1,1));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.