pandas to |
您所在的位置：网站首页 › 怎么调excel表格格式 › pandas to |
pandas to
|
最近有一个需求是对输出的excel格式进行处理，要求要美观，颜色要根据内容来显示不同规则 先上总代码：  
# -*- coding: utf-8 -*-
"""
Created on Fri Oct 29 11:29:39 2021
@author: tianwz
"""
import pandas as pd
from datetime import datetime,timedelta
import time
time_now=datetime.now()
time_now=time_now.strftime("%Y-%m-%d")
df_excel=pd.read_excel('D平台项目三电售后问题管控表_分类结果_'+time_now+'.xlsx',sheet_name=None)
writer = pd.ExcelWriter('D平台项目三电售后问题管控表_分类结果_'+time_now+'格式处理.xlsx') # 此处engine="xlsxwriter"
for sheet_name in df_excel.keys():
print('正在定制: '+sheet_name)
df=pd.read_excel('D平台项目三电售后问题管控表_分类结果_'+time_now+'.xlsx',sheet_name=sheet_name,index_col=0)
df=df.drop('发生时间',axis=1)
workbook = writer.book
# percent_fmt = workbook.add_format({'num_format': '0.00%'})
# amt_fmt = workbook.add_format({'num_format': '#,##0'})
border_format = workbook.add_format({'border': 1})
# example_fmt = workbook.add_format({'bold':True,'font_name':u'阿里巴巴普惠体','font_color':'red','font_strikeout':True,
# 'align': 'center', 'valign': 'vcenter','underline':True})
# 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=sheet_name, encoding='utf8', header=False, index=False,startrow=1)# startcol=0, startrow=2
worksheet1 = writer.sheets[sheet_name]
fmt = workbook.add_format({"font_name": u"阿里巴巴普惠体","font_size":9,'valign': 'vcenter','align': 'center'})
for col_num, value in enumerate(df.columns.values):
worksheet1.write(0, col_num, value, fmt) #列名称写入
# 设置列宽以及格式
worksheet1.set_column('A:T',10, fmt)
worksheet1.set_column('A:A',8, fmt)
worksheet1.set_column('B:B',4, fmt)
worksheet1.set_column('C:C',7, fmt)
worksheet1.set_column('D:D',3, fmt)
worksheet1.set_column('E:E',2, fmt)
worksheet1.set_column('I:I',2, fmt)
worksheet1.set_column('H:H',6, fmt)
worksheet1.set_column('K:K',7, fmt)
worksheet1.set_column('L:L',6, fmt)
fmt = workbook.add_format({"font_name": u"阿里巴巴普惠体","font_size":9,'valign': 'vcenter','align': 'left'})
worksheet1.set_column('J:J',40, fmt)
worksheet1.set_column('P:P',30, fmt)
# 设置具体的样式规则
fmt = workbook.add_format({"font_name": u"阿里巴巴普惠体","font_size":9,'bg_color': '#C5D9F1'})
worksheet1.conditional_format('A1:B1', {'type': 'text', 'criteria': 'containing', 'value': '', 'format': fmt})
fmt = workbook.add_format({"font_name": u"阿里巴巴普惠体","font_size":9,'bg_color': '#DAEEF3'})
worksheet1.conditional_format('C1:I1', {'type': 'text', 'criteria': 'containing', 'value': '', 'format': fmt})
worksheet1.conditional_format('K1:O1', {'type': 'text', 'criteria': 'containing', 'value': '', 'format': fmt})
worksheet1.conditional_format('Q1:S1', {'type': 'text', 'criteria': 'containing', 'value': '', 'format': fmt})
fmt = workbook.add_format({"font_name": u"阿里巴巴普惠体","font_size":9,'bg_color': '#FAF0E7'})
worksheet1.conditional_format('J1:J%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '', 'format': fmt})
worksheet1.conditional_format('P1:P%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '', 'format': fmt})
note_fmt = workbook.add_format({'bold':True,'font_name':u'阿里巴巴普惠体','font_color':'red','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':True})
note_fmt1 = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'orange','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False})
note_fmt2 = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'gray','font_strikeout':True,
'align': 'center', 'valign': 'vcenter','underline':False})
note_fmt3 = workbook.add_format({'bold':True,'font_name':u'阿里巴巴普惠体','font_color':'#a7324a','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':2})
# worksheet1.conditional_format('H2:H%d' % l_end, {'type': 'formula', 'criteria': '', 'format': note_fmt})
# worksheet1.conditional_format('A2:A%d' % l_end, {'type': 'cell', 'criteria': '=', 'value': 38, 'format': note_fmt})
worksheet1.conditional_format('A2:A%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '新发问题', 'format': note_fmt})
worksheet1.conditional_format('A2:A%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '历史问题', 'format': note_fmt1})
worksheet1.conditional_format('A2:A%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '外部因素', 'format': note_fmt2})
worksheet1.conditional_format('A2:A%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '首发问题', 'format': note_fmt3})
note_fmt = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'white','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'#FF0000'})
note_fmt1 = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'black','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'yellow'})
note_fmt2 = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'black','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'#92D050'})
note_fmt3 = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'black','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'#00B050'})
worksheet1.conditional_format('C2:C%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '原因分析', 'format': note_fmt})
worksheet1.conditional_format('C2:C%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '对策实施', 'format': note_fmt1})
worksheet1.conditional_format('C2:C%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '预关闭', 'format': note_fmt2})
worksheet1.conditional_format('C2:C%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '关闭', 'format': note_fmt3})
note_fmt = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'black','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'#FCD5B4'})
worksheet1.conditional_format('G2:G%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '电池', 'format': note_fmt})
note_fmt1 = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'black','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'#C5D9F1'})
worksheet1.conditional_format('G2:G%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '电驱', 'format': note_fmt1})
# worksheet1.conditional_format('N2:N%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': 'SQE电驱', 'format': note_fmt1})
note_fmt2 = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'black','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'#D8E4BC'})
worksheet1.conditional_format('G2:G%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '系统集成', 'format': note_fmt2})
# worksheet1.conditional_format('N2:N%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': 'SQE系统集成', 'format': note_fmt2})
note_fmt = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'orange','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'white'})
worksheet1.conditional_format('Q2:Q%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '充电异常', 'format': note_fmt})
note_fmt = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'#C00000','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'white'})
worksheet1.conditional_format('Q2:Q%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '限功率', 'format': note_fmt})
note_fmt = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'#C00000','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':True,'bg_color':'white'})
worksheet1.conditional_format('Q2:Q%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '上电失败', 'format': note_fmt})
note_fmt = workbook.add_format({'bold':True,'font_name':u'阿里巴巴普惠体','font_color':'#C00000','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':2,'bg_color':'white'})
worksheet1.conditional_format('Q2:Q%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '行车失去动力', 'format': note_fmt})
note_fmt = workbook.add_format({'bold':True,'font_name':u'阿里巴巴普惠体','font_color':'black','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'red'})
worksheet1.conditional_format('Q2:Q%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '电池热失控', 'format': note_fmt})
note_fmt = workbook.add_format({'bold':True,'font_name':u'阿里巴巴普惠体','font_color':'black','font_strikeout':False,
'align': 'center', 'valign': 'vcenter','underline':2,'bg_color':'white'})
worksheet1.conditional_format('Q2:Q%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '碰撞', 'format': note_fmt})
writer.save()
View Code
上述代码思路: 1. 读取一个无格式的excel 2. 筛选出该excel内所有需要处理的excel表格 3. 针对每个excel 进行格式调整 4. 输出一个新的excel PS： 需要安装xlsxwriter， to_excel engine选择该库 核心：定位到问题表格 workbook = writer.book， worksheet1 = writer.sheets[sheet_name] 调整整列格式：worksheet1.set_column('A:T',10, fmt) fmt即为列的格式内容，字体，字体大小，横竖对齐等 fmt = workbook.add_format({"font_name": u"阿里巴巴普惠体","font_size":9,'valign': 'vcenter','align': 'center'}) for col_num, value in enumerate(df.columns.values): worksheet1.write(0, col_num, value, fmt) #列名称写入 # 设置列宽以及格式 worksheet1.set_column('A:T',10, fmt) worksheet1.set_column('A:A',8, fmt) worksheet1.set_column('B:B',4, fmt) worksheet1.set_column('C:C',7, fmt) worksheet1.set_column('D:D',3, fmt) worksheet1.set_column('E:E',2, fmt) worksheet1.set_column('I:I',2, fmt) worksheet1.set_column('H:H',6, fmt) worksheet1.set_column('K:K',7, fmt) worksheet1.set_column('L:L',6, fmt) fmt = workbook.add_format({"font_name": u"阿里巴巴普惠体","font_size":9,'valign': 'vcenter','align': 'left'}) worksheet1.set_column('J:J',40, fmt) worksheet1.set_column('P:P',30, fmt) 根据每列条件调整显示的格式：worksheet1.conditional_format, 同样的，选择区域后(如下代码为C列，L_END提前筛选了列长度)，对保护某一字段进行格式修改。修改为note_fmt note_fmt = workbook.add_format({'bold':False,'font_name':u'阿里巴巴普惠体','font_color':'white','font_strikeout':False, 'align': 'center', 'valign': 'vcenter','underline':False,'bg_color':'#FF0000'}) worksheet1.conditional_format('C2:C%d' % l_end, {'type': 'text', 'criteria': 'containing', 'value': '原因分析', 'format': note_fmt})同样的 criteria 除了containing也可以使用对比符号： # worksheet1.merge_range('A1:B1', u'测试情况统计表', note_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': 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() |
【本文地址】
今日新闻 |
推荐新闻 |