python 使用 openpyxl 操作 excel

python 如何向 excel 中写入某些内容？（一）

1. 修改表格中的内容

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

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet) 
sheet["A1"] = "哈喽" 
# 这句代码也可以改为 cell = sheet["A1"] cell.value = "哈喽" 
workbook.save(filename = "哈喽.xlsx") 
""" 
注意：我们将“A1”单元格的数据改为了“哈喽”，并另存为了“哈喽.xlsx”文
件。 如果我们保存的时候，不修改表名，相当于直接修改源文件；
"""

② .append()：向表格中插入行数据

• .append()方式：会在表格已有的数据后面，增添这些数(按行插入)；
• 这个操作很有用，爬虫得到的数据，可以使用该方式保存成 Excel 文件；

from openpyxl import load_workbook
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
data = [
["西游班","唐僧","男","123456"],
["西游班","孙悟空","男","123456"],
["西游班","猪八戒","男","123456"],
["西游班","沙僧","男","123456"],
]
for row in data:
 sheet.append(row)
workbook.save(filename = "test.xlsx")

注：运行过程中记得关闭要修改的Excel文件，不然会报错
我自己运行的时候不知道为啥修改后的数据字体和之前的数据字体不一样，后面完善一下。

③ 在 python 中使用 excel 函数公式

from openpyxl import load_workbook
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
sheet["D1"] = "标准身高"
for i in range(2,10):
 sheet["D{}".format(i)] = '=IF(RIGHT(C{},2)="cm",C{},SUBSTITUTE(C{},"m","")*100&"cm")'.format(i,i,i)
workbook.save(filename = "test.xlsx")

查看python 支持的“excel 函数公式”

import openpyxl
from openpyxl.utils import FORMULAE 
print(FORMULAE)

④ .insert_cols()和.insert_rows()：插入空行和空列

• .insert_cols(idx=数字编号, amount=要插入的列数)，插入的位置是在 idx 列数的左侧插入；
• .insert_rows(idx=数字编号, amount=要插入的行数)，插入的行数是在 idx 行数的下方插入；

from openpyxl import load_workbook
import openpyxl
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
sheet.insert_cols(idx=4,amount=2)
sheet.insert_rows(idx=5,amount=4)
workbook.save(filename = "test.xlsx")

运行结果如下：

⑤ .delete_rows()和.delete_cols()：删除行和列

• .delete_rows(idx=数字编号, amount=要删除的行数)
• .delete_cols(idx=数字编号, amount=要删除的列数)

from openpyxl import load_workbook
import openpyxl
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(sheet)
# 删除第一列，第一行
sheet.delete_cols(idx=1)
sheet.delete_rows(idx=1)
workbook.save(filename = "test.xlsx")

⑥.move_range()：移动格子

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

⑦ .create_sheet()：创建新的 sheet 表格

workbook = load_workbook(filename = "test.xlsx") 
sheet = workbook.active 
print(sheet) 
workbook.create_sheet("我是一个新的 sheet") 
print(workbook.sheetnames) 
workbook.save(filename = "test.xlsx")

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

• .remove("sheet 名")：删除某个 sheet 表；

from openpyxl import load_workbook
import openpyxl
workbook = load_workbook(filename = "test.xlsx")
sheet = workbook.active
print(workbook.sheetnames)
# 这个相当于激活的这个 sheet 表，激活状态下，才可以操作；
sheet = workbook['我是一个新的 sheet']
print(sheet)
workbook.remove(sheet)
print(workbook.sheetnames)
workbook.save(filename = "test.xlsx")

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

• 这个操作的实质，就是复制某个 excel 表中的 sheet 表，然后将文件存储到另外一张excel 表中；

workbook = load_workbook(filename = "a.xlsx") 
sheet = workbook.active 
print("a.xlsx 中有这几个 sheet 表",workbook.sheetnames) 
sheet = workbook['姓名'] 
workbook.copy_worksheet(sheet) 
workbook.save(filename = "test.xlsx")

⑩ sheet.title：修改 sheet 表的名称

• .title = "新的 sheet 表名"

workbook = load_workbook(filename = "a.xlsx") 
sheet = workbook.active 
print(sheet) 
sheet.title = "我是修改后的 sheet 名" 
print(sheet)

⑪ 创建新的 excel 表格文件

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

⑫ sheet.freeze_panes：冻结窗口

• .freeze_panes = "单元格"

from openpyxl import Workbook, load_workbook
workbook = load_workbook(filename = "a.xlsx")
sheet = workbook.active
print(sheet)
sheet.freeze_panes = "C3"
workbook.save(filename = "a.xlsx")
""" 
冻结窗口以后，你可以打开源文件，进行检验；
"""

⑬ sheet.auto_filter.ref：给表格添加“筛选器”

• .auto_filter.ref = sheet.dimension 给所有字段添加筛选器；
• .auto_filter.ref = "A1" 给 A1 这个格子添加“筛选器”，就是给第一列添加“筛选器”；

workbook = load_workbook(filename = "a.xlsx") 
sheet = workbook.active 
print(sheet) 
sheet.auto_filter.ref = sheet["A1"] 
workbook.save(filename = "a.xlsx")

注：不知道为啥运行报错代码应该没问题，后面发现问题所在再改。