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