Need to export this script result to excel


Duct Schedule Level Wise_2022-05-22_05-21-05

Hi @suhailmuhammedk01 and welcome, zoom in Dynamo so it is clear for you the names of the nodes then export an image of the graph, and try to post the error messages of the last nodes.

Warning: Data.ExportExcel operation failed.

Probably the usual issue we face with Excel, which means you need to do an online repair.

attaching here all files

anyone please help dynamo forum

Hi I would do as Gavin mentation but if dont want that then try this one here from archilab…it had worked for me, before we fix with the repair

Screenshot 2022-05-24 184144

FYI when you type @Dynamo it sends a notification to a user, not the full forum. There is no way to ping the entire forum either, it’d be over reaching if there were.

In this case you have a few things to test.

  1. Does the path you are writing to exist? If not you may not be able to create the path at that location.
  2. Can you write to a file there manually? If not you may have a permission issue, check with your IT team.
  3. Does writing to excel at all work with a basic list of 1…10? If not you likely need to run the Online Repair tool which Gavin mentioned above. More info here: Repair an Office application
  4. Does the write out only fail with your code? If so you likely have a larger issue with your data structure. Check that closely.

As an alternative you could write to a CSV, or use a package that makes use of an XML based Excel writer, or another tool.

I’m in the process of working with microsoft interop tools and have got Import Excel working (see below), but am still working through export Excel. Hit some road blocks but began a thread to work through them here:

I’ve come to the conclusion the online repair method just isn’t feasile, nor is upgrading to Revit 2022 right now and in some cases nor is getting Bumblebee and its Python libraries onto all machines in large firms. My solution is to try to make basic import/export nodes (single sheet/2D matrix) in local Python using the interop library by Microsoft. Yes security exploits, IronPython etc. but it’s really what I need and others probably will too.

Excel Import Python code (coming to Crumple soon-ish):

# Provided by Gavin Crump
# Free for use
# BIM Guru, www.bimguru.com.au

# Code borrowed and adjusted from Bumblebee
# Free for use under lisence on github
# https://github.com/ksobon/Bumblebee

# Boilerplate text
import clr
import System

# import excel for reading
clr.AddReference("Microsoft.Office.Interop.Excel")
from Microsoft.Office.Interop import Excel
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo("en-US")
from System.Runtime.InteropServices import Marshal

# sets up Excel to run not in live mode
def SetUp(xlApp):
	# supress updates and warning pop ups
	xlApp.Visible = False
	xlApp.DisplayAlerts = False
	xlApp.ScreenUpdating = False
	return xlApp

# read excel data of a worksheet
def ReadData(ws, origin, extent, byColumn):

	rng = ws.Range[origin, extent].Value2
	if not byColumn:
		dataOut = [[] for i in range(rng.GetUpperBound(0))]
		for i in range(rng.GetLowerBound(0)-1, rng.GetUpperBound(0), 1):
			for j in range(rng.GetLowerBound(1)-1, rng.GetUpperBound(1), 1):
				dataOut[i].append(rng[i,j])
		return dataOut
	else:
		dataOut = [[] for i in range(rng.GetUpperBound(1))]
		for i in range(rng.GetLowerBound(1)-1, rng.GetUpperBound(1), 1):
			for j in range(rng.GetLowerBound(0)-1, rng.GetUpperBound(0), 1):
				dataOut[i].append(rng[j,i])
		return dataOut

# exit excel once worksheet is read
def ExitExcel(xlApp, wb, ws):
	# 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([ws,wb,xlApp])
	return None

# inputs
filePath = IN[0]
sheetName = IN[1]
byColumn = IN[2]
report = []

# try to get Excel data
try:
	xlApp = SetUp(Excel.ApplicationClass())
	xlApp.Workbooks.open(unicode(filePath))
	wb = xlApp.ActiveWorkbook
	ws = xlApp.Sheets(sheetName)
	originGot = ws.Cells(ws.UsedRange.Row, ws.UsedRange.Column)
	extentGot = ws.Cells(ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row, ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column)
	dataOut = ReadData(ws, originGot, extentGot, byColumn)
	ExitExcel(xlApp, wb, ws)
	report = "Data retrieved successfully."
except:
	dataOut = None
	report = "Data not retrieved successfully. Make sure sheet name is correct"

# return result
OUT = [dataOut,report]

Just bumping this back up to confirm @c.poupin solved this one for me by sharing a python library they made that I adjusted to suit a custom node format. The code is available over on the previously mentioned thread and I’ve got it to work on computers with repair issues.

2 Likes