CODEWITHSUNDEEP
pythonxlsxwriter
krish
krish

Reputation: 21

Save 3 different datasets into 3 different sheet under same excel file

I have 3 different dataset which i want to store in 3 different sheet under same excel file. I try as below. Is there any compact way for it to reduce programming complexity?

xbook = xlsxwriter.Workbook('abcd.xlsx')
xsheet1 = xbook.add_worksheet('img_reshape')
xsheet2 = xbook.add_worksheet('column_sum')
xsheet3 = xbook.add_worksheet('row_sum')

for col,array in enumerate (dataset):
    for row,value in enumerate (array):
    xsheet1.write(row, col, value)
for col,array in enumerate (column_sum):
    for row,value in enumerate (array):
    xsheet2.write(row, col, value)
for col,array in enumerate (row_sum):
    for row,value in enumerate (array):
    xsheet3.write(row, col, value)
xbook.close()

Upvotes: 2

Views: 1774

Answers (3)

jmcnamara
jmcnamara

Reputation: 41554

I'd also recommend Pandas but if you want to do it with just XlsxWriter then you could do something like this:

import xlsxwriter

# Some sample data.
dataset = [[11, 12, 13, 14],
           [21, 22, 23, 24],
           [31, 32, 33, 34]]

column_sum = dataset
row_sum = dataset

xbook = xlsxwriter.Workbook('abcd.xlsx')
xsheet1 = xbook.add_worksheet('img_reshape')
xsheet2 = xbook.add_worksheet('column_sum')
xsheet3 = xbook.add_worksheet('row_sum')

mappings = [(dataset, xsheet1),
            (column_sum, xsheet2),
            (row_sum, xsheet3)]

for dataset, worksheet in mappings:
    for row_num, row_data in enumerate(dataset):
        for col_num, cell_data in enumerate(row_data):
            worksheet.write(row_num, col_num, cell_data)

xbook.close()

Or for the inner loop you could do this:

# Write the data.
for dataset, worksheet in mappings:
    for row_num, row_data in enumerate(dataset):
        worksheet.write_row(row_num, 0, row_data)

Upvotes: 1

patrickjlong1
patrickjlong1

Reputation: 3823

There's an easier way to do it. An example is provided in the xlsxwriter documentation (link here).
I've posted an example below that is fairly simple. I've also added three sample dataframes so that this example can be reproduced by anyone. Also there are a slew of optional parameters that can be used with dataframe.to_excel that may interest you and these are outlined in Pandas Documentation. (link here).

import pandas as pd

df_1 = pd.DataFrame({'Data_1': [10, 20, 30, ]})
df_2 = pd.DataFrame({'Data_2': [20, 30, 40]})
df_3 = pd.DataFrame({'Data_3': [30, 40, 50]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('abcd.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df_1.to_excel(writer, sheet_name='img_reshape')
df_2.to_excel(writer, sheet_name='column_sum')
df_3.to_excel(writer, sheet_name='row_sum')


writer.save()

Upvotes: 1

Eduard Ilyasov
Eduard Ilyasov

Reputation: 3308

You can use pandas (python data analysis library) for this task.

Install it via pip install pandas.

import pandas as pd
datasets = [dataset, column_sum, row_sum]
sheet_names = ['img_reshape', 'column_sum', 'row_sum']
excel_filename = 'abcd.xlsx'
with pd.ExcelWriter(excel_filename) as writer:
    for data, sheet in zip(datasets, sheet_names):
        df = pd.DataFrame(data)
        df.to_excel(writer, sheet_name=sheet, header=None)

Upvotes: 1

Related Questions