在python中的同一工作簿中创建Excel工作表 [英] Creating Excel sheets in same workbook in python

问题描述

在我的代码中需要一个建议.

Need a suggestion in my code.

我在工作簿的sheet1中有一个数据框:

I have a data frame in sheet1 of workbook:

column 1           column 2
000A0000              2
000B0000              3
000A0001              5
000B0001              1

我想要的结果:

在工作簿的表2中:

column 1           column 2
 000A0000              2
 000A0001              5

在工作簿的第3页中:

column 1           column 2
 000B0000              3
 000B0001              1

我已经完成了编码:

import pandas as pd
file="workbook.xlxs"
print(data.sheet_names)
data=data.parse("sheet1")

substrings = ['A', 'B']

T = {x: df[df['sheet1'].str.contains(x, na=False, regex=False)] for x in substrings]

for key, var in T.items():
    var.to_excel(f'{key}.xlsx', index=False)

由此，我可以创建新的工作簿.但是我需要在同一工作簿中创建新的工作表.

by this I can create new workbook. But I need to create new worksheet in same workbook.

任何建议将不胜感激.

推荐答案

要将工作表添加到同一excel文件，请使用

To add sheets to the same excel file use openpyxl module as follows:

import pandas as pd
import openpyxl

#reading the sheet1 using read_excel
df = pd.read_excel('workbook.xlsx', sheet_name='Sheet1')

#creating pandas ExcelWriter object and loading the excel file using `openpyxl`    
df_writer = pd.ExcelWriter('workbook.xlsx', engine='openpyxl')
excel = openpyxl.load_workbook('workbook.xlsx')
df_writer.book = excel

#checking string in column 1 and writing those to respective sheets in same workbook
for string in ['A','B']:
    df[df['column 1'].str.contains(string)].to_excel(df_writer,sheet_name=string)
#saving and closing writer
writer.save()
writer.close()

这篇关于在python中的同一工作簿中创建Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！