python实现从oracle数据库查询数据生成excel透视表发送outlook

# -*- coding: utf-8 -*-
# http://pypi.cq.pegatroncorp.com/simple --trusted-host pypi.cq.pegatroncorp.com

import chardet
import datetime
import time
import xlwt
import win32com.client as win32
from win32com.client import constants
import os
import pandas as pd
import numpy as np
import fsspec
import xlrd#使用pandas需要依賴這個庫，所以需要pip import
from openpyxl import load_workbook
import openpyxl
from xlutils.copy import copy as xls_copy
from bs4 import BeautifulSoup

def connect():
    import cx_Oracle
    user = ''
    password = ''
    host = ''
    service_name = ''
    conn_str = f"{user}/{password}@{host}/{service_name}"
    connect = cx_Oracle.connect(conn_str)
    cursor = connect.cursor()
    sql='''
         SELECT A.DB, A.ISN ,B.SSN,B.ITEM,APPLYOP,APPLYAD,CDATE,FLOWNUMBER,AGREEOP,DEALAD,DEALDATE,REASONCLASS,
         REASON,PLANT  FROM P70014_INFOCHANGE A,ISN@CQABNB02 B WHERE A.ISN=B.ISN
           AND TO_CHAR(A.CDATE,'YYYY/MM/DD') BETWEEN TO_CHAR(SYSDATE-7,'YYYY/MM/DD') and TO_CHAR(SYSDATE-1,'YYYY/MM/DD') UNION ALL SELECT A.DB,A.ISN ,B.SSN,B.ITEM,APPLYOP,APPLYAD,CDATE,FLOWNUMBER,AGREEOP,DEALAD,DEALDATE,REASONCLASS,REASON,PLANT  FROM P70014_INFOCHANGE A,ISN@CQTDNB02_OL_ASP B
 WHERE A.ISN=B.ISN  AND TO_CHAR(A.CDATE,'YYYY/MM/DD') BETWEEN TO_CHAR(SYSDATE-1,'YYYY/MM/DD') and TO_CHAR(SYSDATE-1,'YYYY/MM/DD')
        '''
    cursor.execute(sql)
    res=cursor.fetchall()
    cursor.close()
    connect.close()
    return res
def set_style(name, height, bold = False):
    style = xlwt.XFStyle()# 初始化樣式
    font = xlwt.Font()    # 為樣式創建字體
    pattern=xlwt.Pattern()    #為樣式創建背景圖案
    pattern.pattern=xlwt.Pattern.SOLID_PATTERN#設置背景顔色模式
    pattern.pattern_fore_colour = 22
    font.name = name
    font.bold = bold
    font.color_index = 4
    font.height = height
    style.font = font
    style.pattern=pattern
    return style
def timer():
    today = datetime.date.today()
    tomorrow = today + datetime.timedelta(days=-1)
    oneday = today + datetime.timedelta(days=-7)
    a = oneday.strftime("%Y年%m月%d日")
    b = tomorrow.strftime("%Y年%m月%d日")
    c = ('(' + a + '--' + b + ')' + 'BG1客戶機臺跳站明細匯總')
    return c
def write_excel(filename):
    datas=connect()
    workbook = xlwt.Workbook(encoding='utf-8')
    data_sheet = workbook.add_sheet('BG1客戶機臺跳站明細匯總')
    data_sheet.col(11).width=256*32
    row0 = ['DB', 'ISN', 'SSN', 'ITEM', 'APPLYOP', 'APPLYAD','CDATE','FLOWNUMBER','AGREEOP','DEALAD','DEALDATE','REASONCLASS','REASON','PLANT']
    for i in range(len(row0)):
        data_sheet.write(0, i, row0[i], set_style('Times New Roman', 220, False))
    nrows = len(datas)
    for i in range(nrows):
        for j in range(len(datas[i])):
                data_sheet.write(i+1, j, datas[i][j])
    workbook.save(filename)
a=time.strftime('%Y-%m-%d', time.localtime())
filename=r"d://"+a+".xlsx"
write_excel(filename)

