Hello PyReviter,
i want to extract, better to say, trigger transactions by RevitID.
Background is i got a filled excelsheet from a client. I have the RevitID as a reference. I have to fill based on that 30 parameters (columns)
In dynamo i already solved it. I have difficulties to get my data sorted, my code so far…
import clr
import xlrd
import sys
import os, sys, datetime
from Autodesk.Revit.DB import *
from Autodesk.Revit.UI import *
from Autodesk.Revit.DB.Architecture import *
# pyRevit
from pyrevit import forms, revit, script
# Excel
clr.AddReference("Microsoft.Office.Interop.Excel")
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal
# .NET Imports
clr.AddReference('System')
from System.Collections.Generic import List
from System import Array
# List_example = List[ElementId]()
clr.AddReference('System.Drawing')
import System.Drawing
from System.Drawing import *
import time
doc = __revit__.ActiveUIDocument.Document
uidoc = __revit__.ActiveUIDocument #type: UIDocument
app = __revit__.Application # Application class
selection = uidoc.Selection # type: Selection
active_view = doc.ActiveView
active_level = active_view.GenLevel
rvt_year = int(app.VersionNumber)
PATH_SCRIPT = os.path.dirname(__file__)
# ╦ ╦╔═╗╦═╗╦╔═╗╔╗ ╦ ╔═╗╔═╗
# ╚╗╔╝╠═╣╠╦╝║╠═╣╠╩╗║ ║╣ ╚═╗
# ╚╝ ╩ ╩╩╚═╩╩ ╩╚═╝╩═╝╚═╝╚═╝ VARIABLES
#====================================================================================================
time_start = time.time()
# 🍉 pick excel
xcl_file = forms.pick_excel_file(title = "Select Excel File")
if not xcl_file:
sys.exit()
# 📃 open excel
path = xcl_file
ex = Excel.ApplicationClass()
ex.Visible = False
ex.DisplayAlerts = False
# 📖 get Workbook
workbook = ex.Workbooks.Open(filename = path)
# 📕 report
ws = workbook.Sheets[1]
table = ws.Name
# methode BIMGuru
xlDirecDown = System.Enum.Parse(Excel.XlDirection, "xlDown")
xlDirecRight = System.Enum.Parse(Excel.XlDirection, "xlToRight")
xlDirecUp = System.Enum.Parse(Excel.XlDirection, "xlUp")
xlDirecLeft = System.Enum.Parse(Excel.XlDirection, "xlToLeft")
class ExcelUtils():
def __init__(self, lstData, filepath):
self.lstData = lstData
self.filepath = filepath
def importXls(self, wsName):
ex = Excel.ApplicationClass()
ex.Visible = False
lst_xls = []
workbook = ex.Workbooks.Open(self.filepath)
try:
ws = ex.Sheets(wsName)
wsFound = True
except:
ws = workbook.Worksheets[0]
wsFound = False
##get number of Rows not empty ##
rowCountF = max(ws.Range(i).End(xlDirecUp).Row for i in
["A65536", "B65536", "C65536", "D65536", "E65536", "F65536", "G65536", "H65536"])
# other method if column A is empty
# rowCountF = ws.Range("B65536").End(xlDirecUp).Row
# rowCountF = ws.Columns[1].End(xlDirecDown).Row
##get number of Coloun not empty ##
colCountF = max(ws.Range(i).End(xlDirecLeft).Column for i in
["ZZ1", "ZZ2", "ZZ3", "ZZ4", "ZZ5", "ZZ6", "ZZ7", "ZZ8", "ZZ9"])
# other methods
# colCountF = ws.Range("ZZ9").End(xlDirecLeft).Column
# colCountF = ws.Rows[1].End(xlDirecRight).Column
self.fullrange = ws.Range[ws.Cells(1, 1), ws.Cells(rowCountF, colCountF)]
self.fullvalue = list(self.fullrange.Value2)
# split list into sublist with number of colum
n = colCountF
# close Excel
ex.Workbooks.Close()
ex.Quit()
# other proper way to make sure that you really closed and released all COM objects
if workbook is not None:
Marshal.ReleaseComObject(workbook)
if ex is not None:
Marshal.ReleaseComObject(ex)
workbook = None
ex = None
return [list(self.fullvalue[i:i + n] for i in range(0, len(self.fullvalue), n)), wsFound]
# Try to import the excel file
try:
xclObj = ExcelUtils([], xcl_file)
xclData = xclObj.importXls(table)
result = "Import successful"
except:
result = "Import unsuccessful"
xclData = [None, False]
# Output
print(xclData[0], xclData[1], result)
for raw in xclData[0]:
for i in raw:
print(i)
print(xclData[1])
sys.exit()
my dynamo script is “rocksolid” ( tested with 4 parameters)
here the link unwraped
