Import named range from Excel into Python script

Asked Aug 30 '20 at 14:03

Active Aug 30 '20 at 22:47

Viewed 375 times

I have an excel spreadsheet with a named range. I am trying to extract each column of the named range as a list.

I have written a script so that Python can identify the cell range ( in this case, F4:H10), as shown.

I now want to assign a list for each column, i.e. Height = [100, 200, 400, 200, 300].

I tried following the method attached: Read Excel File in Python

However, I need to use defined ranges, as I move the spreadsheet around often, and can't afford to go through the python script each time I rearrange the excel sheet, and pandas wanted me to identify the column as the letter of the column the table is in.

file = 'foo.xlsx'
wb = openpyxl.load_workbook(file, data_only = True)

address = list(wb.defined_names['mytest'].destinations)

for sheetname, cellAddress in address:
    cellAddress = cellAddress.replace('$', '')
    print(cellAddress)

edited Aug 30 '20 at 22:47

asked Aug 30 '20 at 14:03

Scott Crawshaw

What's the actual question? Looks like you've found the defined names. – Charlie Clark Aug 31 '20 at 18:12
I want to create a Python list of the columns of my defined range, so that I can have Height = [ 100, 200, 400, 200, 300] – Scott Crawshaw Sep 01 '20 at 23:45
So you want the values from the cells in the range? – Charlie Clark Sep 02 '20 at 09:33

Import named range from Excel into Python script

0 Answers0