cancel
Showing results for 
Search instead for 
Did you mean: 

Java exception when trying to load an Excel spreadsheet as data source

SOLVED
Occasional Contributor

Java exception when trying to load an Excel spreadsheet as data source

ReadyAPI Java exception.JPG

The above error occurred when I attempted to import properties from an Excel spreadsheet.

 

Any ideas on how to fix that.?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Occasional Contributor

Re: Java exception when trying to load an Excel spreadsheet as data source

Well, the current workaround I've done is to export the sheet from the original workbook as a CSV file which then forces the cells to be evaluated and thus populated with their final values.  Then I re-import that CSV file back into an Excel file with just that one sheet.  The final Excel file works fine with SoapUI.  An indirect way of doing things.

 

It just surprises me that there is the option in SoapUI to use Excel workbooks as data sources but it can't handle workbooks with complex cell lookups or functions.

View solution in original post

21 REPLIES 21
Highlighted
Community Leader

Re: Java exception when trying to load an Excel spreadsheet as data source

Hi @hazel_chua,

I did not get the same error but I remember I needed to download Microsoft access database engine 2016 redistributable to make it work with TC. You can give it a go.

 

https://www.microsoft.com/en-us/download/details.aspx?id=54920&e6b34bbe-475b-1abd-2c51-b5034bcdd6d2=...

Highlighted
Community Hero

Re: Java exception when trying to load an Excel spreadsheet as data source

Use a text based properties, should be able get an example of property file online.
No need to use Excel for this case.


Regards,
Rao.
Highlighted
Occasional Contributor

Re: Java exception when trying to load an Excel spreadsheet as data source

@nmrao Not my call to make what file I use, it's from a client and they are using Excel.  So, yes, I need to be able to connect to an Excel file that has multiple sheets in it.

Highlighted
Occasional Contributor

Re: Java exception when trying to load an Excel spreadsheet as data source

@Reshail tried it, but it did not work. Smiley Sad

Highlighted
Occasional Contributor

Re: Java exception when trying to load an Excel spreadsheet as data source

Update on this issue.  It seems like the formulae / VLOOKUP functions in the sheets are causing that Java issue.  Many of the cells in the workbook are referencing other sheets and cells across the workbook.  And that seems to cause the import / read of the Excel file to fail.

 

Tested with a dummy Excel file with 2 columns and 20 rows in only one sheet and that worked fine.  But, unfortunately again, that is not what my client's Excel file is like. Smiley Sad

Highlighted
Community Hero

Re: Java exception when trying to load an Excel spreadsheet as data source

Hmm.. never heard using an excel for keeping properties.
Are you sure that the file has key, value pairs?
You check with your team and let them know the supported format.


Regards,
Rao.
Highlighted
Occasional Contributor

Re: Java exception when trying to load an Excel spreadsheet as data source

@nmrao not the project environment properties.  But the Excel spreadsheets do contain the data that is needed for the parameters of the tests to be run.  The keys are the column headers and the values are the rows which should be simple enough, even though many of the cells contain references/formulae that utilises cells from other spreadsheets in the same workbook.

 

The one of the sample projects used in the learning videos here used simple Excel files as well.  Just not the sort of complex, extensive ones that are found in the real world.

Highlighted
Community Hero

Re: Java exception when trying to load an Excel spreadsheet as data source

The statement, "import properties", in the original question confused me.


Regards,
Rao.
Highlighted
Community Hero

Re: Java exception when trying to load an Excel spreadsheet as data source

Hope "Evaluate formulas" check box is ticked.



Regards,
Rao.