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?
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 !!