With a little AI help I seemed to have stumbled upon a solution using COM automation and .NET reflection with explicit parameter handling to work around IronPython’s limitations with the Excel interop assemblies.
Here’s a bit of an example
from Autodesk.Revit.DB import *
import os.path as op
import System
#Fetching parameter values here . . .
#Assemble filepath using project info parameters
directory_str = r"/Detailing/02_BOM/"
Engine = JDE_path.replace("\\","/") + directory_str + JobNum + " " + JobName + " Engine.xlsm"
basefolder = op.dirname(JDE_path.replace("\\","/") + directory_str) #BOM folder for project
# Create Excel application using COM automation
try:
excel_type = System.Type.GetTypeFromProgID("Excel.Application")
ex = System.Activator.CreateInstance(excel_type)
print("Excel application created successfully")
# COM helper functions
def com_setattr(obj, name, value):
obj.GetType().InvokeMember(name,
System.Reflection.BindingFlags.SetProperty,
None, obj, System.Array[System.Object]([value]))
def com_getattr(obj, name, arg1=None, arg2=None):
if arg1 is not None:
if arg2 is not None:
# For two arguments like Item[row, col]
arg_array = System.Array[System.Object]([arg1, arg2])
else:
# For one argument like Item["Members"]
arg_array = System.Array[System.Object]([arg1])
return obj.GetType().InvokeMember(name,
System.Reflection.BindingFlags.GetProperty,
None, obj, arg_array)
else:
# For simple properties
return obj.GetType().InvokeMember(name,
System.Reflection.BindingFlags.GetProperty,
None, obj, None)
def com_invoke(obj, method, arg1=None, arg2=None, arg3=None):
args = [arg for arg in [arg1, arg2, arg3] if arg is not None]
arg_array = System.Array[System.Object](args) if args else None
return obj.GetType().InvokeMember(method,
System.Reflection.BindingFlags.InvokeMethod,
None, obj, arg_array)
# Set Excel properties
com_setattr(ex, "Visible", True)
com_setattr(ex, "DisplayAlerts", False)
print("Excel properties set")
# Open workbook
workbooks = com_getattr(ex, "Workbooks")
wb = com_invoke(workbooks, "Open", Engine)
# Get worksheets
worksheets = com_getattr(wb, "Worksheets")
MembersWS = com_getattr(worksheets, "Item", "Members")
# Get member lines count
rows = com_getattr(MembersWS, "Rows")
first_row = com_getattr(rows, "Item", 1)
value2 = com_getattr(first_row, "Value2")
MembersLines = int(value2[0,0])
for l in range(3, MembersLines + 3):
# Get cell value using COM
cells = com_getattr(MembersWS, "Cells")
id_cell = com_getattr(cells, "Item", l, 2)
id_value = com_getattr(id_cell, "Value2")
# More processing and parameter setting within Revit . . .