python向Excel表格中写入内容（三）

参考文档： 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')