Populating excel template with data from another excel and locking tab except some cells

SebLord

Hi All,

I need some help recreating some a script i have in Python in Alteryx, 

I don't believe it can be done using the python node as contains packages not supported in Alteryx. 

This is a simplified version of the problem but i believe if i can get this to work then i can build my solution around it 

the aim is to  

1. Loads input data from an Excel file (InputDoc.xlsx) containing entities and associated values.
2. Loads a template Excel file (Template.xlsx) which serves as the base for each tax pack.
3. Creates a timestamped folder named Entity _Files [Date Time] in the same directory as the template to store the output files.
4. Loops through each row in the input data:
   • Inserts the entity name into cell A1 of the template.
   • Populates values into cells C4 to C8.
   • Adds a formula in C9 to sum the values.
   • Unlocks cells D4 to D8 for editing.
   • Protects the sheet with a password.
   • Saves the customized file as [Entity].xlsx in the output folder.
5. Prints a confirmation message with the location of the saved files.

Python code

import pandas as pd

import openpyxl

from openpyxl.styles import Protection

import os

from datetime import datetime

# Define file paths

data_file = r'C:\Users\Location\InputDoc.xlsx'

template_file = r'C:\Users\Location\Template.xlsx'

# Load the input data

data_df = pd.read_excel(data_file)

# Create a single timestamped output folder

timestamp = datetime.now().strftime('%Y-%m-%d %H-%M-%S')

base_dir = os.path.dirname(os.path.abspath(template_file))

output_dir = os.path.join(base_dir, f" Entity _Files {timestamp}")

os.makedirs(output_dir, exist_ok=True)

# Loop through each row in the input data

for _, row in data_df.iterrows():

    entity = row['Entity']

    if pd.isna(entity) or str(entity).strip() == '':

        continue  # Skip blank entities

    # Load a fresh copy of the template

    template_wb = openpyxl.load_workbook(template_file)

    template_ws = template_wb.active

    # Populate Entity in A1

    template_ws['A1'].value = entity

    # Populate values from the row

    template_ws['C4'].value = row.get('Value1', 0)

    template_ws['C5'].value = row.get('Value2', 0)

    template_ws['C6'].value = row.get('Value3', 0)

    template_ws['C7'].value = row.get('Value4', 0)

    template_ws['C8'].value = row.get('Value5', 0)

    # Set sum formula in C9

    template_ws['C9'].value = '=SUM(C4:C8)'

    # Unlock cells D4 to D8

    for i in range(4, 9):

        cell = template_ws[f'D{i}']

        cell.protection = Protection(locked=False)

    # Protect the sheet

    template_ws.protection.password = 'LETMEIN'

    template_ws.protection.sheet = True

    # Save the new file in the single output folder

    output_file = os.path.join(output_dir, f"{entity}.xlsx")

    template_wb.save(output_file)

print(f" Processing complete. Files saved to:\n{output_dir}")

Any questions let me know 

Thanks Seb 

InputDoc.xlsx

Template.xlsx

Gaurav_Dhama_

I would limit using python to maybe just the protect cells.

You can create a folder using render tool. Check this thread. Solved: Re: Output issue - Alteryx Community

Then use blob to move your desired file to the folder (replace the file that you created using render tool, by keeping the name same). check this thread: Solved: Re: Using Alteryx to copy pdfs from one folder to... - Alteryx Community

Add values formula using basic data output tool in Alteryx into these files.

Use python only for copying data to the protected cells.

This process will reduce your dependency on python significantly and effectively making your workflow faster.