How to export output data "Excel sheet " with colorized cells

[belal.hasheesh]

Hi ,
am trying to color cells of excell before exporting , i found some topics but i have an error

EXCEL Formatting - Bumblebee

A solution using a dictionary [image] [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 __in…

credit for @c.poupin
first solution working with me but i need with dictionary solution.

Excel Fill Color

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] [image] #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 …

credit for @Steven

excell.dyn (44.8 KB)

[apegaia]

Which error is it you are facing?

Edit: Basically, from what I’ve tested quickly, the python node doesn’t interprets the dictionary as a dict, but as a list (?). Dunno if it’s what you need in this case, but there is some reference about translating .net dicts to python dicts here:

Working with Dictionaries in Python

Hey everyone, i created a python node and need some help finding a way of getting what i want, i am not very familiar with dictionaries yet: I would like python to check if the keys are identical and in this case calculate the areas of the rooms contained in Dictionary 1, copy this value into a Parameter of the fist area (index 0) in Dictionary 2, for example: if key Dictionary 1 == key Dictionary 2, then calculate area Room 1931747 + Area Room 1931748 + Area Room 1931749. Then pick index 0 o…

So basically you can translate it, try to use design script methods inside of it to try to deal with it or just surrender to the simplicity of lists.

Hope this might help!

[jacob.small]

@apegaia

Edit: Basically, from what I’ve tested quickly, the python node doesn’t interprets the dictionary as a dict, but as a list (?)

Shouldn’t be the case with any modern Dynamo build. Note that that thread is from 2019 so you’re 5+ years back at this point and the software is quite different as a result. Are you seeing dictionaries convert?

[c.poupin]

hi,
I didn’t quite understand the purpose of the changes in the python code
here is the corrected dyn

excell_fixed.dyn (46.5 KB)

Python Code

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, sheet_Name, dict_key_color):
		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, sheet_Name, dict_key_color)
obj_xls.ExportXlsColor()

OUT = dict_key_color

[apegaia]

I was running it in 2022 Imperial version, so it really might be the case of being outdated. But, then, this can be the problem he is facing too, I don’t know honestly. May it be related to using IronPython2 too?

Anyway, I don’t have access to the latest builds due to some of my companies’ TI rules, so I can’t test it right now

Edit: appearently nothing to do with the version or with errors around dicts. Sorry for going the wrong direction.

[apegaia]

Seems to be working fine! What did you change in it? Cause from what I tested it wasn’t even getting the keys and values from the dicts.

[belal.hasheesh]

Thanks appreciated
Working fine

[c.poupin]

there was a lacing problem on several nodes before the python script