search

DND-DRDC-RDDC / OS_ORIGAME

ORIGAME is a Python-based discrete event modelling and simulation environment. It has a full-featured graphical user interface (GUI) in which users build models and run Monte Carlo simulations.
GNU General Public License v3.0
4 stars 1 forks source link

Unable to import Excel file to sheet part #6

Open jillianhenderson opened 1 month ago

jillianhenderson commented 1 month ago

Attempted to load n excel file to fill a sheet part by right clicking on the sheet part and selecting "Import from Excel".

When selecting a .xlsx file, the "List Sheets" function throws an error: "The list of sheets could not be retrieved from Excel file 'Path\to\file.xlsx'. Read_from_excel() error. The following error occurred: Excel xlsx file; not supported."

Selecting a .xls file allows the "List Sheets" function to work, but subsequently selecting "OK" throws the error: "The following error was raised: read_from_excel() error. Invalid sheet range. File Path\to\file.xls, Sheet: POP, Range: . More info: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format."

It seems that the "List Sheets" function needs to be updated to the openpyxl functionality or include the ability to use either old (.xls) or new (.xlsx) file formats.

Ryan-Ambrose-DRDC commented 1 month ago

Hmmm this is interesting...I wonder if it is a consequence, or related to, of my earlier issue #1

Whenever I load in Excel Spreadsheets, I always do it from a Function object and use the read_from_excel function. I have not encountered this issue, so there must be a difference in whatever is called with the "Import from Excel" function. I'll try digging into this this afternoon when I'm out of the office.

Ryan-Ambrose-DRDC commented 1 month ago

As I suspected, the issue is related to the previously closed issue.

The Issue

Tracing back the issue:

  1. Exception is raised in sheet_part_editor.py at line 325. Tries to execute sheets = get_excel_sheets(excel_path)
  2. get_excel_sheets is imported from sheet_part.py. Specifically at line 340 trying to execute wb = open_workbook(xls_file)
  3. open_workbook is imported from the 3rd party package xlrd.

XLRD has stopped support for reading xlsx due to security reasons. Openpyxl seems to be the way to read them from now on.

The Solution

Fixing this is straightforward, can replace the call to open_workbook from XLRD with load_workbook from openpyxl. Note that this will require the method call on line 341 to be changed from sheet_names() to wb.sheetnames to match the functionality of openpyxl. Just to make the entire sheet_part consistent, I will also change the last call to open_workbook at line 429, and subsequent references to sheet names (lines 432 and 434).

I'll submit a pull request shortly to make these changes.