Python第二阶段学习总结

【第21天】Python第二阶段学习总结

2021/10/18

一. 办公自动化：Excel文件读取

注意：openpyxl —> 不兼容office 2007以前的版本，如果要读写Excel 2007以前的版本 —> xlwt / xlrd —> xls

from datetime import datetime

import openpyxl
from openpyxl.cell.cell import Cell
from openpyxl.chart import Reference, LineChart
from openpyxl.styles import Alignment, Font, Border, Side
from openpyxl.worksheet.worksheet import Worksheet


def display(value, delim=' '):
    if isinstance(value, float):
        print(f'{value:>10.2f}', end=delim)  # 右对齐，保留10位宽度
    elif isinstance(value, datetime):
        print(value.strftime('%Y年%m月%d日'), end=delim)
    elif isinstance(value, int):
        print(f'{value:>10d}', end=delim)
    else:
        print(value, end=delim)

将代码直接转换为函数：Refactor —> Extract/Introduce —> Method

1. 获取工作簿

workbook = openpyxl.load_workbook('resources/阿里巴巴2020年股票数据.xlsx')

2. 获取工作表

# sheet = workbook.active

# 获取所有的工作表workbook.worksheets
for sheet in workbook.worksheets:
    print(sheet.title)
    
# 通过下标获取工作表
sheet = workbook.worksheets[0]
print(sheet.title)

# 通过工作表名获取工作表（如果没有提示，可以在后面注释变量类型）
sheet = workbook['股票数据']  # type: Worksheet

3. 获取工作表的属性

print(sheet.max_row, sheet.max_column)
print(sheet.dimensions)

4. 操作单元格

cell = sheet['E5']  # type: Cell
# cell = sheet.cell(5, 5)
print(cell.value)

# 修改单元格内容
cell.value = 200
sheet['E5'] = 200
sheet.cell(5, 5, 200)	

5. 遍历循环所有单元格

for row in range(1, sheet.max_row + 1):
    for col in range(1, sheet.max_column + 1):
        display(sheet.cell(row, col).value)
    print()

6. 公式计算

sheet['E256'] = '=average(e2:e255)'
sheet['F256'] = '=sum(f2:f255)'

7. 设置格式

cell = sheet['E256']
cell.value = '我爱你中国'
# 对齐方式
cell.alignment = Alignment(horizontal='center', vertical='center')

# 字体
cell.font = Font(name='微软雅黑', size=20, color='330099')

# 边框
side = Side(color='ff6600', style='mediumDashed')
cell.border = Border(left=side, right=side, top=side, bottom=side)

# 行高和列宽
sheet.row_dimensions[256].height = 50
sheet.column_dimensions['E'].width = 35

边框的参数

8. 绘制图表

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.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "ff0000"
s1.marker.graphicalProperties.line.solidFill = "0000ff"
s1.smooth = True

# 将图表添加到工作表中
sheet.add_chart(c1, "A258")

9. 保存工作簿

workbook.save('resources/阿里巴巴2020年股票数据.xlsx')

二. Python发送邮件

邮件服务器 —> 自己搭建 / 买三方邮件服务

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

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

1. smtplib模块

smtplib模块 —> SMTP_SSL(安全) —> login() / sendmail() / quit()

邮件服务器：smtp.qq.com 端口：465
账号：1837918832@qq.com
授权码（密码）：oynbooayanpzcbhc

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

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

2. 实施步骤

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

1. 创建SMTP_SSL对象

smtp_obj = smtplib.SMTP_SSL(host='smtp.qq.com', port=465)

2. 登录授权

smtp_obj.login('1837918832@qq.com', 'authentication code')

m_part = MIMEMultipart()
m_part['From'] = '1837918832@qq.com'
m_part['To'] = 'haoyuwang0412@hotmail.com'
# mime_text['Cc'] = ''   抄送人
m_part['Subject'] = 'Python办公自动化学习内容'

content = """
    发送邮件，邮件内容。
    
    祝，好！
    
    王浩宇 即日
"""
mime_text = MIMEText(content, 'plain', 'utf-8')
m_part.attach(mime_text)

with open('resources/Base64.pdf', 'rb') as file:
    pdf_file = MIMEText(file.read(), 'base64', 'utf-8')
    pdf_file['content-type'] = 'application/pdf'
    pdf_file['content-disposition'] = 'attachment; filename = "Base64_Wiki.pdf"'
    m_part.attach(pdf_file)

with open('resources/阿里巴巴2020年股票数据.xlsx', 'rb') as file:
    excel_file = MIMEText(file.read(), 'base64', 'utf-8')
    excel_file['content-type'] = 'application/vnd.ms-excel'
    excel_file['content-disposition'] = 'attachment; filename = "alibaba-stock.xlsx"'
    m_part.attach(excel_file)

3. 发送邮件

smtp_obj.sendmail(
    from_addr='1837918832@qq.com',
    to_addrs='haoyuwang0412@hotmail.com',
    msg=m_part.as_string()
)

4. 结束会话

smtp_obj.quit()