In the below code I apply number formatting to each of the columns in my excel sheet. However, I can't seem to figure out to apply multiple formattings to a specific column, either the centering or the numbering end up being over written. Is it even possible to apply two types of formatting to one column?
def to_excel(video_report):
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
# Convert the dataframe to an XlsxWriter Excel object.
video_report.to_excel(writer, sheet_name='Sheet1', na_rep="-")
# Get the xlsxwriter objects from the dataframe writer object.
workbook = writer.book
worksheet = writer.sheets['Sheet1']
# Add some cell formats.
integer = workbook.add_format({'num_format': '0'})
decimal = workbook.add_format({'num_format': '0.00'})
percentage = workbook.add_format({'num_format': '0.0%'})
center = workbook.add_format({'align': 'center'})
# APPLY CENTERING
worksheet.set_column('B:L', None, center)
# APPLY NUMBER FORMATTING
worksheet.set_column('B:B', 13, integer )
worksheet.set_column('C:C', 17, percentage )
worksheet.set_column('D:D', 19, percentage )
worksheet.set_column('E:E', 15, integer )
worksheet.set_column('F:F', 15, percentage )
worksheet.set_column('G:G', 15, decimal )
worksheet.set_column('H:H', 13, integer )
worksheet.set_column('I:I', 13, integer )
worksheet.set_column('J:J', 13, integer )
worksheet.set_column('K:K', 13, integer )
worksheet.set_column('L:L', 13, integer )
writer.save()
to_excel(video_report)
Is there an “AutoFit” option for columns? Unfortunately, there is no way to specify “AutoFit” for a column in the Excel file format. This feature is only available at runtime from within Excel.
To set the color of a cell use the set_bg_color() and set_pattern() methods.
Is it even possible to apply two types of formatting to one column?
Yes. By adding the two or more properties to the format object that you are using. For example:
integer = workbook.add_format({'num_format': '0', 'align': 'center'})
See the XlsxWriter docs on how to use formats.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With