Day7 openpyxl操作Excel文件

一、 Excel文件读操作

excel相关的基本概念
1)工作簿(workbook)：一个Excel文件就是一个工作簿
2）工作表(sheet)：工作表是工作簿的基本单位，每个工作簿至少要有一个工作表
3）单元格(cell)：工作表中每一个用来保存数据的格子就是单元格
4）行(row)号：每一行前面的数字（从1开始）
5）列(column)号：每一列上面的大写字母（也可以用数字1开头，表示列号）

# 1.打开Excel文件(加载Excel文件)
# 1）openpyxl.open(Excel文件地址) - 打开指定的Excel文件得到工作簿对象
# 2）openpyxl.load_workbook(Excel文件地址) - 打开指定的Excel文件得到工作簿对象
# wb = openpyxl.open('files/example.xlsx')
wb = openpyxl.load_workbook('files/example.xlsx')

# 2.获取工作簿所有的工作表的名字
# 工作簿对象.sheetnames
names = wb.sheetnames
print(names)        # ['表1', '表2', 'Sheet1']

# 3.获取工作表
# 1)工作簿.active   -  当前工作簿中的活跃表
sheet1 = wb.active
print(sheet1)

# 2)工作簿[表名]  - 获取工作簿中指定表名对应的工作表
sheet2 = wb['表1']
print(sheet2)

# 4.获取最大行数和最大列数
# 1)工作表.max_row
# 2)sheet2.max_column
m_r = sheet2.max_row
m_c = sheet2.max_column
print(m_r, m_c)

print(wb['Sheet1'].max_row, wb['Sheet1'].max_column)

# 5.获取单元格
# 工作表.cell(行号, 列号)
cell1 = sheet2.cell(2, 2)
cell2 = sheet2.cell(2, 3)
cell3 = sheet2.cell(3, 2)
print(cell1, cell2, cell3)

# 6.获取单元格中的内容
# 单元格.value
print(cell1.value)
print(cell2.value)
print(cell3.value)

二、 Excel文件读操作练习

练习：
1.获取第2列所有内容，然后将数据保存到列表中：[Language, Python, Java, C,...,Go]
2.获取第2行所有内容，然后将数保存到字典中： {'Rank':1, 'Language': 'Python', 'Score': 100}

import openpyxl

wb = openpyxl.open('files/example.xlsx')
sheet = wb['表1']

m_r = sheet.max_row
m_c = sheet.max_column

# 获取第2列所有内容
data = []
for row in range(1, m_r+1):
    cell = sheet.cell(row, 2)
    data.append(cell.value)
print(data)

# 获取第2行所有内容
data = {}
for col in range(1, m_c+1):
    cell1 = sheet.cell(1, col)
    cell2 = sheet.cell(2, col)
    data[cell1.value] = cell2.value
print(data)

# 获取整个文件所有的数据
result = []
for row in range(2, m_r+1):
    line = {}
    for col in range(1, m_c+1):
        key = sheet.cell(1, col).value
        value = sheet.cell(row, col).value
        line.setdefault(key, value)
    result.append(line)
print(result)

三、 Excel文件写操作

import openpyxl
import os

# 注意：Excel文件相关任何写操作，只会在保存以后有效
# 1.新建Excel文件（新建工作簿）
# os.path.exists(文件或者文件夹路径)  -  判断指定的文件或者文件夹是否存在
wb1 = openpyxl.Workbook()

# 2.保存文件: 工作簿.save(文件地址)
wb1.save('files/example2.xlsx')

# 3.新建工作表
# 1)工作簿.create_sheet()
# 2)工作簿.create_sheet(表名)
# 3)工作簿.create_sheet(表名, 下标)
# sheet1 = wb2.create_sheet()
# sheet2 = wb2.create_sheet('student')
# sheet3 = wb2.create_sheet('teacher', 0)

# 4.删除表
# 工作簿.remove(工作表)
# wb2.remove(wb2['Sheet1'])
if 'Sheet1' in wb2.sheetnames:
    wb2.remove(wb2['Sheet1'])

# 5.修改单元格内容
# 单元格.value = 数据
sheet3.cell(2, 1).value = '小明'
sheet3.cell(1, 3).value = 'Tel'
sheet3.cell(1, 2).value = None

wb2.save('files/example3.xlsx')

案例：

# 案例：如果example3.xlsx文件不存在就创建，存在就打开
if os.path.exists('files/example3.xlsx'):
    wb2 = openpyxl.load_workbook('files/example3.xlsx')
else:
    wb2 = openpyxl.Workbook()
    wb2.save('files/example3.xlsx')
    
# 案例：如果teacher表不存在就创建这张表，存在就获取teacher表
if 'teacher' in wb2.sheetnames:
    sheet3 = wb2['teacher']
else:
    sheet3 = wb2.create_sheet('teacher', 0)

四、 Excel文件写操作练习

"""
请输入学生的姓名：xiaoming
请输入学生的年龄：18
请输入学生的性别：男
请输入学生的电话：110
是否继续(Y/N)：Y
...
请输入学生的姓名：xiaoming
请输入学生的年龄：18
请输入学生的性别：男
请输入学生的电话：110
是否继续(Y/N)：N
"""
# 2.将输入的每个学生的信息保存在excel文件
"""
姓名   年龄  性别  电话
xx     xx   xx    xx
xxx    xxx  xxx    xxx
....

"""
import openpyxl
import os
if os.path.exists('files/student.xlsx'):
    wb = openpyxl.load_workbook('files/student.xlsx')
