java.lang.NullPointerException: Cannot invoke method getCell() on null object


#1

Trying to write to excel file from the code in this website - https://docs.katalon.com/katalon-studio/docs/write-data-into-excel-file-at-runtime-during-test-execution.html

But encounter the following error:

“Data_fromCell = getCell(1).getStringCellValue() FAILED.
Reason:
java.lang.NullPointerException: Cannot invoke method getCell() on null object
at AutoTest_Trial.run(AutoTest_Trial:15)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:331)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:322)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:301)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:293)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:227)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1555042548584.run(TempTestCase1555042548584.groovy:21)”

Any idea what is wrong?


#2

Hello, I am pasting the code here to have everything on the same place.

FileInputStream file = new FileInputStream (new File("E:\\Testdata.xlsx"))
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);

'Read data from excel'
String Data_fromCell=sheet.getRow(1).getCell(1).getStringCellValue();
'Write data to excel'
sheet.getRow(1).createCell(1).setCellValue("Mahesh2");

file.close();
FileOutputStream outFile =new FileOutputStream(new File("E:\\Testdata.xlsx"));
workbook.write(outFile);
outFile.close();

The exception says that you are calling getCell() on null object. I’d say your sheet object is either null or empty. Can you check it?

String Data_fromCell=sheet.getRow(1).getCell(1).getStringCellValue();


#3

It is not empty


#4

And is the path to your file correct?


#5

Same problem to me but wanna write in excel


#6

I managed to get it working now. Need to import correct library as well. Here’s the sample that I followed:

import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testobject.TestObject as TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import internal.GlobalVariable as GlobalVariable
import com.kms.katalon.core.testobject.SelectorMethod as SelectorMethod
import org.openqa.selenium.JavascriptExecutor as JavascriptExecutor
import org.openqa.selenium.interactions.Actions as Actions
import org.openqa.selenium.WebDriver as WebDriver
import org.openqa.selenium.WebElement as WebElement
import com.kms.katalon.core.webui.driver.DriverFactory as DriverFactory
import org.openqa.selenium.Keys as Keys
import org.openqa.selenium.By as By
import java.text.DecimalFormat
import java.time.LocalDateTime
import java.time.LocalDate
import java.time.format.DateTimeFormatter
import groovy.io.FileType
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Cell
//import org.apache.poi.ss.usermodel.CellType
import java.io.File
import java.io.FileInputStream
import java.io.FileOutputStream
import java.io.IOException
import java.time.format.TextStyle
import java.text.DecimalFormat

FileInputStream inputStream = new FileInputStream(new File(‘C:\Downloads\test.xlsx’));

Workbook workbook = WorkbookFactory.create(inputStream);

Sheet sheet = workbook.getSheetAt(0)

Row row = sheet.getRow(1);

// Get the Cell at index 2 from the above row

Cell cell = row.getCell(2);

// Create the cell if it doesn’t exist

if (cell == null)

cell = row.createCell(2);

// Update the cell’s value

//cell.setCellType(CellType.STRING)

cell.setCellValue("Updated Value77777777")

FileOutputStream fileOut = new FileOutputStream("C:\Downloads\test.xlsx");

workbook.write(fileOut);

fileOut.close();

// Closing the workbook

inputStream.close()