python实现自动化报表2

自动化一个报表，用到的知识点和上一篇的知识点是一样的，但是有一个点需要注意：

注意点：我们的自动化报表是先从公司网站上将数据下载下来之后，填充到excel（报表模板）中相应的sheet之中，然后运用excel的公式自动化处理数据并呈现成图，实现报表的自动化，但是有一点需要注意，将从网站爬取得数据（DataFrame）插入excel的某个sheet的过程中，excel模板中的将数据呈现为图的一块的公式会缺失，现在在网上找不到原因，DataFrame和excel连接的模块比较少，所以自动化报表最好是针对数据的自动化处理，不带图的显示。

代码如下：

# coding=gbk
import json
import os
import shutil
import time
from datetime import datetime, date, timedelta
import openpyxl
import requests
from bs4 import BeautifulSoup
import pandas as pd
from openpyxl import load_workbook  # openpyxl 版本必须是2.4.2，否则出错
from openpyxl.utils.dataframe import dataframe_to_rows
from sqlalchemy import create_engine
import urllib3

urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
from lxml import etree



class day_report():
	def __init__(self,today,yesterday):
		self.headers = {
		'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36'
	}
		self.cookies = {'我有轻微焦虑症，有些快乐并痛苦的活着'}
		self.today = today
		self.yesterday = yesterday
		self.con = create_engine('mysql+pymysql://xxx:bjsjb123@xxx:3306/001ribao')
		self.today_model1 = 'C:\\Users\\lenovo\\Desktop\\运营中心日报模板\\{}\\'.format(today) + today + '.xlsx'
		self.today_model2 = 'C:\\Users\\lenovo\\Desktop\\运营中心日报模板\\{}\\'.format(today) + today + '_总''.xlsx'
		self.yesterday_model1 = 'C:\\Users\\lenovo\\Desktop\\运营中心日报模板\\{}\\'.format(yesterday) + yesterday + '.xlsx'
		self.yesterday_model2 = 'C:\\Users\\lenovo\\Desktop\\运营中心日报模板\\{}\\'.format(yesterday) + yesterday + '_总''.xlsx'
	
	def copy_files(self):
		# '''生成当日的excel日报的文件夹和文件'''
		os.mkdir(r'C:\Users\lenovo\Desktop\运营中心日报模板\{}'.format(self.today)) #产生一个空的文件夹
		shutil.copy(self.yesterday_model1, self.today_model1)
		shutil.copy(self.yesterday_model2, self.today_model2)
		#分别下载暂存、邮政、快递员和数据库中数据并存入到excel的特定的sheet
		self.zancun()
		self.youzheng_data()
		self.kuaidiyuan()
		self.sql_day_information()
	
	def kuaidiyuan(self):
		'''下载快递员数据详情'''
		# 第一步 先将快递员表格信息下载下来
		target_url1 = 'https://bass.sudiyi.cn/api/v2/admin/auth_file/sign?name=%E8%AE%BE%E5%A4%87%E5%BF%AB%E9%80%92%E5%91%98%E6%95%B0%E6%8D%AE_{}.xlsx'.format(self.yesterday)
		try:
			req = requests.get(url=target_url1, headers=self.headers, cookies=self.cookies, verify=False)
		except Exception as e:
			print('%s出现问题：' % 'target_url1', e)
		else:
			html = req.text  # <class 'bytes'> 存放字节码 text存放 .content编码后的字符串
			html = json.loads(html)
			target_url2 = html['data']['name']
			try:
				req = requests.get(url=target_url2, headers=self.headers, cookies=self.cookies, verify=False)
			except Exception as e:
				print('%s出现问题：' % 'target_url1', e)
			else:
				# req.encoding = 'utf-8' #自动解码
				html = req.content  # <class 'bytes'> 存放字节码 text存放 .content编码后的字符串
				lujing = r'C:\Users\lenovo\Desktop\快递员数据\{}.xlsx'.format(self.yesterday)
				if os.path.exists(lujing):  # 判断文件是否存在
					os.remove(lujing)  # 如果存在文件则删除
				with open(lujing, 'wb') as e:
					e.write(html)
				# 第二步将快递员信息处理之后放入excel指定的sheet中
				a = pd.read_excel(lujing)
				df = a.pivot_table(index='快递员电话', aggfunc={'投件量': 'sum'})
				# 将数据放在已经存在的excel的某个sheet（第一种方法，但是会遗失画图的，所以现在把画图和数据拆开放了）
				book = load_workbook(self.today_model1)
				writer = pd.ExcelWriter(self.today_model1, engine='openpyxl')
				writer.book = book
				book.remove(book['投件快递员'])  # 删除已经存在的sheet
				df.to_excel(excel_writer=writer, sheet_name="投件快递员")
				writer.save()
				writer.close()
				print('快递员信息保存成功')
				
	def youzheng_data(self):
		'''下载邮政数据详情'''
		# 第一步 先将邮政数据信息下载下来
		data = {'id': 124, 'time_type': 1, 'from_date': self.yesterday, 'to_date': self.yesterday}
		r = requests.post('https://bass.sudiyi.cn/api/v2/data/warehouses/124/export', data, cookies=self.cookies, verify=False)
		# 再找到参数
		html = '数据准备中'  # 刚开始数据尚未准备好
		while html != '已完成':
			time.sleep(6)
			try:
				r1 = requests.get(url='https://bass.sudiyi.cn/api/v2/data/export/tasks?per=20&date_type=1',
								  headers=self.headers, cookies=self.cookies, verify=False)
			except Exception as e:
				print('youzheng_data 刷新网页时出错:%s' % e)
			else:
				html = r1.text  # <class 'bytes'> 存放字节码 text存放 .content编码后的字符串
				html1 = json.loads(html)['data']['list'][0]
				html = html1['status']
				print(html + '...')
		canshu = html1['file'].split('_')[-1][:-5]
		try:
			target_url = 'https://bass.sudiyi.cn/api/v2/data/export/download?file=%E5%8D%95%E6%97%A5_%E9%82%AE%E6%94%BFEMS%E6%95%B0%E6%8D%AE_{0}_{1}_{2}.xlsx'.format(
				self.yesterday, self.yesterday, int(canshu))
			req = requests.get(url=target_url, headers=self.headers, cookies=self.cookies, verify=False)
		except Exception as e:
			print('youzheng_data 导出下载链接出现问题：', e)
		else:
			html = req.text  # <class 'bytes'> 存放字节码 text存放 .content编码后的字符串
			html = json.loads(html)
			target_url2 = html['data']['name']
			try:
				req = requests.get(url=target_url2, headers=self.headers, cookies=self.cookies, verify=False)
			except Exception as e:
				print('邮政下载数据出现问题', e)
			else:
				html = req.content  # <class 'bytes'> 存放字节码 text存放 .content编码后的字符串
				daily_machine_data_lujin = r'C:\Users\lenovo\Desktop\邮政数据\%s_%s.xlsx' % (self.yesterday,self.yesterday)
				with open(daily_machine_data_lujin, 'wb') as e:
					e.write(html)
					print('邮政数据下载成功')
				#将邮政数据和自营城市进行匹配
				df = pd.read_excel(daily_machine_data_lujin)
				df1 = pd.read_excel(r'C:\Users\lenovo\Desktop\2018年中邮速递易年度数据分析报告\派件事业部-城市匹配表.xlsx')[['运营城市']].rename(
					columns={'运营城市': '运营城市名'})
				df2 = pd.merge(df, df1, on='运营城市名').set_index('设备ID')
				#因为表中含有 字符"(",这种字符用DataFrame插入excel中会出现错误，所以先将出现这种字符行列的进行替换和删除
				df2.columns = df2.columns.map(lambda x: x if len(x.split('-')) == 1 else '和'.join(x.split('-')))
				df2.drop('网点名', axis=1, inplace=True)
				df2.drop('日期', axis=1, inplace=True)
				book = load_workbook(self.today_model1)
				writer = pd.ExcelWriter(self.today_model1, engine='openpyxl')
				writer.book = book
				book.remove(book['邮政收入'])  # 删除已经存在的sheet
				df2.to_excel(excel_writer=writer, sheet_name="邮政收入")
				writer.save()
				writer.close()
				print('邮政数据保存成功')
				
	def sql_day_information(self):
		yuju = "(select `时间`日期,'全部'设备类型,(sum(`收入总额(元)`)/10000)社会快递收入,(sum(`派件收入`)/10000)派件收入,(sum(`预约收入`)/10000)预约收入,(sum(`取件收入`)/10000)取件收入,count(`设备ID`)设备数,sum(`总投件量`)/sum(`箱格数`)投件率,(sum(`总投件量`)/10000)投件量,(sum(`值守占用箱格总数`)/10000)值守占用数,(sum(`故障箱格总数`)/10000)故障箱格数,sum((case `是否疑似断网` when '断网' then 1 else 0 end))断网断电设备数,sum((case `总投件率` when  0 then 1 else 0 end ))零投件设备数,sum((case when `总投件率`<=0.3 then 1 else 0 end ))低效设备数,(sum(if(`设备类型`!='蜜蜂箱-易邮柜',`滞留件数量`,0))/10000)滞留件数量,sum((case when DATE_FORMAT(`首次激活时间`,'%%Y-%%m-%%d')={} then 1 else 0 end))首次激活设备数,(sum(case `是否开通快递员派件收费` when '付费' then 1 else 0 end )/count(`是否开通快递员派件收费`))设备收费率,(sum(`有效箱格数`)/10000)有效箱格数,(sum(`箱格数`)/10000)总格口数 from `经分-设备大表` where `时间`= {} and `运营城市_系统` in ('北京市','广州市','上海市','深圳市','成都市','大连市','杭州市','南京市','宁波市','青岛市','厦门市','沈阳市','苏州市','天津市','无锡市','武汉市','西安市','长沙市','重庆市','常州市','东莞市','佛山市','福州市','贵阳市','哈尔滨市','海口市','合肥市','惠州市','济南市','嘉兴市','金华市','昆明市','南昌市','南宁市','南通市','泉州市','汕头市','石家庄市','潍坊市','温州市','徐州市','烟台市','扬州市','郑州市','蚌埠市','宝鸡市','德阳市','赣州市','桂林市','衡阳市','湖州市','兰州市','连云港市','临沂市','柳州市','绵阳市','南充市','绍兴市','台州市','威海市','芜湖市','盐城市','宜昌市','岳阳市','漳州市','株洲市','淄博市','达州市','江门市','九江市','十堰市','湘潭市','襄阳市','遂宁市','安阳市','廊坊市','乌鲁木齐市','渭南市','琼海市','眉山市','自贡市','攀枝花市','广安市','巴中市','宜宾市','资阳市','雅安市','铜川市','玉溪市','呼和浩特市','保定市','衡水市','秦皇岛市')) union (select `时间`,RIGHT(`设备类型`,3),(sum(`收入总额(元)`)/10000)收入,(sum(`派件收入`)/10000)派件收入,(sum(`预约收入`)/10000)预约收入,(sum(`取件收入`)/10000)取件收入,count(`设备ID`),sum(`总投件量`)/sum(`箱格数`),sum(`总投件量`)/10000,sum(`值守占用箱格总数`)/10000,(sum(`故障箱格总数`)/10000)故障箱格数,sum((case `是否疑似断网` when '断网' then 1 else 0 end)),sum((case `总投件率` when  0 then 1 else 0 end )),sum((case when `总投件率`<=0.3 then 1 else 0 end )),(sum(if(`设备类型`!='蜜蜂箱-易邮柜',`滞留件数量`,0)))/10000,sum((case when DATE_FORMAT(`首次激活时间`,'%%Y-%%m-%%d')={} then 1 else 0 end)),(sum(case `是否开通快递员派件收费` when '付费' then 1 else 0 end )/count(`是否开通快递员派件收费`))设备收费率,(sum(`有效箱格数`)/10000)有效箱格数,(sum(`箱格数`)/10000)总格口数 from `经分-设备大表` where `时间`={} and `运营城市_系统` in ('北京市','广州市','上海市','深圳市','成都市','大连市','杭州市','南京市','宁波市','青岛市','厦门市','沈阳市','苏州市','天津市','无锡市','武汉市','西安市','长沙市','重庆市','常州市','东莞市','佛山市','福州市','贵阳市','哈尔滨市','海口市','合肥市','惠州市','济南市','嘉兴市','金华市','昆明市','南昌市','南宁市','南通市','泉州市','汕头市','石家庄市','潍坊市','温州市','徐州市','烟台市','扬州市','郑州市','蚌埠市','宝鸡市','德阳市','赣州市','桂林市','衡阳市','湖州市','兰州市','连云港市','临沂市','柳州市','绵阳市','南充市','绍兴市','台州市','威海市','芜湖市','盐城市','宜昌市','岳阳市','漳州市','株洲市','淄博市','达州市','江门市','九江市','十堰市','湘潭市','襄阳市','遂宁市','安阳市','廊坊市','乌鲁木齐市','渭南市','琼海市','眉山市','自贡市','攀枝花市','广安市','巴中市','宜宾市','资阳市','雅安市','铜川市','玉溪市','呼和浩特市','保定市','衡水市','秦皇岛市') group by RIGHT(`设备类型`,3))".format(self.yesterday,self.yesterday,self.yesterday,self.yesterday)
		data_sql2 = pd.read_sql_query(yuju, self.con)
		wb = load_workbook(filename=self.today_model1)
		ws = wb['过渡数据']
		width = 19
		heigth = 7
		for i in range(0, heigth - 1):
			for j in range(0, width):
				a = i + 2
				b = j + 1
				ws.cell(row=a, column=b, value="{0}".format(data_sql2.iloc[i, j]))
		
		wb.save(filename=self.today_model1)
		print('数据库数据保存成功')


	def zancun(self):
		data1, data2 = [], []
		headers = {
			'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36'
		}
		cookies = {
			'阳光雨露泽润大地'}
		target_url1 = 'http://tderp.biyoshop.com/a/reserve/list?page.pageNo=1&page.pageSize=30&orderNo=&orderType=&orderStatus=&deviceCode=&reserveBeginTime={0}+00%3A00%3A00&reserveEndTime={1}+00%3A00%3A00&boxType=&mobile=&postBeginTime=&postEndTime=&pickBeginTime=&pickEndTime=&page.orderBy='.format(
			self.yesterday, self.today)
		try:
			req = requests.get(url=target_url1, headers=headers, cookies=cookies, verify=False)
		except Exception as e:
			print('暂存数据爬取出现问题', e)
		else:
			html = req.text
			sel = etree.HTML(html)
			a = int([i.text for i in sel.xpath('//a[@href]')][-3])
			print('a:', a)
			print('暂存一共%d页' % a)
		for i in range(1, a + 1):
			print('i:', i)
			target_url1 = 'http://tderp.biyoshop.com/a/reserve/list?page.pageNo={0}&page.pageSize=30&orderNo=&orderType=&orderStatus=&deviceCode=&reserveBeginTime={1}+00%3A00%3A00&reserveEndTime={2}+00%3A00%3A00&boxType=&mobile=&postBeginTime=&postEndTime=&pickBeginTime=&pickEndTime=&page.orderBy='.format(
				i, self.yesterday, self.today)
			try:
				req = requests.get(url=target_url1, headers=headers, cookies=cookies, verify=False)
			except Exception as e:
				print('暂存数据爬取出现问题', e)
			else:
				html = req.text
				sel = etree.HTML(html)
				c = [sel.xpath('//*[@id="contentTable"]/tbody/tr[{}]/td[4]'.format(i))[0].text for i in range(1, 31) if
					 sel.xpath('//*[@id="contentTable"]/tbody/tr[{}]/td[4]'.format(i)) != []]
				d = [float(sel.xpath('//*[@id="contentTable"]/tbody/tr[{}]/td[13]'.format(i))[0].text) for i in range(1, 31)
					 if sel.xpath('//*[@id="contentTable"]/tbody/tr[{}]/td[13]'.format(i)) != []]
				data1 += c
				data2 += d
		lujing = r'C:\Users\lenovo\Desktop\暂存\{}.xlsx'.format('.'.join(self.yesterday.split('-')[1:]))
		if os.path.exists(lujing):  # 判断文件是否存在
			os.remove(lujing)  # 如果存在文件则删除
		pd.DataFrame({'订单状态': data1, '支付金额': data2}).to_excel(lujing)
		df = pd.DataFrame({'订单状态': data1, '支付金额': data2}).set_index('订单状态')
		book = load_workbook(self.today_model1)
		writer = pd.ExcelWriter(self.today_model1, engine='openpyxl')
		writer.book = book
		book.remove(book['暂存'])  # 删除已经存在的sheet
		df.to_excel(excel_writer=writer, sheet_name="暂存")
		writer.save()
		writer.close()
		print('暂存数据保存成功')


if __name__ == '__main__':
	yesterday = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d") #今日的日期
	today = time.strftime('%Y-%m-%d', time.localtime(time.time()))		  #昨日的日期
	today_report = day_report(today,yesterday)
	today_report.copy_files()

生成新的报表：