Python Pandas excelWriter 定制格式,定制表头、渲染颜色等

ExcelWriter这个插件有个坑，就是已经设置好的格式是无法更改的，因此，由pandas转成excel的时候，必须将格式清除，尤其是表头的格式，代码如下：

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

标准的保存pandas表到excel的形式为：

writer = pd.ExcelWriter(output_prefix + cv_excel_file_name)
df.to_excel(writer, 'Sheet1')  # 这里假设df是一个pandas的dataframe
writer.save()
writer.close()

如果要定制输出的excel格式，那么得在to_excel和save之间添加代码：

import  pandas as pd
import pandas.io.formats.excel
import openpyxl
import numpy as np
import xlsxwriter

pandas.io.formats.excel.header_style = None #新版不需要也可以
path =r'C:\Users\shzbabao\Desktop\Enterprise title information  2020.xls'
# df =pd.read_excel(path)
# # df.to_csv('cc.csv')
# df6=df.head()
# df7=df.tail()
# df8=df.ix[0].values
# df1 =df.index
# df2=df.columns
# df3=df.T
# df4=df[1:]
# df5 =df.mean()
# print("data:\n{0}".format(df6))
# print("data:\n{0}".format(df7))
# print("data:\n{0}".format(df8))
# print(df5)

# print(df.columns)
# print(df.head(20))

df = pd.read_excel(path,sheet_name='在系统下载下来的原文件')
# print(df.index)
# print(df)
df1 =df[['Number','Description','Description 2nd Line','Product Family','Box Label Type Approval Marks','TA Model No',
         'Product color','EAN Number for single unit','EAN Number for Master Carton','UPC 12',
         'UPC 14 for Inner Carton','UPC 14 for Master Carton','Category','Icons','Country Of Origin',
         'Item Tracking','Data 1 (FCC)','Data 2 (IC)','Data 3 (SRRC)','Data 4 (TELEC)',
         'Data 5 (KCC)','Data 6 (NCC)','Data 7 (SDPPI)','Data 8 (MCMC)','Data 9 (GB)',
         'Data 10 (IFT)','Company Address','Customer Label Info','Consumer Product Description']]
# df1.to_excel(r'C:\Users\shzbabao\Desktop\xx.xlsx',sheet_name='data',encoding='utf-8', index=False)
path2 =r'C:\Users\shzbabao\Desktop\xx.xlsx'
writer = pd.ExcelWriter(path2)
df1.to_excel(writer, 'Sheet1')  
workbook1 = writer.book
worksheets = writer.sheets
worksheet1 = worksheets['Sheet1']
# format1 = workbook1.add_format({'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
format2 = workbook1.add_format({'bold':  True, 'align': 'center', 'valign': 'top','fg_color': 'white', 'text_wrap': False})

bold = workbook1.add_format({
        'bold':  True,  # 字体加粗
        'border': 1,  # 单元格边框宽度
        'align': 'left',  # 水平对齐方式
        'valign': 'vcenter',  # 垂直对齐方式
        'fg_color': '#F4B084',  # 单元格背景颜色
        'text_wrap': False,  # 是否自动换行
    })

#yellow = workbook1.add_format({'fg_color': '#FFEE99'})
worksheet1.set_column("B:AE", 26, cell_format=bold)#设置表格的样式
worksheet1.set_column("C:G", 46)
worksheet1.set_column("T:AE", 56)
worksheet1.set_row(0, cell_format=format2) #设置表头的样式
writer.save()
writer.close()