Excel create exact copy of template (openpyxl?)

Hello dear excel lovers,

I aim to create “exact” copies of a template (roombook) sheet in a new excel file multiple times and then fill those created templates with values form the revit model.

Latter steps are absolutly possibly and i also almost succed in creating “exact” copies of the desired excel template. The only thing missing is the row/cell size.

template:


result:

i work with openpyxl, code below. To my knowledge openpyxl can not copy cell size. At the same time i need this to be possible on PCs without an excel installation.

import os
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import NamedStyle

def copy_style(source_cell, target_cell):
    target_cell.font = source_cell.font.copy()
    target_cell.border = source_cell.border.copy()
    target_cell.fill = source_cell.fill.copy()
    target_cell.number_format = source_cell.number_format
    target_cell.protection = source_cell.protection.copy()
    target_cell.alignment = source_cell.alignment.copy()

def create_template_sheet(template_wb, sheet_name, placeholders_mapping):
    # Create a new sheet
    new_sheet = template_wb.create_sheet(title=sheet_name)

    # Identify the name of the first sheet in the template workbook
    first_sheet_name = template_wb.sheetnames[0]
    
    # Copy styles from the first sheet
    source_sheet = template_wb[first_sheet_name]
    for row_num, row in enumerate(source_sheet.iter_rows(min_row=1), start=1):
        for col_num, source_cell in enumerate(row, start=1):
            target_cell = new_sheet.cell(row=row_num, column=col_num, value=source_cell.value)
            copy_style(source_cell, target_cell)

    # Iterate through each cell in the new sheet
    for row_num, row in enumerate(new_sheet.iter_rows(min_row=1), start=1):
        for col_num, template_cell in enumerate(row, start=1):
            # Check if the cell value contains a placeholder
            template_cell_value = str(template_cell.value)
            for placeholder, data in placeholders_mapping.items():
                if placeholder in template_cell_value:
                    # Replace the placeholder with the actual data
                    template_cell.value = template_cell_value.replace(placeholder, str(data))

    return new_sheet

# Load the template workbook
template_path = IN[0]
template_wb = openpyxl.load_workbook(template_path)

# Example data for placeholders
room_data1 = {'%ROOM_NAME%': 'Living Room', '%ROOM_NUMBER%': '101', '%LEVEL%': '1'}
room_data2 = {'%ROOM_NAME%': 'Bedroom', '%ROOM_NUMBER%': '202', '%LEVEL%': '2'}

# Create sheets in the template workbook based on placeholders
create_template_sheet(template_wb, "Sheet1", room_data1)
create_template_sheet(template_wb, "Sheet2", room_data2)

# Save the modified template workbook
output_folder = IN[1] 
os.makedirs(output_folder, exist_ok=True)
template_wb.save(os.path.join(output_folder, "ModifiedTemplateWorkbook.xlsx"))

# Print a message indicating success
OUT = "Modified template workbook created successfully!"

i tried to implement ideas here: [Stack Overflow] [python 3.x - How to Copy Row Height and Column Width From one Excel File to Another - Stack Overflow] but could not make it work.
like this in the def copy_style:

   # Copy column width
    target_cell.column_dimensions[target_cell.column_letter].width = source_cell.column_dimensions[source_cell.column_letter].width

    # Copy row height
    target_cell.row_dimensions[target_cell.row].height = source_cell.row_dimensions[source_cell.row].height

Any solutions?

Excuse me if this is to excel specific.

Edit: Added link to Stack Overflow

You might want to consider looking into the OpenXML SDK then, as management of OpenPyXl can be problematic for installation in a Dynamo context. Alternatively you can write out to a CSV and link that into a copy of a template excel file programmatically, which may be the fastest path forward that maintains the scope of data.

Hi;
an example of copying a sheet to another workbook (sheet list) using openxml
tested with Ipy3 + DynamoSandbox 3.1 (.Net8)


import sys
import clr
import System

clr.AddReference("DocumentFormat.OpenXml")
from DocumentFormat.OpenXml.Packaging import SpreadsheetDocument, WorkbookPart
from DocumentFormat.OpenXml.Spreadsheet import Sheets, Sheet, Workbook
from DocumentFormat.OpenXml import UInt32Value, StringValue

