# Get Worksets from Excel

**URL:** <https://forum.dynamobim.com/t/get-worksets-from-excel/76631>\
**Category:** Revit\
**Tags:** revit, python\
**Created:** [May 16, 2022, 3:15pm UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631 "2022-05-16T15:15:53Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![paris](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/paris/32/144289_2.png) [@paris](https://forum.dynamobim.com/u/paris)\
**Post date:** [May 16, 2022, 3:15pm UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/1 "2022-05-16T15:15:53Z")

</div>

Hi @Konrad_K_Sobon , @c.poupin , @Jonathan.Olesen  
I have the same problem with version “2021.25.3”, I use an Excel version 2013 and Excel for Microsoft 365 in French language. Any thoughts?  
Excel files:  
“Microsoft Excel 2013 MSO 64 bits”  
ou  
“Microsoft Excel pour Microsoft 365 MSO 64 bit”  
###############  
Dynamo 2.3.1  
Revit 2020  
#########  
Dynamo 2.6.1  
Revit 2021

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/8/8/88b4396b1b34b9c39031527bf6b91c4fe7a04164.png)

```auto
# Copyright(c) 2016, David Mans, Konrad Sobon
# @arch_laboratory, http://archi-lab.net, http://neoarchaic.net

import clr
import sys
import System
from System import Array
from System.Collections.Generic import *

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo("en-US")
from System.Runtime.InteropServices import Marshal

pyt_path = r'C:\Program Files (x86)\IronPython 2.7\Lib'
sys.path.append(pyt_path)

from os import path
assemblies = System.AppDomain.CurrentDomain.GetAssemblies()
path1 = [a.Location for a in assemblies if 'bumblebee,' in a.FullName][0]
path2 = System.IO.Path.GetDirectoryName(path1).rsplit('\\',1)[0]
bb_path = '%s\\extra\\' %path2
sys.path.append(bb_path)
import bumblebee as bb

#The inputs to this node will be stored as a list in the IN variable.
dataEnteringNode = IN

filePath = IN[0]
runMe = IN[1]

if filePath != None:
	if filePath.GetType() == System.IO.FileInfo:
		filePath = filePath.FullName

def SetUp(xlApp):
	# supress updates and warning pop ups
	xlApp.Visible = False
	xlApp.DisplayAlerts = False
	xlApp.ScreenUpdating = False
	return xlApp

def ExitExcel(xlApp, wb):
	# clean up before exiting excel, if any COM object remains
	# unreleased then excel crashes on open following time
	def CleanUp(_list):
		if isinstance(_list, list):
			for i in _list:
				Marshal.ReleaseComObject(i)
		else:
			Marshal.ReleaseComObject(_list)
		return None
		
	xlApp.ActiveWorkbook.Close(False)
	xlApp.ScreenUpdating = True
	CleanUp([wb,xlApp])
	return None

try:
	errorReport = None
	if runMe:
		message = None
		try:
			dataOut = []
			xlApp = SetUp(Excel.ApplicationClass())
			if path.isfile(unicode(filePath)):
				xlApp.Workbooks.open(unicode(filePath))
				wb = xlApp.ActiveWorkbook
				for i in range(0, xlApp.Sheets.Count, 1):
					dataOut.append(xlApp.Sheets(i+1).Name)
			ExitExcel(xlApp, wb)
		except:
			xlApp.Quit()
			Marshal.ReleaseComObject(xlApp)
			pass
	else:
		errorReport = "Set RunMe to True."
except:
		# if error accurs anywhere in the process catch it
		import traceback
		errorReport = traceback.format_exc()

#Assign your output to the OUT variable
if errorReport == None:
	OUT = dataOut
else:
	OUT = errorReport

```

