POWER BI实现日报推送
虽然Power BI 的Power BI Online(网页版)很方便简介,只要打开浏览器就行。但是大家并不是每天都能记得查看报表。这篇文章就给大家介绍一下如何使用Python对报表进行PDF导出,保存下来,再发送邮件。主要用到了WEB自动化工具Selenium
1.需要下载与本地chrome浏览器版本一致的ChromeDriver版本
链接: http://chromedriver.storage.googleapis.com/index.html
提示:如果版本不一致会报错,最好将本地的chrome浏览器设置为不更新。以免更新浏览器之后,ChromeDriver版本不一致报错。
2.安装Selenium:pip install selenium
3.然后需要准备一份待导出日报表。
日报最好简洁明了,报表内容不宜太多。主要显示当前的日期,按照排名筛选前5名和后5名的设备展示。
4.部分代码,主要实现对报表的导出。
从selenium里面导入webdriver
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
import os
import time
import log
driver.implicitly_wait(5) #隐式等待10秒
def get_pdf(url):
logg = log.Logger()
#chromedriver的路径
chromedriver = r"C:\Users\chromedriver.exe"
os.environ["webdriver.chrome.driver"] = chromedriver
#设置chrome开启的模式,headless就是无界面模式
#chrome_options = Options()
#chrome_options.add_argument("--headless")
#driver = webdriver.Chrome(chromedriver,chrome_options=chrome_options)
#driver = webdriver.Chrome(chromedriver)
###
download_location = 'C:\\Users\\aa\\Downloads'
chrome_options = webdriver.ChromeOptions()
prefs = {'download.default_directory': download_location,
'download.prompt_for_download': False,
'download.directory_upgrade': True,
'safebrowsing.enabled': False,
'safebrowsing.disable_download_protection': True}
chrome_options.add_experimental_option('prefs', prefs)
chrome_options.add_argument("--headless")
driver = webdriver.Chrome(chrome_options=chrome_options)
driver.command_executor._commands["send_command"] = ("POST", '/session/$sessionId/chromium/send_command')
params = {'cmd': 'Page.setDownloadBehavior', 'params': {'behavior': 'allow', 'downloadPath': download_location}}
command_result = driver.execute("send_command", params)
for key in command_result:
logg.logger.info("result:" + key + ":" + str(command_result[key]))
控制浏览器写入并转到链接
driver.get(url)
选择网页元素
注意这里必须要等待时间,因为代码运行过快,代码运行完的时候页面还没加载出来就会找不到元素
等loginfmt元素加载出来看到了
find_element_by_id通过元素id定位。登录的账号输入框input:id是i0116//name:loginfmt
element_loginfmt = WebDriverWait(driver, 10).until(EC.visibility_of_element_located((By.NAME, "loginfmt")))
element_loginfmt.send_keys('账号')
下一步input:id是idSIButton9
element_loginfmt_button = driver.find_element_by_id('idSIButton9')
element_loginfmt_button.click()
密码输入框:id是i0118//name是passwd
element_passwd = WebDriverWait(driver, 10).until(EC.visibility_of_element_located((By.NAME, "passwd")))
element_passwd.send_keys('密码')
登录input:id是idSIButton9
element_passwd_button = driver.find_element_by_id('idSIButton9')
element_passwd_button.click()
保持登录:id是idSIButton9
element_keeplog_button = driver.find_element_by_id('idSIButton9')
element_keeplog_button.click()
time.sleep(5)
try:
driver.find_elements_by_class_name('workspacesPaneExpander')
logg.logger.info(driver.find_elements_by_class_name('workspacesPaneExpander'))
except Exception:#有异常,找不到元素
logg.logger.info("error")
return False
else:
无异常,说明在页面中找到了该元素,返回True
logg.logger.info("ok")
工作区button:class是workspacesPaneExpander
element_workspaces_button = WebDriverWait(driver, 10).until(EC.visibility_of_element_located((By.CLASS_NAME, "workspacesPaneExpander")))
element_workspaces_button.click()
我的工作区button:class是myWorkspaceButton
element_myWorkspace_button = WebDriverWait(driver, 10).until(EC.visibility_of_element_located((By.CLASS_NAME, "myWorkspaceButton")))
element_myWorkspace_button.click()
通过xpath表达式定位,标签名为a的超链接,并且href属性包含4c9ee6aa-a7df-4864-ba79-a3a3c4398752
element_dayreport=driver.find_element_by_xpath("//a[contains(@href, '4c9ee6aa-a7df-4864-ba79-a3a3c4398752')]")
element_dayreport.click()
通过xpath表达式定位导出PDF
time.sleep(10) #隐式等待10秒
elebutton_export=driver.find_element_by_xpath('//*[@id="exploration-container-app-bars"]/app-bar/div/div[1]/button[2]')
elebutton_export.click()
elebutton=driver.find_elements_by_class_name('appBarMatMenu')
elebutton[1].click()#名叫appBarMatMenu的第二个
导出
element_primary_button = WebDriverWait(driver, 10).until(EC.visibility_of_element_located((By.CLASS_NAME, "primary")))
element_primary_button.click()
logg.logger.info("导出")
time.sleep(60)
#contentMain error
file_exist = os.path.exists(r"C:\Users\32556\Downloads\dayreport.pdf")
while True:
if file_exist:
logg.logger.info("文件存在")
day=time.strftime("%Y-%m-%d", time.localtime())
oldname="C:\\Users\\aa\\Downloads\\dayreport.pdf"
file="C:\\Users\\aa\\Downloads\\"
newname=file +'dayreport_'+day+'.pdf'
os.rename(oldname,newname)
logg.logger.info("重命名成功")
关掉浏览器
driver.quit()
break
else:
logg.logger.info("文件不存在")
time.sleep(40)
continue
if __name__ == '__main__':
logg = log.Logger()# 实例化类log.Logger
while(True):
time_now = time.strftime("%H:%M",time.localtime())
#每天10:00运行
if time_now == "10:00":
logg.logger.info("处理开始")
while True:
ret = get_pdf('powerbiurl')
if ret ==False:
logg.logger.error("get_pdf不成功")
continue
else:
break
time.sleep(60)
else:
logg.logger.info("未到达处理时间")
time.sleep(20)