Unable to read data from Excel

Hello All,

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”)
}
}
}

Anyone please help me on this

Hello,
this is only article about your issue i remembered and found:


try that workaround and report back if that works please.

Thanks Andrej, i will try and let you know.

Hi Andrej, i tried the work around. But no luck:(

One thing what we see is Katalon is using old POI jars under Plugins folder and these are pretty old. Current POI jars version is 4.0.1

image

@kazurayam, please help me on this

Please describe how your luck was.

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.

Any message?

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)

I am unable to upload all types here. So i have uploaded to google drive and here is the link.
https://drive.google.com/open?id=1ToscVIRl577OPoSlQOuvDbP8AxwcAy55

Please help.

Your demo.groovy is :

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.

I would recommend to you to look for well-designed examples of how to read Excel with POI. For example:
https://www.mkyong.com/java/apache-poi-reading-and-writing-excel-file-in-java/

Most of the public examples use “Iterators” rather than by “for by index”.

Thanks,

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

I have no experience about this problem. I am not capable of helping you, sorrry.

@kazurayam, you cannot help completely or only for updating of plugins you cant?

Why same script works in other IDE not in Katalon is a big confusion for me.

If you find any solution please let me know

I have experience neither about

  • difficulties in programming POI caused by “Enable Editing” setting
  • Office Trust Center setting

oh ok, Thanks for the reply.

Will wait for any Katalon Developer to figure out the issue.

Hi All,

Katalon Team, please help me with the problem

Hi @Sudheer_D_J

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.

Regards !

Yes @ThanhTo, we tried this solution. But it did not work in Katalon.

We tried same in other IDE’s it is working. How different is Katalon and other IDE’s??

In the same message thread i have all my files and log reports. Could you please look into the files.