20211018学习总结

一、办公自动化

1.Excel文件操作

内容注释：

openpyxl —> 不兼容Office 2007之前版本
如果要读写Excel 2007之前版本 —> xlwt / xlrd —> xls

docx —> x —> xml —> 标记语言
xlsx
pptx

from datetime import datetime

import openpyxl
from openpyxl.cell import Cell
from openpyxl.chart import LineChart, Reference
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)
    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)


def main():
    workbook = openpyxl.load_workbook(r'阿里巴巴2020年股票数据.xlsx')

    # sheet = workbook.active
    for sheet in workbook.worksheets:
        print(sheet.title)
    # 拿到工作表
    sheet = workbook['股票数据']  # type: Worksheet
    # 获取工作表的属性
    print(sheet.max_row, sheet.max_column)
    print(sheet.dimensions)
    # 操作单元格
    cell = sheet['E5']  # type: Cell
    # cell = sheet.cell(5, 5)
    print(cell.value)
    # 该单元格的值
    # sheet.cell(5, 5, 200)
    cell.value = 200

    # 循环遍历所有单元格
    for row in range(2, sheet.max_row + 1):
        for col in range(1, sheet.max_column + 1):
            value = sheet.cell(row, col).value
            display(value)
        print()

    # 6.公式计算
    # 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='微软雅黑', size=20, color='669999')
    # 边框
    side = Side(color='ff6600', style='mediumDashed')
    cell.border = Border(left=side, right=side, top=side, bottom=side)
    # 行高和列宽
    sheet.row_dimensions[256].heigth = 50
    sheet.column_dimensions['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.series[0]
    s1.marker.symbol = "triangle"
    s1.marker.graphicalProperties.solidFill = "FF0000"
    s1.marker.graphicalProperties.line.solidFill = "FF0000"
    s1.smooth = True

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

    # 保存工作簿
    workbook.save('阿里巴巴2020年股票数据01.xlsx')


if __name__ == '__main__':
    main()

2.发送邮件操作

内容注释：

邮件服务器 —> 自己搭建/ 买三方邮件服务
SMTP —> Simple Mail Transfer Protocol —> 发邮件协议 —> 跟你的邮件服务器进行网络通信
Python标准库邮件对SMTP进行了封装，我们主要通过创建对象，给对象发消息的方式就可以完成邮件发送

smtplib模块 —> SMTP_SSL —> login() / sendmail()

邮件服务器：smtp.qq.com 端口：465
授权码：kiowjkwtbptxbbhe

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

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

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

# 创建SMTP_SSL对象
smtp_obj = smtplib.SMTP_SSL(host='smtp.qq.com', port=465)
# 登录授权
smtp_obj.login('1050105440@qq.com', 'kiowjkwtbptxbbhe')

content = """
     Excel是Microsoft（微软）为使⽤Windows和macOS操作系统开发的⼀款电⼦表格软件。Excel凭借其直观的界⾯、出⾊的计算功能和图表⼯具，再加上成功的市场营销，⼀直以来都是最为流⾏的个⼈计算机数据处理软件。
     祝， 好！"""
m_part = MIMEMultipart()
m_part['From'] = '1050105440@qq.com'
m_part['To'] = '1132341532@qq.com'
# mime_text['Cc'] = ''
m_part['Subject'] = 'Python办公自动化学习内容'

mime_text = MIMEText(content, 'plain', 'utf-8')
m_part.attach(mime_text)

with open('第17课：面向对象编程入门.pdf', 'rb')as file:
    pdf_file = MIMEText(file.read(), 'base64', 'utf-8')
    pdf_file['content-type'] = 'application/pdf'
    pdf_file['content-disposition'] = 'attachment; filename="Python-email-sms.pdf"'
    m_part.attach(pdf_file)

with open('阿里巴巴2020年股票数据01.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='1050105440@qq.com',
    to_addrs='1132341532@qq.com',
    msg=m_part.as_string()
)
# 结束会话
smtp_obj.quit()