文章目录
1 导入所需模块
#用于连接oracle数据库
import cx_Oracle as cx
#用操作文件或文件夹
import os
import pandas as pd
import numpy as np
#用于操作Excel表格
import openpyxl
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Border,Side
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import shutil as st
#用于发送邮件
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.header import Header
import schedule
import time
2连接ORACLE
#设置ORACLE驱动位置
os.environ['path'] = r'E:\instantclient-basic-windows.x64-12.2.0.1.0\instantclient_12_2'
#ORACLE地址、端口、SID
dsn = cx.makedsn('地址',端口,'SID')
#ORACLE账号、密码、服务器
db = cx.connect('账号', '密码', 服务器)
3 抽取结果
3.1 抽取员工形象
1 从oracle数据库中抽取 员工形象 的结果,并转换为DataFrame;
表中包含的字段为
检查日期 | 架构日期 | 事业部 | 大区 | 区域 | 归属门店 | 被考核人姓名 | 被考核人工号 | 身份证号码 | 被考核人职位 | 不合格原因 | 扣款金额 |
2 在指定路径创建文件夹
3 在 2 中的文件夹中,根据 1 中的大区 创建对应大区的文件夹
4 将 1 中的表按大区切分,写入到 3 对应大区的文件夹中
#创建游标 员工形象
curs_ygxx=db.cursor()
#组织查询语句
curs_ygxx.execute(
"""
WITH CANSHU AS
(
SELECT to_date(开始日期,'yyyymmdd') as 开始日期,
to_date(结束日期,'yyyymmdd') as 结束日期,
to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,
to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM HR_参数表
where 项目='门店6S'
)
select to_char(检查日期,'yyyy-mm-dd') as 检查日期,
to_char(架构日期,'yyyy-mm-dd') as 架构日期
from CDM_HR_门店6S_员工形象
where trunc(检查日期,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)
data_ygxx=pd.DataFrame(curs_ygxx.fetchall()) #将查询结果转换为 DataFrame
colsname_ygxx=curs_ygxx.description #获取列名称
namelist_ygxx=[]
for i in range(len(colsname_ygxx)):
namelist_ygxx.append(colsname_ygxx[i][0])
data_ygxx.columns=namelist_ygxx #添加表名称
path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_ygxx['大区'].unique(): #提取大区名称
if not os.path.exists(path+col): #判断是否存在大区文件夹
os.mkdir(path+col) #在指定路径 创建文件夹
data_ygxx[data_ygxx["大区"] == col].to_excel(path + col + "-员工形象.xlsx",index=False) #在指定路径 写入excel
st.move(path +col + "-员工形象.xlsx",path+col + "/" + col + "-员工形象.xlsx") #将指定路径的文件 移动到 指定路径的文件夹
3.2 抽取门店形象
步骤同上
#创建游标 门店形象
curs_mdxx=db.cursor()
#组织查询语句
curs_mdxx.execute(
"""
WITH CANSHU AS
(
SELECT to_date(开始日期,'yyyymmdd') as 开始日期,
to_date(结束日期,'yyyymmdd') as 结束日期,
to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,
to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM HR_参数表
where 项目='门店6S'
)
select to_char(检查日期,'yyyy-mm-dd') as 检查日期,
to_char(架构日期,'yyyy-mm-dd') as 架构日期,
from CDM_HR_门店6S_门店形象
where trunc(检查日期,'mm')=(SELECT 开始日期 FROM CANSHU)
and 大区 is not null
"""
)
data_mdxx=pd.DataFrame(curs_mdxx.fetchall()) #将查询结果转换为 DataFrame
colsname_mdxx=curs_mdxx.description #获取列名称
namelist_mdxx=[]
for i in range(len(colsname_mdxx)):
namelist_mdxx.append(colsname_mdxx[i][0])
data_mdxx.columns=namelist_mdxx #添加表名称
path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_mdxx['大区'].unique():
if not os.path.exists(path+col):
os.mkdir(path+col) # 在指定路径 创建文件夹
data_mdxx[data_mdxx["大区"] == col].to_excel(path + col + "-门店形象.xlsx",index=False) #在指定路径 写入excel
st.move(path +col + "-门店形象.xlsx",path+col + "/" + col + "-门店形象.xlsx") #奖指定路径的文件 移动到 指定路径的文件夹
3.3 抽取明细汇总
步骤同上
#创建游标 明细汇总
curs_mxhz=db.cursor()
#组织查询语句
curs_mxhz.execute(
"""
WITH CANSHU AS
(
SELECT to_date(开始日期,'yyyymmdd') as 开始日期,
to_date(结束日期,'yyyymmdd') as 结束日期,
to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,
to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM HR_参数表
where 项目='门店6S'
)
select to_char(月份,'yyyy-mm-dd') as 月份
from ADS_HR_门店6S_明细汇总
where trunc(月份,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)
data_mxhz=pd.DataFrame(curs_mxhz.fetchall()) #将查询结果转换为 DataFrame
colsname_mxhz=curs_mxhz.description #获取列名称
namelist_mxhz=[]
for i in range(len(colsname_mxhz)):
namelist_mxhz.append(colsname_mxhz[i][0])
data_mxhz.columns=namelist_mxhz #添加表名称
path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_mxhz['大区'].unique():
if not os.path.exists(path+col):
os.mkdir(path+col) # 在指定路径 创建文件夹
data_mxhz[data_mxhz["大区"] == col].to_excel(path + col + "-明细汇总.xlsx",index=False) #在指定路径 写入excel
st.move(path +col + "-明细汇总.xlsx",path+col + "/" + col + "-明细汇总.xlsx") #奖指定路径的文件 移动到 指定路径的文件夹
4 合并结果
经过3,我们获得了一个文件夹,该文件夹中包含了每个大区对应的文件夹,每个大区的文件夹包含了3个表,分别是员工形象,门店形象,明细汇总。接下来我们需要将这3个表汇总合并在一个EXCEL中,每个表变成其中的一个sheet
# 读取各文件夹中文件 合并成一个SHEET
path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_mxhz['大区'].unique():
#创建工作簿
wb=Workbook()
#创建多个sheet
ws_ygxx=wb.active
ws_mdxx=wb.create_sheet()
ws_mxhz=wb.create_sheet()
#更改sheet的名称
ws_ygxx.title="员工形象"
ws_mdxx.title="门店形象"
ws_mxhz.title="明细汇总"
#获取文件夹下的所有文件名
name_list=os.listdir(path+col)
#for循环遍历读取
for i in name_list:
if "员工形象" in i:
df_ygxx = pd.read_excel(path + col +"/" + i)
df_ygxx["身份证号码"] = df_ygxx["身份证号码"].apply(lambda x: " " + str(x))
for r0 in dataframe_to_rows(df_ygxx.fillna(' '),index=False,header=True): # 缺失值处理 填补为null
ws_ygxx.append(r0)
for c0 in ws_ygxx["A:L"]:
for r0 in c0:
r0.font=Font(name="微软雅黑",size=10) #对单元格字体的设置
r0.fill=PatternFill(fill_type=None) #单元格填充
r0.border=Border(left=Side(border_style="thin"), #边框线设置
right=Side(border_style="thin"),
top=Side(border_style="thin"),
bottom=Side(border_style="thin")
)
r0.alignment=Alignment(horizontal="center",vertical="center") #对齐方式
for c in ws_ygxx[1]: #首行
c.font=Font(name="微软雅黑",size=12,bold=True)
c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")
for c in ["A","B","C","D","E","F","G","H","I","J","K","L"]:
ws_ygxx.column_dimensions[c].width = 20.0
if "门店形象" in i:
df_mdxx = pd.read_excel(path + col +"/" + i)
df_mdxx["区经身份证号"] = df_mdxx["区经身份证号"].apply(lambda x: " " + str(x))
for r3 in dataframe_to_rows(df_mdxx.fillna(' '),index=False,header=True): ## 缺失值处理 填补为null
ws_mdxx.append(r3)
for c3 in ws_mdxx["A:R"]:
for r3 in c3:
r3.font=Font(name="微软雅黑",size=10) #对单元格字体的设置
r3.fill=PatternFill(fill_type=None) #单元格填充
r3.border=Border(left=Side(border_style="thin"), #边框线设置
right=Side(border_style="thin"),
top=Side(border_style="thin"),
bottom=Side(border_style="thin")
)
r3.alignment=Alignment(horizontal="center",vertical="center") #对齐方式
for c in ws_mdxx[1]: #首行
c.font=Font(name="微软雅黑",size=12,bold=True)
c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")
for c in ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R"]:
ws_mdxx.column_dimensions[c].width = 20.0
if "明细汇总" in i:
df_mxhz = pd.read_excel(path + col +"/" + i)
df_mxhz["区域领导身份证号"] = df_mxhz["区域领导身份证号"].apply(lambda x: " " + str(x))
for r4 in dataframe_to_rows(df_mxhz.fillna(' '),index=False,header=True): ## 缺失值处理 填补为null
ws_mxhz.append(r4)
for c4 in ws_mxhz["A:O"]:
for r4 in c4:
r4.font=Font(name="微软雅黑",size=10) #对单元格字体的设置
r4.fill=PatternFill(fill_type=None) #单元格填充
r4.border=Border(left=Side(border_style="thin"), #边框线设置
right=Side(border_style="thin"),
top=Side(border_style="thin"),
bottom=Side(border_style="thin")
)
r4.alignment=Alignment(horizontal="center",vertical="center") #对齐方式
for c in ws_mxhz[1]: #首行
c.font=Font(name="微软雅黑",size=12,bold=True)
c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")
for c in ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O"]:
ws_mxhz.column_dimensions[c].width = 20.0
wb.save(path+col+"/"+col+"-总表.xlsx")
curs_ygxx.close()
curs_mdxx.close()
curs_mxhz.close()
5 删除3中的结果
删除 员工形象 门店形象 明细汇总 表,仅保留总表
path=r'C:/Users/Administrator/Desktop/6S-线上版/'
for col in data_mxhz['大区'].unique():
#获取文件夹下的所有文件名
name_list=os.listdir(path+col)
#for循环遍历读取
for i in name_list:
if "员工形象" in i:
os.remove(path+col+"/"+col+"-员工形象.xlsx")
if "门店形象" in i:
os.remove(path+col+"/"+col+"-门店形象.xlsx")
if "明细汇总" in i:
os.remove(path+col+"/"+col+"-明细汇总.xlsx")
6 写入总表
经过上述步骤,我们获得了一个文件夹,该文件夹中包含了每个大区对应的文件夹,每个大区的文件夹包含了1个总表,这个总表中有3个sheet,分别是员工形象、门店形象、明细汇总。
接下来,我们想要 明细汇总、大区排名、扣款 三个总表。
6.1 明细汇总总表
创建明细汇总总表,并调整其格式,微软雅黑,首行填充黄色背景,单元格间距20
#创建工作簿
wb=Workbook()
ws_mxhzzb=wb.active
##更改sheet的名称
ws_mxhzzb.title="明细汇总总表"
curs_mxhzzb=db.cursor()
#组织查询语句
curs_mxhzzb.execute(
"""
WITH CANSHU AS
(
SELECT to_date(开始日期,'yyyymmdd') as 开始日期,
to_date(结束日期,'yyyymmdd') as 结束日期,
to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,
to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM HR_参数表
where 项目='门店6S'
)
select 月份
from ADS_HR_门店6S_明细汇总
where trunc(月份,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)
data_mxhzzb=pd.DataFrame(curs_mxhzzb.fetchall()) #将查询结果转换为 DataFrame
colsname_mxhzzb=curs_mxhzzb.description #获取列名称
namelist_mxhzzb=[]
for i in range(len(colsname_mxhzzb)):
namelist_mxhzzb.append(colsname_mxhzzb[i][0])
data_mxhzzb.columns=namelist_mxhzzb #添加表名称
for r in dataframe_to_rows(data_mxhzzb.fillna(' '),index=False,header=True): ## 缺失值处理 填补为null
ws_mxhzzb.append(r)
for col in ws_mxhzzb["A:P"]:
for r in col:
r.font=Font(name="微软雅黑",size=10) #对单元格字体的设置
r.fill=PatternFill(fill_type=None) #单元格填充
r.border=Border(left=Side(border_style="thin"), #边框线设置
right=Side(border_style="thin"),
top=Side(border_style="thin"),
bottom=Side(border_style="thin")
)
r.alignment=Alignment(horizontal="center",vertical="center") #对齐方式
for c in ws_mxhzzb[1]: #首行
c.font=Font(name="微软雅黑",size=12,bold=True)
c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")
for col in ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P"]:
ws_mxhzzb.column_dimensions[col].width = 20.0
wb.save(r'C:\Users\Administrator\Desktop\6S-线上版\6S-明细汇总总表.xlsx')
6.2 大区排名总表
大区排名 该表为总表,不进行切分,处理同上
#创建工作簿
wb=Workbook()
ws_dqpm=wb.active
##更改sheet的名称
ws_dqpm.title="大区排名"
#创建游标
curs_dqpm=db.cursor()
#组织查询语句
curs_dqpm.execute(
"""
WITH CANSHU AS
(
SELECT to_date(开始日期,'yyyymmdd') as 开始日期,
to_date(结束日期,'yyyymmdd') as 结束日期,
to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,
to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM HR_参数表
where 项目='门店6S'
)
select 月份
from ADS_HR_门店6S_大区排名
where trunc(月份,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)
data_dqpm=pd.DataFrame(curs_dqpm.fetchall()) #将查询结果转换为 DataFrame
colsname_dqpm=curs_dqpm.description #获取列名称
namelist_dqpm=[]
for i in range(len(colsname_dqpm)):
namelist_dqpm.append(colsname_dqpm[i][0])
data_dqpm.columns=namelist_dqpm #添加表名称
for r in dataframe_to_rows(data_dqpm.fillna(' '),index=False,header=True): #缺失值处理 填补为null
ws_dqpm.append(r)
for col in ws_dqpm["A:P"]:
for r in col:
r.font=Font(name="微软雅黑",size=10) #对单元格字体的设置
r.fill=PatternFill(fill_type=None) #单元格填充
r.border=Border(left=Side(border_style="thin"), #边框线设置
right=Side(border_style="thin"),
top=Side(border_style="thin"),
bottom=Side(border_style="thin")
)
r.alignment=Alignment(horizontal="center",vertical="center") #对齐方式
for c in ws_dqpm[1]: #首行
c.font=Font(name="微软雅黑",size=12,bold=True)
c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")
for col in ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P"]:
ws_dqpm.column_dimensions[col].width = 20.0
wb.save(r'C:\Users\Administrator\Desktop\6S-线上版\6S大区排名.xlsx')
6.3 扣款总表
扣款 该表为总表,不进行切分,处理同上
#创建工作簿
wb=Workbook()
ws_kk=wb.active
#更改sheet的名称
ws_kk.title="扣款"
#创建游标
curs_kk=db.cursor()
#组织查询语句
curs_kk.execute(
"""
WITH CANSHU AS
(
SELECT to_date(开始日期,'yyyymmdd') as 开始日期,
to_date(结束日期,'yyyymmdd') as 结束日期,
to_date(竞赛开始日期,'yyyymmdd') as 竞赛开始日期,
to_date(竞赛结束日期,'yyyymmdd') as 竞赛结束日期
FROM HR_参数表
where 项目='门店6S'
)
select 检查月份
from ADS_HR_门店6S_扣款
where trunc(检查月份,'mm')=(SELECT 开始日期 FROM CANSHU)
"""
)
data_kk=pd.DataFrame(curs_kk.fetchall()) #将查询结果转换为 DataFrame
colsname_kk=curs_kk.description #获取列名称
namelist_kk=[]
for i in range(len(colsname_kk)):
namelist_kk.append(colsname_kk[i][0])
data_kk.columns=namelist_kk #添加表名称
for r in dataframe_to_rows(data_kk.fillna(' '),index=False,header=True): #缺失值处理 填补为null
ws_kk.append(r)
for col in ws_kk["A:J"]:
for r in col:
r.font=Font(name="微软雅黑",size=10) #对单元格字体的设置
r.fill=PatternFill(fill_type=None) #单元格填充
r.border=Border(left=Side(border_style="thin"), #边框线设置
right=Side(border_style="thin"),
top=Side(border_style="thin"),
bottom=Side(border_style="thin")
)
r.alignment=Alignment(horizontal="center",vertical="center") #对齐方式
for c in ws_kk[1]: #首行
c.font=Font(name="微软雅黑",size=12,bold=True)
c.fill=PatternFill(fill_type='solid',start_color="FFFFFF00")
for col in ["A","B","C","D","E","F","G","H","I","J"]:
ws_kk.column_dimensions[col].width = 20.0
wb.save(r'C:\Users\Administrator\Desktop\6S-线上版\扣款.xlsx')
7 自动发送邮件
发送 多个邮件(每个邮件 多个附件) 给多个人
#设置smtplib所需的参数
#下面的发件人,收件人是用于邮件传输的
smtpserver = 'smtp.maitian.cn' #指定发送邮箱的格式smtp.163.com smtp.qq.com 使用的邮箱的smtp服务器地址,这里是qq的smtp地址,其他同理
username = '发件人用户名'#发件人用户名
password='发件人授权码'#发件人授权码,注意对于网页版邮箱,需要的不是密码,而是授权码,授权码要合着写在一起,中间不能有空格
sender='发件人用户名'#发件人,即发件人用户名
#receiver='接收人'#一个接收人
#receiver=['******@126.com','******@126.com'] #多个收件人,用列表表示;同一个邮件给多个收件人
#读取邮件发送列表
df=pd.read_excel(r'C:/Users/Administrator/Desktop/6S-线上版/大区政委邮箱.xlsx',sheet_name = 'Sheet1')
for i in zip(df["大区"],df["收件人姓名"],df["收件人邮箱"]):
receiver=i[2] #接收人 不同的邮件给各自对应的收件人
#构造邮件主题
subject = i[0]+'-6S明细核对-202304'#邮件主题为中英文均可
#构造邮件对象MIMEMultipart对象,用于写邮件的即显示出来的邮件
#下面的主题,发件人,收件人,日期是显示在邮件页面上的
msg = MIMEMultipart()#声明邮件对象
msg['Subject'] = subject #这里的subject即为上面构造的邮件主题
msg['From'] = sender
msg['To'] = receiver
msg['Accept-Language'] = 'zh-CN'
msg['Accept-Charset'] = 'ISO-8859-1,utf-8'
msgAlternative = MIMEMultipart('alternative')
msg.attach(msgAlternative)
#构造正文内容
mail_msg = """
<p> 您好:
<br>\t附件为本月【门店6S】检查结果及明细,因涉及各层级扣款,烦请查收并与区域确认!
<br>\t如有问题,请于今天(25号)18:00前反馈***,不反馈则默认信息无误,感谢配合! </p>
"""
msgAlternative.attach(MIMEText(mail_msg, 'html', 'utf-8'))
#构造附件
currentPath = r'C:/Users/Administrator/Desktop/6S-线上版'+'/' +i[0]
sqlFileList = os.listdir(currentPath)
sqlFilePath = ''
sqlFilePathList = []
#发送多个附件的邮件,这里发送指定目录下所有类型一致的文件
for fileName in sqlFileList:
sqlFilePath = os.path.join(currentPath,fileName)
#print(sqlFilePath)
sqlFilePathList.append(sqlFilePath)
#print(sqlFilePathList)
with open(sqlFilePath,'rb') as h:
content1=h.read()
#设置txt参数
part1 = MIMEText(content1,'base','utf-8')
#附件设置内容类型,方便起见,设置为二进制流
part1['Content-Type'] = 'application/octet-stream'
#设置附件头,添加附件名
part1.add_header('Content-Disposition', 'attachment', filename=fileName)
#将内容附加到邮件主体中
msg.attach(part1)
#发送邮件
try:
smtp = smtplib.SMTP() #实例
smtp.connect('smtp.maitian.cn')
#我们用set_debuglevel(1)就可以打印出和SMTP服务器交互的所有信息。
#smtp.set_debuglevel(1)
smtp.login(username, password)
smtp.sendmail(sender, receiver, msg.as_string())
print (subject+"邮件发送成功")
time.sleep(3) #每封邮件发送间隔3s
except smtplib.SMTPException:
print (subject+"Error: 无法发送邮件")
except:
print("Error: 无法发送邮件")
```