Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to apply multiple formats to one column with XlsxWriter

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)
like image 323
metersk Avatar asked Jun 01 '15 02:06

metersk


People also ask

How do I AutoFit column width in Xlsxwriter?

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.

How do I color a cell in Excel Xlsxwriter?

To set the color of a cell use the set_bg_color() and set_pattern() methods.


1 Answers

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.

like image 70
jmcnamara Avatar answered Oct 25 '22 13:10

jmcnamara