如何在excel中复制工作表并重新命名它?

Question

我有一个名为Salary.xlsx的excel文件第一个表名为week1

我想自动复制week1并重命名为week2,week3,week4，…weekn

如何在Python中使用Pandas或其他框架?

代码如下:以pd方式导入pandas

df = pd.read_excel('my_file.xlsx',sheet_name="week-1")
n = 40
for i in range(0,40):
copy week-1 then rename to week-i
add week-i to my_file.xlsx

自动复制和重命名工作表名称。

=比;Salary.xlsx有表week1, week2, week3, week4, weekn

Answer 1

应该可以:

# read week-1
df = pd.read_excel('my_file.xlsx', sheet_name='week-1')
# open your file to add sheets
my_file_writer = pd.ExcelWriter('my_file.xlsx', engine='openpyxl', mode='a')
n = 40
for i in range(2, n):
# add week-i 
df.to_excel(my_file_writer, index=False, header=True, encoding='utf-8', sheet_name=f'week-{i}')
# save file
my_file_writer.save()

Answer 2

试试这样-

df = pd.read_excel('my_file.xlsx',sheet_name="week-1")
n = 41
for i in range(2,n):
s = 'Sheet' + str(i)
df.to_excel( 'my_file.xlsx', sheet_name=s)

请正确紧跟缩进…

Answer 3

很抱歉忽略了pandas标记，但是恕我冒昧，如果这只是关于操作excel电子表格，openpyxl是更好的模块。要实现你想用openpyxml实现的目标，你可以使用Workbook.copy_worksheet:

from openpyxl import Workbook, load_workbook
wb = load_workbook("Salary.xlsx") # load worksheet
origsheet = wb["Week1"]           # Reference to source of copies
for i in range(2,53):             # Create copies 2-52
duplicate = wb.copy_worksheet(origsheet)
duplicate.title = f"Week{i}"
wb.save("Salary_new.xlsx")        # save under new name