vlambda博客
学习文章列表

前言

为什么需要自动化？随着科技的不断发展，进步与创新，不管是智能工厂，无人驾驶汽车，还是路边面馆的自动削面机器人都应用着自动化技术。自动化可以给我们的生活带来极大便利的同时，也会节省我们的人力成本，那么作为职场人，经常要对一些日报，周报，月报做汇总，并且格式一般情况下都是固定的情景下，我们可以借助Python实现自动化报表，大大节省我们的时间成本，去做更有意义的事情。本次项目抛砖引玉，和大家一起学习交流，从而不断迭代。

1、环境准备

python版本：3.8

安装库：

命令：pip install 包名

需要的包如下：

pandas，numpy，lxml，wheel，yamail，openpyxl，xlwings，PIL，Pyecharts，snapshot_selenium

本次库说明：

pandas：是用来数据处理的，而lxml和wheel是相关依赖

numpy：用于处理数组的即计算

yamail：用来发送邮件

openpyxl、xlwings：用来处理excel的写入与读取

Pyecharts：用来制作可视化图表

snapshot_selenium：用来处理存储可视化图表

PIL：用来处理图像

2、数据处理

2.1、数据来源

数据来源于我的zhihu数据库下的order_details的数据，部分数据如下：

该表中的数据总共有10000行，我们需要按照年份，月份统计销售量。

2.2、数据读取

首先我们需要从数据库读取数据，然后根据要求生成DataFrame格式，方便我们后面做数据处理。数据读取部分如下：

代码如下：

  
    
    
  

   
     
     
   import pandas as pd
import pymysql

class AutoExcel:
 def __init__(self):
 self.conn = pymysql.connect(user="root", password="123456", host="localhost", database='zhihu')
 def _select(self,sql):
 """
 查询数据
 :param sql: 查询语句
 :return: 返回查询结果
 """
 try:
 # 建立一个游标
 cursor = self.conn.cursor()
 # sql = ''
 cursor.execute(sql)
 results = cursor.fetchall()
 return results
 except Exception as e:
 print('查询错误', e)
 def _readData(self):
 """
 读取查询的数据
 :return: 返回DataFrame表格
 """
 sql = 'select * from order_details'
 results = self._select(sql)
 result_list = []
 for result in results:
 result_dict = {}
 result_dict['时间'] = result[0].strftime("%Y-%m-%d")
 result_dict['销量'] = result[1]
 result_list.append(result_dict)
 df = pd.DataFrame(result_list)
 #print(df)
 return df

if __name__ == '__main__':
 ae = AutoExcel()
 ae._readData()
  
    
    
  

2.3、数据统计

我们需要分别按照年份和月份去计算销量，通过日期字段，添加年份和月份字段，然后分别按照年份和年份-月份分组求和，然后再转换成DataFrame表格方面可视化

部分数据示例：

代码如下：

  
    
    
  

   
     
     
    def _transfromData(self):
 """
 数据统计
 :return: 返回年份销量和月份销量
 """
 df = self._readData()
 df['年份'] = df.时间.str[:4]
 df['月份'] = df.时间.str[5:7]
 #统计每年销量
 yearDf = df.groupby(by='年份')['销量'].count()
 _yearDf = pd.DataFrame({'年份':yearDf.index.tolist(),'销量':yearDf.values.tolist()})
 # print(_yearDf)
 #统计每月销量
 monthYearDf = df.groupby(by=['年份','月份'])['销量'].count()
 _yaer = [y[0] for y in monthYearDf.index.tolist()]
 _month = [y[1] for y in monthYearDf.index.tolist()]
 _monthYearDf = pd.DataFrame({'年份':_yaer,'月份':_month,'销量':monthYearDf.values.tolist()})
 # print(_monthYearDf)
 return _yaer,_monthYearDf
  
    
    
  

2.4、图表可视化

我们利用Pyecharts制作图表，为啥利用Pyecharts这个库呢？是因为Pyecharts这个库是基于百度开源的Echarts封装的，图表非常丰富。

我们专门建立一个图片文件夹，用来存储我们的图表。

可视化图表如下：

