Forum Discussion

hazel_chua's avatar
hazel_chua
Contributor
5 years ago

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

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

 

Any ideas on how to fix that.?

Data-Driven Testing
REST
Scripting
  • hazel_chua's avatar
    hazel_chua
    5 years ago

    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.

  • HimanshuTayal's avatar
    HimanshuTayal
    Community Hero
    5 years ago

    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.

     

    • hazel_chua's avatar
      hazel_chua
      Contributor
      5 years ago

      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. :(

      • HimanshuTayal's avatar
        HimanshuTayal
        Community Hero
        5 years ago

        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.

         

      • hazel_chua's avatar
        hazel_chua
        Contributor
        5 years ago

        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. :smileysad:

  • nmrao's avatar
    nmrao
    Champion Level 3
    5 years ago
    Use a text based properties, should be able get an example of property file online.
    No need to use Excel for this case.
    • hazel_chua's avatar
      hazel_chua
      Contributor
      5 years ago

      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.

      • nmrao's avatar
        nmrao
        Champion Level 3
        5 years ago
        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.
  • nmrao's avatar
    nmrao
    Champion Level 3
    5 years ago
    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 $