I would like to read the content of excel tables (*) into pandas DataFrames with xlwings.
I know about: pass openpyxl data to pandas and about: openpyxl.worksheet.table module
Such module provides a bunch of functionality for creating & modifying excel tables but I can not figure out how to simply pass the table into a DataFrame, nor do I know if that is actually possible (i.e. I don't think there is a method to read xlwings object into Pandas)
Any idea?
NOTES: 1) Please be aware that I am talking about tables NOT sheets.(I know about pd.read_excel('name.xlsx', sheetname='Sheet1')
2) I know as well that a way around is skipping rows or columns when reading a sheet, that would not make it in my case. I need to refer to a named table.
3) There is often a missunderstanding of what a table is in Excel for non excel-users. A table is a named ranged (you can go to INSERT tab and you see "Table"). That object is refered in VBA as Listobject. So a table is not a simple range in excel, nor a named range, it is more than that, it is actually the excel version of the pandas dataframe.
Have you tried the Builtin Converters of the xlwings module ? They seem to have prebuilt functions to convert to a Pandas DataFrame as well as a Pandas Series.
Here is the quick example they give:
>>> sht = xw.Book().sheets[0]
>>> df = sht.range('A1:D5').options(pd.DataFrame, header=2).value
>>> df
a b
c d e
ix
10 1 2 3
20 4 5 6
30 7 8 9