pandas ExcelWriter customized format (customized header, rendering color, etc., non-ExcelWriter standard creation mode)

Excel Writer plug-in has a pit that the format that has been set can not be changed. Therefore, when converting from pandas to excel, the format must be cleared, especially the format of the header. The code is as follows:

import pandas.io.formats.excel
pandas.io.formats.excel.header_style = None

The standard form of saving pandas table to excel is:

writer = pd.ExcelWriter(output_prefix + cv_excel_file_name)
df.to_excel(writer, 'Sheet1')  # Let's assume that df is a pandas dataframe
writer.save()
writer.close()

If you want to customize the excel format of the output, you need to add code between to_excel and save:

writer = pd.ExcelWriter(output_prefix + cv_excel_file_name)
df.to_excel(writer, 'Sheet1')  # Let's assume that df is a pandas dataframe

# =================== add self define code here =======================
# from xlsxwriter.workbook import Workbook
# from xlsxwriter.worksheet import Worksheet
workbook1 = writer.book
worksheets = writer.sheets
worksheet1 = worksheets['Sheet1']

writer.save()
writer.close()

With the corresponding workbook and worksheet objects, we can refer to them. rom To customize our output excel format!

Before we can manipulate the worksheet, we need to add a custom format set to the workbook:

format1 = workbook1.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
format2 = workbook1.add_format({'bold':  True, 'align': 'left', 'valign': 'top', 'text_wrap': True})

 

Add:

New format:

bold = f.add_format({
        'bold':  True,  # font-weight
        'border': 1,  # Cell border width
        'align': 'left',  # align
        'valign': 'vcenter',  # valign
        'fg_color': '#F4B084',  # Cell background color
        'text_wrap': True,  # Whether to change lines automatically
    })

Add cell data:

//Row: row, col: column, data: data to be written, bold: cell style
worksheet1.write(row, col, data, bold)

 

Delete:

 

Change (here mainly introduces the method of change):

Set column width and color:

yellow = workbook1.add_format({'fg_color': '#FFEE99'})
worksheet1.set_column("E:E", 16, cell_format=yellow)

Set column width (all column widths from column B to column AE are 16):

worksheet1.set_column("B:AE", 16)

Set conditional formatting (let columns A, 2 to 55, select the'top1'with the largest value, and render with format1 format and color):

worksheet1.conditional_format('A2:A55', {'type': 'top', 'value': 1, 'format': format1})

Set the row format (change the header format):

worksheet1.set_row(0, cell_format=format2)

 

Check: