Hi there,
And I have attached my script as well.Dynamo_Design Option Excel Report & Revit Text List Tool_MK016_Cell overrides not working yet.dyn (256.9 KB)
I want to colour code my Design Option sets and so forth.
Please help. Thank you so much !!
@Konrad_K_Sobon Can you please help me with this??
Hello, an example using Interrop and Python to color cells by values
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, search_values, strRgb):
self.lstData = lstData
self.filepath = filepath
self.search_values = search_values
self.color = eval("ColorTranslator.ToOle(Color.FromArgb(" + strRgb + "))")
def ExportXlsColor(self):
ex = Excel.ApplicationClass()
ex.Visible = True
ex.DisplayAlerts = False
workbook = ex.Workbooks.Add()
ws = workbook.Worksheets[1]
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.search_values:
cellRg.Interior.Color = self.color
#
used_range = ws.UsedRange
for column in used_range.Columns:
column.AutoFit()
workbook.SaveAs(self.filepath)
file_path = IN[0]
data_values = IN[1]
search_values = IN[2]
ds_color = IN[3]
str_color = "{},{},{}".format(ds_color.Red, ds_color.Green, ds_color.Blue)
obj_xls = ExcelUtils(file_path, data_values, search_values, str_color)
obj_xls.ExportXlsColor()
3 Likes
You can also create the appropriate cell range(s) based on your values.
1 Like
Hey Mate,
Thank you so much for your input. It worked perfectly if I run it as a separate dynamo script. In which I have to define values.
Hey Nick,
Update - The Python Code only works if I run it as a separate Dynamo Script. It works as per my needs.
@chhabrat the Excel workbook must be closed beforehand
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, search_values, sheet_Name, strRgb):
self.lstData = lstData
self.filepath = filepath
self.search_values = search_values
self.sheet_Name = sheet_Name
self.color = eval("ColorTranslator.ToOle(Color.FromArgb(" + strRgb + "))")
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.search_values:
cellRg.Interior.Color = self.color
#
used_range = ws.UsedRange
for column in used_range.Columns:
column.AutoFit()
workbook.SaveAs(self.filepath)
file_path = IN[0]
data_values = IN[1]
search_values = IN[2]
ds_color = IN[3]
sheet_Name = IN[4]
str_color = "{},{},{}".format(ds_color.Red, ds_color.Green, ds_color.Blue)
obj_xls = ExcelUtils(file_path, data_values, search_values, sheet_Name, str_color)
obj_xls.ExportXlsColor()
2 Likes
Hey Cyril,
Firstly, I really appreciate your effort to maintain this community of people who wants to learn and providing your input to people who you don’t even know. I applaud that. I am very grateful.
Secondly, I am not sure why isn’t this working out for me.
Dynamo_Design Option Excel Report & Revit Text List Tool_MK016_Cell overrides not working yet.dyn (77.5 KB)
Can you point to anything which I may be missing?
Thanks mate.
Update - @c.poupin Your first python node is working perfectly.
But I can’t seem to add Sheet name to your old script. is there any way I can do that?
Thank you so much !!!
A solution using a dictionary
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()
similar topic here
Hello all,
I am trying to Fill multiple Excel Cells with a different unique color (See image below). I would like the cell to be colored rather than have the RGB value. Each line should have a unique color.
[image]
I have looked at BumbleBee but it seems to only work with conditional formatting (see the two sections below). Is there a way or a package to set the color of an individual cell?
BumbleBee Primer
Fill Style: https://konradsobon.gitbooks.io/bumblebee-primer/content/210_fill_style…
3 Likes
Hey @c.poupin Cyril,
It’s Perfect. I can’t thank you enough for your time.
You’re a Legend. Thank you so much !!!
Update -
PS It’s Overwriting the data in the workbook, is there a way, if I run the script creates another sheet instead of overwriting it? Or if the sheet name is already there it overwrites that…?
Thank you so much !!!
1 Like