def copy_worksheet2(source_file_path, sheet_name, new_file_path, lst_out_sheet_name):
    # Open the source workbook
    with SpreadsheetDocument.Open(source_file_path, False) as src:
        src_workbook_part = src.WorkbookPart
        src_sheet = next((s for s in src_workbook_part.Workbook.Sheets if s.Name.Value == sheet_name), None)
        
        if src_sheet is None:
            raise ValueError("Sheet with the specified name does not exist in the source workbook.")
        # Create a new workbook
        with SpreadsheetDocument.Create(new_file_path, src.DocumentType) as dest:
            # Copy workbook parts (styles, shared strings, etc.) to new workbook
            dest.AddWorkbookPart()
            dest.WorkbookPart.Workbook = Workbook()
            dest.WorkbookPart.Workbook.Sheets = Sheets()
            for idx, sheet_name in enumerate(lst_out_sheet_name):
                # Clone worksheet part
                src_worksheet_part = src_workbook_part.GetPartById(src_sheet.Id)
                cloned_worksheet_part = dest.WorkbookPart.AddPart(src_worksheet_part)
                
                # Add cloned sheet to new workbook
                cloned_sheet = Sheet()
                cloned_sheet.Id = dest.WorkbookPart.GetIdOfPart(cloned_worksheet_part)
                cloned_sheet.SheetId = UInt32Value(idx + 1) 
                cloned_sheet.Name = sheet_name 
                dest.WorkbookPart.Workbook.Sheets.AppendChild(cloned_sheet)
                
                # Ensure other parts like styles and shared strings are also cloned
                if src_workbook_part.WorkbookStylesPart is not None and idx == 0:
                    dest.WorkbookPart.AddPart(src_workbook_part.WorkbookStylesPart)
                    
                if src_workbook_part.SharedStringTablePart is not None and idx == 0:
                    dest.WorkbookPart.AddPart(src_workbook_part.SharedStringTablePart)
                
            dest.WorkbookPart.Workbook.Save()

# Example usage
source_file_path = IN[0]
new_file_path = IN[1]
sheet_name = IN[2]
lst_out_sheet_name = IN[3]

copy_worksheet2(source_file_path, sheet_name, new_file_path, lst_out_sheet_name)

OUT = new_file_path

to write datas you can find some examples on web

2 Likes

Sorry for the late reply. I am in the middle of moving and time is in short supply (and internet access).

Great resources, thank you for that.

Can you provide me some way of checking if this is doable in dynamo core 2.19.3.6394?
Because i was not able to bring this to work.
Excuse my ignorant thinking, but i was of the belief that this is the most advanced version of dynamo one could get with revit 2024.

This is a Dynamo Sandbox version - which i know to little about. I believe it is desynchronized from revit and therefore not able to acess elements.

it’s works with all python engines in Revit 2024 (Dynamo Core 2.19)
just need to add some cast and explicit conversions with CPython3/PythonNet

import sys
import clr
import System

clr.AddReference("DocumentFormat.OpenXml")
from DocumentFormat.OpenXml.Packaging import SpreadsheetDocument, WorkbookPart
from DocumentFormat.OpenXml.Spreadsheet import Sheets, Sheet, Workbook
from DocumentFormat.OpenXml import UInt32Value, StringValue

def copy_worksheet2(source_file_path, sheet_name, new_file_path, lst_out_sheet_name):
    # Open the source workbook
    with SpreadsheetDocument.Open(source_file_path, False) as src:
        src_workbook_part = src.WorkbookPart
        src_sheet = next((s for s in src_workbook_part.Workbook.Sheets if s.Name.Value == sheet_name), None)
        
        if src_sheet is None:
            raise ValueError("Sheet with the specified name does not exist in the source workbook.")
        # Create a new workbook
        with SpreadsheetDocument.Create(new_file_path, src.DocumentType) as dest:
            # Copy workbook parts (styles, shared strings, etc.) to new workbook
            dest.AddWorkbookPart()
            dest.WorkbookPart.Workbook = Workbook()
            dest.WorkbookPart.Workbook.Sheets = Sheets()
            for idx, sheet_name in enumerate(lst_out_sheet_name):
                # Clone worksheet part
                src_worksheet_part = src_workbook_part.GetPartById(src_sheet.Id.Value)
                cloned_worksheet_part = dest.WorkbookPart.AddPart(src_worksheet_part)
                
                # Add cloned sheet to new workbook
                cloned_sheet = Sheet()
                cloned_sheet.Id = StringValue(dest.WorkbookPart.GetIdOfPart(cloned_worksheet_part))
                cloned_sheet.SheetId = UInt32Value(idx + 1) 
                cloned_sheet.Name = StringValue(sheet_name)
                dest.WorkbookPart.Workbook.Sheets.AppendChild(cloned_sheet)
                
                # Ensure other parts like styles and shared strings are also cloned
                if src_workbook_part.WorkbookStylesPart is not None and idx == 0:
                    dest.WorkbookPart.AddPart(src_workbook_part.WorkbookStylesPart)
                    
                if src_workbook_part.SharedStringTablePart is not None and idx == 0:
                    dest.WorkbookPart.AddPart(src_workbook_part.SharedStringTablePart)
                
            dest.WorkbookPart.Workbook.Save()

# Example usage
source_file_path = IN[0]
new_file_path = IN[1]
sheet_name = IN[2]
lst_out_sheet_name = IN[3]

copy_worksheet2(source_file_path, sheet_name, new_file_path, lst_out_sheet_name)

OUT = new_file_path
1 Like

Thank you Cyril,

suddenly it works like a charm.
I actually tried to convert cloned_sheet.Id to StringValue.
Must have implemented it wrong.

Again thank you for your time

1 Like