Export data to Excel using Python/Microsoft Interop

Revit
, ,
10

Thanks Cyril, this will be very helpful for both learning and potentially integrating into a set of nodes. I’ll look into alternatives such as openpyxml in the short-mid term as well for sure.

Appreciate your tips and speedy replies everyone!

I have tested it on a machine with repair issues and pleased to report it works. The stitched code from the Python script in Dynamo is below if anyone needs to use it. Beautifully written and easy to use/work with as always Cyril - have just begun making classes myself and really appreciate how you put these examples together:

python
python1359×399 41 KB 

# Copied and pasted/modified by Gavin Crump
# Based mostly on code by Cyril Poupin
# https://github.com/Cyril-Pop/IronPython-Samples/blob/master/Excel%20Interop/Import_Export_Xls_v2.0.py

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

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 exportXls(self, wsName):
		
		ex = Excel.ApplicationClass()
		ex.Visible = True
		ex.DisplayAlerts = False
		workbook = ex.Workbooks.Add()
		workbook.SaveAs(self.filepath)
		ws = workbook.Worksheets[1]
		ws.Name = wsName
		nbr_row = len(self.lstData)
		nbr_colum = len(self.lstData[0])
		xlrange  = ws.Range[ws.Cells(1, 1), ws.Cells(nbr_row, nbr_colum)]
		a = Array.CreateInstance(object, nbr_row, nbr_colum)
		for indexR, row in enumerate(self.lstData):
			for indexC , value in  enumerate(row):
				a[indexR,indexC] = value
				
		#copy Array in range			
		xlrange.Value2 = a		
		used_range = ws.UsedRange	
		for column in used_range.Columns:
			column.AutoFit()
		# apply style
		missing = System.Type.Missing
		try:
			currentWs.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlrange, missing, Excel.XlYesNoGuess.xlGuess, missing).Name = "WFTableStyle"
			currentWs.ListObjects["WFTableStyle"].TableStyle = "TableStyleMedium6"
		except:pass

# Preparing input from dynamo to revit
filepath = IN[0]
sheetName = IN[1]
dataMatrix = IN[2]

# Try export the excel file
try:
	classTry = ExcelUtils(dataMatrix,filepath)
	classTry.exportXls(sheetName)
	OUT = [filepath,"Export successful"]
except:
	OUT = [None,"Export unsuccessful"]
5 Likes