使用openpyxl操作excel(二)

1、修改表格中的内容

1.1 向某个格子中写入内容并保存

from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet["A1"] = "哈喽"
workbook.save(filename = "哈喽.xlsx")

1.2 .append():向表格中插入行数据

from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
data = [
      ["唐僧","男","180cm"],
      ["孙悟空","男","188cm"],
      ["猪八戒","男","175cm"],
      ]
for row in data:
    sheet.append(row)
workbook.save(filename = "test.xlsx")

1.3 在python中使用excel函数公式

from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet["B1"] = "另一个A1"
for i in range(2,16):
    sheet["B{}".format(i)] = '=IF(RIGHT(A{},2)="cm",A{},SUBSTITUTE(C{},"m","")*100&"cm")'.format(i,i,i)
workbook.save(filename = "test.xlsx")

1.4 .insert_cols()和.insert_row()：插入空行和空列

#.insert_cols(idx=数字编号，amount=要插入的列数)，插入的位置是在idx列数的左侧插入
#.insert_rows(idx=数字编号，amount=要插入的行数)，插入的位置是在idx行数的下方插入
from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet.insert_cols(idx=4,amount=2)
sheet.insert_rows(idx=5,amount=4)
workbook.save(filename = "test.xlsx")

1.5 .delete_rows()和.delete_cols():删除行和列

#.delete_rows(idx=数字编号，amount=要删除的行数)
#.delete_cols(idx=数字编号，amount=要删除的行数)
from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet.delete_cols(idx=1)
sheet.delete_rows(idx=1)
workbook.save(filename = "test.xlsx")

1.6 .move_range()：移动格子

# .move_range("数据区域",rows=,cols=)：正整数为向下或向右、负整数为向左或向上：
# 向左移动两列，向下移动两行
sheet.move_range("C1:D4",rows=2,cols=-2)

1.7 .create_sheet():创建新的sheet表格

# .create_sheet("新的sheet名"):创建一个新的sheet表：
from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
workbook.create_sheet("我是一个新的sheet")
workbook.save(filename = "test.xlsx")

1.8 .remove()：删除某个sheet表

# .remove("sheet名")：删除某个sheet表：
from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet = workbook['我是一个新的sheet']
workbook.remove(sheet)
workbook.save(filename = "test.xlsx")

1.9 .copy_worksheet()：复制一个sheet表到另外一张excel表

from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet = workbook['新sheet']
workbook.copy_worksheet(sheet)
workbook.save(filename = "test.xlsx")

1.10 sheet.title:修改sheet表的名称

# .title = "新的sheet表名"
from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet.title = "我是修改后的sheet名"

1.11 创建新的excel表格文件

from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = "表格1"
workbook.save(filename = "新建的excel表格")

1.12 sheet.freeze_panes:冻结窗口

# .freeze_panes = "单元格"
from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet.freeze_panes = "C3"
workbook.save(filename = "test.xlsx")

1.13 sheet.auto_filter.ref:给表格添加"筛选器"

# .auto_filter.ref = sheet.dimension 给所有字段添加筛选器
# .auto_filter.ref = "A1" 给A1这个格子添加筛选器，就是给第一列添加筛选器
from openpyxl import load_workbook
workbook = load_workbook(filename="test.xlsx")
sheet = workbook.active
sheet.auto_filter.ref = sheet["A1"]
workbook.save(filename = "test.xlsx")