Openpyxl读、写、改、修改样式Excel文件
一、读Excel文件
1.Excel相关概念
1)工作簿 - 一个Excel文件(后缀是.xls或者.xlsx的文件)就是一个工作簿
2)工作表 - Excel文件中每个活动页就是一个工作表
3)单元格 - 工作表中的每一个格子就是一个单元格
import openpyxl
# 1.打开Excel文件获取工作簿对象
wb = openpyxl.open('files/data.xlsx')
2.获取工作表相关信息
# 1)获取工作簿所有工作表名称:工作簿对象.sheetnames
all_sheet_name = wb.sheetnames
print(all_sheet_name) # ['student', 'teacher']
# 2)获取指定的工作对象
# a.工作簿对象.active - 获取当前工作簿的活跃表(默认选中的表)
# b.工作簿对象[表名] - 获取工作簿中指定名字对应的工作表
a_sheet = wb.active
print(a_sheet) # <Worksheet "teacher">
student_s = wb['student']
print(student_s)
teacher_s = wb['teacher']
print(teacher_s)
# 3)获取单元格相关信息
# a.工作表对象.max_row - 最大行数
# b.工作表对象.max_column - 最大列数
mr = student_s.max_row
mc = student_s.max_column
print(mr, mc)
# c.工作表对象.cell(行号, 列号) - 获取指定行中的指定列对应的单元格(获取指定位置对应的单元格)
cell1 = student_s.cell(1, 2)
cell2 = student_s.cell(3, 1)
# d.单元格对象.value - 获取指定单元格中的内容
print(cell1.value) # 姓名
print(cell2.value) # 002
# 练习1:获取学生表中第五个学生的所有信息
stu = []
row = 5 + 1
for col in range(1, mc+1):
stu.append(student_s.cell(row, col).value)
print(stu)
# 练习2:获取所有学生的性别
genders = []
col = 4
for row in range(2, mr + 1):
value = student_s.cell(row, col).value
genders.append(value)
print(genders)
# 练习3:获取老师表的所有的数据
teacher_s = wb['teacher'] # 取表
t_mc = teacher_s.max_column # 最大列数
t_mr = teacher_s.max_row # 最大行数
print(t_mr, t_mc) # 4 2
for row in range(1, t_mr+1):
row_data = []
for col in range(1, t_mc+1):
value = teacher_s.cell(row, col).value
row_data.append(value)
print(row_data)
二、Excel写操作
import openpyxl
import os
# 补充:
# os.path.exists(文件或者文件路径) - 判断指定文件或者文件件是否存在
# 1.新建工作簿(新建Excel文件)
if os.path.exists('files/data2.xlsx'):
wb1 = openpyxl.open('files/data2.xlsx')
else:
wb1 = openpyxl.Workbook()
# 2.新建工作表
# 工作簿对象.create_sheet(表名) - 创建名字为指定值的工作表,并且返回工作表对象
if 'student' in wb1.sheetnames:
print('获取学生表')
student = wb1['student']
else:
print('创建学生表')
student = wb1.create_sheet('student') # 执行一次新建一个表格
# 3.删除工作表
# 工作簿对象.remove(工作表)
if 'Sheet' in wb1.sheetnames:
wb1.remove(wb1['Sheet'])
# 4.修改单元格内容
# 单元格.value = 数据
student.cell(1, 3).value = '张三'
student.cell(1, 1).value = '李四'
student.cell(1, 2).value = None
# 保存工作簿(保存文件) - 所有的写操作只要在保存之后有效
wb1.save('files/data2.xlsx')
练习:
# 新建Excel文件(信息.xlsx),然后再新建’学生表‘,将所有的students中所有学生信息写入新建的’学生表‘表中(并且需要自己添加表头)
# 姓名、年龄、性别、电话、联系人姓名、联系人电话
students = [
{'name': 'stu1', 'age': 20, 'gender': '男', 'tel': '11987223', 'linkman': {'name': '张三', 'tel': '81923'}},
{'name': 'stu2', 'age': 22, 'gender': '女', 'tel': '8293212', 'linkman': {'name': '小明', 'tel': '6666'}},
{'name': 'stu3', 'age': 25, 'gender': '女', 'tel': '727332', 'linkman': {'name': '李四', 'tel': '829333'}},
{'name': 'stu4', 'age': 19, 'gender': '男', 'tel': '563743', 'linkman': {'name': '老王', 'tel': '778822'}},
{'name': 'stu5', 'age': 23, 'gender': '男', 'tel': '0928322', 'linkman': {'name': '赵六', 'tel': '829101'}}
]
import openpyxl
import os
# 1.创建工作簿
if os.path.exists('files/信息.xlsx'):
wb = openpyxl.open('files/信息.xlsx')
else:
wb = openpyxl.Workbook()
# 2.创建工作表
if '学生表' in wb.sheetnames:
sheet = wb['学生表']
else:
sheet = wb.create_sheet('学生表')
# 3.添加数据
# 添加表头
headers = ['姓名', '年龄', '性别', '电话', '联系人姓名', '联系人电话']
for col in range(1, len(headers)+1):
sheet.cell(1, col).value = headers[col-1]
# 添加学生信息
row = 2
for stu in students:
sheet.cell(row, 1).value = stu['name']
sheet.cell(row, 2).value = stu['age']
sheet.cell(row, 3).value = stu['gender']
sheet.cell(row, 4).value = stu['tel']
sheet.cell(row, 5).value = stu['linkman']['name']
sheet.cell(row, 6).value = stu['linkman']['tel']
row += 1
三、设置样式(openpyxl)
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
wb = openpyxl.open('files/data.xlsx')
# sheet = wb['teacher']
sheet = wb.active
# ---------------------设置样式--------------------------
# 1.设置宽度和高度
# 设置指定列的宽度
sheet.column_dimensions['A'].width = 20
sheet.column_dimensions['B'].width = 18
# 设置指定行的高度
sheet.row_dimensions[1].height = 50
# 设置多行的高度
for x in range(2, 5):
sheet.row_dimensions[x].height = 30
# 2.设置单元格字体样式
# 1)创建字体对象
font1 = Font(
name='黑体', # 字体名称
# strike=True, # 是否添加删除线
color='643850', # 文字颜色
size=15, # 字体大小
bold=True, # 是否加粗
italic=True, # 是否倾斜
# underline=None # 下划线, 'singleAccounting', 'double', 'single', 'doubleAccounting'
)
# r(0~255)、g(0~255)、b(0~255) - (255,0,0) 红色 电脑的三元色
# 2)设置单元格的字体
# sheet.cell(4, 1).font = font1
for row in range(2, 5):
sheet.cell(row, 1).font = font1
# 3.设置填充样式
# 1)创建填充对象
fill1 = PatternFill(
# 设置填充样式:'lightGray', 'solid', 'darkUp', 'gray0625', 'lightDown', 'lightGrid', 'mediumGray', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkDown', 'lightHorizontal', 'darkTrellis', 'darkVertical', 'lightTrellis', 'lightVertical', 'lightUp', 'gray125'
fill_type='darkHorizontal',
# 起始颜色:fill_type为solid的时候只有起始颜色有效
start_color='BA55D3',
# 结束颜色
end_color='32CD32'
)
# 2)设置单元格填充样式
sheet.cell(1, 1).fill = fill1
# 4.设置单元格对齐方式
ag1 = Alignment
# 垂直对齐方式:center、top、bottom
vertical='center',
# 水平对齐方式:center、left、right
horizontal='right'
)
sheet.cell(1, 1).alignment = ag1
# 5.设置边框样式
# 1)创建边对象
side1 = Side(
border_style='medium', # 'hair', 'mediumDashDot', 'mediumDashDotDot', 'thick', 'dashDotDot', 'dotted', 'medium', 'thin', 'double', 'slantDashDot', 'dashDot', 'dashed', 'mediumDashed'
color='800000'
)
side2 = Side(
border_style='dotted',
color='000000'
)
# 2)创建边框
border1 = Border(bottom=side1, top=side1, left=side2, right=side2)
# 3)设置单元格边框样式
sheet.cell(2, 4).border = border1
wb.save('files/data.xlsx')