EXCEL Formatting - Bumblebee

Hi there,
I am trying to format my excel output based on values.
I Know bumblebee does that, and I went through a lot of posts about the same issue but couldn’t get any definite answer for my problem. Please help
This is my output

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.
So my range is not fixed, hence I can’t use Format excel from bumblebee either.

Please help. Thank you so much !! :slight_smile:

@Konrad_K_Sobon Can you please help me with this??
Thank you !!

Hello, an example using Interrop and Python to color cells by values

mark_values_xls2

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.
But when I use the Code in my existing dynamo script using the values generated through script itself, It doesn’t work. Maybe some input issue or something. I am sure, it is just minute away from the solution. Can you please have a look ? I want all the inputs as per the screenshot. It would be awesome. Please Thank you.

Hey Nick,
That’s a great suggestion. I’ll look into it. I was just hoping, somehow it formats the excel based on values than cells.
Thanks though. If nothing works, It will be my last resort.

Update - The Python Code only works if I run it as a separate Dynamo Script. It works as per my needs.
Can you please help me if I could have both scripts in a single script? Please Thank you.

@chhabrat
replace the Data.ExportExcel node by the Python Node.
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.
It’s taking List as its input not any string.


But I can’t seem to add Sheet name to your old script. is there any way I can do that?
Also, if I can have multiple inputs of color for different values?

Thank you so much !!!

A solution using a dictionary

test export and mark xls

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

2 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…?
If you choose not answer I would understand. You have helped me a lot. And I can’t thank you enough.

Thank you so much !!!