Python Forum
Best way to set cell background colour in openpyxl
Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Best way to set cell background colour in openpyxl
#1
Using openpyxl to manipulate Excel files, what is the best way to set the cell background colour?

I got this from stackoverflow:

from openpyxl.styles import Color, Fill
from openpyxl.cell import Cell

_cell.style.fill.fill_type = Fill.FILL_SOLID
_cell.style.fill.start_color.index = Color.DARKRED
This post in from 2011. Is there a better way to do it in python 3.5?

All I want is for every other column to have a yellow background, makes reading the table easier somehow.
Reply
#2
from openpyxl.styles import PatternFill
sheet['A1'].fill = PatternFill(bgColor="FFC7CE", fill_type = "solid")
Copied from post https://stackoverflow.com/questions/3591...l-openpyxl
by https://stackoverflow.com/users/4686330/avik-samaddar
Reply
#3
Thanks a lot! I see I need to get better at searching the web!

Oh dear!

Using this code:

for sheet in targetFileSheetNames:
	targetFileActiveSheet = targetFile.get_sheet_by_name(sheet)
	maxRow = targetFileActiveSheet.max_row
	maxCol = targetFileActiveSheet.max_column
	for colNum in range(2, maxCol + 1, 2):
		for rowNum in range(1, maxRow + 1):
			targetFileActiveSheet.cell(row=rowNum, column=colNum).fill = PatternFill(bgColor='FFEE08', fill_type = 'solid')
no matter what hex number I use, from FFFFFF to 000000, all I get are black columns.

According to gimp, a nice yellow colour has the number FFEE08.

Can anyone see what is wrong? I imported PatternFill and I get no errors, just black columns after saving.

This does the job:

targetFileActiveSheet.cell(row=rowNum, column=colNum).fill = PatternFill(start_color='FFEE08', end_color='FFEE08', fill_type = 'solid')
No idea why bgColor does not work, but I get what I want with this.
Reply
#4
try "fgColor" instead of "bgColor"
targetFileActiveSheet.cell(row=rowNum, column=colNum).fill = PatternFill(fgColor='FFEE08', fill_type = 'solid')
seems to work with openpyxl 2.5.0
(don't ask why... just guessed and tried...)
Reply
#5
(Feb-20-2018, 08:37 PM)theozh Wrote: try "fgColor" instead of "bgColor"
targetFileActiveSheet.cell(row=rowNum, column=colNum).fill = PatternFill(fgColor='FFEE08', fill_type = 'solid')
seems to work with openpyxl 2.5.0
(don't ask why... just guessed and tried...)

I used this and all above mentioned methods but nothing has been reflected in my excel file. I seen that there are no errors are reflected in my python console.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Question convert autohotkey script to python / macro - press key when pixel get colour willson94d 1 3,574 Jan-01-2022, 08:13 PM
Last Post: Yoriz
  How to append a value to specific excel cell using openpyxl hobbyist 0 4,768 Mar-05-2021, 07:14 PM
Last Post: hobbyist
  P3, openpyxl, csv to xlsx, cell is not number, problem with colorize genderbee 1 2,100 Sep-29-2020, 03:20 PM
Last Post: Larz60+
  Adding colour to Python console Archangelos 4 2,688 Apr-15-2020, 05:15 PM
Last Post: newbieAuggie2019
  openpyxl, if value in cell then change format genderbee 1 5,181 Nov-05-2019, 01:37 PM
Last Post: genderbee
  Need to copy column of cell values from one workbook to another with openpyxl curranjohn46 3 11,086 Oct-12-2019, 10:57 PM
Last Post: curranjohn46
  How to obtain the background color of a cell using xlrd? Krszt 1 12,423 Mar-12-2019, 11:23 PM
Last Post: hshivaraj
  Is it possible to have my python shell change the backgrounds colour? OTO1012 2 3,713 Mar-07-2019, 09:32 PM
Last Post: woooee
  OpenPyxl Cell.value Alignment pcsailor 0 8,781 Sep-10-2018, 01:09 AM
Last Post: pcsailor
  I am trying to make a Colour Wars game, "Winning code" missing Ondrejoda 0 2,249 Feb-18-2018, 11:06 AM
Last Post: Ondrejoda

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020