CODEWITHSUNDEEP
pythonexcelpandasopenpyxlxlsxwriter
Johnson Francis
Johnson Francis

Reputation: 271

Discrepancies on excel cell color reading and setting

I have a requirement to read the color of an excel sheet(sheet#1) cell and use that color while writing another excel sheet (sheet#2). This sheet#1 is something like a template sheet with many other settings which is used to write the sheet#2. This sheet#2 is a larger sheet with data and the columns will be colored as per the sheet#1. And this is what I tried in a nut shell.

Used openpyxl to read the cell color..

df=pd.read_excel(excel_file, sheet_name='Sheet2')

wb = load_workbook(excel_file,data_only=True)
sh = wb['Sheet2']
lst_color=[]
for i in range(2,len(df.index)+2):
    color_index = str(sh['C'+str(i)].fill.start_color.index)
    if color_index.isnumeric() :
        color_in_hex = styles.colors.COLOR_INDEX[int(color_index)][2:] # First two bytes are Alpha
    else :
        color_in_hex = color_index[2:] # First two bytes are Alpha
    lst_color.append('#' + color_in_hex) 
df.Color_Val=lst_color

print(df)

And this is what I get as the colors..

Color_Name & XLSXWrite_Value are columns based on the xlsxwriter And C column I have filled wiht corresponding colors in excel sheet. for the xlswriter, this link shows what RGB codes to be passed for the corresponding colors. https://xlsxwriter.readthedocs.io/working_with_colors.html

And then Color_val column is prepared with the above logic.

     Color_Name XLSXWriter_Value Color_Val
0      black          #000000   #FFFFFF
1       blue          #0000FF   #0070C0
2      green          #008000   #00B050
3     orange          #FF6600   #FFC000
4     purple          #800080   #7030A0
5        red          #FF0000   #FF0000
6      white          #FFFFFF   #000000
7     yellow          #FFFF00   #FFFF00

So, what we get is totally different RGB color values. (for Yellow & Red, it is same. White & black, they are reversed.) Obviously, when I write the 2nd excel sheet with these RGB values (Color_Val), It will not get the same colors.

FYI : I use xlsxwriter for writing excel sheet.

writer = pd.ExcelWriter(dataFolder + 'tmp_12_test126.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer,sheet_name='Sheet1',index=False)

# Get the xlsxwriter workbook and worksheet objects.
worksheet = writer.sheets['Sheet1']
workbook  = writer.book

# Start iterating through the columns and the rows to apply the format
for col in range(df.shape[1]):
    for row in range(df.shape[0]):
        if len(col_format[col]) > 0 :
            worksheet.write(row+1,col,df.iloc[row,col],workbook.add_format(col_format[col]))

# Finally write the file
writer.save()

This col_format list will look like something like the below.

col_format=[{},{'bg_color': '#FFFFFF', 'num_format': '@'}]

So, what can I do to fulfill my requirement.

Upvotes: 2

Views: 455

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

I don't think you need to access the fill.start_color.index property since that is rarely used to hold a color index in the xlsx format (Note 1). Instead what you probably need is the fill.start_color.rgb.

color_index = str(sh['C'+str(i)].fill.start_color.rbg)

If in doubt you can check the fill.start_color.type first. It should be 'rgb'. Also, you won't need the if color_index.isnumeric(): check anymore.

One thing to look out for is to make sure that the template cells have a solid (i.e., non-patterned) fill. If it has a pattern fill then the background/foreground (start_color/end_color) color roles are reversed. However, since you are creating the template file it should be easy to specify a solid fill.

Note 1, for completeness: The color index is used to hold the default index 64 background color in solid fills; used for compatibility with older xls files; and used for some non-cell formatting like comments.

Upvotes: 1

Related Questions