Fill cells with colors using openpyxl?

Question

I am currently using openpyxl v2.2.2 for Python 2.7 and i wanted to set colors to cells. I have used the following imports

import openpyxl,
from openpyxl import Workbook
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.cell import Cell

and the following is the code I tried using:

wb = openpyxl.Workbook()
ws = wb.active

redFill = PatternFill(start_color='FFFF0000',
                   end_color='FFFF0000',
                   fill_type='solid')

ws['A1'].style = redFill

but I get the following error:

Traceback (most recent call last)
  self.font = value.font.copy()
AttributeError: 'PatternFill' object has no attribute 'font'

Any idea on how to set cell A1 (or any other cells) with colors using openpyxl?

Could you provide the full trace back – The6thSense May 27 '15 at 13:45 — The6thSense, May 27 '15 at 13:45

score 75 · Accepted Answer · edited Jan 21 '18 at 21:41

75

I believe the issue is that you're trying to assign a fill object to a style.

ws['A1'].fill = redFill should work fine.

edited Jan 21 '18 at 21:41

Brent Writes Code

19,075
7
52
56

answered May 27 '15 at 14:57

Charlie Clark

18,477
4
49
55

4

Thanks Charlie. I'm quite new to Python and the openpyxl docs were not clear about how to apply the fill to a cell. Your answer has solved the problem. – Ahmed Rashad May 28 '15 at 08:12
4

Is it possible to apply this to a selection of columns, IE A1:A5? – Callam Delaney May 07 '19 at 23:07
@CallamDelaney see my answer https://stackoverflow.com/a/66074706/4094231 – Umair Ayub Feb 06 '21 at 07:55

ldrg · Answer 2 · 2017-10-20T16:37:17.550

17

The API for styles changed once again. What worked for me was

my_red = openpyxl.styles.colors.Color(rgb='00FF0000')
my_fill = openpyxl.styles.fills.PatternFill(patternType='solid', fgColor=my_red)
cell.fill = my_fill

Color is an alpha RGB hex color. You can pass it in as 'rrggbb' with a default alpha of 00 or specify the alpha with 'aarrggbb'. A bunch of colors are defined as constants in openpyxl.styles.colors if you need to grab one quickly.

edited Oct 20 '17 at 16:37

answered Sep 07 '17 at 19:04

ldrg

4,150
4
43
52

1

my_fill = openpyxl.styles.fills.PatternFill(patternType='solid', fgColor=my_red) – Gourav Chawla Oct 20 '17 at 13:34

score 9 · Answer 3 · edited Sep 09 '16 at 08:23

9

This worked for me. They changed things and most of the help you see on the internet is for older versions of the openpyxl library from what I am seeing.

# Change background color 
xls_cell.style = Style(fill=PatternFill(patternType='solid',
                                        fill_type='solid', 
                                        fgColor=Color('C4C4C4')))

edited Sep 09 '16 at 08:23

jmcnamara

38,196
6
90
108

answered Sep 08 '16 at 21:23

M T Head

1,085
9
13

3

Something to be careful about. If you make multiple changes to the cell previous changes are wiped away with the most recent changes. If you want to make say 6 changes, you have to do all 6 in the same command. You can not change them one by one. If you do only the last change will persist. – M T Head Sep 08 '16 at 21:24

score 7 · Answer 4 · edited Aug 03 '19 at 20:42

7

in python 3.x

wb = openpyxl.Workbook()
ws = wb.active
redFill = PatternFill(start_color='FFFF0000',
                   end_color='FFFF0000',
                   fill_type='solid')
ws['A1'].fill = redFill

that working but i dont know in python 2.x i hope working just put ws['A1'].fill=redFill

edited Aug 03 '19 at 20:42

Rabbid76

202,892
27
131
174

answered Aug 03 '19 at 20:18

ismail

175
2
6

1

To make PatternFill work you need `from openpyxl.styles import PatternFill` – Shane S Sep 23 '20 at 18:35

score 6 · Answer 5 · edited Jan 03 '23 at 13:35

from openpyxl import Workbook, load_workbook
from openpyxl.styles import PatternFill

_file_name = "Test.xlsx"
_sheet_name = "Test_Sheet"

def new_workbook(_file_name, _sheet_name):
    wb = Workbook()  # Workbook Object
    ws = wb.active  # Gets the active worksheet
    ws.title = _sheet_name  # Name the active worksheet

    # Writing the header columns
    ws['A1'] = 'Name'
    ws['B1'] = 'Class'
    ws['C1'] = 'Section'
    ws['D1'] = 'Marks'
    ws['E1'] = 'Age'
    

    col_range = ws.max_column  # get max columns in the worksheet

    # formatting the header columns, filling red color
    for col in range(1, col_range + 1):
        cell_header = ws.cell(1, col)
        cell_header.fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type="solid") #used hex code for red color

    wb.save(_file_name)  # save the workbook
    wb.close()  # close the workbook

if __name__ == '__main__':
    new_workbook(_file_name, _sheet_name)

Result -

score 5 · Answer 6 · edited Apr 07 '22 at 07:24

5

What I would do for Excel is this:

from openpyxl import Workbook, load_workbook
from openpyxl.styles import PatternFill

wb = load_workbook("test.xlsx")
ws = wb.active

ws["A1"].fill = PatternFill("solid", start_color="FFA500")

You can replace "A1" with another cell and start_color has to be a hex color.

edited Apr 07 '22 at 07:24

Sergey Antopolskiy

3,970
2
24
40

answered Feb 19 '22 at 08:44

tuurtje11

189
1
8

score 4 · Answer 7 · answered Feb 06 '21 at 07:55

4

To fill a range of rows/columns, do this

for cell in ws['A1:A100']:
   cell[0].fill = redFill

To fill all rows of a column

for cell in ws['A1:{}'.format(ws.max_row)]:
   cell[0].fill = redFill

answered Feb 06 '21 at 07:55

Umair Ayub

19,358
14
72
146

score 3 · Answer 8 · answered Aug 26 '21 at 18:28

Use a nested for loop to fill a two dimensional range.

Python 3.9

import openpyxl as op
fill_gen = op.styles.PatternFill(fill_type='solid',
                                 start_color='FFFFFF',
                                 end_color='FFFFFF')
for row in ws["A1:BB50"]:
    for cell in row:
        cell.fill = fill_gen

Fill cells with colors using openpyxl?

8 Answers8

Result -

Linked

Related