day07-openpyxl操作Excel文件

day07-openpyxl操作Excel文件

注意: openpyxl这个第三方库只能操作后缀 .xlsx 的Excel文件

01openpyxl读Excel文件

1. Excel相关的基本概念

工作簿	一个Excel文件就是一个工作簿
工作表	数据是以单元格为单位保存到工作表中, 一个工作簿中可以有一个或者多个工作表(至少一个)
单元格	工作表中的每一个格子

# 导入openpyxl库
import openpyxl

2. 打开Excel文件, 获得一个工作簿对象

openpyxl.open(excel文件路径)

openpyxl.load_workbook(excel文件路径)

3. 获取工作簿中所有工作表的表名

# 变量名 = 工作簿对象.sheetnames
names = wb.sheetnames
print(names)

4. 获取工作表对象

工作簿对象.active - 获取当前Excel文件中的活跃表(被选中的那张表)

工作簿对象[表名] - 获取指定表名对应的工作表

5. 获取最大行数和列数

工作表.max_row - 获取指定工作表中的最大行数

工作表.max_column - 获取指定工作表中的最大列数

6. 获取单元格

# 变量名 = 工作表.cell(行数, 列数)
cell1 = sheet2.cell(2, 1)
print(cell1)

7. 获取单元格中的内容

# 单元格变量名.value
print(cell1.value)

02灵活获取excel文件中的数据

import openpyxl

wb = openpyxl.load_workbook('文件路径.xlsx')

sheet = wb['原始数据']

# 假设获取第四行每一列中的数据
row = 4
for col in range(1, sheet.max_column + 1):
    cell = sheet.cell(row, col)
    print(cell.value)

# 假设获取第二列上所有行的数据
column = 2
for row in range(1, sheet.max_row + 1):
    cell = sheet.cell(row, column)
    print(cell.value)

# 获取每个学生的所有成绩
for row in range(1, sheet.max_row + 1):
    for col in range(1, sheet.max_column + 1):
        cell = sheet.cell(row, col)
        print(cell.value)

# 练习: 获取每个学生的所有成绩, 保存到一个列表中, 列表中的元素是字典
# [{'姓名': '李楠', '英语': 70, '办公软件操作': 96, '电子商务': 73, '计算机基础': 79}, ]

all_stu = []
for row in range(2, sheet.max_row + 1):
    stu = {}
    for col in range(1, sheet.max_column + 1):
        key_cell = sheet.cell(1, col).value
        data_cell = sheet.cell(row, col).value
        # stu[key_cell] = data_cell
        stu.setdefault(key_cell, data_cell)
    all_stu.append(stu)
print(all_stu)

03excel写操作

# 导入openpyxl库
import openpyxl

1. 创建工作簿(新建一个excel文件)

# 创建工作簿
wb = openpyxl.Workbook()

# 如果工作簿存在可以使用load_workbook()打开工作簿
wb1 = openpyxl.load_workbook('文件路径.xlsx')

2. 新建工作表

使用默认的名字新建工作表	wb.create_sheet()
使用指定的名字新建工作表	wb.create_sheet('student')
在指定位置前插入指定名字的工作表	wb.create_sheet('teacher', 0)
在已经存在的工作簿中新建表	wb.create_sheet('电影信息')

3. 删除工作表

工作簿.remove(工作表对象)

if 'Sheet1' in wb1.sheetnames:
    wb1.remove(wb1['Sheet1'])

4. 修改单元格内容

单元格对象.value = 数据

# 增加内容
sheet5.cell(1, 6).value = '平均分'

# 修改内容
sheet5.cell(6, 2).value = '(缺考)'

# 删除单元格内容
sheet5.cell(2,2).value = None

5. 保存excel文件

工作簿.save(文件路径.xlsx)

04excel写操作的灵活运用

import openpyxl
import os
import smtplib

from email.mime.multipart import MIMEMultipart
from email.header import Header
from email.mime.text import MIMEText

