Not to overwrite while saving excel python dynamo

1

Hi there,

I have a quick question. I have a python script thanks to @c.poupin which maps out data based on 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?

image
image967×163 2.48 KB 

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

IN[3] is a worksheet name input, that should automatically create a new worksheet when you give it a new sheetname

3

Hey, I know but it is overwriting data every time I run the script.
Is there anything I am missing?

4

Hello, try this version

https://s8.gifyu.com/images/mark_values_xls_v3.gif(image larger than 4 MB)

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):
		try:
			cur_ex = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
			curr_workbook = cur_ex.ActiveWorkbook
			if curr_workbook is None or curr_workbook.FullName  != self.filepath:
				raise Exception("Error", "The xls file is not open")
			else:
				ex = cur_ex
		except Exception as ex_error:
			print(ex_error)
			ex = Excel.ApplicationClass()
		ex.Visible = True
		ex.DisplayAlerts = False
		workbook = ex.Workbooks.Open(self.filepath)
		# add new sheet
		ws = workbook.Sheets.Add()
		# delete old if exist
		exist_ws = next((w for w in workbook.Worksheets if w.Name == self.sheet_Name), None)
		if exist_ws is not None:
			exist_ws.Delete()
		# rename the new sheet
		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()
2 Likes
5

Hey Mate,
You’re a genius !! Thank you so much !!!