Read Excel Table Headers With Xlwings

How can I use xlwings to read a 'table' in excel, into a pandas DataFrame, where the table 'headers' become the DataFrame column names? Every way I have tried to read the table, th

Solution 1:

Hoping this is not the best answer, but I did find I could reference the named range, then .offset(-1).expand('vertical')

Solution 2:

Another option is to use the api and Excel's ListObject

import xlwings as xw

wb = xw.books.active
ws = wb.sheets('MySheet')
tbl = ws.api.ListObjects('MyTable') # or .ListObjects(1)
rng = ws.range(tbl.range.address) # get range from table address

df = rng.options(pd.DataFrame, header=True).value # load range to dataframe

Solution 3:

Let me add the comment by Felix Zumstein as explicit answer, because in my opinion it is the best solution.

b.sheets['Sheet1'].range('Table1[[#All]]').options(pd.DataFrame)

Using the square bracket notation expands the selection from the table body to the header row as well. Subsequently the conversion to a pandas DataFrame, can use it as header.

To read more, check this answer.