panda/F6--
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
F6--/其它系统脚本/报表结果通过邮件发送.ipynb
T
panda f1831c31b4 脚本
2026-01-30 11:28:35 +08:00

628 lines
25 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 发送邮件"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"开始截图，请耐心等待。。。\n",
"截图成功，一共截图1张图片。【保存在C:\\Users\\admin\\Desktop//截图-styled_table//】\n",
"邮件发送完成--\n"
]
}
],
"source": [
"import pandas as pd\n",
"import excel2img\n",
"import os\n",
"\n",
"#excel表格文件路径（与程序在同一个文件夹内，可以只写文件名称；不在同一个文件夹内的，要填写完整路径）\n",
"excel_file = r'C:\\Users\\admin\\Desktop\\styled_table.xlsx'\n",
"#要截图的工作表名称\n",
"sheet_list = [\"Sheet\"]\n",
"\n",
"#接下来开始运行程序\n",
"#要保存的图片路径(保存为png图片格式)\n",
"if os.path.dirname(excel_file) == \"\" :\n",
" excel_file = os.getcwd()+\"//\"+excel_file\n",
"img_save = os.path.dirname(excel_file)+\"//截图-\"+os.path.splitext(os.path.basename(excel_file))[0]+\"//\"\n",
"if not os.path.exists(img_save):\n",
" os.makedirs(img_save)\n",
"# 保存为图片。\n",
"try:\n",
" print(\"开始截图，请耐心等待。。。\")\n",
" for i in range(len(sheet_list)):\n",
" excel2img.export_img(excel_file, img_save+sheet_list[i]+\".png\", sheet_list[i], None)\n",
"except :\n",
" print(\"【没有截图成功！！！】请检查excel文件路径名称、工作表的名称是否全部正确！！！\")\n",
"else:\n",
" print(\"截图成功，一共截图\"+str(len(sheet_list))+\"张图片。【保存在\"+img_save+\"】\")\n",
"\n",
"\n",
"from smtplib import SMTP_SSL\n",
"from email.header import Header\n",
"from email.mime.text import MIMEText\n",
"from email.mime.multipart import MIMEMultipart\n",
"from email.mime.application import MIMEApplication\n",
"from email.mime.image import MIMEImage\n",
"from email.utils import formataddr\n",
"\n",
"send_usr = '798191621@qq.com' # 发件人\n",
"send_pwd = 'owuorsaybhxzbbea' # 授权码，邮箱设置\n",
"reverse = 'etu5981@dingtalk.com' # 接收者 # sunyulei@ncarzone.com etu5981@dingtalk.com\n",
"content = '<p>测试正文</p>'\n",
"#content 内容设置\n",
"html_img = f'<p>{content}<br><img src=\"cid:image1\"></br></p>' # html格式添加图片\n",
"email_server = 'smtp.qq.com'\n",
"email_title = '测试' # 邮件主题\n",
"def send_email():\n",
" msg = MIMEMultipart() # 构建主体\n",
" msg['Subject'] = Header(email_title,'utf-8') # 邮件主题\n",
" msg['From'] = formataddr(pair=(\"F6推送看板\", send_usr)) # 发件人 send_usr '发件人名称 <发件人邮箱>'\n",
" msg['To'] = Header('测试','utf-8') # 收件人--这里是昵称\n",
" # msg.attach(MIMEText(content,'html','utf-8')) # 构建邮件正文,不能多次构造\n",
" attchment = MIMEApplication(open('C:/Users/admin/Desktop/styled_table.xlsx','rb').read()) # 文件\n",
" attchment.add_header('Content-Disposition','attachment',filename='styled_table.xlsx')\n",
" msg.attach(attchment) # 添加附件到邮件\n",
" f = open(\"Sheet.png\", 'rb') #打开图片\n",
" msgimage = MIMEImage(f.read())\n",
" f.close()\n",
" msgimage.add_header('Content-ID', '<image1>') # 设置图片\n",
" msg.attach(msgimage)\n",
" msg.attach(MIMEText(html_img,'html','utf-8')) # 添加到邮件正文\n",
" try:\n",
" smtp = SMTP_SSL(email_server) #指定邮箱服务器\n",
" smtp.ehlo(email_server) # 部分邮箱需要\n",
" smtp.login(send_usr,send_pwd) # 登录邮箱\n",
" smtp.sendmail(send_usr,reverse,msg.as_string()) # 分别是发件人、收件人、格式\n",
" smtp.quit() # 结束服务\n",
" print('邮件发送完成--')\n",
" except:\n",
" print('发送失败')\n",
"\n",
"send_email()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 发送正文格式设置"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": [
"import openpyxl\n",
"from openpyxl.styles import Font, Alignment, Border, Side, PatternFill\n",
"\n",
"# 创建一个新的工作簿\n",
"workbook = openpyxl.Workbook()\n",
"\n",
"# 选择活动工作表\n",
"worksheet = workbook.active\n",
"\n",
"# 设置单元格样式\n",
"font = Font(name='Calibri', size=14, bold=True, italic=False, color='2D2C2C') # 设置字体样式\n",
"alignment = Alignment(horizontal='center', vertical='center') # 设置对齐方式\n",
"border = Border(left=Side(border_style='thin', color='000000'), # 设置边框样式\n",
" right=Side(border_style='thin', color='000000'),\n",
" top=Side(border_style='thin', color='000000'),\n",
" bottom=Side(border_style='thin', color='000000'))\n",
"fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') # 设置填充样式\n",
"\n",
"# 应用样式到单元格\n",
"for row in range(1, 6):\n",
" for col in range(1, 6):\n",
" cell = worksheet.cell(row=row, column=col)\n",
" cell.value = f'单元格({row}, {col})'\n",
" cell.font = font\n",
" cell.alignment = alignment\n",
" cell.border = border\n",
" cell.fill = fill\n",
" \n",
"# 合并单元格B1到B3\n",
"worksheet.merge_cells('B1:B3')\n",
"\n",
"# 设置单元格宽高\n",
"worksheet.column_dimensions['A'].width = 20\n",
"worksheet.row_dimensions[1].height = 30\n",
"\n",
"# 保存工作簿到文件\n",
"workbook.save(r'C:\\Users\\admin\\Desktop\\styled_table.xlsx')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# 发送数据源读取并加工"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [],
"source": [
"import mysql.connector\n",
"import pandas as pd\n",
"# 创建连接\n",
"cnx = mysql.connector.connect(\n",
" host=\"rm-uf6r230vbtxf5gdz63o.mysql.rds.aliyuncs.com\",\n",
" user=\"ro_yida\",\n",
" password=\"j39+2t@rR$F&71\",\n",
" database=\"f6custom\"\n",
")\n",
"\n",
"# 创建游标\n",
"cursor = cnx.cursor()\n",
"\n",
"# 执行SQL查询\n",
"sql = \"SELECT * FROM rpt_customized_ht_real_sales_detail WHERE finish_day = '2023-11-01'\"\n",
"cursor.execute(sql)\n",
"# 获取结果集的每一行\n",
"rows = cursor.fetchall()\n",
"# 获取所有字段名\n",
"all_fields = cursor.description\n",
"#执行结果转化为dataframe\n",
"col = []\n",
"for i in all_fields:\n",
" col.append(i[0])\n",
"rpt_customized_ht_real_sales_detail = pd.DataFrame(list(rows),columns=col)\n",
"# rpt_customized_ht_real_sales_detail.to_excel(r'C:\\Users\\admin\\Desktop\\[20231113]rpt_customized_ht_real_sales_detail.xlsx')\n",
"\n",
"# 关闭游标和连接\n",
"cursor.close()\n",
"cnx.close()\n"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>id</th>\n",
" <th>id_own_org</th>\n",
" <th>org_name</th>\n",
" <th>id_own_group</th>\n",
" <th>group_name</th>\n",
" <th>large_area_id</th>\n",
" <th>large_area_name</th>\n",
" <th>small_area_id</th>\n",
" <th>small_area_name</th>\n",
" <th>third_area_id</th>\n",
" <th>...</th>\n",
" <th>service_duration</th>\n",
" <th>order_id</th>\n",
" <th>order_bill_no</th>\n",
" <th>order_business_type</th>\n",
" <th>order_business_type_name</th>\n",
" <th>pick_employee_id</th>\n",
" <th>pick_employee_name</th>\n",
" <th>service_employees_code</th>\n",
" <th>repair_person</th>\n",
" <th>repair_person_contact</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>11800328039710089311_11800328040049827883_2023...</td>\n",
" <td>11240984669917088021</td>\n",
" <td>清和北街</td>\n",
" <td>11240984669917083018</td>\n",
" <td>天猫养车恒泰联合运营管理中心</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>11800328039710089311_11800328040049827883_2023...</td>\n",
" <td>11240984669917088021</td>\n",
" <td>清和北街</td>\n",
" <td>11240984669917083018</td>\n",
" <td>天猫养车恒泰联合运营管理中心</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>11800328039710089311_11800328040049827884_2023...</td>\n",
" <td>11240984669917088021</td>\n",
" <td>清和北街</td>\n",
" <td>11240984669917083018</td>\n",
" <td>天猫养车恒泰联合运营管理中心</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>11800328039710089311_11800328040049827884_2023...</td>\n",
" <td>11240984669917088021</td>\n",
" <td>清和北街</td>\n",
" <td>11240984669917083018</td>\n",
" <td>天猫养车恒泰联合运营管理中心</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>11800328039710089311_11800436637849116704_2023...</td>\n",
" <td>11240984669917088021</td>\n",
" <td>清和北街</td>\n",
" <td>11240984669917083018</td>\n",
" <td>天猫养车恒泰联合运营管理中心</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4311</th>\n",
" <td>11850543680416931906_11850543680584704039_2023...</td>\n",
" <td>10691192243916416683</td>\n",
" <td>瑞麟大街</td>\n",
" <td>10546050787360507919</td>\n",
" <td>西安恒泰汽车服务有限公司</td>\n",
" <td>429257</td>\n",
" <td>临潼渭南区</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4312</th>\n",
" <td>11850543842375790639_11850543842535170132_2023...</td>\n",
" <td>10691192243916416683</td>\n",
" <td>瑞麟大街</td>\n",
" <td>10546050787360507919</td>\n",
" <td>西安恒泰汽车服务有限公司</td>\n",
" <td>429257</td>\n",
" <td>临潼渭南区</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4313</th>\n",
" <td>11850543842375790639_11850543842535170133_2023...</td>\n",
" <td>10691192243916416683</td>\n",
" <td>瑞麟大街</td>\n",
" <td>10546050787360507919</td>\n",
" <td>西安恒泰汽车服务有限公司</td>\n",
" <td>429257</td>\n",
" <td>临潼渭南区</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4314</th>\n",
" <td>11850544102103871561_11850544102305198143_2023...</td>\n",
" <td>10691192243916416683</td>\n",
" <td>瑞麟大街</td>\n",
" <td>10546050787360507919</td>\n",
" <td>西安恒泰汽车服务有限公司</td>\n",
" <td>429257</td>\n",
" <td>临潼渭南区</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4315</th>\n",
" <td>11850544102103871561_11850544102305198144_2023...</td>\n",
" <td>10691192243916416683</td>\n",
" <td>瑞麟大街</td>\n",
" <td>10546050787360507919</td>\n",
" <td>西安恒泰汽车服务有限公司</td>\n",
" <td>429257</td>\n",
" <td>临潼渭南区</td>\n",
" <td></td>\n",
" <td></td>\n",
" <td></td>\n",
" <td>...</td>\n",
" <td>00:00:00</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>-1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td></td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>4316 rows × 179 columns</p>\n",
"</div>"
],
"text/plain": [
" id id_own_org \n",
"0 11800328039710089311_11800328040049827883_2023... 11240984669917088021 \\\n",
"1 11800328039710089311_11800328040049827883_2023... 11240984669917088021 \n",
"2 11800328039710089311_11800328040049827884_2023... 11240984669917088021 \n",
"3 11800328039710089311_11800328040049827884_2023... 11240984669917088021 \n",
"4 11800328039710089311_11800436637849116704_2023... 11240984669917088021 \n",
"... ... ... \n",
"4311 11850543680416931906_11850543680584704039_2023... 10691192243916416683 \n",
"4312 11850543842375790639_11850543842535170132_2023... 10691192243916416683 \n",
"4313 11850543842375790639_11850543842535170133_2023... 10691192243916416683 \n",
"4314 11850544102103871561_11850544102305198143_2023... 10691192243916416683 \n",
"4315 11850544102103871561_11850544102305198144_2023... 10691192243916416683 \n",
"\n",
" org_name id_own_group group_name large_area_id \n",
"0 清和北街 11240984669917083018 天猫养车恒泰联合运营管理中心 \\\n",
"1 清和北街 11240984669917083018 天猫养车恒泰联合运营管理中心 \n",
"2 清和北街 11240984669917083018 天猫养车恒泰联合运营管理中心 \n",
"3 清和北街 11240984669917083018 天猫养车恒泰联合运营管理中心 \n",
"4 清和北街 11240984669917083018 天猫养车恒泰联合运营管理中心 \n",
"... ... ... ... ... \n",
"4311 瑞麟大街 10546050787360507919 西安恒泰汽车服务有限公司 429257 \n",
"4312 瑞麟大街 10546050787360507919 西安恒泰汽车服务有限公司 429257 \n",
"4313 瑞麟大街 10546050787360507919 西安恒泰汽车服务有限公司 429257 \n",
"4314 瑞麟大街 10546050787360507919 西安恒泰汽车服务有限公司 429257 \n",
"4315 瑞麟大街 10546050787360507919 西安恒泰汽车服务有限公司 429257 \n",
"\n",
" large_area_name small_area_id small_area_name third_area_id ... \n",
"0 ... \\\n",
"1 ... \n",
"2 ... \n",
"3 ... \n",
"4 ... \n",
"... ... ... ... ... ... \n",
"4311 临潼渭南区 ... \n",
"4312 临潼渭南区 ... \n",
"4313 临潼渭南区 ... \n",
"4314 临潼渭南区 ... \n",
"4315 临潼渭南区 ... \n",
"\n",
" service_duration order_id order_bill_no order_business_type \n",
"0 00:00:00 None None -1 \\\n",
"1 00:00:00 None None -1 \n",
"2 00:00:00 None None -1 \n",
"3 00:00:00 None None -1 \n",
"4 00:00:00 None None -1 \n",
"... ... ... ... ... \n",
"4311 00:00:00 None None -1 \n",
"4312 00:00:00 None None -1 \n",
"4313 00:00:00 None None -1 \n",
"4314 00:00:00 None None -1 \n",
"4315 00:00:00 None None -1 \n",
"\n",
" order_business_type_name pick_employee_id pick_employee_name \n",
"0 None None None \\\n",
"1 None None None \n",
"2 None None None \n",
"3 None None None \n",
"4 None None None \n",
"... ... ... ... \n",
"4311 None None None \n",
"4312 None None None \n",
"4313 None None None \n",
"4314 None None None \n",
"4315 None None None \n",
"\n",
" service_employees_code repair_person repair_person_contact \n",
"0 \n",
"1 \n",
"2 \n",
"3 \n",
"4 \n",
"... ... ... ... \n",
"4311 None None \n",
"4312 None None \n",
"4313 None None \n",
"4314 None None \n",
"4315 None None \n",
"\n",
"[4316 rows x 179 columns]"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"rpt_customized_ht_real_sales_detail"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "F6processing",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.4"
},
"orig_nbformat": 4
},
"nbformat": 4,
"nbformat_minor": 2
}
Reference in New Issue View Git Blame Copy Permalink