How to duplicate sheet in excel and rename it?-CodePudding

i have a excel file named Salary.xlsx and the first sheet named week1

I want duplicate automatically week1 and rename to week2, week3, week4, . . .weekn

How can i do that in Python with Pandas or another framework?

Code like this: import pandas as pd

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

Automatically copy and rename sheet name.

=> the Salary.xlsx have sheet week1, week2, week3, week4, weekn,

CodePudding user response：

This should work:

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

CodePudding user response：

Use 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

CodePudding user response：

Try something like this -

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)

Please follow indentation properly ...