注意：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)
1. 获取工作簿将代码直接转换为函数：Refactor —> Extract/Introduce —> Method
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发送邮件
1. smtplib模块邮件服务器 —> 自己搭建 / 买三方邮件服务
SMTP —> Simple Mail Transfer Protocol —> 跟你的邮件服务器进行网络通信
Python标准库已经对SMTP进行了封装，我们主要通过创建对象，给对象发消息的方式就可以完成邮件发送
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编码处理）
import smtplib from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText1. 创建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)
smtp_obj.sendmail(
from_addr='1837918832@qq.com',
to_addrs='haoyuwang0412@hotmail.com',
msg=m_part.as_string()
)
4. 结束会话
smtp_obj.quit()
