CODEWITHSUNDEEP
pythonexcelopenpyxl
dylosaur
dylosaur

Reputation: 149

Changing style for one row or column in OpenPyxl

I've switched from NamedStyle to just regular styles, which seems to work better for me. Now I'm just stuck on trying to fill a single row and column-- I have some over engineered bits that work, but I'm curious why the colA bit doesn't. Gives me an AtrributeError.

import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill, Border, Side, Alignment

wb = openpyxl.load_workbook('example5.xlsx')
sheet = wb['Sheet1']

day = ''
week = ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY',
        'FRIDAY', 'SATURDAY', 'SUNDAY')

sheet['A2'] = 'Monday'
sheet['A3'] = 'Tuesday'
sheet['A4'] = 'Wednesday'
sheet['A5'] = 'Thursday'
sheet['A6'] = 'Friday'
sheet['A7'] = 'Saturday'
sheet['A8'] = 'Sunday'

sheet['A1'] = 'Day'
sheet['B1'] = 'In'
sheet['C1'] = 'Out'
sheet['D1'] = 'Total Hours'
sheet['E1'] = 'ST Hours'
sheet['F1'] = 'OT Hours'
sheet['G1'] = 'ST Rate'
sheet['H1'] = 'OT Rate'


while day not in week:
    day = input('What day of the week is it? ')
    day = day.upper()


def sheetStyle(var):
    sheet['B' + var].number_format = 'H:MM AM/PM'
    sheet['F' + var].number_format = 'H:MM AM/PM'
    sheet['C' + var].number_format = 'H:MM AM/PM'
    sheet['E' + var].number_format = 'General'
    sheet['F' + var].number_format = '#,##0.00'
    sheet['G' + var].number_format = '[$$]#,##0.00'
    sheet['H' + var].number_format = '[$$]#,##0.00'
    sheet['B' + var] = input('Enter the time you clocked in: ')
    sheet['C' + var] = input('Enter the time you clocked out: ')
    sheet['D' + var] = '=MOD(C' + var + '-B' + var + ',1)*24'
    sheet['E' + var] = '=D' + var + '-F' + var
    sheet['F' + var] = '=MAX(D' + var + '-8,0)'
    sheet['G' + var] = '=E' + var + '*15'
    sheet['H' + var] = '=F' + var + '*22.5'


def sheetInput(day=''):
    for i, w in enumerate(week):
        if day == w:
            sheetStyle(str(i + 2))


grayFill = PatternFill(start_color='D0CECE', end_color='D0CECE',
                       fill_type='solid')
whiteFill = PatternFill(start_color='FFFFFF', end_color='FFFFFF',
                        fill_type='solid')

for columns in sheet.iter_cols(min_col=None, max_col=None,
                               min_row=None, max_row=None):
    for cell in columns:
        cell.fill = grayFill

# for row in sheet.iter_rows(min_row=None, max_col=1, max_row=8):
#    for cell in row:
#        cell.fill = whiteFill

# for cell in sheet[1:1]:
#    cell.fill = whiteFill

colA = sheet['A']
colA.fill = whiteFill

column = 1
while column < 9:
    col = get_column_letter(column)
    sheet.column_dimensions[col].width = 20
    column += 1

for d in week:
    if d == day:
        sheetInput(day)

wb.save('example5.xlsx')

And here's the error:

Traceback (most recent call last):

  File "<ipython-input-59-275cd7bf6699>", line 1, in <module>
    runfile('D:/Users/D/Desktop/Python/hello.py', wdir='D:/Users/D/Desktop/Python')

  File "C:\Users\D\AppData\Local\conda\conda\envs\test_env\lib\site-packages\spyder\utils\site\sitecustomize.py", line 705, in runfile
    execfile(filename, namespace)

  File "C:\Users\D\AppData\Local\conda\conda\envs\test_env\lib\site-packages\spyder\utils\site\sitecustomize.py", line 102, in execfile
    exec(compile(f.read(), filename, 'exec'), namespace)

  File "D:/Users/D/Desktop/Python/hello.py", line 84, in <module>
    colA.fill = whiteFill

AttributeError: 'tuple' object has no attribute 'fill'

Upvotes: 2

Views: 9893

Answers (1)

Charlie Clark
Charlie Clark

Reputation: 19507

The error is clear: you are trying to apply a style to a column. openpyxl provides rows and columns as a convenience but these are always a collection of cells over which you need to loop.

for cell in ws['A']:
    cell.fill = whiteFill

Upvotes: 5

Related Questions