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.

hazel_chua's avatar
hazel_chua
Contributor

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.
  • hazel_chua's avatar
    hazel_chua
    Contributor
    5 years ago

    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.

    • nmrao's avatar
      nmrao
      Champion Level 3
      5 years ago
      The statement, "import properties", in the original question confused me.
    • nmrao's avatar
      nmrao
      Champion Level 3
      5 years ago
      Hope "Evaluate formulas" check box is ticked.

      • hazel_chua's avatar
        hazel_chua
        Contributor
        5 years ago

        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.