Shows "[Utils] [ERROR] [Error] java.lang.NullPointerException" for data driven(excel File) API Automation test(Rest Assured)

Question

It throws a java.lang.NullPointerException, but why it shows that, I could not find. Here is my code.

ExcelUtil

package Utils;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.DataFormatter;
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 ExcelUtil {
 
 public static FileInputStream fi;
 public static FileOutputStream fo;
 public static XSSFWorkbook wb;
 public static XSSFSheet ws;
 public static XSSFRow row;
 public static XSSFCell cell;
 
 public static int getRowCount(String excelPath, String sheetName) throws IOException {
 fi= new FileInputStream(excelPath);
 wb= new XSSFWorkbook(fi);
 ws= wb.getSheet(sheetName);
 int rowcount= ws.getLastRowNum();
 System.out.println(rowcount);
 wb.close();
 fi.close();
 return rowcount;
 }
 
 public static int getCellCount(String excelPath, String sheetName, int rownum) throws IOException {
 fi= new FileInputStream(excelPath);
 wb= new XSSFWorkbook(fi);
 ws= wb.getSheet(sheetName);
 row= ws.getRow(rownum);
 int cellcount= row.getLastCellNum();
 wb.close();
 fi.close();
 return cellcount;
 }
 
 public static String getCellData(String excelPath, String sheetName, int rownum, int colnum) throws IOException {
 fi= new FileInputStream(excelPath);
 wb= new XSSFWorkbook(fi);
 ws= wb.getSheet(sheetName);
 row= ws.getRow(rownum);
 cell= row.getCell(colnum);
 String data;
 try {
 DataFormatter formatter = new DataFormatter();
 String cellData = formatter.formatCellValue(cell);
 return cellData;
 
 } catch (Exception exp) {
 data="";
 System.out.println(exp.getMessage());
 System.out.println(exp.getCause());
 exp.printStackTrace();
 }
 wb.close();
 fi.close();
 return data;
 }
}

DataDrivenPostRequest

package DataDrivenAPICollection;
import static io.restassured.RestAssured.given;
import java.io.IOException;
import org.json.simple.JSONObject;
import org.testng.annotations.DataProvider;
import org.testng.annotations.*;
import All_Utils.ExcelUtils;
import Utils.ExcelUtil;
import io.restassured.http.ContentType;
public class DataDrivenPostRequest {
 
 JSONObject request = new JSONObject();
 
 String path = System.getProperty("user.dir");
 String excelPath = path + "./API_Data/TestData.xlsx";
 String sheetName = "Data1";
 
 ExcelUtils excel = new ExcelUtils(excelPath, sheetName);
 
 @Test(dataProvider="empdataprovider")
 public void PostRequestTest(String Name, String Image, String Age, String DateofBirth, String Mobile, String Email, String Password) {
 request.put("Name", Name);
 request.put("Image", Image);
 request.put("Age", Age);
 request.put("DateofBirth", DateofBirth);
 request.put("Mobile", Mobile);
 request.put("Email", Email);
 request.put("Password", Password);
 given().
 header("Content-Type","application/json").
 contentType(ContentType.JSON).
 accept(ContentType.JSON).
 body(request.toString()).
 when().
 post("https://almashossain.com/APP/VIEWS/API.php?RequestTitle=EmployeeRegistration").
 then().
 statusCode(200).
 log().all();
 
 }
 
 @DataProvider(name="empdataprovider")
 String [][] getEmpdata() throws IOException{
 String excelPath = "./API_Data/TestData.xlsx";
 String sheetName = "Data1";
 
 ExcelUtil excel = new ExcelUtil();
 
 int rownum = excel.getRowCount(excelPath, sheetName);
 int colcount = excel.getCellCount(excelPath, sheetName,1);
 
 String empdata[][] = new String[rownum][colcount]; 
 
 for (int i = 1; i <= rownum; i++) {
 for (int j = 0; j < colcount; j++) {
 empdata[i-1][j] = excel.getCellData(excelPath, sheetName, i, j);
 
 }
 
 }
 
 return(empdata);
 }
}

Error messages:

