Read Excel Cell Fill Color

1

I have an Excel spreadsheet that has lots of data but the author used color fills to differentiate types of data and I’m trying not to have to edit the original Excel file to parse for the colored data.

Is there a node or Python script that will read an Excel cell color value?

Thanks

2

EDIT2: Remove the ex.Visible = True line to prevent Excel from opening which will make it run faster.

Hacked something together pretty quickly:

image
image.png1043×650 41.2 KB

This is largely copied from the IronPython Cookbook.

It does require excel to be installed to work, but you will be accessing everything through the Python node rather than having to do everything in Excel. The colors are returned in Decimal format (0 is black, 16777215 is white).

Code below as well for anyone who does not want to download the file:

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel

ex = Excel.ApplicationClass()   
ex.DisplayAlerts = False
filepath = IN[0]

workbook = ex.Workbooks.Open(filepath)
ws = workbook.Worksheets[1]
used_range = ws.UsedRange

values = []
for row in used_range.Rows:
	temp = []
	for cell in row.Columns:
		temp.append(cell.Interior.Color)
	values.append(temp)

OUT = values

EDIT: Here is an alternate version which outputs a list of colors and a list of cell values:

image
image.png856×770 45.6 KB 

import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel

ex = Excel.ApplicationClass()   
ex.DisplayAlerts = False
filepath = IN[0]

workbook = ex.Workbooks.Open(filepath)
ws = workbook.Worksheets[1]
used_range = ws.UsedRange

values = []
colors = []
for row in used_range.Rows:
	temp_colors = []
	temp_vals = []
	for cell in row.Columns:
		temp_colors.append(cell.Interior.Color)
		temp_vals.append(cell.Value2)
	colors.append(temp_colors)
	values.append(temp_vals)

OUT = [colors, values]

Excel Cell Color Alternate.dyn (8.2 KB)

Excel Cell Color.dyn (3.9 KB) Color Test.xlsx (8.7 KB)

7 Likes
3

Hi, what this code would look like in the CPython 3 version? Do you have any experience?