def edit_excel():
    excel = win32.gencache.EnsureDispatch('Excel.Application')# 此代码确保正确调度 Excel 应用程序以在 Win32 环境中使用
    mybook= excel.Workbooks.Open(r"d://"+a+".xlsx")
    excel.Visible = True
    mysheet = mybook.Worksheets(1)
    SrcData = mysheet.Range("A:N")
    pt_sht = mybook.Worksheets.Add()
    pt_sht.Name = "透视表"
    StartPvt = pt_sht.Range("A1")
    pt_cache = mybook.PivotCaches().Create(SourceType=constants.xlDatabase, SourceData=SrcData)
    pt = pt_cache.CreatePivotTable(TableDestination=StartPvt,  TableName="PivotTable1")
    pt.AddFields(RowFields=["REASON"])
    pt.AddDataField(Field=pt.PivotFields("ISN"))

    pt_sht2 = mybook.Worksheets.Add()
    pt_sht2.Name = "透视表2"
    StartPvt = pt_sht2.Range("A1")
    pt_cache = mybook.PivotCaches().Create(SourceType=constants.xlDatabase, SourceData=SrcData)
    pt = pt_cache.CreatePivotTable(TableDestination=StartPvt,  TableName="PivotTable2")
    pt.AddFields(RowFields=["REASONCLASS"])
    pt.AddDataField(Field=pt.PivotFields("ISN"))

    pt_sht2 = mybook.Worksheets.Add()
    pt_sht2.Name = "data"
    worksheet = mybook.Worksheets('透视表2')#選擇sheet作爲活動sheet
    worksheet.Range("A3:B16").Copy()#複製指定區域單元格内容
    worksheet = mybook.Worksheets('data')
    worksheet.Cells(1, 1).Value = timer() #在第一行第一列插入当前时间
    worksheet.Cells(2, 1).Value = '跳站類型' #在第二行第一列插入“跳站類型”
    worksheet.Cells(2, 2).Value = '數量' #在第二行第二列插入“數量”
    worksheet.Range('A3').PasteSpecial() #在第三行粘贴之前复制的单元格内容
    last_row = worksheet.UsedRange.Rows.Count #获取已使用的行数
# worksheet.Rows(5).Delete()#刪除指定行
    worksheet.Rows(last_row - 1).Delete() #删除倒数第二行
    
    # worksheet.Range('A1:B2').Merge()#開始合并居中單元格
    # worksheet.Range('A1:B2').HorizontalAlignment = win32.constants.xlCenter
    # worksheet.Range('A1:B2').VerticalAlignment = win32.constants.xlCenter
    mybook.SaveAs(r"d://"+timer()+".xlsx")
    mybook.Close()
    excel.Quit()
def sendmail(mailto, mailsubject,FILE):
    outlook = win32.Dispatch('Outlook.Application')
    mail = outlook.CreateItem(0)
    mail.To = mailto
    mail.cc=mm
    mail.Subject = mailsubject
    mail.BodyFormat = 2
    mail.Attachments.Add(Source=FILE)
    mail.Body ='''
<html>
<body>
<font  color="pink">
<h4>Dear Sir:</h4>
</font>
 附件為'''+ timer()+'''，請查收。謝謝！
</body>
</html>
'''
    df = pd.read_excel(r"d://"+timer()+".xlsx", index_col=0)#讀取excel表格轉換爲datafram對象，并且將第一列設置爲索引
    html_table = df.to_html()#将DataFrame对象转换为HTML格式的表格字符串
    soup = BeautifulSoup(html_table, 'html.parser')#使用BeautifulSoup库将HTML表格字符串转换为BeautifulSoup对象。
    tags_to_remove = soup.select('table thead tr:first-child')#table下的所有thread元素下的第一個元素，組成一個列表
    # print(BeautifulSoup(html_table, 'html.parser',from_encoding='cp1252').select('table th'))
    print("all thread:",soup.select('table thead'))
    print("all first-child:",soup.select('table thead :first-child'))
    print("all tr:first-child:",tags_to_remove)
    for tag in tags_to_remove:
        tag.extract()#遍历表格头部第一行的标签集合，并将其从BeautifulSoup对象中删除
    tags_to_remove = soup.select('table thead :nth-child(2)')#table下的所有thread元素下的第一個元素是th的，組成一個列表
    print(tags_to_remove)
    for tag in tags_to_remove:
        tag.extract()#遍历表格头部第二列的标签集合，并将其从BeautifulSoup对象中删除
    tags_to_td = soup.select('table th')#查找表格头部的所有<th>标签，并将其存储在标签集合中
    for th_element in tags_to_td:  #遍历表格头部所有<th>标签，将其替换为<td>标签，并将其内容复制到<td>标签中
        td_element = soup.new_tag('td') #创建一个新的<td>标签，并将其存储到td_element变量中。
        td_element.string = th_element.string# 将当前循环中的<th>标签的内容作为字符串赋值给td_element标签的内容
        th_element.replace_with(td_element)#使用BeautifulSoup库中的replace_with()方法，将当前循环中的<th>标签替换为新创建的<td>标签，完成替换操作
    first_td = soup.find('td')#查找表格中第一个<td>标签
    if first_td:#如果找到第一个<td>标签，则将其设置为粉色背景，并将其colspan属性设置为2
        first_td['style'] = 'background-color: pink'
        first_td['colspan'] = '2'
    html_table = str(soup)#将处理后的BeautifulSoup对象转换为HTML表格字符串，并将其存储在变量html_table中
    # html_table = html_table.replace('<table border="1" class="dataframe">', '<table style="border-collapse: collapse; border: 1px solid black;">')
    # html_table = html_table.replace('<td>', '<td  style="border: 1px solid black; padding: 5px;">')
    mail.HTMLBody = mail.Body+'<br>'+html_table
    mail.Send()

mailsubject = ""+ timer()+""
#mailto='Bu@pegatroncorp.com'+';'+'Zhao@pegatroncorp.com'
# mm='Bu@pegatroncorp.com'

edit_excel()
FILE=r"d://"+timer()+".xlsx"
sendmail(mailto, mailsubject,FILE)