Cannot Read Data from .xlsx Files

[ryan-ambrose-gc]

Description

SheetParts are unable to read data from .xlsx files due to the third-party library xlrd method _openworkbook. Starting with xlrd version 2.0.0, support for .xlsx files was dropped for security vulnerabilities. If an .xlsx spreadsheet is attempted to be read using the _read_fromexcel method, an erroneous error message reporting

"read_from_excel() error. Invalid sheet name. File: {}, Sheet: {}"

is returned due to an XLRDError being raised in the try block of _read_fromexcel (consult sheet_part.py, starting at line 347). However, if the excel spreadsheet is converted to the .xls format, the spreadsheet will be read without issue.

Potential Solutions

• The requirements.txt version for xlrd can just roll back before 2.0.0...but omitting security updates probably aren't a great idea
• If the input file to _read_fromexcel has the .xlsx file extension a more informative error message can be raised to convert the spreadsheet to an older version

Recreate

1. Create new scenario
2. In same directory, create Excel Spreadsheet (e.g. Temp.xlsx)
3. Two new parts, one Function and one Sheet
4. Link the two parts
5. Have the following code within Function Part link.sheet.read_excel(get_scenario_path() + 'Temp.xlsx', 'temp', 'A1:B2')
6. Run code, should encounter error
7. Convert Temp.xlsx to Temp.xls, update code in Function Part to reflect this
8. Code should work as expected

[ryan-ambrose-gc]

Actually, found a perhaps better solution. openpyxl is a package available on all platforms that can read .xlsx files and requires minimal changes to the sheets data part. I intend on submitting a pull request later today where I replace the problematic calls to xlrd with openpyxl.