【第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()