Python pyexcelerate library writing multiple sheets to same workbook?

Question

I am trying to write multiple sheets into one workbook and I'm using pyexcelerate to utilize it's optimized writing time.

Here's my current code to write to a workbook:

def df_to_excel(df, path, sheet_name='Sheet 1'):
    data = [df.columns.tolist(), ] + df.values.tolist()
    wb = Workbook()
    wb.new_sheet(sheet_name, data=data)
    wb.save(path)

now this works perfectly fine if i only need one sheet; however, if i write in multiple sheet, only the last sheet will be kept (all sheets generated before will be replaced).

I want to keep all the sheets (with diff names ofc), and I looked into their github page, but I cannot find info on such capability: https://github.com/kz26/PyExcelerate

I also looked into a few other stackoverflow posts but they are using different packages:

any help is appreciated!

You always create a new **empty** Workbook using `wb = Workbook()`, reread the linked Examples. — stovfl, Sep 14 '17 at 19:28
but none of the links use pyexcelerate library. are the methods usable across diff libraries? — alwaysaskingquestions, Sep 14 '17 at 19:30
Create your Workbook **only once**, outside and pass it to your `def df_to_excel(wb, ...` — stovfl, Sep 14 '17 at 19:59

score 1 · Answer 1 · answered Mar 11 '21 at 21:43

If you want yo save two different DataFrames in different worksheets of a workbook (taking your code as a reference):

from pyexcelerate import Workbook

def df_to_excel(df1, df2, path, sheet_name1='Sheet 1', sheet_name2='Sheet 2'):
    data1 = [df1.columns] + list(df1.values)
    data2 = [df2.columns] + list(df2.values)
    wb = Workbook()
    wb.new_sheet(sheet_name1, data=data1)
    wb.new_sheet(sheet_name2, data=data2) #Just add another sheet
    wb.save(path)

Pramit Barua · Answer 2 · 2017-12-06T17:22:59.267

If you want to save different variable in different sheet of one excel file faster and also want header and index of the pandas.dataframe in the excel file. You can do something as follows,

    import timeit
    from pyexcelerate import Workbook

    def to_Excel(data, fileName):
        start_time = timeit.default_timer()
        wb = Workbook()
        for key in data.keys():
            ws = wb.new_sheet(key)
            frame = data[key]
            frame = frame.transpose()
            frame.reset_index(level=0, inplace = True)
            frame = frame.transpose()
            frame.reset_index(level=0, inplace = True)
            row_num = frame.shape[0]    
            col_num = 1
            for col_name, col_series in frame.iteritems():
                ws.range((1,col_num), (row_num,col_num)).value = [[x] for x in col_series]
                col_num += 1       
        wb.save(fileName)
        end_time = timeit.default_timer()
        delta = round(end_time-start_time,2)
        print("Took "+str(delta)+" secs")

'data' is a dictionary variable. The 'key' of 'data' works as 'sheet name' and data type of each key value should be in pandas dataframe.

score 0 · Answer 3 · answered Feb 27 '19 at 08:50

We suppose that we have list of list data results and we want to write multiple sheets into one workbook by using Python pyexcelerate.

results = [[(0.0014, 0.0052, 0.827, 10, 'Andy'), (0.0012, 0.0063, 0.906, 11, 'Julia')]]
headers = list(['PERFORMANCE', 'SPEED', 'DEGREE', 'WINS', 'NAME'])
path = "/home/yagmurs/Downloads/" + 'Performance'
workbook = pyexcelerate.Workbook()
worksheet = workbook.new_sheet('Bench')
row = 1
for enum in range(len(headers)):
    worksheet.set_cell_value(row, enum + 1, headers[enum])
for result in results:
    for line in result:
        row = row + 1
        for col in range(len(headers)):
            val = line[col]
            worksheet.set_cell_value(row, col + 1, val)
workbook.save(path + '.xlsx')

Python pyexcelerate library writing multiple sheets to same workbook?

3 Answers3