A whole sheet into a panda dataframe with xlwings

Question

Thanks to panda, 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

Solution 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

Solution 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.

Solution 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.

Solution 4

In fact, I could do something like that :

import xlwings as xw
import pandas as pd

def GetDataFrame(Sheet,N,M):
    wb = xw.Workbook.active()
    Data=xw.Range(Sheet,(1,1),(N,M)).value
    Data=pd.DataFrame(Data)
    Data=Data.dropna(how='all',axis=1)
    Data=Data.dropna(how='all',axis=0)
    return Data

Solution 5

I spent more time reading a 20M Excel using pandas.read_excel. But xlwings reads Excel very quickly. I will consider reading with xlwings and converting to a Dataframe. I think I have the same needs as the title owner. Xlwings made some adjustments during the four years. So I made some changes to the code of the first answerer. `

import xlwings as xw
import pandas as pd

def GetDataFrame(wb_file,Sheets_i,N,M):
    wb = xw.books(wb_file)   #open your workbook
         #Specify the value of the cell of the worksheet
    Data=wb.sheets[Sheets_i].range((1,1),(N,M)).value  
    Data=pd.DataFrame(Data)
    Data=Data.dropna(how='all',axis=1)
    Data=Data.dropna(how='all',axis=0)
    return Data

`

View more solutions

Comments

[Coolpix]

Yes I already do this but I was looking for an other solution.