CODEWITHSUNDEEP
pythonopenpyxl
Zedak
Zedak

Reputation: 289

Openpyxl: set background color to a row and column Attribute Error

having looked at a few examples over here I tried to set background color to an entire row and column. I have done

 import openpyxl
 from openpyxl.styles import PatternFill
 wb = openpyxl.load_workbook(self.inputfile)
 ws = wb.active
 ws['A1'].fill = PatternFill(bgColor="FFC7CE", fill_type = "solid")`

I get an Attribute error if I do `ws[1].fill =PatternFill(bgColor="FFC7CE", fill_type = "solid")

The above code fills a single cell(A1). But how do I go forward if I want to fill an entire row(1), and an entire column(A).

Upvotes: 6

Views: 31477

Answers (3)

stovfl
stovfl

Reputation: 15513

Iterates all columns, starting at the Column specified in the min_col=1 argument.
Ends after one row, as the row arguments min_row=1 and max_row=1 are equal.
Arguments min_row/max_row can point to any row, even also outside data.

  for rows in ws.iter_rows(min_row=1, max_row=1, min_col=1):
    for cell in rows:
      cell.fill = PatternFill(bgColor="FFC7CE", fill_type = "solid")

For entire Column, use:

iter_cols(min_col=None, max_col=None, min_row=None, max_row=None)

If you only give min_* attribute values, max row/column are used.

Tested with Python:3.4.2 - openpyxl:2.4.1

Upvotes: 18

Steven Barnard
Steven Barnard

Reputation: 604

Slight modification on the accepted answer (white background):

   for rows in sheet.iter_rows(min_row=1, max_row=40, min_col=1, max_col=40):
    for cell in rows:
        cell.fill = PatternFill(start_color='00FFFFFF', end_color='00FFFFFF', fill_type="solid")

This provides a white fill on the sheet starting at row 1 column 1 and going to row 40 and column 40.

Upvotes: 5

Saji Xavier
Saji Xavier

Reputation: 2360

I tried the accepted answer but row background was always black, So i have to change the PatternFill to following.

  cell.fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type = "solid")

Upvotes: 3

Related Questions