Excel Fill Color

Packages
, ,
1

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
image634Ă—739 206 KB

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.html
Conditional Formatting: https://konradsobon.gitbooks.io/bumblebee-primer/content/310_conditional_formatting_overview.html

2

Have you tried xlsxwriter? Pretty powerful library:

https://xlsxwriter.readthedocs.io/index.html

5

Hello
a solution to apply a color by row with a Python dictionary and Microsoft API

# coding: utf-8 
import clr
clr.AddReference('ProtoGeometry')
from Autodesk.DesignScript.Geometry import *

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

xlDirecDown = System.Enum.Parse(Excel.XlDirection, "xlDown")
xlDirecRight = System.Enum.Parse(Excel.XlDirection, "xlToRight")


class Lst_Xls():
	def __init__(self, path, dictcolor):
		self.dictcolor = dictcolor
		self.lsta = []
		ex = Excel.ApplicationClass()
		ex.Visible = True
		lst_xls = []

		workbook = ex.Workbooks.Open(path)
		ws = workbook.Worksheets[1]
		
		##get number of Rows ##
		rowCountF = ws.Columns[1].End(xlDirecDown).Row
		##get number of Coloun  ##
		colCountF = ws.Rows[1].End(xlDirecRight).Column
		self.fullrange = ws.Range[ws.Cells(1, 1), ws.Cells(rowCountF, colCountF)]
		self.fullvalue = self.fullrange.Value2
		
				
	def setRowColorbyDict(self):	
		for row in self.fullrange.Rows:
			rangerow = row.Value2
			firstvalue = rangerow.GetValue(1,1)
			if firstvalue in self.dictcolor:
				strRgb = dictcolor.get(firstvalue)
				row.Interior.Color = eval("ColorTranslator.ToOle(Color.FromArgb(" + strRgb + "))")
				self.lsta.append([firstvalue, strRgb])				
			

#dictionary to complete
dictcolor = {"CW_CP":"192,192,255", "CW_SP":"85,168,255", "CW_GL":"0,0,255", "FC_XX":"128,0,64"}		
xlspath = IN[0]	

obj_xl_lst = Lst_Xls(xlspath, dictcolor)
obj_xl_lst.setRowColorbyDict()

OUT =  obj_xl_lst.lsta

the dictionnary “dictcolor” is to be completed

setColorXlsByContent3.py
setColorXlsByContent3.py800Ă—450 2.87 MB

2 Likes
6

You are a great programmer! Tres bien! I tried to solve that, too but I could not assign RGB value to Interior.Color section. Why does not it work to pass RGB function in Python?

7

hello @Deniz_Maral
RGB function is a function for VBA not .NET

reponse RGB Color Function
reponse RGB Color Function1373Ă—607 170 KB

8

Hello Cyril,

but here under .NET you can find it or am I wrong?

9

It’s a property not a function
an example here

10

Merci! I did not use ForeColor.RGB… that is problem…

11

All,

Thank you for your help and comments. A Coworker and I were able to get what we needed. @c.poupin your code was extremely helpful.

The modification is not beautiful but get the job done for now and what we need. I will hopefully clean up the script in the near future.

image
image2032Ă—1068 539 KB

image
image776Ă—932 58.7 KB 

#https://forum.dynamobim.com/t/excel-fill-color/46984/5
# coding: utf-8 
import clr
clr.AddReference('ProtoGeometry')
from Autodesk.DesignScript.Geometry import *

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

xlDirecDown = System.Enum.Parse(Excel.XlDirection, "xlDown")
xlDirecRight = System.Enum.Parse(Excel.XlDirection, "xlToRight")


class Lst_Xls():
	def __init__(self, path):
		self.lsta = []
		ex = Excel.ApplicationClass()
		ex.Visible = True
		lst_xls = []

		workbook = ex.Workbooks.Open(path)
		ws = workbook.Worksheets[1]
		
		#set the start row and start column
		self.fullrange = ws.Range[ws.Cells(startRow, startColumn), ws.Cells(startRow+len(colors), startColumn)]
		self.fullvalue = self.fullrange.Value2
		
				
	def setRowColorbyDict(self):
		index = 0
		for row in self.fullrange.Rows:
			if index == len(colors):
				break
			row.Interior.Color = eval("ColorTranslator.ToOle(Color.FromArgb(" + colors[index] + "))")
			index+=1
			
				
#Inputs
xlspath = IN[0]	
colors = IN[1]
startRow = IN[2]
startColumn = IN[3]

obj_xl_lst = Lst_Xls(xlspath)
obj_xl_lst.setRowColorbyDict()

OUT = str(len(colors))+' colors added to excel'
5 Likes
12

Great work, thanks for sharing!
I used it with my MEP system graphics and worked great

13

Hello, I’m trying this python script, but this is the error, any help?

image_2024_07_10T14_24_36_017Z
image_2024_07_10T14_24_36_017Z1041Ă—638 347 KB

14

This appears to be solved in your initial post. Have to tried the solution there?

1 Like