Excel named range for openpyxl

Question

I have an excel file which have multiple sheets, Unit_1, Unit_2... The form of the sheets is identical but the data varies. On each sheet, there is named range called "SIGNALS" which defines the area "A1:C4. I want to access the data by referring to the named range while looping through the sheets.

I'm using the function from here as a base to access to the data, but the named range reference does not work when I'm trying to define the sheet as well. https://stackoverflow.com/a/45554742/7661466

If I define the range_name as "SIGNALS", I get the data from the Unit_1 sheet, I assume because the it is active by default.

If I define it as "Unit_2!A1:C4", I get the data from Unit_2 sheet as expected.

If I define it for example as "Unit_1!SIGNALS" I get an ValueError: SIGNALS is not a valid coordinate or range.

How should I refer to a named range in certain sheet?

For an example, a table for a "Unit_1" sheet.

user_7661466 · Answer

It seems on openpyxl you cannot refer to named range easily by referring to sheet and the named range. Named range works easily only on active sheet but changing active sheet seemed to be rather cumbersome as well.

I modified the function by Mathias Fripp from https://stackoverflow.com/a/45554742/7661466 to better suit my needs, although it might not be the most elegant way.

First a dataframe is constructed with the sheet, cell and named range data from excel, and from that data the cell range is masked by the sheet name and "named range" -name.

def dataframe_from_xlsx(xlsx_file, range_name):
""" Get a single rectangular region from the specified file.
range_name can be a standard Excel reference ('Sheet1!NAMED_RANGE')."""
wb = openpyxl.load_workbook(xlsx_file, data_only=True, read_only=True)

# get named range definitions from excel
named_ranges = wb.defined_names.definedName
data = []
for range in named_ranges:
    sheet, cells = range.value.split("!")
    data.append([sheet, cells, range.name, ])
named_ranges = pd.DataFrame(data, columns=['sheet', 'cells', 'name'])

ws_name, reg = range_name.split('!')

if ws_name.startswith("'") and ws_name.endswith("'"):
    ws_name = ws_name[1:-1]

# get the cell range by masking
mask = (named_ranges['sheet'] == ws_name) & (named_ranges['name'] == reg)
cell_reg = named_ranges[mask]['cells'].iloc[0]
region = wb[ws_name][cell_reg]

df = pd.DataFrame([cell.value for cell in row] for row in region)
return df

Excel named range for openpyxl

1 Answers