1

It is possible to get/set the colour of a range using xlwings like this:

import xlwings as xw

# Define RGB codes
green = (226, 239, 218)
red = (252, 228, 214)
grey = (242, 242, 242)

# Connect to the Excel file
wb = xw.Book(EXCEL_FILENAME)
sht = wb.sheets[EXCEL_SHEETNAME]

# Set the color of the whole range to grey
sht.range("A1:C7").color = grey
print(sht.range("A1:C7").color)  # prints (242, 242, 242)

# Set the color to some sub-ranges
sht['A1'].color = green
print(sht.range("A1").color)  # prints (226, 239, 218)
sht['B2:B6'].color = green
sht['A4:A6'].color = red
print(sht.range("A4:A6").color)  # prints (252, 228, 214)
sht['C1'].color = red
sht['C3:C7'].color = red

Getting/Setting the colour of a range works well, as long as there is only one colour in this range. But when there are several colours, it cannot handle the different codes properly

print(sht.range("A1:C7").color)  # prints (0, 0, 0)

I am trying to find a way to retrieve in a single call a pandas dataframe with the corresponding colours of range. In a similar way that it is possible to get/set all the values or even formula of a range.

# Retrieve the values of a range
print(sht.range("A1:C7").value)  
# example: [[1.0, 'b1', 3.0], [2.0, 3.0, None], [3.0, 'b3', 'c3'], [6.0, 'b4', 'c4'], [5.0, 'b5', 'c5'], [6.0, 'b6', 'c6'], [7.0, 'b7', 'c7']]

# Retrieve the formula of a range
print(sht.range("A1:C7").formula)
# example: (('1', 'b1', '=A1+2'), ('2', '=A2+1', ''), ('3', 'b3', 'c3'), ('=A3+3', 'b4', 'c4'), ('5', 'b5', 'c5'), ('6', 'b6', 'c6'), ('7', 'b7', 'c7'))

# Retrieve the formula of a range
print(sht.range("A1:C7").color)
# From our previous example: (0, 0, 0)

Is it possible to handle several colours in one call instead of having to split per continuous ranges of the same colours? It would be great to be able to get/set a list of tuples (containing the RGB codes) instead of a single one for the whole range.

Many thanks in advance!

Romain Capron
  • 1,565
  • 1
  • 18
  • 23
  • it looks like you need to loop via range and call each cell for it's colour attribute... – NoobVB Jul 05 '22 at 17:32
  • Yes but this is inefficient and slow. I am looking for a way to do it in a just two calls to Excel. A single get() and a single set(). There can be intermediate computation in Python. – Romain Capron Jul 06 '22 at 07:13
  • 1
    Color and ColorIndex only have the one value so doesn't look like it. I was going to suggest you can raise an issue on xlwings github for and see if you get an answer from the originator but I just checked and see that you already have. – moken Jul 08 '22 at 08:20
  • Yes, it seems that it not yet possible to do that efficiently with xlwings. – Romain Capron Jul 08 '22 at 08:25

0 Answers0