在同一个excel-fi下将3个不同的数据集保存到3个不同的表中

Question

我有3个不同的数据集，我想存储在3个不同的表在同一个excel文件。我尝试如下。有没有什么简洁的方法可以降低编程的复杂性？在

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()

Answer 1

您可以将pandas（python数据分析库）用于此任务。在

通过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)

Answer 2

有更简单的方法。xlsxwriter文档（链接here）中提供了一个示例。
我在下面贴了一个很简单的例子。我还添加了三个示例数据帧，这样任何人都可以复制这个示例。还有一系列可选参数可用于dataframe.to\u excel你可能会感兴趣，这些在熊猫文档中有概述。（链接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()

Answer 3

我也推荐熊猫，但是如果你想用XlsxWriter来做，你可以这样做：

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()

或者对于内部循环，您可以这样做：

^{pr2}$