python用workbook类方法带格式保存excel

主要步骤：

• 1.用add_worksheet添加一个新的工作表，sheetname为工作表名称
• 2.用add_format在工作表中创建一个新的格式对象来格式化单元格
• 3.用set_row和set_column方法设置sheet表单元格的行高和列宽
• 4.用write方法将普通数据写入单元格中

本例是直接将现有数据存到excel为例的

def change_format(self,df, writer, Item):
	"""
	修改dataframe的数据格式，如小数转为%，日期改成%Y-%m-%d
	"""
	df.to_excel(writer, sheet_name=Item, index=False)
	workbook = writer.book
	worksheet = writer.sheets[Item]
	cell_fmt = {'bold': False, 'font_name': '微软雅黑', 'font_size': 8, 'align': 'center', 'valign': 'vcenter',
	            'border': 0,'text_wrap':True}
	header_fmt = {'bold': True, 'font_name': '微软雅黑', 'font_size': 6, 'align': 'center', 'valign': 'vcenter',
	              'border': 0, 'bg_color': '#808080','text_wrap':True}
	cell_format = workbook.add_format(cell_fmt)
	header_format = workbook.add_format(header_fmt)

	df_col = [each for each in df.columns if each not in ['date', 'cus_type', '活跃人数','ad_channel','当日游客数','当日设备数']]
	# format 部分列进行%和保留两位小数处理
	if '比例' in list(df.iloc[:,0]):
	    for i in range(row_num,df.shape[0]):
	        for j in df_col:
	            try:
	                if float(df.loc[i,j])<=1:
	                    df.loc[i, j] = str(df.loc[i, j]*100)[:4]+ '%'
	                    print(df.loc[i, j])
	            except Exception as ExceptionError:
	                print('format % 每日新增数据格式处理 比例异常',ExceptionError)
	                pass
	elif '人均' in list(df.iloc[:,0]):
	    for i in range(row_num,df.shape[0]):
	        for j in df_col:
	            try:
	                df.loc[i, j] = np.round(df.loc[i, j],2)
	            except Exception as ExceptionError:
	                print('format % 每日新增数据格式处理 人均异常',ExceptionError)
	                pass
	else:
	    pass
	#format datetime
	for k in range(df.shape[0]):
	    try:
	        df.iloc[k,0] = pd.to_datetime(df.iloc[k,0]).strftime("%Y-%m-%d")
	    except Exception as ExceptionError:
	        print('时间格式处理异常:', str(ExceptionError))
	        pass
	# 列标题单元格格式处理
	# for colx, value in enumerate(df.columns.values):
	#     worksheet.write(0, colx, value, header_format)
	#     worksheet.write(row_num+1, colx, value, header_format)
	# A:C列宽16，D:Z列宽8，并全部按照cell_format格式处理
	worksheet.set_column('A:C', 16, cell_format)
	worksheet.set_column('D:Z', 8, cell_format)
	df.to_excel(writer, sheet_name=Item, index=False)