业务需求
每天定时发送excel生成的数据报表。
功能模块
主要功能分四部分:
- 爬虫代码爬取excel需要的数据源;
- 运行excel的宏,执行刷新保存功能;
- 对指定区域截图保存;
- 发送图片到企业微信;
爬虫
数据源是公司业务数据,爬取下载方式多样,略过;
运行excel宏,刷新保存excel数据
- excel内用power query 做数据处理,也可以用python,生成报表数据,设置格式;
- 录制数据刷新保存的宏;
- 运行python代码执行刷新保存的宏
import win32com
import pythoncom
import os
# 宏名称
refresh_name = '刷新保存宏'
def useVBA(VBA, file): # file为需要运行宏刷新保存的文件
# 开启多线程
pythoncom.CoInitialize()
xlApp = win32com.client.DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = False
xlBook = xlApp.Workbooks.Open(file, False)
try:
xlBook.Application.Run(VBA)
except EOFError as e:
print(e)
finally:
xlBook.Close(True) # True为保存
xlApp.quit()
# 关闭多线程
pythoncom.CoUninitialize()
useVBA(refresh_name, os.path.join(file_path, file_name))
指定区域截图
指定区域截图代码
from PIL import ImageGrab
from win32com.client import DispatchEx
import pythoncom
import os
import time
class Excel_screenshot(object):
def __init__(self, path, file_name, sheet_name, sheet_area):
img_name = file_name.split('.')[0]
file_path = os.path.join(path, file_name)
img_path = os.path.join(path, img_name)
pythoncom.CoInitialize() # 开启多线程
# 创建Excel对象
excel = DispatchEx('excel.application')
excel.visible = False # 不显示Excel
excel.DisplayAlerts = False # 关闭系统警告(保存时不会弹出窗口)
# excel.ScreenUpdating = 1 # 关闭屏幕刷新
try:
workbook = excel.workbooks.Open(file_path) # 打开Excel文件
sheet = workbook.worksheets[sheet_name]
# 截图区域为sheet_area = 'A1:I{}' ,sheet_max_row 为检测有数据的最大行,填充到sheet_area
# 经过测试,sheet_max_row只要取大于有数据的行数,就会截取当前有数据的区域
sheet_max_row = 9999
print('---------------------------------')
for row in range(2, 9999):
# print(row)
sheet_value = sheet.Cells(row, 1).Value
if sheet_value == None:
sheet_max_row = row
break
print('最大行:{}'.format(row))
print('---------------------------------')
screen_area = sheet_area.format(sheet_max_row) # 有内容的区域
screen_area = sheet.Range(screen_area)
# print(screen_area)
# 以下5个time.sleep是为了解决偶尔截图为空的bug,可以删减,具体需要测试
time.sleep(2)
screen_area.CopyPicture() # 复制图片区域
time.sleep(1)
sheet.Paste() # 粘贴
time.sleep(1)
excel.Selection.ShapeRange.Name = img_name # 将刚刚选择的Shape重命名,避免与已有图片混淆
time.sleep(1)
sheet.Shapes(img_name).Copy() # 选择图片
time.sleep(1)
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
# time.sleep(2)
print(img)
img.save(img_path + ".png")
except EOFError as e:
print(e)
finally:
workbook.Close(False) # 关闭Excel文件,不保存
excel.Quit() # 退出Excel
pythoncom.CoUninitialize() # 关闭多线程
if __name__ == "__main__":
Excel_screenshot(r'E:\报表自动发送', '年末激励方案.xlsm', 'sheet1', 'A1:I{}')
发送图片到企业微信
发送图片到企业微信代码,以下代码发送了截图和附加的文字信息,send_url是企业微信机器人的url,其他需要发送给个人或群聊(只有用API建的群聊才有群聊ID)的参考企业微信API:https://work.weixin.qq.com/api/doc/90000/90135/90664
发送的图片需要经过base64编码并传递md5参数
import os
import time
import requests
import base64
import hashlib
class Sending(object):
def __init__(self, img_path, img_name, message, send_url):
base64_data = base64.b64encode(self.open_img(img_path, img_name))
# print(base64_data)
base64_data = str(base64_data, encoding='utf-8')
img_read_md5 = hashlib.md5(self.open_img(img_path, img_name)).hexdigest()
headers = {"Content-Type": "text/plain"}
json_img = {"msgtype": "image", "image": {"base64": base64_data, "md5": img_read_md5}}
rsp_data = requests.post(send_url, headers=headers, json=json_img)
print(rsp_data.text)
json_message = message
json_text = {"msgtype": "text", "text": {"content": json_message}}
rsp_data = requests.post(send_url, headers=headers, json=json_text)
print(rsp_data.text)
def open_img(self, path, name):
with open(os.path.join(path, name), 'rb') as f:
return f.read()
if __name__ == "__main__":
Sending(r'E:\报表自动发送', '年末激励方案.png', 'test', 'test')
最后主程序调用以上模块,循环检测时间,定时发送截图
import os
import time
import win32com
import pythoncom
from win32com.client import DispatchEx
from python_code.spider_v3_offline import soure_offdata # 爬虫模块
from python_code.spider_v3_online import soure_ondata # 爬虫模块
from python_code.excel_screenshot_news import Excel_screenshot # 截图模块
from python_code.news_sending import Sending # 发送模块
file_path = r'E:\报表自动发送'
file_name = '年末激励方案.xlsm'
img_name = '年末激励方案.png'
# 截图表单
sheet_name = 'sheet1'
# 截图区域
sheet_area = 'A1:I{}'
# 附加信息
message = '@所有人 截止{}日{}点,******'
# 机器人url
url = '******'
# 发送时间
send_time = ['12:00', '14:00', '16:00', '18:00', '20:00', '22:00', '00:00']
# 宏名称
refresh_all = '刷新保存宏'
def useVBA(VBA, file):
# 开启多线程
pythoncom.CoInitialize()
xlApp = win32com.client.DispatchEx("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = False
xlBook = xlApp.Workbooks.Open(file, False)
try:
xlBook.Application.Run(VBA)
except EOFError as e:
print(e)
finally:
xlBook.Close(True)
xlApp.quit()
# 关闭多线程
pythoncom.CoUninitialize()
while True:
on_time = time.strftime("%H:%M", time.localtime(time.time()))
on_time_print = time.strftime("%H:%M:%S", time.localtime(time.time()))
print('当前时间:%s' % on_time_print)
time.sleep(1)
if on_time in send_time:
localtime = time.localtime(time.time())
if on_time == '00:00':
message_send = message.format(str(int(localtime.tm_mday) - 1), '24')
else:
message_send = message.format(localtime.tm_mday, localtime.tm_hour)
# 下载数据
print('-' * 60 + '下载数据')
soure_offdata()
time.sleep(5)
soure_ondata()
time.sleep(5)
print('-' * 60 + '下载数据完成')
# 运行VBA
print('-' * 60 + '运行VBA刷新报表')
useVBA(refresh_all, os.path.join(file_path, file_name))
print('-' * 60 + '运行VBA刷新报表完成')
time.sleep(5)
print('-' * 60 + '报表截图')
Excel_screenshot(file_path, file_name, sheet_name, sheet_area)
time.sleep(5)
print('-' * 60 + '报表发送')
Sending(file_path, img_name, message_send, url)
print('-------------------------------时间:%s 发送完成!' % on_time)