Excel import node using node

Revit
1

Hi all. Using this node from crumple it doesnt seem like I am able to import anything because its saying the custom node isnt loaded. It worked fine yesterday. I migrated the python and now Im sitting with this

image
image744×694 75.3 KB

2

Make sure you have the ironpython package installed. I haven’t really used crumple much in 2023 yet but seem to recall it worked OK last I did. Looks like this isnt my actual custom node (.Python2?) so can’t lend much support in regards to how the code behaves if lifted out and renoded.

If for some reason that doesnt work let me know and I’ll cross check it works on my side. The IP2/CP3 issue is one I expect we’ll see a lot more topics for in future. Crumple will unlikely ever become a Python3 package as its used in revit builds earlier than when Python3 is available, and by the time it isn’t I expect I’ll be mostly focused on pyrevit, but i digress.

3

Are you primarily in a newer version of Dynamo, @technitutors ?

If so, have you tried the OpenXML nodes?

1 Like
4

Good suggestion John, given they’re likely sticking with this version unless they know the tricks to downgrade scripts. The XML method is certainly faster than mine as I use the microsoft interop library in mine and this takes a while to run.

@technitutors I mainly made my Excel nodes as a workaround for the many issues the Excel nodes have in Dynamo prior to the XML nodes added in 2023. I’ve tested mine and they seem to work OK with the DSIronpython 2.7 package installed. See below the outcomes using Crumple or XML nodes in 2023. The XML nodes are quite fast, and run without the IronPython package needed:

excel crumple
excel crumple1631×788 126 KB

standard
standard1612×694 108 KB

Also as it usually appears on these threads as a request, here is the Python I’m using in Crumple for reference if people are trying to fix Excel issues in future like this pre 2023.

# Copied and pasted/modified by Gavin Crump
# Free for use (besides any terms by author below)
# BIM Guru, www.bimguru.com.au

# Based mostly on code by Cyril Poupin
# https://github.com/Cyril-Pop/IronPython-Samples/blob/master/Excel%20Interop/Import_Export_Xls_v2.0.py
# Thankyou for your ongoing contributions to the community Cyril :)

# Boilerplate text
import clr

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

clr.AddReference('System.Drawing')
import System.Drawing
from System.Drawing import *

# Try and get specific interop, if not just the general one
try:
	clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
except:
	clr.AddReference('Microsoft.Office.Interop.Excel')

from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal

## CYRIL'S CODE FROM HERE (minor edits) ##

# Excel range parsers
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]
	
## GAVIN'S PACKAGING FROM HERE ##

# Preparing input from dynamo to revit	
filePath = IN[0]
sheetName = IN[1]

# Try to import the excel file
try:
	xclObj = ExcelUtils([],filePath)
	xclData = xclObj.importXls(sheetName)
	result = "Import successful"
except:
	result = "Import unsuccessful"
	xclData = [None,False]

# Output
OUT = [xclData[0],xclData[1],result]
1 Like