Win32: Python to get all Column Names from Excel
<p>How can I get all defined names from a Excel Worksheet using the COM-interface, i.e. Win32 API in Python?</p>

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

<pre><code>cols['Stamm_ISIN'] = 2
</code></pre>

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

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

<p><a href="http://i.stack.imgur.com/pjQ2d.png" rel="nofollow"><img src="http://i.stack.imgur.com/pjQ2d.png" alt="enter image description here"></a></p>

<p>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 <code>cols</code> dictionary. This can then be used in conjunction with <code>Range()</code> to set all cells in a given named range to a given value:</p>

<pre><code>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()
</code></pre>

<p>In this example, your Excel file would need to have a named range called <code>TestRange</code>.</p>

python excel winapi com