A whole sheet into a pandas 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

Welcome to SO. To have a better chance of getting your question answered in a timely manner, please read this: http://stackoverflow.com/help/how-to-ask — Sam, Dec 21 '15 at 10:07
Please provide a minimal example so we can reproduce the problem and try to help you. — Fabio Lamanna, Dec 21 '15 at 10:08
Alright, I will make an example. But it's not really a problem. It's "how could I get easily the information from a sheet and pass them into a dataframe". — Coolpix, Dec 21 '15 at 10:40
"(which will take too long to execute by the way) " Do you have an example that shows read_excel is slower than xlwings? — DeepSpace, Dec 21 '15 at 10:41
In fact, I work with multiple Workbook. Prior to the process, I copy all the important data into a unique Workbook. So it's obvious that read_excel will take more time to process between multiple workbook than xlwings between multiple sheets. Therefore, I'll make a real example to present the whole problem ! — Coolpix, Dec 21 '15 at 10:52
What do mean by *Workbook*. An Excel file or a sheet within one Excel file? Put differently: do you have several Excel files to read or multiple sheets per file? — Mike Müller, Dec 22 '15 at 08:26
First, I have several Excel files. I put all the important data into one Excel file with several sheets. And I would like to read each sheet as dataframe (like read_excel function). — Coolpix, Dec 22 '15 at 08:38

score 20 · Answer 1 · answered Mar 14 '18 at 19:37

20

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

answered Mar 14 '18 at 19:37

kateryna

1,239
9
10

TheLittleNaruto · Answer 2 · 2020-02-24T11:54:40.560

7

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.

edited Feb 24 '20 at 11:54

answered Feb 24 '20 at 11:43

TheLittleNaruto

8,325
4
54
73

score 4 · Answer 3 · answered Dec 22 '15 at 10:33

4

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.

answered Dec 22 '15 at 10:33

Mike Müller

82,630
20
166
161

1

Yes I already do this but I was looking for an other solution. – Coolpix Dec 22 '15 at 14:21

score 4 · Answer 4 · answered Dec 23 '15 at 08:04

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

score 1 · Answer 5 · answered Oct 08 '19 at 03:17

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

`

A whole sheet into a pandas dataframe with xlwings

5 Answers5