Duplicate and Rename Excel Sheet, while preserving Formatting, using Alteryx

[calebhowellks]

Hello all - 

I have a scenario where I have an Excel template sheet, which contains some formulas, formatting, and specific sizing. Nothing too crazy. I need to fill out several hundred of these templates for a downstream process. For sake of argument today, let's assume that the formatting needs to remain unchanged. 

My initial plan was to write specific values to specific cells (ie, write out an employee name to cell A2 and preserving formatting) by using the new Excel output functionality included in the 2020.1+ releases. This worked great. 

The issue arises when I want to have each sheet in the Excel file dynamically renamed to a different employee name. I have dynamically written out sheet names before many times, but I have never had to do this with existing formatting on that sheet, meaning that I can't just create it out of nothing. Therefore, I need to:

1) Dynamically copy & rename sheets within an Excel file, while preserving formatting.

2) Output data to specific cells on specific sheets. 

Thanks in advance! Looking forward to seeing what you guys think I should do.  

[AkimasaKajitani · Accepted solution]

Hi @calebhowellks ,

I don't know how to copy the sheets by only Alteryx tools.

So I go to the route to use Python tool.

I used the python package "openpyxl". you need run this function only one time. After that, you can comment out this function.

Package.installPackages("openpyxl")

Python code is as follows.

from ayx import Alteryx
import openpyxl
import pandas as pd

#read data from AYX
df1 = Alteryx.read("#1")
templatepath = df1.iat[0,df1.columns.get_loc('TemplatePath')]
copysheet = df1.iat[0,df1.columns.get_loc('TemplateSheetName')]
savepath = df1.iat[0,df1.columns.get_loc('NewExcelPath')]
newsheetname = Alteryx.read("#2")

#prepare the output dataframe
df=pd.DataFrame(index=[],columns=['SaveExcelPath','NewSheetName'])

#Open workbook
workbook = openpyxl.load_workbook(filename=templatepath)

for shtname in newsheetname['NewName']:
    #Copy worksheet
    worksheet = workbook.copy_worksheet(workbook[copysheet])
    #change name from the sheetName to the NewSheetName
    worksheet.title = shtname
    print(shtname)
    print(savepath)
    df = df.append(pd.Series([savepath,shtname],index=df.columns),ignore_index=True)
    
#Save workbook
workbook.save(savepath)
#Close ExcelFile
workbook.close()
print(df)
Alteryx.write(df,1)

Input data is ...

 - Template Excel File Path as TemplatePath

 - Template Sheet Name as TemplateSheetName

 - Save Excel Path as NewExcelPath

And sheet names that you want to copy.

Finally, you can get the copied file and the bellow output from #1 output of Python tool.

Please check the attached workflow. And if you have any questions, let me know.

[calebhowellks]

Thanks @AkimasaKajitani!

Great idea. This could be turned into a Batch Macro and very useful. Thanks for the code!

***

Update @AkimasaKajitani - 

Turned your workflow into a simple batch macro. Added a remove sheet function to your script to remove the original template sheet from the new files. Works like a charm.

Thanks so much!!!

[AkimasaKajitani]

Hi @calebhowellks ,

Adding the function of removing template sheet is a good idea!

I assumed that the workflow is used as Batch macro, but I didn't know the detail needs, so I showed the basic workflow.

I'm glad it worked well!

[187]

Hi @AkimasaKajitani 

I tried with your workflow it copies all excel sheets into a new file.

Can it only copy the "TemplateSheetName" and rename to the "NewName" instead of copying all the other sheets?

[AkimasaKajitani]

Hi @187 

In that case, a simpler code is possible.

from ayx import Alteryx
import openpyxl
import pandas as pd

#read data from AYX
df1 = Alteryx.read("#1")
templatepath = df1.iat[0,df1.columns.get_loc('TemplatePath')]
copysheet = df1.iat[0,df1.columns.get_loc('TemplateSheetName')]
savepath = df1.iat[0,df1.columns.get_loc('NewExcelPath')]
newsheetname = df1.iat[0,df1.columns.get_loc('NewSheetName')]