代码如下：

  
    
    
  

   
     
     
    def _makeBarEcharts(self):
 """
 绘制柱状图
 :return:
 """
 bar = (
 Bar()
 .add_xaxis(self._yearDf['年份'].tolist())
 .add_yaxis("销量", self._yearDf['销量'].tolist())
 .set_global_opts(title_opts=opts.TitleOpts(title="年度总销量", subtitle="数据来源：亮哥数据实验室",
 pos_left="center"),
 xaxis_opts=opts.AxisOpts(name="年份"), # 添加X轴标题
 yaxis_opts=opts.AxisOpts(name="销量"), # 添加Y轴标题
 legend_opts=opts.LegendOpts(is_show=False)) # 不显示图例

 )
 make_snapshot(snapshot, bar.render(), "./图片/年度总销量.png",is_remove_html=True)
 def _makeLineEcharts(self):
 """
 绘制折线图
 :return:
 """
 # 绘制
 mt = self._monthYearDf['年份']+self._monthYearDf['月份']
 line = (
 Line()
 .add_xaxis(mt.values.tolist())
 .add_yaxis("销量", self._monthYearDf['销量'].tolist())
 .set_global_opts(title_opts=opts.TitleOpts(title="每月销量", subtitle="数据来源：亮哥数据实验室",
 pos_left="center"),
 xaxis_opts=opts.AxisOpts(name="月份"), #添加X轴标题
 yaxis_opts=opts.AxisOpts(name="销量"), #添加Y轴标题
 legend_opts=opts.LegendOpts(is_show=False)) #不显示图例

 )
 make_snapshot(snapshot, line.render(), "./图片/每月销量.png", is_remove_html=True)
  
    
    
  

2.5、数据写入Excel

我们需要把我们的数据写入Excel，这里利用Openpyxl库进行写入。

这里我们我数据居中，设置字体为微软雅黑，边框，细线，指定区域填充白色，并且插入我们的图片，如下图：

代码如下：

  
    
    
  

   
     
     
    def _excelStyle(self,ws):
 """
 设置表格样式
 :param ws: 传入sheet对象
 :return: 
 """
 # 设置边框,字体，数据居中
 side = Side(style="thin", color="000000")
 border = Border(left=side, right=side, top=side, bottom=side)
 for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=ws.max_row):
 for cell in row:
 cell.font = Font(name='微软雅黑', size=12)
 cell.border = border
 cell.alignment = Alignment(horizontal="center", vertical="center")
 for row in ws.iter_rows(min_row=1, max_col=ws.max_column, max_row=1):
 for cell in row:
 cell.font = Font(name='微软雅黑', size=14, bold=True)
 cell.border = border
 cell.alignment = Alignment(horizontal="center", vertical="center")
 # 填充指定区域为白色
 for row in ws.iter_rows(min_row=1, min_col=4, max_row=25, max_col=14):
 for cell in row:
 cell.fill = PatternFill("solid", fgColor="FFFFFF")
 def _writerDataExcel(self):
 """
 写入数据
 :return: 
 """
 wb = Workbook()
 wb.properties.creator = '亮哥数据实验室'
 #激活表格
 ws = wb.active
 ws.title = "年度销量"
 ws1 = wb.create_sheet(title="每月销量")
 #添加年度销量数据
 for r in dataframe_to_rows(self._yearDf, index=False):
 ws.append(r)
 #添加每月销量数据
 for r in dataframe_to_rows(self._monthYearDf, index=False):
 ws1.append(r)
 # 设置年度销量列宽
 ws.column_dimensions['A'].width = 8.5
 ws.column_dimensions['B'].width = 9
 # 设置每月销量列宽
 ws1.column_dimensions['A'].width = 8.5
 ws1.column_dimensions['B'].width = 5
 ws1.column_dimensions['C'].width = 8.5
 #设置年度销量边框,字体，数据居中,填充量指定区域为白色
 self._excelStyle(ws)
 # 设置每月销量边框,字体，数据居中
 self._excelStyle(ws1)

 #插入年度销量图片
 yearDfImg = Image('./图片/年度总销量.png')
 imgSize = (700,500)
 yearDfImg.width,yearDfImg.height = imgSize #设置图片大小
 ws.add_image(yearDfImg, 'D2')
 #插入每月销量图片
 yearDfImg = Image('./图片/每月销量.png')
 imgSize = (700, 500)
 yearDfImg.width, yearDfImg.height = imgSize # 设置图片大小
 ws1.add_image(yearDfImg, 'D2')
 wb.save('./表格/亮哥数据实验室.xlsx')
 wb.close()
  
    
    
  