Thanks in advanvce Cheers  
[Get Worksheet Names\_D230\_R20.dyn](https://forum.dynamobim.com/uploads/short-url/ndLQVuVpVMwwchbwcRw3MVRN3gE.dyn) (17.7 KB)  
[Get Worksheet Names\_D261\_R21.dyn](https://forum.dynamobim.com/uploads/short-url/l9p6xMSQv9XKD8UzsJP7EWkaizr.dyn) (19.3 KB)  
[test\_Classeur1.xlsx](https://forum.dynamobim.com/uploads/short-url/pCYZku8cm6vK6vEQwfdPkWpG8rh.xlsx) (8.5 KB)  
[test\_Classeur2.xlsx](https://forum.dynamobim.com/uploads/short-url/wp8tplpJiP5aaHb8CUWgYMVmrLX.xlsx) (8.5 KB)

---

<div class="post-metadata">

**Author:** ![jacob.small](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/jacob.small/32/161030_2.png) [@jacob.small](https://forum.dynamobim.com/u/jacob.small)\
**Post date:** [May 16, 2022, 5:29pm UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/2 "2022-05-16T17:29:24Z")

</div>

Try running the office repair tool, which you can find on-line via a few google searches.

Also a quick reminder that office 2013 reaches the end of it’s supported lifecycle in 11 months; an upgrade now may save you the headache of unsupported software later and resolve that issue (or it could make it worse - partner with your MSFT expert to confirm).

---

<div class="post-metadata">

**Author:** ![c.poupin](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/c.poupin/32/89152_2.png) [@c.poupin](https://forum.dynamobim.com/u/c.poupin)\
**Post date:** [May 16, 2022, 8:45pm UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/3 "2022-05-16T20:45:03Z")

</div>

Hi, @paris  
obviously, ‘your Dynamo’ cannot find an assembly because it is not loaded. (bumblebee.dll)  
Check the presence of the DLL and if they are not locked

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/9/6/966e50446e81734c1138045ae4c8e0110e6bdb0c.png)

---

<div class="post-metadata">

**Author:** ![paris](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/paris/32/144289_2.png) [@paris](https://forum.dynamobim.com/u/paris)\
**Post date:** [May 16, 2022, 11:06pm UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/4 "2022-05-16T23:06:00Z")

</div>

Hi @c.poupin , thank you very much, you’re right, it works as expected with a package loaded.  
Is there any way to get the Worksheet Names of excels files from path with a python node (standalone, without .DLL) ? . Thanks in advance for your help. Have a good day. Cheers

---

<div class="post-metadata">

**Author:** ![GavinNicholls](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/gavinnicholls/32/190442_2.png) [@GavinNicholls](https://forum.dynamobim.com/u/GavinNicholls)\
**Post date:** [May 17, 2022, 3:36am UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/5 "2022-05-17T03:36:30Z")

</div>

Check out the Sparrow package which has a node in it that can read worksheets and is in Python. It uses the microsoft interop utilities which are available as long as you have Excel installed I think.

---

<div class="post-metadata">

**Author:** ![c.poupin](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/c.poupin/32/89152_2.png) [@c.poupin](https://forum.dynamobim.com/u/c.poupin)\
**Post date:** [May 17, 2022, 10:58am UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/6 "2022-05-17T10:58:57Z")

</div>

> [@paris](#):
>
> Is there any way to get the Worksheet Names of excels files from path with a python node

Hello,  
here a solution

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/e/b/ebfb368f210f7255d10b450ceb5c3e5b9fafdcd9.png)

```auto
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

```

---

<div class="post-metadata">

**Author:** ![paris](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/paris/32/144289_2.png) [@paris](https://forum.dynamobim.com/u/paris)\
**Post date:** [May 17, 2022, 5:03pm UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/7 "2022-05-17T17:03:05Z")

</div>

Hi @c.poupin thank you very much for your great code, but  
with “Microsoft Excel 2013 MSO 64 bits” (from Microsoft Office Standard 2013) I have the output values trasposed:

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/6/6/66c3e7f451216d7127c4e59c1e75994595005ef9.png)

and if I use Excel “Microsoft 365 Apps for entreprise” I have this issue:

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/b/e/be9c5ebf9d14278ee762bd638106412bb2c7274a.png)

Thanks in advance.

Cheers

---

<div class="post-metadata">

**Author:** ![paris](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/paris/32/144289_2.png) [@paris](https://forum.dynamobim.com/u/paris)\
**Post date:** [May 17, 2022, 5:06pm UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/8 "2022-05-17T17:06:40Z")

</div>

> [@GavinNicholls](#):
>
> microsoft interop utilities which are available as long as you have Excel installed I think

Thank you very much @GavinNicholls it works as expected (without DLL) !  
Have a good day. Cheers

---

<div class="post-metadata">

**Author:** ![c.poupin](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/c.poupin/32/89152_2.png) [@c.poupin](https://forum.dynamobim.com/u/c.poupin)\
**Post date:** [May 17, 2022, 7:14pm UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/9 "2022-05-17T19:14:50Z")

</div>

> [@paris](#):
>
> with “Microsoft Excel 2013 MSO 64 bits” (from Microsoft Office Standard 2013) I have the output values transposed

hi, thanks for the report, I fix the code here

> [@Get Worksets from Excel](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/6):
>
> Hello, here a solution 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 DocumentManag…

_Note_  
_unfortunately, I can’t test with Microsoft 365 currently_

---

<div class="post-metadata">

**Author:** ![paris](https://sea2.discourse-cdn.com/flex022/user_avatar/forum.dynamobim.com/paris/32/144289_2.png) [@paris](https://forum.dynamobim.com/u/paris)\
**Post date:** [May 30, 2022, 9:22am UTC](https://forum.dynamobim.com/t/get-worksets-from-excel/76631/10 "2022-05-30T09:22:54Z")

</div>

> [@c.poupin](#):
>
> ```auto
> 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
> 
> ```

Thank you @c.poupin , I still have some issue with the python node, I’ll investigate a bit more the causes…maybe it’s a bug if I use Excel 2013… Cheers

 ![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/4/f/4f65a526262fba032a57b7ac377a280dc862f4fd.png)

![image](https://us1.discourse-cdn.com/flex022/uploads/dynamobim/original/3X/d/a/daab4564c283deb5a60d77622eeb78a7dd4c38b8.png)

[test\_excel.xlsx](https://forum.dynamobim.com/uploads/short-url/aeF6FSO3fvTFuYiWOgqZEpW23B.xlsx) (8.7 KB)