CODEWITHSUNDEEP
pythonopenpyxl
ac24
ac24

Reputation: 5565

Openpyxl: listing all defined names of a workbook

I am trying to read in all the data contained in named ranges in a xlsx workbook across multiple sheets, but for the purposes of this exercise, I don't know what the names of any named ranges will be ahead of time.

I can access wb.defined_names but this returns a DefinedNameList; if I try to iterate over it I don't seem to be able to access any of the data underneath.

How can I access the names of named ranges and the corresponding cell references as a list of strings?

Context: Up until now I have been achieving this with xlwings, but am having issues with xlwings opening a wb whilst I have other wb tabs open. Switching to openpyxl seems like a good possible solution if I can fix this issue

Upvotes: 3

Views: 5413

Answers (2)

Vijay Bokade
Vijay Bokade

Reputation: 179

try this simple command for Openpyxl version>3.1.0.

excel_file_path = r"sheet.xlsx"
wb = load_workbook(excel_file_path)
Name_Ranges = list(wb.defined_names)# all name ranges in this list
print(Name_Ranges)

Upvotes: 0

Charlie Clark
Charlie Clark

Reputation: 19497

[dn for dn in wb.defined_names.definedName]

Upvotes: 10

Related Questions