参考文档: python——向Excel读取或写入数据
1、写入内容的最基本方法
from openpyxl import load_workbook
workbook = load_workbook(filename='./2012.xlsx')
print(workbook.sheetnames)
sheet = workbook.active
cell = sheet['A3']
cell.value = "你好啊"
workbook.save(filename="./2012.xlsx")
2、用python列表数据插入一行
sheet.append(python列表)-----会在表格中插入这些列表数据
from openpyxl import load_workbook
workbook = load_workbook(filename='./2012.xlsx')
data = [
["小样", 23],
["叼毛", 45]
]
sheet = workbook.active
for data_value in data:
sheet.append(data_value)
workbook.save(filename="./2012.xlsx")
3、插入公式----直接赋值公式字符串
from openpyxl import load_workbook
workbook = load_workbook(filename='./2012.xlsx')
sheet = workbook.active
sheet['B8'] = '=AVERAGE(B1:B7)'
workbook.save(filename='./2012.xlsx')
查看openpyxl支持的公式
from openpyxl.utils import FORMULAE
print(FORMULAE)
4、用python插入一列数据
.insert_cols(idx=数字编号,amount=要插入的列数(几列))--------在idx列的左边插入一列
from openpyxl import load_workbook
workbook = load_workbook(filename='./2012.xlsx')
sheet = workbook.active
sheet.insert_cols(idx=2)
workbook.save(filename='./2012.xlsx')
5、用python插入一行数据—(用法同4)
.insert_rows(idx=数字编号,amount=要插入的行数(几行))--------在idx行的上边插入一列
6、用python删除列
.delete_cols(idx=数字编号,amount=要删除的列数(几列))----从idx这一列开始,包括idx这一列
from openpyxl import load_workbook
workbook = load_workbook(filename='./2012.xlsx')
sheet = workbook.active
sheet.delete_cols(idx=2)
workbook.save(filename='./2012.xlsx')
6、用python删除行(同5操作)
.delete_rows(idx=数字编号,amount=要删除的行数(几行))----从idx这一行开始,包括idx这一行
7、移动格子
.move_range(“C1:D4”, rows=2,cols=-2)-----正整数为向下或向右、负整数向左或向上(C1:D4是一组格子)
8、创建新的sheet
workbook.create_sheet(sheet名称)
from openpyxl import load_workbook
workbook = load_workbook(filename='./2012.xlsx')
sheet = workbook.active
workbook.create_sheet("你好吗?")
workbook.save(filename='./2012.xlsx')
print(workbook.sheetnames)
9、删除一个sheet
workbook.remove(sheet实例)
from openpyxl import load_workbook
workbook = load_workbook(filename='./2012.xlsx')
sheet = workbook.active
sheet1 =workbook["你好吗?"]
workbook.remove(sheet1)
workbook.save(filename='./2012.xlsx')
print(workbook.sheetnames)
10、复制一个sheet
workbook.copy_worksheet(sheet实例)
from openpyxl import load_workbook
workbook = load_workbook(filename='./2012.xlsx')
sheet = workbook.active
sheet1 =workbook["表格1"]
workbook.copy_worksheet(sheet1)
workbook.save(filename='./2012.xlsx')
print(workbook.sheetnames)
11、修改sheet名称
sheet.title='想修改成的名字’
12、创建新的Excel表格文件
from openpyxl import load_workbook,Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = "测试表格"
workbook.save(filename="new.xlsx")
13、冻结窗格
sheet.freeze_panes = “G2”—就不会移动和修改
14、添加筛选
sheet.auto_filter.ref = sheet.dimensions
from openpyxl import load_workbook,Workbook
workbook = load_workbook(filename='./2012.xlsx')
sheet = workbook.active
data_size = sheet.dimensions
sheet.auto_filter.ref = data_size
workbook.save(filename='./2012.xlsx')