Excel create exact copy of template (openpyxl?)

Developers
5

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

image
image1622×658 145 KB

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