I have a scenario where i need to download the excel from a link and verify the cell data.
In my excel i will be having 350 columns of data. So Katalon will not support the data. Because we have a restriction of 100 columns. So i have added the POI jars and wrote a Keyword to verify the cell data in excel.
When i download the excel from a link and execute my keyword, it is getting failed because there is a setting for “Enable Editing” by default in the header when the file is downloaded.
Manually when i enable the “Enable editing” and execute keyword, it is successfully passed.
How to achieve this scenario?
We have also tried disabling the Protected view settings in excel. But no luck
Currently Katalon is using 3.9 version of POI jar and it seems that is pretty old and 9 versions behind of the current version.
Below is the code
File file = new File(filePath)
FileInputStream fis = new FileInputStream(file)
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fis)
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
int numberOfRows = getRowCount()
int numberOfColumns = xssfSheet.getRow(1).getLastCellNum()
testdata = new String[numberOfRows - 1][numberOfColumns]
for (int i = 1; i < numberOfRows; i++) {
for (int j = 0; j < numberOfColumns; j++) {
XSSFRow row = xssfSheet.getRow(i)
XSSFCell cell = row.getCell(j)
String value = xssfcellToString(cell)
testdata[i - 1][j] = value
if (value == null) {
System.out.println(“data empty”)
}
}
}
Kazurayam, we tried using the method which Andrej suggested. But it did not work.
The same code when we run in other IDE like eclipse or intellij. We are able to successfully run the script and get the values from Excel.
But in katalon it is not working. We doubt with the Jars which Katalon is using. They are pretty old. You can find the Jars screenshot in the same thread.
You showed your test case code (which was edited to hide sensitive information, removed import statements, etc), but you do not provide the Excel file you are working on. I can not reproduce your problem on my side, therefore I have no idea. It is only you who can help yourself.
IMHO, the POI is very stable project. I guess it is unlikely that the oldness of the POI version causes problem.
If you have any doubt about the version of POI, why not you download the POI jar of your favorites version into the plugins directory, delete the old ones and see what happens?
I am getting NullPointerException. Please find the attached log report.
POI jars(3.9) which Katalon is using was last modified was in 2012. So it is only a guess because when i use latest version of POI jars in other IDE’s i dont see any issue.
When i update the latest jars in the Plugins folder, Katalon studio is throwing some error while opening. Please find the log file 1551408391082.log (354.6 KB)
package com.database
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 com.kms.katalon.core.annotation.Keyword
public class demo {
XSSFWorkbook xssfWorkbook = null
XSSFSheet xssfSheet = null
@Keyword
public String[][] readDataFromExcel(String filePath) throws IOException {
String [][] testdata
if (filePath.endsWith(".xlsx")) {
File file = new File(filePath)
FileInputStream fis = new FileInputStream(file)
xssfWorkbook = new XSSFWorkbook(fis)
xssfSheet = xssfWorkbook.getSheetAt(0);
int numberOfRows = getRowCount()
int numberOfColumns = xssfSheet.getRow(1).getLastCellNum()
testdata = new String[numberOfRows - 1][numberOfColumns]
for (int i = 1; i < numberOfRows; i++) {
for (int j = 0; j < numberOfColumns; j++) {
XSSFRow row = xssfSheet.getRow(i)
XSSFCell cell = row.getCell(j)
String value = xssfcellToString(cell)
testdata[i - 1][j] = value
if (value == null) {
System.out.println("data empty")
}
}
}
}
return testdata
}
public int getRowCount() {
return xssfSheet.getLastRowNum() + 1
}
public String xssfcellToString(XSSFCell cell) {
Object result=""
if (cell != null) {
int type = cell.getCellType()
switch (type) {
case 0:
result = cell.getNumericCellValue()
break
case 1:
result = cell.getStringCellValue()
break
case 2:
result = cell.getCellFormula()
break
case 3:
result = ""
break
default:
throw new RuntimeException("no support for this cell")
}
}
return result.toString()
}
}
When I execute it with the Excel file you provided, I got the following message:
java.lang.NullPointerException: Cannot invoke method getLastCellNum() on null object
at com.database.demo.readDataFromExcel(demo.groovy:24)
...
This message implies that xssfSheet.getRow(1) returned null.
Why null? — I am not sure, but it is possible. The POI API javadoc says Sheet#getRow(int) may return null if the row is not defined. See the documentation.
Your code:
int numberOfColumns = xssfSheet.getRow(1).getLastCellNum()
is careless, is not defensive enough against null to be returned by Sheet#getRow(int) method.
We have a data in excel for both Row and column.
When i run the same in other IDE . It wont throw any error. It will be Pass
or
Click on Enable edit and save the file and try to run the same in Katalon. It will work
This seems to be a problem with Office Trust Center setting. Please read the following StackOverflow answer and try it out. It doesn’t appear to be a POI issues.
