4

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
  • 357
  • 5
  • 17

1 Answers1

5

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.

user76595
  • 357
  • 5
  • 17