How to add cell color to cell range using Python Pandas ExcelWriter?

Question

I am trying to add a background cell color to a cell range using Python Pandas ExcelWriter. Here is what I have so far:

 pivotChartsTab = pd.read_sql(sql4, conn)
 import pandas.io.formats.excel
 pandas.io.formats.excel.header_style = None
 pivotChartsTab.to_excel(writer, 'Pivot Tables', index=False)

#Format sheets
workbook = writer.book
format1 = workbook.add_format({'num_format': '#,##'}) 
format2 = workbook.add_format({'num_format': '#,##0.00'}) 
format3 = workbook.add_format({'num_format': '0%'})
format4 = workbook.add_format()
format4.set_pattern(1)
format4.set_bg_color('green')


#format Pivot Tables            
worksheet = writer.sheets['Pivot Tables']        
for i, col in enumerate(pivotChartsTab.columns):
    if i == 0:
        worksheet.set_column(i, i, 20)
    elif i == 1:
        worksheet.set_column(i, i, 15)
    elif i == 2:
        worksheet.set_column(i, i, 18)
    elif i == 3:
        worksheet.set_column(i, i, 20, format3)
    elif i == 4:
        worksheet.set_column(i, i, 25, format2)
    elif i == 5:
        worksheet.set_column(i, i, 25, format2) 
    elif i == 6:
        worksheet.set_column(i, i, 18, format3)
worksheet.write_row('A1:G1', format4)
writer.save()

So essentially I am trying to make my own customer header style. I'm wanting the background color green and row height to be 25, but can't get the coloring working so haven't tried to alter the row height yet.

I feel like i'm missing something easy or fundamental. Thanks for any help.

user76595 · Accepted Answer · 2018-03-16 20:58:57Z

6

This seemed to work, but I'm not sure how it would react if the cell is null or blank etc...

format4 = workbook.add_format({'bg_color': '#92D050'})
format5 = workbook.add_format({'text_wrap':True})

worksheet.set_row(0, 28, format5)
worksheet.conditional_format('A1:G1', {'type':'cell', 'criteria':'>=','value': 0, 'format':format4})

This does text wrapping on my header, row height to 28 and the color I wanted.

answered Mar 16, 2018 at 20:58

user76595

3775 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add cell color to cell range using Python Pandas ExcelWriter?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related