cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Highlighted
Occasional Contributor

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

@nmrao Ahhh ok.. well when you attempt to connect to an Excel file as the datasource, it asks if you would like to import properties as well, which actually just means the header row of the sheet.  It worked fine on my dummy file of 20 rows and only 2 columns.  So, in that case, there are only 2 properties imported; the headers for the 2 columns.

 

As for checking that "Evaluate formula" box, the initial load up screen doesn't even allow me to do that, then it spits that dummy BEFORE going into the screen that has the actual checkbox.  Design flaw in SoapUI maybe..?? I won't know.  But even after I ticked that box, it still wouldn't read the file and still gives that same error.

 

Works for a dummy Excel file but not with a more realistic, albeit more complex, file.

Highlighted
Community Hero

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

Asked that as the previous reply of yours states it includes formulas.

What is the extension of the file? ".xlsx"?


Regards,
Rao.
Highlighted
Occasional Contributor

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

Both actual and dummy files are .xlsx files

Highlighted
Community Leader

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

@hazel_chua :

This error generally occurs when there is formulae in the excel sheet.

Check if any formulae is there in the sheet or not.

If any formulae is there then check whether it is working fine or not.

 


Click "Accept as Solution" if my answer has helped, and remember to give "kudos" Smiley Happy

Thanks and Regards,
Himanshu Tayal
Highlighted
Occasional Contributor

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

@HimanshuTayal yes there are formulae in many of the cells and many of them have a VLOOKUP function in them too.  The Excel workbook look fine and all values are updated, but SoapUI just doesn't seem to like it. Smiley Sad

Highlighted
Community Hero

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

May be it would be helpful to see whole stacktrace from the log file.

Look for the formaula where it has some special characters such as $


Regards,
Rao.
Highlighted
Community Leader

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

@hazel_chua , i have worked with excel sheet having formulae but didn't get error while using it with datasource... 

As @nmrao is saying just check for the special character..might be some ambiguity with soapui special character.

 


Click "Accept as Solution" if my answer has helped, and remember to give "kudos" Smiley Happy

Thanks and Regards,
Himanshu Tayal
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

Highlighted
Occasional Contributor

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

@HimanshuTayal and @nmrao,

 

Sample of the formulae: =VLOOKUP(A2, Details!$A$2:$B$43,2,FALSE)

 

Within the Excel workbook itself, that evaluates fine.  But I guess SoapUI doesn't know how to handle the '$'.

Highlighted
Community Hero

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

SoapUI / Groovy has special processing for $ such as "${#TestCase#Property}", $variable, context.expand('${..}'). So not sure which is case bug or limitation.
Another problematic characters could be "!", and #.
You may try with sample workbook to isolate exact issue and report to SmartBear team.
And good that you found workaround and proceeding.


Regards,
Rao.