data1 = [
    {'姓名': '李楠', '英语': 78, '办公软件操作': 96, '电子商务': 73, '计算机基础': 79},
    {'姓名': '方鹏', '英语': 63, '办公软件操作': 94, '电子商务': 91, '计算机基础': 78},
    {'姓名': '李磊', '英语': 89, '办公软件操作': 65, '电子商务': 0, '计算机基础': 0},
    {'姓名': '王小若', '英语': 81, '办公软件操作': 77, '电子商务': 73, '计算机基础': 80},
    {'姓名': '陈雨', '英语': 0, '办公软件操作': 87, '电子商务': 84, '计算机基础': 88},
    {'姓名': '石璐', '英语': 91, '办公软件操作': 95, '电子商务': 84, '计算机基础': 80},
    {'姓名': '张瑛', '英语': 73, '办公软件操作': 92, '电子商务': 92, '计算机基础': 89},
    {'姓名': '程晓', '英语': 80, '办公软件操作': 86, '电子商务': 0, '计算机基础': 0},
    {'姓名': '王丽', '英语': 72, '办公软件操作': 55, '电子商务': 88, '计算机基础': 70},
    {'姓名': '赵军力', '英语': 69, '办公软件操作': 69, '电子商务': 63, '计算机基础': 76},
    {'姓名': '王明', '英语': 96, '办公软件操作': 80, '电子商务': 85, '计算机基础': 64},
    {'姓名': '李丽', '英语': 64, '办公软件操作': 0, '电子商务': 74, '计算机基础': 86},
    {'姓名': '张帆', '英语': 72, '办公软件操作': 64, '电子商务': 97, '计算机基础': 74},
    {'姓名': '张珊珊', '英语': 60, '办公软件操作': 95, '电子商务': 69, '计算机基础': 61},
    {'姓名': '刘丽丽', '英语': 78, '办公软件操作': 93, '电子商务': 81, '计算机基础': 0},
    {'姓名': '石节庆', '英语': 62, '办公软件操作': 63, '电子商务': 64, '计算机基础': 70},
    {'姓名': '路瑶', '英语': 0, '办公软件操作': 0, '电子商务': 0, '计算机基础': 63},
    {'姓名': '李贵明', '英语': 84, '办公软件操作': 90, '电子商务': 87, '计算机基础': 99}
]

data2 = [
    {'name': '李楠', 'gender': '女', 'score': 1000, 'email': 'xxx@qq.com'},
    {'name': '赵军力', 'gender': '男', 'score': 567, 'email': 'xxx@163.com'},
    {'name': '张帆', 'gender': '男', 'score': 478, 'email': 'xx@163.com'},
    {'name': '王小若', 'gender': '女', 'score': 672, 'email': 'xx@qq.com'},
    {'name': '李贵明', 'gender': '男', 'score': 325, 'email': 'xx@qq.com'}
]

connect = smtplib.SMTP_SSL('smtp.qq.com', 465)

connect.login('发件人邮箱账号', '授权码')

for user in data2:
	to_email = user['email']
	email = MIMEMultipart()
	email['Subject'] = Header('成绩单', 'utf-8').encode()
	email['From'] = 'xxx <发件人邮箱账号>'
	email['To'] = to_email
	msg = MIMEText('%s%s, \n\t')

# 1. 如果对应的excel文件已经存在就打开这个文件, 不存在就创建
if os.path.isfile('xxx.xlsx'):
    wb = openpyxl.load_workbook('xxx.xlsx')
else:
    wb = openpyxl.Workbook()

# 2. 如果学生信息表已经存在就获取这个表, 如果不存在就创建这个表
if '学生信息' in wb.sheetnames:
    stu_sheet = wb['学生信息']
else:
    stu_sheet = wb.create_sheet('学生信息')

# 1) 学生信息  -- 将表头(第一行内容写到工作表中)
col = 1
for key in data1[0]:
    stu_sheet.cell(1, col).value = key
    col += 1

# 2) 学生信息  -- 从第二行开始写入所有学生的信息
row = 2
for stu in data1:
    col = 1
    for value in stu:
        stu_sheet.cell(row, col).value = stu[value]
        # print(stu[value])
        col += 1
    row += 1

# 创建 联系方式 对应的表
if '学生联系方式' in wb.sheetnames:
    contact_sheet = wb['学生联系方式']
else:
    contact_sheet = wb.create_sheet('学生联系方式')

col = 1
for key in data2[0]:
    contact_sheet.cell(1, col).value = key
    col += 1

row = 2
for contact in data2:
    col = 1
    for value in contact:
        contact_sheet.cell(row, col).value = contact[value]
        col += 1
    row += 1

wb.save('xxxx.xlsx')