|
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')
3、在 python 中使用 excel 函数公式
python 究竟支持写哪些“excel 函数公式”呢？我们可以使用如下操作查看一下
from openpyxl import load_workbook
from openpyxl.utils import FORMULAE
print(FORMULAE)
计算平均值 
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')
效果如下，删除第二列，从第三行开始删除两行，表格又回复原样了
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')
效果如下 
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')
效果如下 
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')
效果如下：第一二行固定在表格顶部，第一二列固定在左边，其余可拖动滚动条进行滑动查看 
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')
效果如下 
|