A whole sheet into a panda dataframe with xlwings

Question

Thanks to pandas, we could read a whole sheet into a data frame with the "read_excel" function.

I would like to use the same method using xlwings. In fact, my Workbook is already open and I don't want to use read_excel function (witch will take too long to execute by the way) but use the power of xlwings to save into a dataframe a whole sheet.

In fact with xlwings we could save a range into a dataframe. That mean I have to known the range size. But I guess there is a better (and quicker!) way to do that, isn't it?

Do you have some ideas to do that? Thanks a lot!

Edit: One exemple of one sheet I would like to transfer into a dataframe as read_excel would do it.

Name Point  Time    Power   Test1   Test2   Test3   Test4 ##
Test    0   1   10  4   24  144
        2   20  8   48  288
        3   30  12  72  432
        4   40  16  96  576
        5   50  20  120 720
        6   60  24  144 864
        7   70  28  168 1008
        8   80  32  192 1152
        9   90  36  216 1296
        10  100 40  240 1440
        11  110 44  264 1584
        12  120 48  288 1728

Answer 1

You can use built-in converters to bring it in one line:

df = sht.range('A1').options(pd.DataFrame, 
                             header=1,
                             index=False, 
                             expand='table').value

Answer 2

xlwings does provide api to load whole sheet. To do that, use used_range api which reads whole used part of the sheet. (Of course we don't want to get unused rows values, do we? ;-)) Anyway here is a snippet code on how to do it:

import pandas as pd
import xlwings as xw

workbook = xw.Book('some.xlsx')
sheet1 = workbook.sheets['sheet1'].used_range.value
df = pd.DataFrame(sheet1)

That's all.

Answer 3

You can read from multiple sheets with pandas:

excel_file = pd.ExcelFile('myfile.xls')
df1 = excel_file.parse('Sheet1')
df2 = excel_file.parse('Sheet2') 

So, just open one file after the other, read from the sheets you want and process the data frames.