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?
1 Like
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();
And is the path to your file correct?
Same problem to me but wanna write in excel
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()
Hi ,
I am also facing the same issue, I figured out when it is happening for me. When I am trying to write data on empty cell then this error comes up.
What I did was ,manually edited the cell in sheet with some random text, then I ran script then it is able to write the data to cell through scripts.
But How can I write data to empty cell too, please suggest me, it would be great help to me.
Thanks
Shiva
The error is happening because cell does not exists and getCell method returns null. Basically you’re trying to set value to null filed. This block of code should help:
try {
// Block of code to try to write to cell
sheet.getRow(rowNumber).getCell(colNumber).setCellValue(value)
}
catch(Exception e) {
// Block of code to handle errors and crate cell and write to it
sheet.getRow(rowNumber).createCell(colNumber).setCellValue(value)
}