2.6、实现自动化截图

我们有一个需求是自动化截图，把我们数据和图表添加到我们邮件中，这里需要用到xlwings来进行截图操作。

如下图：

代码如下：

  
    
    
  

   
     
     
    def _cropExcelImag(self,path,*args):
 """
 实现sheet截图
 :param path: 目标excel文件路径
 :param args: 参数
 :return: 返回图像路径
 """
 app = xw.App(visible=False, add_book=False) # 使用xlwings的app启动
 wb = app.books.open(path) # 打开文件
 sheet = wb.sheets[args[0]] # 选定sheet
 sheet[args[1]].api.CopyPicture() # 复制图片区域
 sheet.api.Paste() # 粘贴
 img_name = args[0]
 pic = sheet.pictures[1] # 当前图片
 pic.api.Copy() # 复制图片
 img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
 img_path = "./截图/{}".format(datetime.datetime.now().date()) + img_name + ".png"
 img.save(img_path) # 保存图片
 pic.delete() # 删除sheet上的图片
 wb.close() # 不保存，直接关闭
 # app.kill()
 app.quit()
 # print(img_path)
 return img_path
 def _cropImag(self,path):
 """
 实现截取多个excel图像
 :param path: 目标excel文件路径
 :return: 返回图像路径
 """
 sheetName1 = '年度销量'
 sheetName2 = '每月销量'
 sheetArea = 'A1:N25'
 imgPath1 = self._cropExcelImag(path,sheetName1,sheetArea)
 time.sleep(5)
 imgPath2 = self._cropExcelImag(path, sheetName2,sheetArea)
 return imgPath1,imgPath2
  
    
    
  

3、实现带图表的邮件自动化

前面的准备工作都完成了，我们只需要把我们的表啊，图啊，写入邮件，实现自动发送。需要发送邮件我们有两个库smtplib和yamail，而yamail是前者的封装，代码更为简洁，即用后者来实现我们的需求。

邮件效果如下：

代码如下：

  
    
    
  

   
     
     
    def _sendEmail(self,path,*args):
 """
 发送邮件
 :param path: 表格文件路径
 :param args: 图片路径
 :return: 
 """
 excel_path = path
 yag = yamail.SMTP(user='你的qq邮箱',
 password='qq授权码',
 host='smtp.qq.com')
 # 邮件内容
 select_datetime = datetime.datetime.now()
 img_path = args
 contents = [
 '更新时间: {}'.format(select_datetime),
 '数据内容：年度销量和每月销量可视化',
 yamail.inline(img_path[0]),
 yamail.inline(img_path[1])
 ]

 yag.send(to='你的qq邮箱', subject='Python自动化', contents=contents,
 attachments=excel_path)
 print('邮件发生完成')
  
    
    
  

4、部署

部署有两种方式：

a）部署在window或者Maxc电脑上

b）部署在服务器上

这里对于大众来说，部署在本地电脑即可。

部署步骤：

①快捷键：win+R，输入control后确定,找到管理程序并点击，找到任务计划程序并点击

②点击创建任务

③在常规选项填写一些基本信息

④切换到触发器，点击新建，在弹出的窗口选择程序触发的时间，根据实际需求修改

⑤切换到操作选项，点击新建，在程序或脚本输入你的脚本路径，然后在起始于输入开始目录，点击确定

⑥点击确定，就可以看见我们的程序已经部署好了

写到最后，如果你看到这里，说明你对整个自动化的流程有了大概的了解，只看不练永远都无法使自己进步。如果你需要进行学习，却无从下手，可以后台咨询亮哥，一对一指导服务。