nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Reporting

Batch Macro

Dynamic Processing

Accepted answers

AkimasaKajitani

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.

CopyExcelSheet.yxmd

All comments

AkimasaKajitani

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.

CopyExcelSheet.yxmd

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!!!

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?

You are a master !

Thank you buddy!

@AkimasaKajitani I got this error when running your code: "Failed to find virtual env named: "designerbasedtools_venv"

Do you know how to fix it? I'm using Version: 2020.4.5.12471

AkimasaKajitani

@KimLamNg

I think the problem is happening with older versions as follows.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Error-Failed-to-find-virtual-env-named-quot-designerbasetools/ta-p/751898

Please make a new workflow and paste that code to it in your designser and then run it.

Note that don't copy the Python tool from older workflow.

chanmills3

When I use this I lose the conditional formatting from my original sheet. Is there a way to preserve conditional formatting?

Elise_Prime

I have been using this for a while but am now running into issues. I believe the errors I'm seeing are because of the most recent pandas version wanting to use concat instead of append. I'm not sure how to update that portion of the code and am wondering if someone here knows how that would be done? Thanks very much for any help here!

juanigr

Hi @AkimasaKajitani,

Thanks for sharing your solution. I find this thread helpful.

I am also working on an almost similar case, but I need to copy and rename 2 specific sheets (red tabs) within the template as the name of the other sheets are static. I am also thinking of removing the old sheets once it is replaced with the new copied sheets and retain other tabs. For example: Q1 Summary and Q1 Detailed needs to be replaced with Q2 Summary and Q2 Detailed for the next cycle run. Is this also possible with python?

sample tabs.png

AkimasaKajitani

@juanigr

I think you can do it based on this code https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Duplicate-and-Rename-Excel-Sheet-while-preserving-Formatting/m-p/979253/highlight/true#M242227 .

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15898

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703