Search code examples
pythonpandasxlsxwriter

How to export Excel from Pandas with custom style - alternate row colors - xlsxwriter

I'm looking in the XLSXwriter documentation but I can't find a simple way to export dataframes to excel according to the format I use.

I tried the code below. However, it is changing the values ​​instead of just formatting.

My code with Foo data

What I want would be something like this:

import pandas as pd
foo_data = {'guitar_player': ['Jimmy Hendrix', 'Slash', 'Joe Satriani', 'Steve Ray Vaughan'],
              'guitar_brand': ['Fender', 'Gibson', 'Ibanez', 'Fender'],
              'born': ['27/11/1942', '23/07/1965', ' 15/06/1956', '03/10/1954']}
df = pd.DataFrame.from_dict(foo_data)
writer = pd.ExcelWriter('guitar_foo.xlsx' , engine='xlsxwriter')
df.to_excel(writer, sheet_name='foo', index=False)
workbook  = writer.book
worksheet = writer.sheets['foo']
my_header_style = { 'bold': True,
                    'text_wrap': True,
                    'align': 'center',
                    'valign': 'vcenter',
                    'fg_color': '#008080',
                    'font_color': '#FFFFFF',
                    'border': 1,
                    'border_color': '#646464'}

row_white_style = { 
                    'valign': 'vcenter',
                    'fg_color': '#FFFFFF',
                    'border_color': '#646464',
                    'font_color': '#000000',
                    'border': 1
                  }

row_light_gray_style = { 
                    'valign': 'vcenter',
                    'fg_color': '#F5F5F5',
                    'border_color': '#646464',
                    'font_color': '#000000',
                    'border': 1
                  }

header_format = workbook.add_format(my_header_style)
row_white_format = workbook.add_format(row_white_style)
row_light_gray_format = workbook.add_format(row_light_gray_style)

for col_num, value in enumerate(df.columns.values):
            worksheet.write(row, col_num , value, header_format)

for row in range(0,len(df)):
    if row == 0:
        pass
    elif row%2 == 0:
        for col_num, value in enumerate(df.columns.values):
            worksheet.write(row, col_num , value, row_white_format)
    else:
        for col_num, value in enumerate(df.columns.values):
            worksheet.write(row, col_num , value, row_light_gray_format) 

writer.save()

What I want:

enter image description here

What's coming out:

enter image description here

can anybody help me? I looked for several posts here on stackoverflow and I didn't find the solution to my problem.

Thanks.

Solution

  • I have found a solution

    import pandas as pd

foo_data = {'guitar_player': ['Jimmy Hendrix', 'Slash', 'Joe Satriani', 'Steve Ray Vaughan'],
              'guitar_brand': ['Fender', 'Gibson', 'Ibanez', 'Fender'],
              'born': ['27/11/1942', '23/07/1965', ' 15/06/1956', '03/10/1954']}

df = pd.DataFrame.from_dict(foo_data)

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook  = writer.book
worksheet = writer.sheets['Sheet1']


light_gray_format = workbook.add_format({'bg_color': '#F4F4F4', 'border_color': '#646464', 'valign': 'vcenter', 'border': 1})
white_format = workbook.add_format({'bg_color': '#FFFFFF', 'border_color': '#646464', 'valign': 'vcenter', 'border': 1})

header_format = workbook.add_format({'bold': True,
                                     'text_wrap': True, 
                                     'align': 'center',
                                     'valign': 'vcenter',
                                     'bg_color': '#008080',
                                     'font_color': '#ffffff',
                                     'border': 1,
                                     'border_color': '#646464'})

worksheet.conditional_format('A1:C5', {'type': 'formula',
                                        'criteria': '=ROW()=1',
                                        'format': header_format})

worksheet.conditional_format('A2:C5', {'type': 'formula',
                            'criteria': '=EVEN(ROW())=ROW()',
                            'format': white_format})

worksheet.conditional_format('A2:C5', {'type': 'formula',
                            'criteria': '=ODD(ROW())=ROW()',
                            'format': light_gray_format})

writer.save()