I am trying to read data from excel with pandas.read_excel and xlwings. I have succeeded with pandas but still not managed to get the same dataframe structure with xlwings, which is a pity as it seems it is the only solution to read data from an already open workbook in excel.
Here are the steps I have followed :
I have the following data in excel where the table can be expanded in any direction :

when I read it with pandas.read_excel, I get the dataframe as expected :
import pandas as pd
df = pd.read_excel(open('multiindex.xlsx','rb'), "0001",header=[0,1,2,3],index_col=[0,1,2])
print(df.axes[1])
print(df.axes[0])
MultiIndex([('Planning', 'Discovery', 'Sprint 0', 'W1'),
('Planning', 'Discovery', 'Sprint 0', 'W2'),
('Planning', 'Discovery', 'Sprint 1', 'W3'),
('Planning', 'Discovery', 'Sprint 1', 'W4'),
('Planning', 'Discovery', 'Sprint 2', 'W5'),
('Planning', 'Discovery', 'Sprint 2', 'W6'),
('Planning', 'Build', 'Sprint 3', 'W7'),
('Planning', 'Build', 'Sprint 3', 'W8'),
('Planning', 'Build', 'Sprint 4', 'W9'),
('Planning', 'Build', 'Sprint 4', 'W10')],
names=['Full project', 'Phase', 'Sprint', 'Project week'])
MultiIndex([('Dev', 'dev1', 10),
('Dev', 'dev2', 10),
('Dev', 'dev3', 10),
( 'QA', 'qa1', 10),
( 'QA', 'qa2', 10)],
names=['Profile', 'Name', 'Daily rate'])
Now I want to be able to access the workbook while it is opened. It seems that only xlwings offer this possibility but I can't find the proper poptions to get the same dataframe shape. For the moment this is what I get :
import xlwings as xw
workbook = xw.Book('multiindex.xlsx')
dfxl = workbook.sheets["0001"].range("A1:M10").options(pd.DataFrame,header=4,index=3).value
print(dfxl.axes[1])
print(dfxl.axes[0])
MultiIndex([('Planning', 'Discovery', 'Sprint 0', 'W1'),
( nan, nan, nan, 'W2'),
( nan, nan, 'Sprint 1', 'W3'),
( nan, nan, nan, 'W4'),
( nan, nan, 'Sprint 2', 'W5'),
( nan, nan, nan, 'W6'),
( nan, 'Build', 'Sprint 3', 'W7'),
( nan, nan, nan, 'W8'),
( nan, nan, 'Sprint 4', 'W9'),
( nan, nan, nan, 'W10')],
)
MultiIndex([('Profile', 'Name', 'Daily rate'),
( 'Dev', 'dev1', 10.0),
( nan, 'dev2', 10.0),
( nan, 'dev3', 10.0),
( 'QA', 'qa1', 10.0),
( nan, 'qa2', 10.0)],
names=[None, None, 'Project week'])
2 issues :
- The multiindex are not named correctly
- I have to specify the range as using autoexpand returned an empty dataframe.