20230214 Python操作excel的xlsx格式文件

一、Excel文件读操作

导入openpyxl库文件

import openpyxl

1. excel相关的基本概念

1.1 工作簿(workbook)：一个Excel文件就是一个工作簿

1.2 工作表(sheet)：工作簿中的基本单位，每个工作簿只是有1个表

1.3 单元格(cell)：工作表中没一个用来保存数据的格子就是单元格

1.4 行(row)：每一行前面的数字（从1开始）

1.5 列(column)：每一列列头的大写字母（也可以用数字1开头表示列号）

2.打开Excel文件（加载Excel文件）

2.1 openpyxl.open(Excel文件地址) - 打开指定的Excel文件得到工作簿对象

或load_workbook(Excel文件地址) - 打开指定的Excel文件得到工作簿对象

wb = openpyxl.open('files/example.xlsx')
或 wb = openpyxl.load_workbook('files/example.xlsx')

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

工作簿对象.sheetnames

names = wb.sheetnames
print(names)

4.获取工作簿中的某一个工作表

4.1 工作簿.active - 当前工作簿中的活跃表

sheet1 = wb.active
print(sheet1)

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

sheet1 = wb['表1']
print(sheet1)

5.获取最大行数和最大列数

5.1 工作表.max_row
5.2 工作表.max_column

max_r = sheet1.max_row
max_c = sheet1.max_column
print('最大行数：', max_r, '最大列数：', max_c)
print(wb['表1'].max_row, wb['表1'].max_column)

6.获取单元格

工作表.cell(行号, 列号)

cell1 = sheet1.cell(2, 3)
print(cell1)

7.获取单元格中的数据

单元格.value

print(cell1.value)

二、Excel文件写操作

import openpyxl
import os

注意：Excel相关的任何写的操作最后需要保存后有效

1.新建一个Excel（也可以是打开一个Excel）

ex1 = openpyxl.Workbook()

2.os.path.exists(‘文件或文件夹路径’) - 判断指定的文件或文件夹地址是否存在

print(os.path.exists('files/example2.xlsx'))

3.保存文件：save(‘文件名或文件地址’)

ex1.save('files/example2.xlsx')

案例：如果example3。xlsx文件不存在就粗昂，存在就打开

if os.path.exists('files/example3.xlsx'):
    ex2 = openpyxl.open('files/example3.xlsx')
else:
    ex2 = openpyxl.Workbook()
    ex2.save('files/example3.xlsx')

4.新建工作表

4.1 工作簿.create_sheet()

4.2 工作簿.create_sheet(‘表名’)

4.2 工作簿.create_sheet(‘表名’, 下标)

sheet1 = ex2.create_sheet('teacher', 0)
ex2.save('files/example3.xlsx')

案例：如果teacher表不存在就创建这张表，存在就获取这张表

if 'teacher' in ex2.sheetnames:
    sheet2 = ex2['teacher']
else:
    sheet2 = ex2.create_sheet('teacher', 0)
    ex2.save('files/example3.xlsx')

5.删除表（需谨慎操作，最好提前备份）

工作簿.remove(工作簿[‘表名’])

ex2.remove(ex2['student'])
if 'student' in ex2.sheetnames:
    ex2.remove(ex2['student'])

6.修改单元格内容

单元格.cel(row, column).value = ‘数据’

sheet2.cell(2, 1).value = '小明'
sheet2.cell(1, 3).value = 'Tel'
sheet2.cell(1, 2).value = None

7.保存Excel文档

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

三、Excel文件样式的修改

import openpyxl

ex = openpyxl.open('files/example3.xlsx')
sheet1 = ex['teacher']

1.设置行高和列宽

1.1 工作表.row_dimensions[行号]。height = 高度

sheet1.row_dimensions[1].height = 50
sheet1.column_dimensions['A'].width = 20

2.设置单元格字体样式

from openpyxl.styles import Font

2.1 创建字体对象

f1 = Font(
    name='黑体',
    color='8DC43C',
    size=16,
    bold=True,
    strike=True,        # 删除线
    underline='single'  # 下划线
)

2.2 设置单元格字体

sheet1.cell(1, 1).font = f1

3.设置填充样式

from openpyxl.styles import PatternFill

3.1 创建填充对象

