Python Code for Excel new sheet within a workbook

Revit
, ,
1

Hi there,

I have a quick question. I have a python script which maps out data based on a few values and colour code it in excel through Dynamo.
But the issue is, It is overwriting the existing information already in the excel doc.
I am trying to Audit 100s of file and I need everything in one excel doc.

Let me explain a bit better
I Audit one revit file and it spits out all the information into the excel doc as per the name of the revit file. Then, I move on to another revit file, and I repeat but I don’t want the script to overwrite the existing information, rather I want it to create another sheet within the workbook.
I tried workbook.add method but it didn’t work because maybe I didn’t do it right.

Can someone please help me do that?

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

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


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


class ExcelUtils():
	def __init__(self, filepath, lstData, dict_key_color, sheet_Name):
		self.lstData = lstData
		self.filepath = filepath
		self.dict_key_color = dict_key_color
		self.sheet_Name = sheet_Name
		
		
	def ExportXlsColor(self):
		ex = Excel.ApplicationClass()
		ex.Visible = True
		ex.DisplayAlerts = False
		workbook = ex.Workbooks.Add()
		ws = workbook.Worksheets[1]	
		ws.Name = self.sheet_Name 
		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
		# color cells 
		for cellRg in xlrange:
			if cellRg.Value2 in self.dict_key_color:
				cellRg.Interior.Color = self.dict_key_color.get(cellRg.Value2 )
		#
		used_range = ws.UsedRange	
		for column in used_range.Columns:
			column.AutoFit()
		workbook.SaveAs(self.filepath)

def convert_to_OLE(ds_color):
	strRgb = "{},{},{}".format(ds_color.Red, ds_color.Green, ds_color.Blue) 
	return eval("ColorTranslator.ToOle(Color.FromArgb(" + strRgb + "))")
	
file_path = IN[0]
data_values = IN[1]
dict_key_color = {key_ : convert_to_OLE(ds_color) for key_, ds_color in zip(IN[2].Keys, IN[2].Values)}
sheet_Name = IN[3]

obj_xls = ExcelUtils(file_path, data_values, dict_key_color, sheet_Name)
obj_xls.ExportXlsColor()

Thank you so much !!

2

I haven’t messed with the MS Office stuff, but a quick search brought me here:

Looks like you need to use workbook.Worksheets.Add()