python实现自动化报表1

公司每天需要做一个报表，其中步骤也很简单，就是从网上上下载三个不同的表，然后分别将三个表中的数据塞到一个单独的表中，用这个单独表中写好的公式将这三张表的数据进行处理之后就是我想要的结果，熟练之后整个过程只需要5分钟，但是既然学了python，就想着用python实现以下：

实现了之后用代码也是5分钟，但是这个期间自己可以去干点其他的事情，啦啦啦~~~

整个过程的需要的知识点实际上也是十分简单，主要是这三个知识点和一个注意点：

第一个知识点：

爬虫：用爬虫主要是实现爬取公司网站上的表格，主要用fiddler进行抓包，根据是判断是post请求还是get请求将表格从网站上爬取下来，储存成excel表格形式，用了request库。

第二个知识点：

python和excel:讲数据爬取下来之后，如何将其存入已经存在excel的sheet呢，这个主要用的包是openpyxl,这个地方有一个需要注意的地方，openpyxl的版本必须是2.4.2，其余版本的就会出现错误。

第三个知识点：

python和sql:有几个数必须是用sql从数据库中提取的，提取出来之后呢，将其插入到已经存在的excel的指定位置，用的sqlalcgemy和openpyxl。

一个注意点：用python从excel中提取数据并插入到另一个sql的时候，是先将其提取为DataFrame格式，然后在插入的，但是这个时候会将index作为单独的一个列插入，我再用openpyxl去删除sheet中的列是，居然提示sheet没有这个功能（实际上最近版本的openpyxl有这个删除列和移动单元格的功能，但是现在我指定opnepyxl的版本是2.4.2，所以不能实现这个功能了，所以DataFrame在插入sheet之前，先将DataFrame的第一列列为index，这样插入的内容就和实际的内容一致）

import json
import shutil
import time
import requests
import pandas as pd
from openpyxl import load_workbook  # openpyxl 版本必须是2.4.2，否则出错

requests.packages.urllib3.disable_warnings()
from datetime import datetime, date, timedelta
from sqlalchemy import create_engine


class data_baobiao():
	def __init__(self):
		self.yesterday_model = r'E:\周剑辉\运营中心城市日报工具.xlsx'
		self.today_model = r'E:\周剑辉\2019\2019\运营中心城市日报%s.xlsx' % time.strftime('%Y-%m-%d', time.localtime(time.time()))
		self.con = create_engine('mysql+pymysql://sss:sss@192.175.1.10:3306/001ribao')
		self.yesterday = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")
		
		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 = {'UM_distinctid': '1670c3d124faa-03b7aab7b25d64-3a3a5c0e-100200-1670c3d1250577',
						'user_name': '不能告诉你哦',
						'TY_SESSION_ID': '神秘的西夏',
						'CNZZDATA1262040078': '721139589-1542093807-https%253A%252F%252Fhome.sudiyi.cn%252F%7C1550979300',
						'sudiyi.cas': 'dingdiangdangdangmao'}
	
	def copy_(self):
		shutil.copy(self.yesterday_model, self.today_model)
	
	def xinzengyonghubiao(self):
		target_url1 = 'https://bass.sudiyi.cn/api/v2/user/append/country/download?begin_date=&date_type=1&end_date=%s&region_id=1&download_type=area' % 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.content  # <class 'bytes'> 存放字节码 text存放 .content编码后的字符串
			xinzengyonghubiao_lujin = r'E:\周剑辉\2019\2019\新增用户表\新增用户表%s.xlsx' % self.yesterday
			with open(xinzengyonghubiao_lujin, 'wb') as e:
				e.write(html)
				print('新增用户数据数据下载成功')
			return xinzengyonghubiao_lujin
	
	"""第二个：新增快递员"""
	
	def toujian_kuaidiyuan(self):
		
		target_url1 = 'https://bass.sudiyi.cn//api/v3/business/delivery/region/1/download?date_type=1&region_id=1&begin_date=&end_date=%s&download_type=area' % 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.content  # <class 'bytes'> 存放字节码 text存放 .content编码后的字符串
			toujian_kuaidiyuan_lujin = r'E:\周剑辉\2019\2019\投件分析表\投件分析表%s.xlsx' % self.yesterday
			with open(toujian_kuaidiyuan_lujin, 'wb') as e:
				e.write(html)
				print('新增快递员数据下载成功')
			return toujian_kuaidiyuan_lujin
	
	def daily_machine_data(self):
		'''下载日常设备数据详情'''
		# 先下载数据
		data = {'id': 234, 'time_type': 1, 'from_date': self.yesterday, 'to_date': self.yesterday}
		r = requests.post('https://bass.sudiyi.cn/api/v2/data/warehouses/234/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('刷新网页时出错:%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_%E6%97%A5%E5%B8%B8%E8%AE%BE%E5%A4%87%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('导出数据1出现问题：', 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('导出数据2出现问题', e)
			else:
				# req.encoding = 'utf-8' #自动解码
				html = req.content  # <class 'bytes'> 存放字节码 text存放 .content编码后的字符串
				daily_machine_data_lujin = r'E:\周剑辉\2019\2019\日常设备数据\单日_日常设备数据_%s.xlsx' % self.yesterday
				with open(daily_machine_data_lujin, 'wb') as e:
					e.write(html)
					print('日常设备数据下载成功')
				return daily_machine_data_lujin


def sql_day_information(self):
	yuju = "(SELECT `时间`,'全部'设备类型,(sum(`总投件量`)/sum(`箱格数`))投件率 from `经分-设备大表` where `时间` = DATE_SUB(CURDATE(),INTERVAL 1 DAY ) and `设备类型` in ('一代柜','二代柜','易邮柜') ) UNION(SELECT `时间`,`设备类型`,(sum(`总投件量`)/sum(`箱格数`))投件率 from `经分-设备大表` where `时间` = DATE_SUB(CURDATE(),INTERVAL 1 DAY) and `设备类型` in ('一代柜','二代柜','易邮柜') GROUP BY `设备类型`)"
	data_sql2 = pd.read_sql_query(yuju, self.con)
	wb = load_workbook(filename=self.today_model)
	ws = wb['运营中心城市日报-终表']
	l = [3, 11, 12, 13]
	for i, j in enumerate(l):
		ws.cell(row=88, column=j, value="{0}".format(data_sql2.iloc[i, 2]))
	wb.save(filename=self.today_model)


def baocun(self, df1, name):
	if name == '日常设备数据':
		df1.set_index('设备ID', inplace=True)
		print(df1.head())
	else:
		df1.set_index('城市', inplace=True)
		book = load_workbook(self.today_model)
		writer = pd.ExcelWriter(self.today_model, engine='openpyxl')
		writer.book = book
		book.remove_sheet(book.get_sheet_by_name(name))
		df1.to_excel(excel_writer=writer, sheet_name=name)
		writer.save()
		writer.close()


if "__main__" == __name__:
	baobiao = data_baobiao()
	baobiao.copy_()  # 复制一个表先
	df1 = pd.read_excel(baobiao.xinzengyonghubiao())
	df1['新增用户数'] = df1['新增预注册用户'] + df1['新增app用户']
	df2 = pd.read_excel(baobiao.toujian_kuaidiyuan())
	df3 = pd.read_excel(baobiao.daily_machine_data())
	baobiao.baocun(df1, '新增用户数')
	baobiao.baocun(df2, '投件分布报表')
	baobiao.baocun(df3, '日常设备数据')

 实现自动生成每日报表