fill1 = PatternFill(
    fill_type='solid',
    start_color='FFFF00',
)

3.2 设置单元格填充样式

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

4.设置边框样式

from openpyxl.styles import Side, Border

4.1 创建边

s1 = Side(
    border_style='medium',
    color='EE1111'
)
s2 = Side(
    border_style='medium',
    color='0909F7'
)

4.2 创建边框

b1 = Border(
    top=s1,
    bottom=s1,
    left=s1,
    right=s1,
)
b2 = Border(
    top=s1,
    bottom=s1,
    left=s1,
    right=s1,
)

4.3 设置单元格的边框

sheet1.cell(2, 3).border = b1
sheet1.cell(6, 2).border = b2

5.设置单元格对齐方式

from openpyxl.styles import Alignment

5.1 创建填充对象

al1 = Alignment(
    horizontal='center',  # 水平方向:center, left, right
    vertical='center'  # 垂直方向: center, top, bottom
)

5.2 设置单元格填充样式

sheet1.cell(1, 1).alignment = al1

6.保存Excel文档

ex.save('files/example3.xlsx')
print('文档保存成功！')

7.Excel文件操作作业

import openpyxl
import os
from openpyxl.styles import Font, PatternFill, Side, Border, Alignment
# 调用第三方库函数转换列为数字
from openpyxl.utils import get_column_letter

ex = openpyxl.Workbook()

if os.path.exists('files/学生信息.xlsx'):
    ex = openpyxl.open('files/学生信息.xlsx')
else:
    ex = openpyxl.Workbook()
    ex.save('files/学生信息.xlsx')

if 'students' in ex.sheetnames:
    sheet1 = ex['students']
else:
    sheet1 = ex.create_sheet('students', 0)
    ex.save('files/学生信息.xlsx')

if 'students2' in ex.sheetnames:
    sheet2 = ex['students2']
else:
    sheet2 = ex.create_sheet('students2', 1)
    ex.save('files/学生信息.xlsx')

# 文字样式1 红色
f1 = Font(
    color='FF0000',
)
# 文字样式2 12号加粗 深绿字
f2 = Font(
    size=12,
    bold=True,
    color='636C3F',
)
# 填充样式1 绿底
fill1 = PatternFill(
    fill_type='solid',
    start_color='A9C43C',
)
# 填充样式2 浅绿底
fill2 = PatternFill(
    fill_type='solid',
    start_color='E9F1C9',
)
# 边框样式1
s1 = Side(
    border_style='medium',
    color='BBBB44'
)
b1 = Border(
    top=s1,
    bottom=s1,
    left=s1,
    right=s1,
)
# 对齐方式1
al1 = Alignment(
    horizontal='center',  # 水平方向:center, left, right
    vertical='center'  # 垂直方向: center, top, bottom
)

max_r = sheet1.max_row
max_c = sheet1.max_column
max_c2 = sheet2.max_column

# 配置表头
sheet2.cell(1, 1).value = '姓名'
sheet2.cell(1, 2).value = '年龄'
sheet2.cell(1, 3).value = '电话'
sheet2.cell(1, 4).value = '分数'
sheet2.cell(1, 5).value = '留级建议'
sheet2.row_dimensions[1].height = 20
for c in range(1, max_c2 +1):
    col_latter = get_column_letter(c)
    sheet2.column_dimensions[col_latter].width = 20

# 表头样式配置
for c in range(1, max_c2+1):
    sheet2.cell(1, c).font = f2
    sheet2.cell(1, c).fill = fill1
    sheet2.cell(1, c).alignment = al1
    sheet2.cell(1, c).border = b1

# 表内容样式配置
for r in range(2, max_r+1):
    sheet2.row_dimensions[r].height = 16
    for c in range(1, max_c2+1):
        sheet2.cell(r, c).value = sheet1.cell(r, c).value
        sheet2.cell(r, c).border = b1
        sheet2.cell(r, c).fill = fill2
    if sheet2.cell(r, 4).value < 60:
        sheet2.cell(r, 4).font = f1
        sheet2.cell(r, 5).value = '留级'
    if sheet2.cell(r, 5).value == '留级':
        sheet2.cell(r, 5).font = f1
        sheet2.cell(r, 5).alignment = al1

# 保存文档
ex.save('files/学生信息.xlsx')