import pandas as pd
import xlwt
import os
import glob
import matplotlib.pyplot as plt
from openpyxl import Workbook
from openpyxl import load_workbook
from matplotlib import rcParams
##获取当前路径
path = os.getcwd()
##获取当前路径下（不包含子目录)的所有xlsx文件
zx_workbooks = glob.glob(path + r"\*.xlsx")
yys_infos = []
yys_prices = []
yys_month = 1
for workbook in zx_workbooks:
wb = load_workbook(workbook, data_only=True)
ws = wb.worksheets[0]
yd_number = ws["O1"].value
lt_number = ws["P1"].value
dx_number = ws["Q1"].value
yd_month_price = ws["O2"].value
lt_month_price = ws["P2"].value
dx_month_price = ws["Q2"].value
yys_info = {
'月份' :yys_month,
'移动数量':yd_number,
'联通数量':lt_number,
'电信数量':dx_number,
}
yys_infos.append(yys_info)
yys_price = {
'月份':yys_month,
'移动每月费用': yd_month_price,
'联通每月费用': lt_month_price,
'电信每月费用': dx_month_price,
}
yys_prices.append(yys_price)
yys_month += 1
##创建一个临时excel文件，并命名一个名为temp的sheet
workbook = xlwt.Workbook(encoding="utf-8")
table1 = workbook.add_sheet('temp1')
table2 = workbook.add_sheet('temp2')
##写入表头
table1.write(0,0,'月份')
table1.write(0,1,'移动数量')
table1.write(0,2,'联通数量')
table1.write(0,3,'电信数量')
table2.write(0,0,'月份')
table2.write(0,1,'移动每月费用')
table2.write(0,2,'联通每月费用')
table2.write(0,3,'电信每月费用')
##写入数据
curr_row = 1
for yys in yys_infos:
table1.write(curr_row,0,yys['月份'])
table1.write(curr_row,1,int(yys['移动数量']))
table1.write(curr_row,2,int(yys['联通数量']))
table1.write(curr_row,3,int(yys['电信数量']))
curr_row += 1
curr_row = 1
for yys in yys_prices:
table2.write(curr_row,0,yys['月份'])
table2.write(curr_row,1,int(yys['移动每月费用']))
table2.write(curr_row,2,int(yys['联通每月费用']))
table2.write(curr_row,3,int(yys['电信每月费用']))
curr_row += 1
workbook.save('temp.xlsx')
df1 = pd.read_excel("temp.xlsx",sheet_name='temp1')
df2 = pd.read_excel("temp.xlsx",sheet_name='temp2')
##定义拆现图字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
fig = plt.figure(figsize=(7,5))
ax = fig.add_subplot(1,2,1)
##设置拆线图数据来源 标签 颜色 字体颜色 字体大小
ax.plot(df1["月份"],df1["移动数量"],label='移动',linewidth=1,color='c',marker='o',markerfacecolor='blue',markersize=5)
ax.plot(df1["月份"],df1["联通数量"],label='联通',linewidth=1,color='y',marker='o',markerfacecolor='blue',markersize=5)
ax.plot(df1["月份"],df1["电信数量"],label='电信',linewidth=1,color='r',marker='o',markerfacecolor='blue',markersize=5)
##设置拆线图横坐标
ax.set_xlabel("每月专线数量")
##设置拆线图纵坐标
ax.set_ylabel("数量")
bx = fig.add_subplot(1,2,2)
bx.ticklabel_format(style = "plain")
##设置拆线图数据来源 标签 颜色 字体颜色 字体大小
bx.plot(df2["月份"],df2["移动每月费用"],label='移动',linewidth=1,color='c',marker='o',markerfacecolor='blue',markersize=5)
bx.plot(df2["月份"],df2["联通每月费用"],label='联通',linewidth=1,color='y',marker='o',markerfacecolor='blue',markersize=5)
bx.plot(df2["月份"],df2["电信每月费用"],label='电信',linewidth=1,color='r',marker='o',markerfacecolor='blue',markersize=5)
##设置拆线图横坐标
bx.set_xlabel("每月专线金额")
##设置拆线图纵坐标
bx.set_ylabel("金额 单位：元")
bx.legend()
bx.grid()
plt.show()
##删除temp.xlsx文件
os.remove(path + r"\temp.xlsx")