#prepare the output dataframe
df=pd.DataFrame(index=[],columns=['SaveExcelPath','NewSheetName'])

#Open workbook
workbook = openpyxl.load_workbook(filename=templatepath)

#Copy worksheet
worksheet = workbook.copy_worksheet(workbook[copysheet])
#change name from the sheetName to the NewSheetName
worksheet.title = newsheetname
    
df = df.append(pd.Series([savepath,newsheetname],index=df.columns),ignore_index=True)
    
#Save workbook
workbook.save(savepath)
#Close ExcelFile
workbook.close()
print(df)
Alteryx.write(df,1)

[187]

Hi @AkimasaKajitani 

Thanks for the reply I tried this, yes it copied the sheet and rename it but all other sheet from the template excel file are all copied to the destination file as well, is that possible that i can ONLY copy one sheet ?

[AkimasaKajitani]

Hi @187 

The Copy method can not copy the worksheet between different workbook, so we need to copy and delete(remove) the unnecessary sheets.

To delete them, use the following code

for sheetname in workbook.sheetnames:
    if sheetname != newsheetname:
        workbook.remove(workbook[sheetname])

After appending this code, full code is as follows.

from ayx import Alteryx
import openpyxl
import pandas as pd

#read data from AYX
df1 = Alteryx.read("#1")
templatepath = df1.iat[0,df1.columns.get_loc('TemplatePath')]
copysheet = df1.iat[0,df1.columns.get_loc('TemplateSheetName')]
savepath = df1.iat[0,df1.columns.get_loc('NewExcelPath')]
newsheetname = df1.iat[0,df1.columns.get_loc('NewSheetName')]

#prepare the output dataframe
df=pd.DataFrame(index=[],columns=['SaveExcelPath','NewSheetName'])

#Open workbook
workbook = openpyxl.load_workbook(filename=templatepath)

#Copy worksheet
worksheet = workbook.copy_worksheet(workbook[copysheet])
#change name from the sheetName to the NewSheetName
worksheet.title = newsheetname

for sheetname in workbook.sheetnames:
    if sheetname != newsheetname:
        workbook.remove(workbook[sheetname])

df = df.append(pd.Series([savepath,newsheetname],index=df.columns),ignore_index=True)
    
#Save workbook
workbook.save(savepath)
#Close ExcelFile
workbook.close()
print(df)
Alteryx.write(df,1)

[187]

@AkimasaKajitani 

this is great, perfect!  Can I ask more question how can we copy sheet cross file? it seems that this solution only work for copy sheet within one excel file, is that possible i can copy sheet to another workbook? 

[AkimasaKajitani]

@187 

Sorry the late reply.

I use the openpyxl in this code. But the openpyxl doesn't have the function which copy from one Excel file to another Excel file. If you need that function, you have to use the pywin32 package. It need to be installed Excel application. The code is as follows.

from ayx import Package
Package.installPackages(['pywin32'])

from ayx import Alteryx
from win32com.client import Dispatch

df1 = Alteryx.read("#1")

CopyFromPath = df1.iat[0,df1.columns.get_loc('CopyFromPath')]
CopySheetName = df1.iat[0,df1.columns.get_loc('CopyFromSheet')]
NewPath = df1.iat[0,df1.columns.get_loc('NewFileExcelPath')]

xl = Dispatch("Excel.Application")
xl.Visible = True  # You can remove this line if you don't want the Excel application to be visible

wbOld = xl.Workbooks.Open(Filename=CopyFromPath)
wbNew = xl.Workbooks.Open(Filename=NewPath)

wsOld = wbOld.Worksheets(CopySheetName)
wsOld.Copy(Before=wbNew.Worksheets(1))

wbNew.Close(SaveChanges=True)
xl.Quit()