wenhaha 的文章目录
欢迎阅读wenhaha的第一篇文章
excle表格相信大家都不陌生,就是通过表来对数据进行展示和处理。生活中有很多的职业需要每天处理非常多的excle表格,进行数据的添加、修改和查找(还包括一些统计)等操作。openpyxl是一个Python的模块,可以用来处理excle表格。接下来我会在本篇文章中介绍如何使用python 中的 openpyxl 模块来帮我们处理 Excel 表的工作。
1.Excel 文档简介
首先,让我们来看一些基本定义。
- 一个 Excel 电子表格文档称为一个 工作簿 。
- 一个 工作簿保存在扩展名为 .xlsx 的文件中。
- 每个工作簿可以包含多个 Sheet (表, 也称为工作表)。
- 用户当前查看的表(或关闭 Excel 前最后查看的表或创建excel文件后的第一张表),称为 活动表 (active sheet)。
- 每个表都有一些列(地址是 从A 开始的字母 )和一些行(地址是从 1 开始的数字)。我们通常使用字母+数字获取某一个格子的数据,例如B3代表第2列第3排的那一个数据。
- 在特定行和列的方格称为 单元格 。
- 每个单元格都包含一个 数字、文本或空值。
- 单元格形成的网格和数据构成了 表 。
2.安装 openpyxl 模块
Python 没有自带 openpyxl,所以必须安装。直接 pip install openpyxl
就可以。
3.Excel 文档的基本操作实例
该实例将使用一个电子表格 test.xlsx,它保存在工程目录的 files 文件夹中。下图是文件内容截图,主要包含了默认的两个表:Shee1, Sheet2
3.1 用 openpyxl 模块打开 Excel 文档
在导入 openpyxl 模块后,就可以使用openpyxl.load_workbook()函数来打开指定的 excel 文件,也可以使用openpyxl.Workbook()创建一个新的Workbook(工作簿)对象:
openpyxl.load_workbook(地址)
- 打开给定的文件名并返回 工作簿openpyxl.Workbook()
- 新建一个 Workbook(工作簿)对象 ,即excel文件
import openpyxl
# openpyxl.load_workbook(需要打开的excel文件路径)
wb = openpyxl.load_workbook('files/test.xlsx')
print(type(wb)) # 结果: <class 'openpyxl.workbook.workbook.Workbook'>
openpyxl.load_workbook()函数接受文件名,返回一个 workbook 数据类型的值。这 个 workbook 对象代表这个 Excel 文件,有点类似 File 对象代表一个打开的文本文件。
3.2 从工作簿中取得工作表
工作簿对象.sheetnames
- 获取当前工作簿中 所有表的名字工作簿对象.active
- 获取当前 活动表1 对应的Worksheet对象工作簿对象[表名]
- 根据表名获取指定 表对象表对象.title
- 获取表对象的 表名表对象.max_row
- 获取表的 最大有效行数表对象.max_column
- 获取表的 最大有效列数
import openpyxl
# 打开工作簿
wb = openpyxl.load_workbook('files/test.xlsx')
# 获取所有表的表名
sheets_names = wb.sheetnames
print(sheets_names) # 结果: ['Sheet1', 'Sheet2']
# 获取活动表对应的表对象(表对象就是Worksheet类的对象)
active_sheet = wb.active
print(active_sheet) # 结果:<Worksheet "Sheet1">
# 根据表名获取工作簿中指定的表
sheet2 = wb['Sheet2']
print(sheet2) # 结果:<Worksheet "Sheet2">
# 根据表对象获取表的名字
sheet_name1 = active_sheet.title
sheet_name2 = sheet2.title
print(sheet_name1, sheet_name2) # 结果:Sheet1 Sheet2
# 获取第二列的所有内容
sheet = wb.active
row_num = sheet.max_row # 获取当前表中最大的行数
for row in range(1, row_num+1):
cell = sheet.cell(row, 2)
print(cell.value)
3.3 从表中取得单元格
获取到表以后,可以通过表去获取表中的单元格:
表对象['列号行号']
- 获取指定列的指定行对应的单元格对象(单元格对象是 Cell 类的对象,列号是从A开始,行号是从1开始)表对象.cell(行号, 列号)
- 获取指定行指定列对应的单元格(这儿的行号和列好号都可以用数字)表对象.iter_rows()
- 一行一行的取表对象.iter_cols()
- 列表一列的取单元格对象.value
- 获取单元格中的内容单元格对象.row
- 获取行号(数字1开始)单元格对象.column
- 获取列号(数字1开始)单元格对象.coordinate
- 获取位置(包括行号和列号)
import openpyxl
# 打开excel文件
wb = openpyxl.load_workbook('files/test.xlsx')
# 获取活跃表对象
sheet = wb.active
# 获取单元格对应的 Cell 对象
a1 = sheet['A1'] # A1 表示A列中的第一行,这儿的列号采用的是从A开始的
print(a1)
# 获取单元格中的内容
content = a1.value
print(content)
# 调用表的 cell()方法时,可以传入整数 作为 row 和 column 关键字参数,也可以得到一个单元格
content2 = sheet.cell(2,2).value
print(content2)
# 获取单元格的行和列信息
row = a1.row
print('行:', row)
column = a1.column
print('列:', column)
coordinate = a1.coordinate
print(coordinate)
输出结果:
<Cell 'Sheet1'.A1>
Rank
python
行: 1
列: 1
A1
在 Z 列之后,列开始使用两个字母:AA、AB、AC 等。作为替代,在调用表的 cell()方法时,可以传入整数 作为 row 和 column 关键字参数,也可以得到一个单元格。注意:第一行或第一列的对应的整数 是 1,不是 0。
# 获取第二列的所有内容
row_num = sheet.max_row # 获取当前表中最大的行数
for row in range(1, row_num+1):
cell = sheet.cell(row, 2)
print(cell.value)
'''
结果:
Language
python
java
c
html
'''
3.4 从表中取得行和列
在实际的对Excel的操作中,我们经常需要对一个区域的数据进行处理。表对象也可以像列表或者字符串那样进行 切片 操作,来获取电子表格中一行、一列或一个 矩形区域 中的所有 Cell 对象,然后就可以对数据进行相应的操作。具体的用法如下:
表对象[位置1:位置2]
- 获取指定范围中的所有的单元格
import openpyxl
from openpyxl.utils import get_column_letter
# 打开excel文件
wb = openpyxl.load_workbook('files/example1.xlsx')
# 获取表
sheet = wb.active
# 1.获取整个一行的单元格
max_column = sheet.max_column # 获取最大列数
column = get_column_letter(max_column) # 获取最大列数对应的字母列号
# 获取第一行所有单元格对象
row2 = sheet['A1':'%s1' % column] # ((<Cell '表1'.A1>, <Cell '表1'.B1>, <Cell '表1'.C1>),)
print(row2)
for row_cells in row2:
for cell in row_cells:
print(cell.coordinate, cell.value)
# 2.获取整个列的单元格
max_row = sheet.max_row
columnB = sheet['B1':'B%d' % max_row]
# 获取B列对应的所有单元格对象
for column_cells in columnB:
for cell in column_cells:
print(cell.coordinate, cell.value)
# 3. 获取矩形区域中的单元格对象
cell_tuples = sheet['A1': 'C3']
print(cell_tuples)
for cells in cell_tuples:
for cell in cells:
print(cell.coordinate, cell.value)
输出结果:
((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>),)
A1 Rank
B1 Language
C1 Score
B1 Language
B2 python
B3 java
B4 c
B5 html
((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))
A1 Rank
B1 Language
C1 Score
A2 1
B2 python
C2 100
A3 2
B3 java
C3 99.5
Process finished with exit code 0
3.5 通过创建Workbook对象的方式创建Excel文件并保存
openpyxl.Workbook()
- 创建空的 Excel 文件对应的工作簿对象工作簿对象.save(文件路径)
- 保存文件工作簿对象.create_sheet(title, index)
- 在指定工作簿中的指定位置(默认是最后)创建指定名字的表,并且返回表对象工作簿对象.remove(表对象)
- 删除工作簿中的指定表表对象[位置] = 值
- 在表中指定位置对应的单元格中写入指定的值,位置是字符串:‘A1’(第1列的第一行)、‘B1’(第二列的第一行)
import openpyxl
# 创建空的Workbook对象
wb = openpyxl.Workbook()
# 获取所有表名
print(wb.sheetnames) # ['Sheet']
# 可知默认情况下,新建的Workbook对象对应的Excel 文件中只有一张名字是 'Sheet' 的表
# 获取活动表
sheet_active = wb.active
# 修改表的名字
sheet_active.title = 'student_info'
# 保存至文件
wb.save(filename='files/school.xlsx')
# 新建表
wb.create_sheet()
wb.create_sheet()
# for i in range(20):
# wb.create_sheet()
print(wb.sheetnames) # ['student_info', 'Sheet', 'Sheet1']
# 新建表时,从Sheet开始一直到Sheet n
wb.create_sheet('teacher_info')
# `工作簿对象.create_sheet(title, index)` - 在指定工作簿中的指定位置(默认是最后)创建指定名字的表,并且返回表对象
wb.create_sheet('course_info', 1)
print(wb.sheetnames) # ['student_info', 'course_info', 'Sheet', 'Sheet1', 'teacher_info']
# 删除表
wb.remove(wb['Sheet'])
wb.save(filename='files/school.xlsx')
# 写入数据
wb = openpyxl.load_workbook('files/school.xlsx')
sheet = wb['student_info'] # 获取表
sheet['A1'] = '姓名'
sheet['B1'] = '年龄'
sheet['C1'] = '性别'
sheet['A2'] = 'wenhaha'
sheet['B2'] = 20
sheet['C2'] = '男'
wb.save('files/school.xlsx')
4.实例1-利用requests获取疫情数据,并且将获取到的数据使用excel文件保存到表中
# 利用requests获取天行数据中疫情数据,并且将获取到的数据使用excel文件保存到表中。
import requests, openpyxl
from openpyxl.utils import get_column_letter
# 1.获取数据
url = 'http://api.tianapi.com/txapi/ncovabroad/index?key=c9d408fefd8ed4081a9079d0d6165d43'
rep = requests.get(url)
news_list = rep.json()['newslist']
# 2.设置表头信息
headers = {'continents': ('洲', 'A'),
'provinceName': ('国家', 'B'),
'currentConfirmedCount': ('现有确诊', 'C'),
'confirmedCount': ('累计确诊', 'D'),
'curedCount': ('治愈', 'E'),
'deadCount': ('死亡', 'F')}
# 3.创建工作表
wb = openpyxl.Workbook()
sheet = wb.active
# 4.写入数据
# 先写入第一行的表头
column_num = 1
for key in headers:
column = get_column_letter(column_num)
location = f'{column}1'
sheet[location] = headers[key][0]
column_num += 1
# 再从第二行开始写入爬取到的数据
row = 2
for news in news_list: # 遍历每条数据项,一个数据项对应一个字典
for key in news: # 遍历数据键值
if key in headers: # 保证键是表头中的某一项我们需要的数据
location = f'{headers[key][1]}{row}' # 存在表中的位置
value = news[key] # 需要的数据
sheet[location] = value # 写入
row += 1
wb.save(filename='files/epidemic.xlsx')
5.实例2-通过openpyxl操作单元格中的内容的样式
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
# 字体、 填充图案、 边框、 侧边、 对齐方式
# 1.打开工作簿
wb = openpyxl.load_workbook('files/epidemic.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 # 下划线, 'single', 'singleAccounting', 'double','doubleAccounting'
)
"""
# 创建字体对象,并调整合适的参数
font1 = Font(
name='微软雅黑',
size=15,
italic=False,
color='ff0000',
bold=False,
strike=False,
underline='single',
)
# 设置指定单元格的字体
# 单元格对象.font = 字体对象
area = sheet['A1':'F1']
for row in area:
for _ in row:
_.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'
# '深色网格“,”深色网格“,”深色水平“,”深色灰色“,”浅色向下“,”浅灰色“,”纯色“,”浅色网格“,”灰色125“,”浅色水平“,”浅色网格“,”深色向下“,”中灰色“,”灰色0625“,”深色向上“,”深色垂直“,”浅色垂直“,”浅色向上“
start_color=None # 设置填充颜色
)
"""
# 设置填充对象
fill = PatternFill(fill_type='solid', start_color='FFC0CB')
# 设置单元格的填充样式
# 单元格对象.fill = 填充对象
area = sheet['A1':'F1']
for row in area:
for _ in row:
_.fill = fill # 调整填充格式
# 4. 设置单元格对齐样式
# 创建对象
al = Alignment(
horizontal='right', # 水平方向:center, left, right
vertical='top', # 垂直方向: center, top, bottom
)
# 设置单元格的对齐方式
sheet['B2'].alignment = al
# 5. 设置边框样式
# 设置边对象(四个边的边可以是一样的也可以不同,如果不同就创建对个Side对象)
# border_style取值('dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin')
# 大致翻译: 单点划线,双点划线,点划线,双划线,头发,中划线,单点中划线,双点中划线,中划线,斜划线,粗划线,细划线
side = Side(border_style='thin', color='000000')
# 设置边框对象
# 这儿的left、right、top、bottom表示的是边框的四个边,四个边可以使用一个边对象
bd = Border(left=side, right=side, top=side, bottom=side)
# 设置单元格的边框
area = sheet['A1':'F1']
for row in area:
for _ in row:
_.border = bd
# 6.设置单元格的宽度和高度
# 设置列宽
sheet.column_dimensions['A'].width = 10
# 设置行高
sheet.row_dimensions[1].height = 30
# 7. 保存
wb.save(filename='files/epidemic.xlsx')
太丑了!!! 请结合实际工作需要自行调整hhh
6.点个赞吧!我只会心疼giegie!!
如果你的文件打开后关闭,则关闭时的表被python认为是活动表。 ↩︎