高效应用Python处理电子表格
# -*- coding: utf-8 -*-
"""
Created on Sat Mar 7 10:23:05 2020
功能说明：（已完成）
1.从子表中收集日得分和日期汇总至'总体情况'表格中
2.'总体情况'未达标项目标红字体（达标设置为95）、统计错误数目、不达标项数、已达标项目数
3.日报标识不达标项目、统计错误项目
@author: xixi
"""
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.styles.colors import RED,BLACK
from openpyxl.drawing.image import Image
#定义全局变量
fill_red=PatternFill("solid",fgColor=colors.RED)
REDFont=Font(color=RED)
BLACKFont=Font(color=BLACK)
img = 'test.png'
# 设置绘图风格
plt. style.use("ggplot")
# 设置中文编码和符号的正常显示
plt.rcParams["font.sans-serif"] = "KaiTi"
plt.rcParams["axes.unicode_minus"] = False
def restart_count(file_name):
wb = load_workbook(file_name,data_only=True)
all_sheetsname = wb.sheetnames
print('原表格数量：%d个\n'%len(all_sheetsname)+'原表格名称：'+str(all_sheetsname))
#新建总表，用于统计周期得分情况
csheet='总体情况'
while(all_sheetsname[0]==csheet):
wb.remove(wb['总体情况'])
print('删除<'+str(csheet)+'>成功！')
break
wb.save(file_name)#一定要保存，不然白做！
def color_number(sheetcolor):
rows=sheetcolor.max_row
for i in range(1,rows):
number=round(sheetcolor.cell(i+1,5).value,2)#强制转换数值
if number<95:#设置判断阈值95
sheetcolor.cell(i+1,5).font=REDFont#不达标项目字体变红色
def find_dailyIndex_in_xlsx(file_name):
restart_count(file_name)
wb = load_workbook(file_name,data_only=True)
all_sheets= wb.sheetnames
print('日指标统计天数：%d天\n'%len(all_sheets)+'表格名称：'+str(all_sheets))
#新建总表，用于统计周期得分情况
csheetname='总体情况'
ws=wb.create_sheet(csheetname,0)
ws['A1']='序号'
ws['B1']='统计日期'
ws['C1']='指标满分'
ws['D1']='指标得分'
ws['E1']='百分制得分'
ws['F1']='环比'
ws['G1']='100%指标数'
ws['H1']='问题指标数'
ws['I1']='指标统计错误数'
for i in range(len(all_sheets)):
#获取表格信息
sheet = all_sheets[i]
print ('表格%d: ' %i+ str(sheet)+': max_row: ' + str(wb[sheet].max_row) + ' max_column: ' + str(wb[sheet].max_column))
#获取指标日期和得分信息
dateIndex=wb[sheet]['B27'].value#指标日期-其他获取方式dateIndex=wb[sheet].cell(27,2).value
HezhouIndex=wb[sheet]['D26'].value#指标得分
print('指标统计日期：'+str(dateIndex)+' 指标得分：%.2f'%HezhouIndex)
##统计日报不达标指标项目标红
sctcolor=wb[sheet]
red_count=0
black_count=0
err_count=0
for j in range(1,25):
sct_number=round(sctcolor.cell(j+1,4).value,2)#强制转换数值
target_number=round(sctcolor.cell(j+1,3).value,2)#强制转换数值
if sct_number<target_number:#设置判断阈值target_number
sctcolor.cell(j+1,4).font=REDFont#不达标项目字体变红色
red_count+=1
elif sct_number==target_number:
sctcolor.cell(j+1,4).font=BLACKFont#达标项目字体变黑色
black_count=black_count+1
else:
sctcolor.cell(j+1,4).font=BLACKFont#达标项目字体变黑色
sctcolor.cell(j+1,4).fill=fill_red#指标统计错误项目背景填充红色
err_count=err_count+1
#汇总得分情况至总表
ws.cell(i+2,1).value=i+1
ws.cell(i+2,2).value=dateIndex
ws.cell(i+2,3).value=wb[sheet]['C26'].value
ws.cell(i+2,4).value=float("%.2f"%HezhouIndex)#转换2位小数，其他方式：round(HezhouIndex,2)
sct=ws.cell(i+2,4).value*100/ws.cell(i+2,3).value#计算百分制得分
ws.cell(i+2,5).value=float("%.2f"%sct)#format(ws.cell(i+2,3).value*100/ws.cell(i+2,2).value,'.4')
if i==0:
ws.cell(i+2,6).value=''
else:
ws.cell(i+2,6).value=format((ws.cell(i+2,5).value-ws.cell(i+1,5).value)*100/ws.cell(i+1,5).value,'.3')
ws.cell(i+2,8).value=red_count
ws.cell(i+2,7).value=black_count-err_count
ws.cell(i+2,9).value=err_count
while(err_count!=0):
ws.cell(i+2,9).fill=fill_red
break
color_number(ws)#标识不达标项
wb.save(file_name)#一定要保存，不然白做！
def draw_Index(file_name):
# 设置图框的大小
#fig = plt.figure(figsize = (1000,600))
#获取xlsx中的sheet的辅助列0，x轴数据列1，y轴数据列4
sct=pd.read_excel(file_name,sheet_name='总体情况',usecols=[0,1,4])
#print(sct)
#绘制坐标图x=辅助列，y=y轴数据列
sct.plot(x=sct.columns[0],y=sct.columns[2],
linestyle = '-', # 折线类型
linewidth = 2, # 折线宽度
color = 'steelblue', # 折线颜色
marker = 'o', # 点的形状
markersize = 6, # 点的大小
markeredgecolor='black', # 点的边框色
markerfacecolor='brown')
#替换x轴刻度和标识
x = np.array(sct[sct.columns[0]])
y = np.array(sct[sct.columns[2]])
x_t = np.array(sct[sct.columns[1]])
plt.xlabel('')
plt.xticks(x,x_t)
# print(x[len(x)-1])
#绘制指标线95
target=95
plt.axhline(y=target,ls=":",c="red")
plt.text(x[len(x)-1],target,'考核目标值'+str(target),color='r',verticalalignment="baseline",
horizontalalignment="right")
# 添加标题和坐标轴标签
plt.ylim(80,100)#设置Y轴取值范围
plt.title('3月实用化指标趋势')
plt.xlabel('统计日期')
plt.ylabel('日指标得分')
# 剔除图框上边界和右边界的刻度
plt.tick_params(top = 'off', right = 'off')
#在折线点标识每日得分
for a,b in zip(x,y):
plt.annotate('(%s)'%b,xy=(a,b),xytext=(-10,10),
textcoords='offset points')
# 插入图形
wb = load_workbook(file_name)
plt.savefig(img)
Image_all=Image(img)
ws = wb['总体情况']
ws.add_image(Image_all,'J1')
wb.save(file_name)#一定要保存，不然白做！
# 主程序
file_name='test003.xlsx'
find_dailyIndex_in_xlsx(file_name)
draw_Index(file_name)
运行效果图：