Search code examples
pythonexcelwinapicom

Win32: Python to get all Column Names from Excel

How can I get all defined names from a Excel Worksheet using the COM-interface, i.e. Win32 API in Python?

I want to obtain some sort of a dict, e.g. in the example below it would be:

cols['Stamm_ISIN'] = 2

so that I could access the column later using it's ID 2 to set some values: excel.Cells(row, cols['Stamm_ISIN']).Value = 'x'

I'm somewhat stuck at the API documentation https://msdn.microsoft.com/en-us/library/office/ff841280.aspx so it seems to somehow be possible... Any moreover I couldn't find any result when googeling :(

enter image description here

Solution

  • The following script shows you how to display all of the column headings for a given WorkSheet. First it calculates the number of columns used and then enumerates each column. Next it displays all of the named ranges for a given WorkBook, for each it stores the instance into the cols dictionary. This can then be used in conjunction with Range() to set all cells in a given named range to a given value:

    import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(r"test file.xlsx")
ws = wb.Worksheets("Sheet1")

cols = {}       # Dictionary holding named range objects

# Determine the number of columns used in the top row
xlToLeft = -4159
col_count = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

# Print each column heading
for col in range(1, col_count + 1):
    print "Col {}, {}".format(col, ws.Cells(1, col).Value)

# Enumerate all named ranges in the Workbook
for index in range(1, wb.Names.Count + 1):
    print "Workbook: ", wb.Names(index).Name, wb.Names(index).Value
    cols[wb.Names(index).Name] = wb.Names(index)

# Enumerate any named ranges in the current Worksheet
for index in range(1, ws.Names.Count + 1):
    print "Sheet: ", ws.Names(index).Name, ws.Names(index).Value

# Change all cells for a named range called "TestRange" to "TEST"
ws.Range(cols["TestRange"]).Value = "TEST"

# Save the changes 
wb.Save()
excel.Application.Quit()

    In this example, your Excel file would need to have a named range called TestRange.