else:
    wb = openpyxl.Workbook()
    wb.save('files/student.xlsx')

if 'student ' in wb.sheetnames:
    sheet = wb['student']
else:
    sheet = wb.create_sheet('student', 0)

sheet.cell(1, 1).value = '姓名'
sheet.cell(1, 2).value ='年龄'
sheet.cell(1, 3).value ='性别'
sheet.cell(1, 4).value = '电话'

while True:
    name = input('请输入你的姓名：')
    age = input('请输入你的年龄：')
    gender = input('请输入你的性别：')
    tel = input('请输入你的电话：')
    max_row = sheet.max_row
    sheet.cell(max_row + 1, 1).value = name
    sheet.cell(max_row + 1, 2).value = age
    sheet.cell(max_row + 1, 3).value = gender
    sheet.cell(max_row + 1, 4).value = tel

    str = input('是否继续(Y/N)：')
    if str != 'Y':
        break
    else:
        pass



wb.save('files/student.xlsx')

五、 Excel文件样式的修改

import openpyxl

wb = openpyxl.open('files/example3.xlsx')
# sheet1 = wb['teacher']
sheet1 = wb.active

# 1. 设置行高和列宽度
# 1）工作表.row_dimensions[行号].height = 高度
# 2）工作表.column_dimensions[列号(字母)].width = 宽度
sheet1.row_dimensions[1].height = 30
sheet1.column_dimensions['A'].width = 30

# 2.设置单元格字体样式
# 1)创建字体对象
# 导入字体类
from openpyxl.styles import Font
f1 = Font(
    name='黑体',
    size=15,
    color='CC00FF',
    strike=True,
    bold=True,
    italic=True,
    underline='single'
)

# 2)设置单元格字体
sheet1.cell(1, 1).font = f1

# 3.设置填充样式
from openpyxl.styles import PatternFill
# 1)创建填充对象
fill1 = PatternFill(
    fill_type='darkDown',
    start_color='FFFF00',
    end_color='FF0000'
)

sheet1.cell(1, 2).fill = fill1

# 4.设置边框样式
from openpyxl.styles import Side, Border
# 1)创建边
s1 = Side(
    border_style='medium',
    color='ff0000'
)
s2 = Side(
    border_style='mediumDashDot',
    color='00ff00'
)
# 2）创建边框
b1 = Border(
    top=s1,
    bottom=s1,
    left=s1,
    right=s1
)
b2 = Border(
    top=s2,
    bottom=s1,
    # left=s2,
    # right=s2
)

# 3)设置单元格的边框
sheet1.cell(4, 2).border = b1
sheet1.cell(6, 2).border = b2

wb.save('files/example3.xlsx')

六、 作业

"""
Author:ZouYan
Create Time:2023/2/14 18:45
"""
import openpyxl
import os

wb = openpyxl.open('files/学生信息.xlsx')
sheet1 = wb['students']

if 'Students1' in wb.sheetnames:
    sheet2 = wb['Students1']
else:
    sheet2 = wb.create_sheet('Students1', 0)

sheet2.cell(1, 1).value ='姓名'
sheet2.cell(1, 2).value = '年龄'
sheet2.cell(1, 3).value = '电话'
sheet2.cell(1, 4).value = '分数'

m_r = sheet1.max_row
m_c = sheet1.max_column
print(m_c, m_r)

for row in range(2, m_r+1):
    for col in range(1, m_c+1):
        sheet2.cell(row, col).value = sheet1.cell(row, col).value

if 'Students2' in wb.sheetnames:
    wb.remove(wb['Students2'])


sheet1.cell(1, 5).value = '留级建议'
for row in range(2, m_r+1):
    if sheet1.cell(row, 4).value < 60:
        sheet1.cell(row, 5).value = '留级'
    else:
        sheet1.cell(row, 5).value = '不留级'

sheet1 = wb.active
sheet1.column_dimensions['C'].width = 20
sheet1.column_dimensions['A'].width = 13
sheet1.column_dimensions['E'].width = 13

from openpyxl.styles import Font,PatternFill,Border,Side,Alignment
f1 = Font(
    name='黑体',
    size=15,
    color='8BBCA0',
    bold=True

)
f2 = Font(
    name='黑体',
    size=15,
    color='BBBBBB',
    bold=True
)
f3 = Font(
    name='黑体',
    size=10,
    color='E0ABAC',
    bold=True
)

for col in range(1, m_c+1):
    sheet1.cell(1, col).font = f1
for row in range(2, m_r+1):
    sheet1.cell(row,1).font = f2
for row in range(2, m_r+1):
    sheet1.cell(row, 5).font = f3

a1 = Alignment(
    horizontal='center',
    vertical='center'
)
for row in range(1, m_r+1):
    for col in range(1, m_c+1):
        sheet1.cell(row, col).alignment = a1

fill1 = PatternFill(
    fill_type='solid',
    start_color='C3D79A',
    end_color='C3D79A'
)
for row in range(1, m_c+1):
    sheet1.cell(1, row).fill = fill1

side1 = Side(
    border_style='medium',
    color='800080'
)
border1 = Border(bottom=side1, top=side1, left=side1, right=side1)

for row in range(1, m_r+1):
    for col in range(1, m_c+1):
        sheet1.cell(row,col).border = border1

# sheet1['B'].alignment = a1

wb.save('files/学生信息.xlsx')