[RemoteTestNG] detected TestNG version 7.3.0
[Utils] [ERROR] [Error] java.lang.NullPointerException
 at Utils.ExcelUtil.getRowCount(ExcelUtil.java:27)
 at DataDrivenAPICollection.DataDrivenPostRequest.getEmpdata(DataDrivenPostRequest.java:56)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:132)
 at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:76)
 at org.testng.internal.MethodInvocationHelper.invokeMethodNoCheckedException(MethodInvocationHelper.java:45)
 at org.testng.internal.MethodInvocationHelper.invokeDataProvider(MethodInvocationHelper.java:145)
 at org.testng.internal.Parameters.handleParameters(Parameters.java:797)
 at org.testng.internal.Parameters.handleParameters(Parameters.java:740)
 at org.testng.internal.ParameterHandler.handleParameters(ParameterHandler.java:59)
 at org.testng.internal.ParameterHandler.createParameters(ParameterHandler.java:38)
 at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:789)
 at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:147)
 at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
 at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
 at java.util.ArrayList.forEach(Unknown Source)
 at org.testng.TestRunner.privateRun(TestRunner.java:764)
 at org.testng.TestRunner.run(TestRunner.java:585)
 at org.testng.SuiteRunner.runTest(SuiteRunner.java:384)
 at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:378)
 at org.testng.SuiteRunner.privateRun(SuiteRunner.java:337)
 at org.testng.SuiteRunner.run(SuiteRunner.java:286)
 at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
 at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
 at org.testng.TestNG.runSuitesSequentially(TestNG.java:1218)
 at org.testng.TestNG.runSuitesLocally(TestNG.java:1140)
 at org.testng.TestNG.runSuites(TestNG.java:1069)
 at org.testng.TestNG.run(TestNG.java:1037)
 at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
 at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
 at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)
SKIPPED: PostRequestTest
java.lang.RuntimeException: java.lang.NullPointerException
 at org.testng.internal.MethodInvocationHelper.invokeMethodNoCheckedException(MethodInvocationHelper.java:49)
 at org.testng.internal.MethodInvocationHelper.invokeDataProvider(MethodInvocationHelper.java:145)
 at org.testng.internal.Parameters.handleParameters(Parameters.java:797)
 at org.testng.internal.Parameters.handleParameters(Parameters.java:740)
 at org.testng.internal.ParameterHandler.handleParameters(ParameterHandler.java:59)
 at org.testng.internal.ParameterHandler.createParameters(ParameterHandler.java:38)
 at org.testng.internal.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:789)
 at org.testng.internal.TestInvoker.invokeTestMethods(TestInvoker.java:147)
 at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
 at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:128)
 at java.util.ArrayList.forEach(Unknown Source)
 at org.testng.TestRunner.privateRun(TestRunner.java:764)
 at org.testng.TestRunner.run(TestRunner.java:585)
 at org.testng.SuiteRunner.runTest(SuiteRunner.java:384)
 at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:378)
 at org.testng.SuiteRunner.privateRun(SuiteRunner.java:337)
 at org.testng.SuiteRunner.run(SuiteRunner.java:286)
 at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:53)
 at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:96)
 at org.testng.TestNG.runSuitesSequentially(TestNG.java:1218)
 at org.testng.TestNG.runSuitesLocally(TestNG.java:1140)
 at org.testng.TestNG.runSuites(TestNG.java:1069)
 at org.testng.TestNG.run(TestNG.java:1037)
 at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:115)
 at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
 at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)
Caused by: java.lang.NullPointerException
 at Utils.ExcelUtil.getRowCount(ExcelUtil.java:27)
 at DataDrivenAPICollection.DataDrivenPostRequest.getEmpdata(DataDrivenPostRequest.java:56)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:132)
 at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:76)
 at org.testng.internal.MethodInvocationHelper.invokeMethodNoCheckedException(MethodInvocationHelper.java:45)
 ... 25 more

Answer 1

Check if you have correct sheet name. As you can see in stacktrace, error happens in ExcelUtil line 27. Debug/Print the sheet name to see if the value is correct.

• Almas Hossain: Yeah, I solved the problem. Some how my modified sheet name was not saved properly.