Python3 pandas DataFrame自定义输出excel样式两个实例

import pandas as pd
import random
import numpy as np
import os
 
 
#
# data = [10, 20, 30, 40, 50, 60]
# df = pd.DataFrame({'Heading': data,
#                    'Longer heading that should be wrapped' : data})
df = pd.DataFrame(np.arange(16).reshape(4,4), columns=list('ABCD'))
 
BASEINPUT=os.path.dirname(__file__)
OUTPUTH=os.path.join(BASEINPUT,'result.xlsx')
writer = pd.ExcelWriter(OUTPUTH, engine='xlsxwriter')
 
# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=0,index=False)
 
# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
 
# Add a header format.
header_format = workbook.add_format({
    'bold': True, # 字体加粗
    'text_wrap': True, # 是否自动换行
    'valign': 'top',  #垂直对齐方式
    'align': 'right', # 水平对齐方式
    'fg_color': '#D7E4BC', # 单元格背景颜色
    'border': 2}) # 单元格边框宽度
 
 
yellow = workbook.add_format({'fg_color': '#FFEE99'})
red=workbook.add_format({'fg_color': '#2dB054'})
 
# Write the column headers with the defined format.
for col_num, value in enumerate(df.columns.values):
    if col_num%2==0:
        worksheet.write(0, col_num, value, header_format)
    else:
        worksheet.write(0, col_num, value, yellow)
 
# Write the row with the defined format.
for index, value in df.iterrows():
    print(index," -- > ",value.values)
    if index % 2 == 0:
        worksheet.write(index+1, 0, value[0], header_format)
    else:
        worksheet.write(index+1, 0, value[0], yellow)
 
worksheet.set_column("A:C", 16)
format2 = workbook.add_format({'bold':  True, 'align': 'vcenter', 'valign': 'top', 'text_wrap': True})
worksheet.set_row(0, cell_format=format2)
# Close the Pandas Excel writer and output the Excel file.
writer.save()

 

参考链接：https://xlsxwriter.readthedocs.io/example_pandas_column_formats.html

2、从接切板获得表格数据

import pandas as pd
from  datetime import datetime,timedelta
df = pd.read_clipboard() # 从粘贴板上读取数据
t = datetime.now().date() - timedelta(days=1)
writer = pd.ExcelWriter('样式%d%02d%02d.xlsx' %(t.year,t.month,t.day))
workbook = writer.book
fmt = workbook.add_format({"font_name": u"微软雅黑"})
percent_fmt = workbook.add_format({'num_format': '0.00%'})
amt_fmt = workbook.add_format({'num_format': '#,##0'})
border_format = workbook.add_format({'border': 1})
note_fmt = workbook.add_format(
    {'bold': True, 'font_name': u'微软雅黑', 'font_color': 'red', 'align': 'left', 'valign': 'vcenter'})
date_fmt = workbook.add_format({'bold': False, 'font_name': u'微软雅黑', 'num_format': 'yyyy-mm-dd'})

date_fmt1 = workbook.add_format(
    {'bold': True, 'font_size': 10, 'font_name': u'微软雅黑', 'num_format': 'yyyy-mm-dd', 'bg_color': '#9FC3D1',
     'valign': 'vcenter', 'align': 'center'})
highlight_fmt = workbook.add_format({'bg_color': '#FFD7E2', 'num_format': '0.00%'})

l_end = len(df.index) + 2 # 表格的行数,便于下面设置格式
df.to_excel(writer, sheet_name=u'测试页签', encoding='utf8', header=False, index=False, startcol=0, startrow=2)
worksheet1 = writer.sheets[u'测试页签']
for col_num, value in enumerate(df.columns.values):
    worksheet1.write(1, col_num, value, date_fmt1)
worksheet1.merge_range('A1:B1', u'测试情况统计表', note_fmt)
# 设置列宽
worksheet1.set_column('A:D', 30, fmt)
# 有条件设定表格格式：金额列
worksheet1.conditional_format('B3:E%d' % l_end, {'type': 'cell', 'criteria': '>=', 'value': 1, 'format': amt_fmt})
# 有条件设定表格格式：百分比
worksheet1.conditional_format('E3:E%d' % l_end,
                              {'type': 'cell', 'criteria': '<=', 'value': 0.1, 'format': percent_fmt})
# 有条件设定表格格式：高亮百分比
worksheet1.conditional_format('E3:E%d' % l_end,
                              {'type': 'cell', 'criteria': '>', 'value': 0.1, 'format': highlight_fmt})
# 加边框
worksheet1.conditional_format('A1:E%d' % l_end, {'type': 'no_blanks', 'format': border_format})
# 设置日期格式
worksheet1.conditional_format('A3:A62', {'type': 'no_blanks', 'format': date_fmt})
writer.save()

原文https://www.jianshu.com/p/25a42309cfec

 

 

错误修正：

样式中垂直居中应该是：

{'valign':'vcenter', # 垂直对齐方式}