I was implementing Data Driven Framework, i am getting an Exception in thread “main” java.lang.NullPointerException at utility.ExcelUtils.setCellData(ExcelUtils.java:71) at ET8.Test.main(Test.java:11). Could any one help me on this please

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;
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{

Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);

String CellData = Cell.getStringCellValue();

return CellData;

}catch (Exception e){

return””;

}

}

//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.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(Constant.Path_TestData + Constant.File_TestData);

ExcelWBook.write(fileOut);

fileOut.flush();

fileOut.close();

}catch(Exception e){

throw (e);

}

}

}
————————————————————————————————–
package ET8;

import utility.Constant;
import utility.ExcelUtils;

public class Test {

public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
ExcelUtils.setExcelFile(Constant.Path_TestData + Constant.File_TestData,”Sheet1″);
ExcelUtils.setCellData(“Pass”, 1, 3);
}

}

Add Comment
1 Answer(s)

Use these 3 classes , Change the path and filename in the Constant class and you are set.

/*————————————————————-*/
/* Class 1 – Excel file Initialization, Fetching Data and Setting Data is done Here */

package apache.poi.datadriven.anonymous;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import apache.poi.datadriven.anonymous.Constant;
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;

public class forumsqaJuly24 {
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{

Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);

String CellData = Cell.getStringCellValue();

return CellData;

}catch (Exception e){

return””;

}

}

//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.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(Constant.Path_TestData + Constant.File_TestData);

ExcelWBook.write(fileOut);

fileOut.flush();

fileOut.close();

}catch(Exception e){

throw (e);

}

}

}

/*————————————————————-*/

/*Main Method to run */

package apache.poi.datadriven.anonymous;

import apache.poi.datadriven.anonymous.Constant;
import apache.poi.datadriven.anonymous.forumsqaJuly24;

public class MainJuly24 {

public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
forumsqaJuly24.setExcelFile(Constant.Path_TestData + Constant.File_TestData,”Sheet1″);
forumsqaJuly24.setCellData(“Pass”, 1, 3);
}

}

/*————————————————————-*/
/*Constant initialization*/

package apache.poi.datadriven.anonymous;

public class Constant {

public static final String URL = “http://www.store.demoqa.com”;

public static final String Username = “testuser_1″;

public static final String Password = “Test@123″;

public static final String Path_TestData = “D://”;

public static final String File_TestData = “Logins.xlsx”;
}

/*————————————————————-*/

suhabe_shaikh Train Answered on July 24, 2015.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.