Get Worksets from Excel

Revit
,
6

Hello,
here a solution

image
image907×412 25.3 KB

import clr
import sys
import System

#import Revit API
clr.AddReference('RevitAPI')
import Autodesk
from Autodesk.Revit.DB import *
import Autodesk.Revit.DB as DB

#import net library
from System import Array
from System.Collections.Generic import List, IList, Dictionary

#import transactionManager and DocumentManager (RevitServices is specific to Dynamo)
clr.AddReference('RevitServices')
import RevitServices
from RevitServices.Persistence import DocumentManager
from RevitServices.Transactions import TransactionManager

doc = DocumentManager.Instance.CurrentDBDocument

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' )
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal

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():
	@staticmethod			
	def GetDatas(filepath):
		ex = Excel.ApplicationClass()
		ex.Visible = False
		lst_xls = []
		workbook = ex.Workbooks.Open(filepath)
		ws = workbook.Worksheets[1]
		##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"])
		##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"])
		if rowCountF == colCountF == 1:
			fullrange = ws.Cells(1, 1)
			fullvalue = [fullrange.Value2]
		else:
			fullrange = ws.Range[ws.Cells(1, 1), ws.Cells(rowCountF, colCountF)]
			fullvalue = list(fullrange.Value2)
		print(fullvalue)
		#split list into sublist with number of colum
		n = colCountF					
		datas = list(fullvalue [i:i+n] for i in range(0, len(fullvalue ), n))
		ex.Workbooks.Close()
		ex.Quit()
		if workbook is not None:
			Marshal.ReleaseComObject(workbook)
		if ex is not None:
			Marshal.ReleaseComObject(ex)     
		workbook = None        
		ex = None
		return datas

toList = lambda x : x if hasattr(x, '__iter__') and not  isinstance(x, (str, System.String)) else [x]
#Preparing input from dynamo to revit
lstPath = toList(IN[0])
out = []
for xlspath in lstPath:
	lst_xls_Wkset_Name = ExcelUtils.GetDatas(xlspath)
	# flat
	lst_xls_Wkset_Name = sum(lst_xls_Wkset_Name, [])
	lstWkset = FilteredWorksetCollector(doc).OfKind(WorksetKind.UserWorkset).ToWorksets().FindAll(lambda x : x.Name in lst_xls_Wkset_Name)
	lstWkset = sorted(lstWkset, key = lambda x : lst_xls_Wkset_Name.index(x.Name))
	out.append([lst_xls_Wkset_Name, lstWkset])
OUT = out
3 Likes