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
# 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
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).
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
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
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.
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
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:
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
