Adding a background color to Cell OpenPyXL

user_5351192 0
Viewed 49971

I'm trying to make it where my code gives off a Green background on cell when it is "Present" and a Red background on cell if it is "Absent". Here is my code.

ws1.cell(column=1, row=t, value="%s" % blue_student_list)
if (student_check(i)):
    ws1.cell(column=2, row=t, value="%s" % "Present")
else:
    ws1.cell(column=2, row=t, value="%s" % "Absent")

This code works flawlessly, I'm just wondering how I can add in the background color behind the cell.

openpyxl excel python
5 Answers

With openpyxl 2.5.3, the code above does not work.

after trying, following code worked:

from openpyxl.styles import PatternFill
sheet['A1'].fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type = "solid")
user_16162630

As @Charlie Clark (co-author of openpyxl) suggests, Conditional formatting might be a better way to go. More information in the official doc

If you want to change the background color, from more recent versions, keyword bgcolor seems not to work (in my case, it the color of the cell ends up black).

Instead, you can use start_color or fgColor. For example, both solutions work:

from openpyxl.styles import PatternFill
from openpyxl.styles.colors import YELLOW

sheet['A1'].fill = PatternFill(start_color="FFC7CE", fill_type = "solid")
sheet['A1'].fill = PatternFill(fgColor=YELLOW, fill_type = "solid")
user_16034800

This is for openpyxl V 2.6 and above


from openpyxl.styles import PatternFill

for col_range in range(1, 12):
    cell_title = sheet.cell(1, col_range)
    cell_title.fill = PatternFill(start_color="8a2be2", end_color="8a2be2", fill_type="solid")

This will colour the cells from A1 to K1

user_30382080

I had great difficulty copying the background color of cells (from a tab in one book to a tab in another...)

After a lot of attempted workarounds, I found this to work:

 from openpyxl.styles import PatternFill
 from openpyxl.styles.colors import COLOR_INDEX
 from copy import copy as shallow_copy, deepcopy as deep_copy
 # .
 # .
 # .
 if from_cell.has_style:
    if isinstance(from_cell.fill.start_color.index, int):
       start_color=COLOR_INDEX[from_cell.fill.start_color.index]

    else: # _sometimes_ a string...
          start_color=from_cell.fill.start_color.index

    end_color=start_color
    to_cell.fill=shallow_copy(PatternFill(fill_type=from_cell.fill.fill_type,
                                          start_color=start_color,
                                          end_color=end_color,
                                          patternType=from_cell.fill.patternType
                                 )
                     )

FYI: openpyxl is a great product; I don't think many developers realize what's possible using it. I will be supporting the project.

I think perhaps a number of developers mistakenly think it's anti-open source; but xlsx documents ARE also rendered by LibreOffice...

user_62212070
Related