python 使用 openpyxl 修改表格中的内容
1、向某个格子中写入内容并保存2、向表格中插入行数据 .append()3、在 python 中使用 excel 函数公式4、插入空行和空列 .insert_cols()和.insert_rows()5、删除行和列 .delete_rows()和.delete_cols()6、移动格子 .move_range()7、创建新的 sheet 表格 .create_sheet()8、删除某个 sheet 表 .remove()9、复制一个 sheet 表 .copy_worksheet()10、修改 sheet 表的名称 sheet.title11、创建新的 excel 表格文件12、冻结窗口 sheet.freeze_panes13、给表格添加“筛选器” sheet.auto_filter.ref
注意:通过脚本对表格进行操作的时候,不要用其他软件打开表格,否则可能会造成脚本运行失败
1、向某个格子中写入内容并保存
方式一:相当于直接修改源文件中的单元格
from openpyxl import load_workbook
workbook = load_workbook(filename='../excelDemo/testCase.xlsx')
sheet = workbook.active
print(sheet)
# 方式一:相当于直接修改源文件
sheet["C2"] = "无效"
workbook.save(filename='../excelDemo/testCase.xlsx')
方式二:修改单元格内容后另存文件
from openpyxl import load_workbook
workbook = load_workbook(filename='../excelDemo/testCase.xlsx')
sheet = workbook.active
print(sheet)
# 方式二:将“C4”单元格的数据改为了“失效”,并另存为了“test.xlsx”文件(路径为当前py程序下)
cell = sheet["C4"]
cell.value = "失效"
workbook.save(filename = "test.xlsx")
2、向表格中插入行数据 .append()
.append()方式:会在表格已有的数据后面,增添这些数(按行插入); 这个操作很有用,爬虫得到的数据,可以使用该方式保存成 Excel 文件;
from openpyxl import load_workbook
workbook = load_workbook(filename='./test1.xlsx')
sheet = workbook.active
# print(sheet)
data = [
["机审规则","不同用户类型跑不同publine","主"],
["机审规则","不同用户类型跑不同publine","低"],
["机审规则","不同用户类型跑不同publine","中"],
["机审规则","不同用户类型跑不同publine","中"],
]
for row in data:
sheet.append(row)
# print(row)
workbook.save(filename='./test2.xlsx')
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200825104034246.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjE0MDI2,size_16,color_FFFFFF,t_70#pic_center)
3、在 python 中使用 excel 函数公式
python 究竟支持写哪些“excel 函数公式”呢?我们可以使用如下操作查看一下
from openpyxl import load_workbook
from openpyxl.utils import FORMULAE
print(FORMULAE)
计算平均值 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200825112435380.png#pic_center)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200825112621336.png#pic_center)
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
# print(sheet)
sheet["k1"] = "平均消费"
for i in range(2,5):
sheet[f"k{i}"] = f'=AVERAGE(B{i}:J{i})'
workbook.save(filename='./cash.xlsx')
4、插入空行和空列 .insert_cols()和.insert_rows()
.insert_cols(idx=数字编号, amount=要插入的列数),插入的位置是在 idx 列数的左
侧插入;
.insert_rows(idx=数字编号, amount=要插入的行数),插入的行数是在 idx 行数的下
方插入;
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
# print(sheet)
sheet.insert_cols(idx=2,amount=1)
# 在第二列插入一列空列
sheet.insert_rows(idx=3,amount=2)
# 在第三行插入两行空行
workbook.save(filename='./cash.xlsx')
效果如下,在第二列插入一列空列,在第三行插入两行空行。 注意:
通过函数对表格进行操作时,表格不能用其他软件进行打开,否则该脚本会运行失败插入空行和空列,会对之前的使用 excel 函数公式产生影响,例如饮料喝肉类两行的平均消费的值就发生了改变,因为计算平均值的时候,是根据所限定范围内的单元格进行操作的,插入空行和空列后,指定的单元格的内容已经发生了改变
5、删除行和列 .delete_rows()和.delete_cols()
.delete_rows(idx=数字编号, amount=要删除的行数)
.delete_cols(idx=数字编号, amount=要删除的列数)
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
# print(sheet)
sheet.delete_cols(idx=2,amount=1)
# 删除第二列
sheet.delete_rows(idx=3,amount=2)
# 从第三行开始删除两行
workbook.save(filename='./cash.xlsx')
效果如下,删除第二列,从第三行开始删除两行,表格又回复原样了 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200825112621336.png#pic_center)
6、移动格子 .move_range()
.move_range("数据区域",rows=,cols=):正整数为向下或向右、负整数为向左或向上
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
# print(sheet)
sheet.move_range("D2:G5",rows=2,cols=1)
# 将"D2:G5"这块区域向下平移2行,向右平移一列
workbook.save(filename='./cash.xlsx')
效果如下 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200926192247237.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjE0MDI2,size_16,color_FFFFFF,t_70#pic_center)
7、创建新的 sheet 表格 .create_sheet()
.create_sheet("新的 sheet 名"):创建一个新的 sheet 表
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
# print(sheet)
workbook.create_sheet("newSheet")
print(workbook.sheetnames)
# ['Sheet1', 'newSheet'] 现在存在两个sheet表,其中newSheet是新建的
workbook.save(filename='./cash.xlsx')
效果如下 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200926193024373.png#pic_center)
8、删除某个 sheet 表 .remove()
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
print(workbook.sheetnames)
# ['Sheet1', 'newSheet'] 目前有两个sheet表
# 这个操作相当于激活的这个要删除的 sheet 表,激活状态下,才可以操作;
remove_sheet = workbook['newSheet']
workbook.remove(remove_sheet)
print(workbook.sheetnames)
# ['Sheet1'] newSheet表已被删除,现在只有一张表
workbook.save(filename='./cash.xlsx')
9、复制一个 sheet 表 .copy_worksheet()
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
print(workbook.sheetnames)
# ['Sheet1']
# 这个操作相当于激活的这个要操作的 sheet 表,激活状态下,才可以操作;
copy_sheet = workbook['Sheet1']
workbook.copy_worksheet(copy_sheet)
print(workbook.sheetnames)
# ['Sheet1', 'Sheet1 Copy']
workbook.save(filename='./cash.xlsx')
当然,你还可以再对这个复制出来的表再进行一次复制操作
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
print(workbook.sheetnames)
# ['Sheet1', 'Sheet1 Copy']
# 这个操作相当于激活的这个要操作的 sheet 表,激活状态下,才可以操作;
copy_sheet = workbook['Sheet1 Copy']
workbook.copy_worksheet(copy_sheet)
print(workbook.sheetnames)
# ['Sheet1', 'Sheet1 Copy', 'Sheet1 Copy Copy']
workbook.save(filename='./cash.xlsx')
10、修改 sheet 表的名称 sheet.title
.title = "新的 sheet 表名"
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
print(workbook.sheetnames)
# ['Sheet1', 'Sheet1 Copy', 'Sheet1 Copy Copy']
sheet.title = "sheetUpdate"
print(workbook.sheetnames)
# ['sheetUpdate', 'Sheet1 Copy', 'Sheet1 Copy Copy'],第一张 sheet 表的表名已改变
# 若excel表中存在多个 sheet 表,默认修改第一张 sheet 表
# 也可以指定 sheet 表更改表名
updateSheet = workbook['Sheet1 Copy Copy']
updateSheet.title = "sheetUpdate2"
print(workbook.sheetnames)
# ['sheetUpdate', 'Sheet1 Copy', 'sheetUpdate2']
workbook.save(filename='./cash.xlsx')
11、创建新的 excel 表格文件
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
# sheet 表名为"表格 1"
sheet.title = "表格 1"
# excel 表名为 “新建的 excel 表格”
workbook.save(filename = "新建的 excel 表格")
12、冻结窗口 sheet.freeze_panes
.freeze_panes = "单元格"
from openpyxl import load_workbook
workbook = load_workbook(filename='./cash.xlsx')
sheet = workbook.active
print(sheet)
# 冻结 "C3"单元格
sheet.freeze_panes = "C3"
workbook.save(filename='./cash.xlsx')
效果如下:第一二行固定在表格顶部,第一二列固定在左边,其余可拖动滚动条进行滑动查看 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200927202333898.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjE0MDI2,size_16,color_FFFFFF,t_70#pic_center)
13、给表格添加“筛选器” sheet.auto_filter.ref
.auto_filter.ref = sheet.dimension 给所有字段添加筛选器;
.auto_filter.ref = "A1" 给 A1 这个格子添加“筛选器”,就是给第一列添加“筛选器”
from openpyxl import load_workbook
workbook = load_workbook(filename='./test2.xlsx')
sheet = workbook.active
# print(sheet)
sheet.auto_filter.ref = "C1"
workbook.save(filename='./test2.xlsx')
效果如下 ![在这里插入图片描述](https://img-blog.csdnimg.cn/2020092720505397.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0NjE0MDI2,size_16,color_FFFFFF,t_70#pic_center)
|