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