Python中使用OpenpyXL操作Excel
一、安装openpyxl库
可以使用命令
pip install openpyxl
指定版本与切换国内源请查看pyMySQL库那那一文章的详细解答
Python中使用PyMySQL库连接MySQL数据库 - AiniIT琦玉 - 博客园 (cnblogs.com)
二、读取Excel
1、读取全部
读取excel写法逻辑如下：
读取
# 调用函数，传递文件名
# 获取需要读取的单元表
# 读数据
# 关闭文件
实例：
import openpyxl
# 引用openpyxl
#获取表对象
r = openpyxl.load_workbook('test.xlsx')
#获取当前表的sheet
r_table = r.active
#创建空列表
data_list = []
for x in r_table.values: # 读取所有的数据以元祖类型返回
# 将每个遍历出来的值添加到空列表中
data_list.append(x)
# 输出列表中的值
print(data_list)
# 关闭连接
r.close()
我们在获取表的sheet的时候，active只获取在退出表格时点击保存过后的sheet界面，如果想获取sheet，我们可以使用worksheets、sheetnames、和直接指定sheet名称等多种方式，下面开始演示
使用直接引用sheet名称
import openpyxl
# 引用openpyxl
# 获取表对象
r = openpyxl.load_workbook('test.xlsx')
# 获取当前表的sheet
r_table = r['Sheet1'] # 注意这里直接引用了sheet的名称
# 创建空列表
data_list = []
for x in r_table.values: # 读取所有的数据以元祖类型返回
# 将每个遍历出来的值添加到空列表中
data_list.append(x)
# 输出列表中的值
print(data_list)
# 关闭连接
r.close()
使用worksheets方法：
import openpyxl
# 引用openpyxl
# 获取表对象
r = openpyxl.load_workbook('test.xlsx')
# 获取当前表的sheet
r_table = r.worksheets[0] # 注意这里是用切片方法来选择sheet表格的位置
# 创建空列表
data_list = []
for x in r_table.values: # 读取所有的数据以元祖类型返回
# 将每个遍历出来的值添加到空列表中
data_list.append(x)
# 输出列表中的值
print(data_list)
# 关闭连接
r.close()
# 输出结果为
[('1', '2', '3', '4', '5', '6'), ('7', '8', '9', '0', '11', '12')]
使用sheetnames方法：
这种方法就是通过列表对象直接选择sheet表格一样，只不过通过sheetnames将sheet名称获取到了而已
import openpyxl
# 引用openpyxl
# 获取表对象
r = openpyxl.load_workbook('test.xlsx')
# 获取当前表的sheet
r_table = r[r.sheetnames[0]]
print(r.sheetnames[0])
# 创建空列表
data_list = []
for x in r_table.values: # 读取所有的数据以元祖类型返回
# 将每个遍历出来的值添加到空列表中
data_list.append(x)
# 输出列表中的值
print(data_list)
# 关闭连接
r.close()
# 输出结果为
Sheet1
[('1', '2', '3', '4', '5', '6'), ('7', '8', '9', '0', '11', '12')]
2、按照指定的范围读取
我们需要先设置指定的行和列
实例
import openpyxl
# 根据传递的行和列进行读取
r = openpyxl.load_workbook('test.xlsx')
# 默认是获取关闭保存时所在的excel中的单元表
r_table = r.active
# d_table = r['单元表名称'] # 读取指定的单元表
# 设置外层空列表
data_list = []
# 读取1-2行，第2-第5列的值
# r_table.max_row代表最大行数，但是range方法用的是左闭右开的方式取值，所以要在后面加一，才能读取到最后一行的值
for x in range(1, r_table.max_row+1): # x代表行数
# 设置内层空列表
tmp = []
# r_table.max_column代表最大列数，但是range方法用的是左闭右开的方式取值，所以要在后面加一，才能读取到最后一列的值
for y in range(1, r_table.max_column+1): # y代表列数
# 将获取到的值添加到内层空列表tmp中
tmp.append(r_table.cell(x, y).value)
# 将每一次获取到的值添加到外层列表的data_list中
data_list.append(tmp)
print(data_list)
r.close()
三、写入Excel
写入时，我们也需要引用openpyxl库
写入逻辑如下：
# 写入
# 初始化类，创建单元表格
# 获取单元表
# 写入数据
# 保存
实例
import openpyxl
# 创建一个新的工作簿对象
wb = openpyxl.Workbook()
# 获取工作表对象(sheet)
ws = wb.active
print(ws)
# 设置Sheet名称
ws.title = '学生表'
# 创建一个新sheet，可以指定名称，index表示新创建的工作簿放在第几个位置, index从0开始计数
ws_1 = wb.create_sheet(index=1, title='成绩表')
ws_2 = wb.create_sheet(index=2, title='科目表')
# 获取所有工作表名称
print(wb.sheetnames)
# 方法一：写入单个单元格
ws['A1'] = '姓名'
ws['B1'] = '班级'
ws['c1'] = '年龄'
# 方法二：写入单个单元格(行，列，内容); 也可以直接ws.cell(1, 4, '学校')
ws.cell(row=1, column=4, value='学校')
# 写入多个单元格(追加模式，不会覆盖之前的，从有数据的下一行开始)
ws.append(['王明', '三年级一班', '9岁'])
# 第一列和第三列插入数据，第二列插入[None]值
ws.append(['王五', None, '10岁'])
# 追加第二行数据，过滤空值
# 获取工作表总行数
max_row = ws.max_row
col_values = []
# 遍历第二列所有行
for row in range(1, max_row+1):
# 获取当前单元格的值
cell_value = ws.cell(row=row, column=2).value
# 如果当前单元格的值不为空
if cell_value is not None:
# 将当前单元格的值添加到列表中
col_values.append(cell_value)
print("第二列有 %d 行数据" % len(col_values))
ws.cell(len(col_values)+1, 2, "三年级二班")
# 将新数据追加到最后一行，忽略第二列
data = [['张三', '10岁'], ['李四', '15岁']]
for row in data:
# 在第二列插入 None 值，实现跳过该列
row.insert(1, None)
ws.append(row)
# 保存
wb.save('1.xlsx')
我们在实际操作时，基本上使用的比较多的就是批量多行写入数据
实例
import openpyxl
# 创建表对象
w = openpyxl.Workbook()
# 获取工作对象sheet
x_table = w.active
# 写入单行单列(每一个列个上写数据)
# x_table.append(['a', 'b', 'c', 'd', 'e'])
# 写入多行多列，需要嵌套列表类型，使用循环方式写入
data_list = [
['1','2','3','4','5','6'],
['7','8','9','0','11','12']
]
# 遍历写入数据列表
for x in data_list:
# 将遍历好的数据依次写入工作对象sheet中
x_table.append(x)
# 保存并设置文件名称
w.save('test.xlsx')
