PowerBI—微软数据分析和可视化工具&工业数据分析（四）

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)