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

Question

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:

What's coming out:

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

Thanks.

Answer 1

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()

Answer 2

There are some errors in your "for row" loop where you re-add the column headers for each row. It is also questionable whether it is worth using Pandas to_excel() if you are going to iterate through the entire dataframe and write the data out row by row.

Instead a better, and simpler, approach to get what you want would be to use an Excel table to format that data. 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')


# Write the dataframe data to XlsxWriter. Turn off the default header and
# index and skip one row to allow us to insert a user defined header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False, index=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Get the dimensions of the dataframe.
(max_row, max_col) = df.shape

# Create a list of column headers, to use in add_table().
column_settings = [{'header': column} for column in df.columns]

# Add the Excel table structure. Pandas will add the data.
worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})

# Make the columns wider for clarity.
worksheet.set_column(0, max_col - 1, 15)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

See this section of the XlsxWriter docs for more information: Adding a Dataframe to a Worksheet Table.

You can also change the Table style to one of the others supported by Excel.