day21-办公自动化：利用Python发邮件

Excel中绘制图表

下载三方库openpyxl

pip install openpyxl

from datetime import datetime

from openpyxl
from openpyxl.chart import Reference, LineChart
from openpyxl.cell.cell import cell
from openpyxl.styles import Alignment, Front, Border, Side


def display(value, delim=''):
    if isinstance(value, float):
        print(f'{value:>10.2f}', end=delim)
    elif isinstance(value, int):
        print(f'{value:>10}', end=delim)
    elif isinstance(value, datetime):
        print(value.strftime('%Y年%m月%d日'), end=delim)
    else:
        print(value, end=delim)


def main():
    # 1.加载工作表
    workbook = openpyxl.load_workbook('阿里巴巴2020年股票数据.xlsx')
    
    # 2.获取工作表	代码后面的类型注释可以让PyCharm在后面的代码中提示出属性和方法
    sheet = workbook['股票数据']  # type: Worksheet
    
    # 3.获取工作表的属性
    print(sheet.max_row, sheet.max_column)		# 255 7
    print(sheet.dimensions)		# A1:G255   - 	单元格的范围
    
    # 4.操作单元格
    cell = sheet['E5']	# type: Cell
    cell1 = sheet.cell(5, 5)
    print(cell.value, cell1.value)	# 216.639999389648 216.639999389648
    
    # 5.循环遍历所有单元格
    for row in range(2, sheet.max_row + 1):
        for col in range(2, sheet.max_column + 1):
            display(sheet.cell(row, col).value)
        print()
        
    # 6.公式计算
    # sheet['D256'] = '=sum(d2:d255)'
    # sheet['E256'] = '=average(e2:e255)'
    # sheet['F256'] = '=sum(f2:f255)'
    cell = sheet['E256']
    cell.value = '我爱你中国'
    
    # 对齐方式
    cell.alignment = Alignment(horizontal='center', vertical='center')
    # 字体
    cell.font = Font(name='ArialUnicodeMS', size=20, color='330099')
    # 边框
    side = Side(color = 'ff6600', style = 'double')
    cell.border = Border(left=side, right=side, top=side, bootom=side)
    # 行高
    sheet.row_dimensions[256].height = 50
    sheet.coloumn_cimensions['E'].width = 35
    
    # 绘制图表
    c1 = LineChart()
    c1.title = '一月份收盘价和开盘价'
    c1.style = 13
    c1.y_axis.title = '价格'
    c1.x_axis.title = '日期'
    # 绘图添加数据
    data = Reference(sheet, min_col=4, min_row=1, max_row=23)
    c1.add_data(data, titles_from_data=True)
    # 设置横轴
    cats = Reference(sheet, min_col=1, min_row=2, max_row=23)
    c1.set_categories(cats)
    # 给数据系列设置样式
    s1 = c1.sheet[0]
    s1.maker.symbol = 'circle'
    s1.maker.graphicalProperties.solidFill = 'ff0000'
    s1.maker.graphicalProperties.line.solidFill = '0000ff'
    s1.smooth = True
    # 将图添加到工作表
    sheet.add_chart(c1, 'A258')
    
    # 7.保存工作簿
    workbook.save('阿里巴巴2020年股票数据.xlsx')
    

if __name__ == '__main__':
    main()

办公自动化 —> 利用Python发邮件

• 邮件服务器 -》 自己搭建 / 买三方服务器

  SMTP：Simple Mail Transfer Protocol -> 跟你的邮件服务器进行网络通信

  Python标准库已经对SMTP进行了封装，我们主要通过创建对象，给对象发消息的方式就可以发送邮件

  smtplib模块 —》SMTP_SSL —》 login() / sendmail() / quit()

  邮件服务器：smtp.163.com

  端口号：465 / 25

MIME —> Multi-purpose Internet Mail Extension
—> text/html
—> image/png
—> image/gif
—> audio/mp3
—> video/mp4
—> application/pdf

MIMEMultipart —> attach —> 添加文本、附件等其他内容
MIMEText —> 可以封装文本内容，也可以封装二进制数据（需要经过BASE64编码处理）

import smtplib

# 创建SMYP_SSL对象
smtp_obj = smtplib.SMTP_SSL(host='smtp.163.com', port=465)

# 1.登录授权，如果没有开启SMTP服务，调用login方法会直接报SMTPAuthenticationError错误
smtp_obj.login('lijunbo_k@163.com', 'TKOBFAKALTBDJAUB') # 授权码
content = """
	在前⾯的课程中，我们已经教会⼤家如何⽤Python程序⾃动的⽣成Excel、Word、PDF⽂档，接下来我们还可以更进⼀步，就是通过邮件将⽣成好的⽂档发送给指定的收件⼈，然后⽤短信告知对⽅我们发出了邮件。这些事情利⽤Python程序也可以轻松愉快的解决。
"""
m_part = MIMEMultipart()
m_part['From'] = 'lijunbo_k@163.com'
m_part['To'] = '1248094500@qq.com'
m_part['Subject'] = 'Python办公自动化学习内容'

# 添加附件
mime_text = MIMEText(content, 'plain', 'utf-8')
m_part.attach(mime_text)

with open('Base64.pdf', 'rb') as file:
    pdf_file = MIMEText(file.read(), 'base64', 'utf-8')
    pdf_file['content-type'] = 'application/pdf'	 # application/cotet-stream
    # 设置内容的处置方式（可下载的附件）
    pdf_file['contnet-disposition'] = 'attachment; filename="aaa.pdf"'
    m_part.attach(pdf_file)
    
with open('阿里巴巴2020股票数据.xlsx', 'rb') as file:
    excel_file = MIMEText(file.read(), 'base64', 'utf-8')
    excel_file['content-type'] = 'application/vnd.ms-excel'# application/cotet-stream
    excel_file['content-disposition'] = 'attachment; filename="alibaba-sock.xlsx"'
    m_part.attach(excel_file)

# 2.发送邮件
smtp_obj.sendmail(
	from_addr='lijunbo_k@163.com',
    to_addrs='1248094500@qq.com',
    msg=m_part.as_string()
)

# 3.结束会话
smtp_obj.quit() # smtp_obj.close()