Need to export this script result to excel

[GavinCrump]

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:

Export data to Excel using Python/Microsoft Interop

I’ve been trying to pick apart the methods and logic used to export a basic 2D matrix of data to a worksheet in an existing file today. I managed to work with some code from Bumblebee to get importing to work consistently, but have been hitting a lot of errors with exporting of data. I know part of this relates to how COM objects are released and the XlApp is quit, as files are sometimes left in a readonly state until I restart, but generally the warnings indicate I’m also getting something wro…

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]