目录
openpyxl 是一个处理Excel表格的第三方库。openpyxl 库可以处理Excel2010以后的电子表格格式，包括：xlsx/xlsm/xltx/xltm。
一、文件读操作
工作簿(workbook): excel文件
工作表(worksheet): 工作簿中的每一个活动就是一个工作表
单元格(cell): 工作表中用来存储数据的每个格子
注意：openpyxl只能操作新版本的excel文件(后缀为.xlsx)
1. 打开excel文件，得到一个工作簿对象
openpyxl.load_workbook(excel文件路径)
2. 获取工作簿相关信息
1）获取工作簿中所有的工作表的名称
变量 = 工作簿.sheetnames
2) 获取所有的工作表，返回值是一个列表，列表中的元素是工作表对象
变量 = 工作簿.worksheets
3) 获取指定工作表:
变量 = 工作簿对象[表名]
4) 获取活跃表(当前处于选中状态的工作表)
变量 = 工作簿.active
3. 获取工作表的相关信息
1) 获取数据的最大行数和列数
工作表对象.max_row - 获取最大行数
工作表对象.max_column - 获取最大列数
2) 获取指定单元格(注意这里写的是行号和列号，都是从1开始)
变量 = 工作表.cell(row, column)
4. 获取单元格相关信息
获取单元格内容
变量 = 工作表.cell(row, column).value
import openpyxl
# 注意：openpyxl只能操作新版本的excel文件(后缀为.xlsx)
# 工作簿(workbook): excel文件
# 工作表(worksheet): 工作簿中的每一个活动就是一个工作表
# 单元格(cell): 工作表中用来存储数据的每个格子
# 1. 打开excel文件，得到一个工作簿对象
# openpyxl.load_workbook(excel文件路径)
workbook = openpyxl.load_workbook('files/2020年销售数据.xlsx')
# 2. 获取工作簿相关信息
# 1）获取工作簿中所有的工作表的名称
names = workbook.sheetnames
print(names)
# 2)获取所有的工作表，返回值是一个列表，列表中的元素是工作表对象
all_sheet = workbook.worksheets
print(all_sheet)
# 3)获取指定工作表: 工作簿对象[表名]
sheet1 = workbook['data']
print(sheet1)
# 4)获取活跃表(当前处于选中状态的工作表)
sheet2 = workbook.active
print(sheet2)
# 3. 获取工作表的相关信息
# 1)获取数据的最大行数和列数
# 工作表对象.max_row - 获取最大行数
# 工作表对象.max_column - 获取最大列数
print(sheet1.max_row, sheet1.max_column)
print(sheet2.max_row, sheet2.max_column)
# 2)获取指定单元格
# 注意这里写的是行号和列号，都是从1开始
cell1 = sheet1.cell(7, 4)
print(cell1)
# 4. 获取单元格相关信息
# 获取单元格内容
result = cell1.value
print(result)练习：
import openpyxl
# 获取工作簿
wb = openpyxl.load_workbook('files/2020年销售数据.xlsx')
# 获取工作表
datasheet = wb['data']
# 案例：获取第五行
max_col = datasheet.max_column # 最大列数
data = []
for col in range(1, max_col + 1):
cell = datasheet.cell(5, col)
data.append(cell.value)
print(data)
print('-----------------------分------割------线-----------------------')
# 练习1：获取data中第5列所有的数据
data1 = []
max_row = datasheet.max_row
for row in range(1, max_row + 1):
cell = datasheet.cell(row, 5)
data1.append(cell.value)
print(data1)
print('-----------------------分------割------线-----------------------')
# 练习2：获取所有的品牌(列表去重)
brand_list = []
max_row = datasheet.max_row
for row in range(3, max_row + 1):
value = datasheet.cell(row, 5).value
if value not in brand_list:
brand_list.append(value)
print(brand_list)
print('-----------------------分------割------线-----------------------')
# 练习3：计算不同品牌的总的销售额
# 方法1：使用上面已经搜索出来的品牌列表
sale = {}
for b in brand_list:
sum1 = 0
for row in range(3, max_row+1):
if datasheet.cell(row, 5).value == b:
sum1 += datasheet.cell(row, 8).value
sale[b] = sum1
print(sale)
print('-----------------------分------割------线-----------------------')
# print(type(datasheet.cell(row, 5).value))
# 方法2：
dic = {}
for row in range(3, max_row + 1):
brand = datasheet.cell(row, 5).value
sale = datasheet.cell(row, 8).value
# a.使用setdefault函数添加键值对
# dic.setdefault(brand, 0)
# dic[brand] += sale
# b.使用if判断
# if brand in dic:
# dic[brand] += sale
# else:
# dic[brand] = sale
# c.使用.get()方法读取第一个数据，则不会报错
dic[brand] = dic.get(brand, 0) + sale
print(dic)
print('-----------------------分------割------线-----------------------')
dic = {'八匹马': 0, '皮皮虾': 0, '壁虎': 0, '花花姑娘': 0, '啊哟喂': 0}
# dic = {}
max_row = datasheet.max_row
for row in range(3, max_row + 1):
value = datasheet.cell(row, 5).value
for d in list(dic.keys()):
if value == d:
dic[d] += datasheet.cell(row, 8).value
print(dic)
print('-----------------------分------割------线-----------------------')
# data2 = []
# for row in range(1, max_row):
# temp = []
# for col in range(1, max_col):
# cell = datasheet.cell(row, col)
# temp.append(cell.value)
# data2.append(temp)
# print(data2)二、文件写操作
注意：所有写操作在保存后才会有效
1. 新建工作簿(创建一个工作簿对象)
新建的工作簿中默认有一个工作表
变量 = openpyxl.Workbook()
2. 添加工作表
工作簿对象.create_sheet() - 在工作簿的最后添加一个名字为Sheet?的工作表
工作簿对象.create_sheet(表名) - 在工作簿的最后添加一个名字为指定值的工作表
工作簿对象.create_sheet(表名,下标) - 在指定下标对应位置添加名字为指定值的工作表
3. 删除工作表
工作簿对象.remove(工作簿[表名])
del 工作簿[表名]
4.修改单元格内容
工作表.cell(row, column, 修改/添加内容)
工作表.cell(row, column).value = 修改/添加内容
import openpyxl
# 1. 新建工作簿(创建一个工作簿对象)
wb = openpyxl.Workbook()
# 2. 添加工作表
wb.create_sheet()
# 注意：添加工作表的逻辑 - 不存在才添加
if 'students' not in wb.sheetnames:
wb.create_sheet('students')
wb.create_sheet('teachers', 0)
# 3. 删除工作表
# 工作簿对象.remove(工作簿[表名])
# del 工作簿[表名]
if 'Sheet' in wb.sheetnames:
wb.remove(wb['Sheet'])
if 'Sheet1' in wb.sheetnames:
del wb['Sheet1']
# 4.修改单元格内容
students_sheet = wb['students']
students_sheet.cell(1, 1, '姓名') # 方法1
students_sheet.cell(1, 2).value = '年龄' # 方法2
wb.save('files/school.xlsx')案例：
import openpyxl
# 1.将data数据写深入到新建的excel中默认的工作表中的第二行
data = ['2020-1-1', '上海', '天猫', '205654-021', '八匹马', 169, 85, 14365]
# 准备工作表
wb1 = openpyxl.Workbook()
sheet = wb1.active
# 写入数据
for x in range(len(data)):
value = data[x]
col = x + 1
sheet.cell(2, col, value)
wb1.save('files/data1.xlsx')练习：
练习1：将data中的写入到新建的表中的第3列中
# 练习1：将data中的写入到新建的表中的第3列中
data = ['八匹马', '皮皮虾', '壁虎', '花花姑娘', '啊哟喂']
sheet1 = wb1.create_sheet('练习1')
for x in range(len(data)):
row = x + 1
value = data[x]
sheet1.cell(row, 3, value)
wb1.save('files/data1.xlsx')
练习2：将class1中所有学生的信息以合理方式保存到新建的'学生表'中
# 练习2：将class1中所有学生的信息以合理方式保存到新建的'学生表'中
class1 = {
'name': 'python2402',
'address': '6教室',
'lecturer': {'name': '余婷', 'age': 18, 'gender': '女', 'qq': '726550822'},
'head_teacher': {'name': '舒嚒嚒', 'age': 18, 'gender': '女', 'tel': '110'},
'students': [
{'name': 'stu1', 'age': 17, 'gender': '男', 'score': 89, 'link_man': {'name': '张三', 'tel': '122334'}},
{'name': 'stu2', 'age': 28, 'gender': '女', 'score': 99, 'link_man': {'name': '李四', 'tel': '29833'}},
{'name': 'stu3', 'age': 22, 'gender': '女', 'score': 65, 'link_man': {'name': '王五', 'tel': '22223'}},
{'name': 'stu4', 'age': 22, 'gender': '男', 'score': 77, 'link_man': {'name': '赵六', 'tel': '6544'}},
{'name': 'stu5', 'age': 21, 'gender': '男', 'score': 46, 'link_man': {'name': '何七', 'tel': '664322'}},
{'name': 'stu6', 'age': 16, 'gender': '女', 'score': 82, 'link_man': {'name': '李八', 'tel': '12278334'}}
]
}
data = ['姓名', '年龄', '性别', '分数', '联系人', '联系人电话']
sheet2 = wb1.create_sheet('学生表')
for x in range(len(data)):
col = x + 1
value = data[x]
sheet2.cell(1, col, value)
student_list = class1['students']
for x in range(len(student_list)):
stu = student_list[x]
row = x + 2
stu_value = list(stu.values())
link_man = stu_value.pop()
# print(stu_value,link_man) # ['stu1', 17, '男', 89] {'name': '张三', 'tel': '122334'}
stu_value.append(link_man['name'])
stu_value.append(link_man['tel'])
# print(stu_value) # ['stu1', 17, '男', 89, '张三', '122334']
for y in range(len(stu_value)):
col = y + 1
value = stu_value[y]
sheet2.cell(row, col, value)
wb1.save('files/data1.xlsx')练习3：将所有的0分都替换成补考；添加总分列，并且计算出每个学生的总分
import openpyxl
# 练习3：将所有的0分都替换成补考；添加总分列，并且计算出每个学生的总分
wb = openpyxl.load_workbook('files/data2.xlsx')
sheet1 = wb['Sheet1']
max_row = sheet1.max_row # 19
max_col = sheet1.max_column # 5
sheet1.cell(1, max_col + 1, '总分')
for row in range(2, max_row + 1):
sum1 = 0
for col in range(2, max_col + 1):
value = sheet1.cell(row, col).value
sum1 += value
if value == 0:
sheet1.cell(row, col).value = '补考'
sheet1.cell(row, max_col + 1).value = sum1
wb.save('files/data2.xlsx')三、修改单元格样式
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
# 1.打开工作簿
wb = openpyxl.load_workbook('files/school.xlsx')
sheet = wb.active
# 2.设置单元格字体样式
"""
Font(
name=None, # 字体名，可以用字体名字的字符串
strike=None, # 删除线，True/False
color=None, # 文字颜色
size=None, # 字号
bold=None, # 加粗, True/False
italic=None, # 倾斜，Tue/False
underline=None # 下划线, 'singleAccounting', 'double', 'single', 'doubleAccounting'
)
"""
# 1) 创建字体对象
font1 = Font(
size=20,
italic=True,
color='ff0000',
bold=True,
strike=True
)
# 2) 设置指定单元格的字体
# 单元格对象.font = 字体对象
sheet['B2'].font = font1
# 3. 设置单元格填充样式
"""
PatternFill(
fill_type=None, # 设置填充样式: 'darkGrid', 'darkTrellis', 'darkHorizontal', 'darkGray', 'lightDown', 'lightGray', 'solid', 'lightGrid', 'gray125', 'lightHorizontal', 'lightTrellis', 'darkDown', 'mediumGray', 'gray0625', 'darkUp', 'darkVertical', 'lightVertical', 'lightUp'
start_color=None # 设置填充颜色
)
"""
# 1） 设置填充对象
fill = PatternFill(
fill_type='solid',
start_color='ffff00'
)
# 2）设置单元格的填充样式
# 单元格对象.fill = 填充对象
sheet['B2'].fill = fill
# 4. 设置单元格对齐样式
# 1）创建对象
al = Alignment(
horizontal='right', # 水平方向:center, left, right
vertical='top' # 垂直方向: center, top, bottom
)
# 2） 设置单元格的对齐方式
sheet['B2'].alignment = al
# 5. 设置边框样式
# 1）设置边对象（四个边的边可以是一样的也可以不同，如果不同就创建对个Side对象）
side = Side(border_style='thin', color='0000ff')
# 2) 设置边框对象
# 这儿的left、right、top、bottom表示的是边框的四个边，这儿四个边使用的是一个边对象
bd = Border(left=side, right=side, top=side, bottom=side)
# 3）设置单元格的边框
sheet['B2'].border = bd
# 6.设置单元格的宽度和高度
# 设置指定列的宽度
sheet.column_dimensions['A'].width = 20
# 设置指定行的高度
sheet.row_dimensions[1].height = 45
# 7. 保存
wb.save('files/school.xlsx')
简单示例：
"""
Author: 余婷
Create Time: 2024/5/28 16:58
你只管努力，时间会给你惊喜！
"""
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
wb = openpyxl.load_workbook('files/data1.xlsx')
sheet1 = wb['Sheet']
# 1. 设置字体
# 1)创建字体对象
f1 = Font(
name='楷体',
color='990033',
size=20,
bold=True
)
# 2)设置单元格的字体
sheet1.cell(2, 2).font = f1
# 2. 填充单元格
# 1)创建填充对象
fill1 = PatternFill(
fill_type='solid',
start_color='FFFFCC'
)
# 2)设置单元格的填充样式
sheet1.cell(2, 2).fill = fill1
# 3. 设置边框样式
# 1）创建边对象
s1 = Side(
border_style='medium',
color='663366'
)
s2 = Side(
border_style='mediumDashDot',
color='009966'
)
# 2)创建边框对象
border1 = Border(
bottom=s1,
top=s1,
left=s2,
right=s2
)
# 3)设置单元格的边框
sheet1.cell(2, 2).border = border1
wb.save('files/data1.xlsx')