from openpyxl import Workbook
wb = Workbook()
wb.save("create_workbook.xlsx")
这样就会生成一个xlsx文件，工作表的名称默认为Sheet
新建工作表
通过wb.create_sheet(title)新建工作表
from openpyxl import Workbook
wb = Workbook()
wb.create_sheet("Sheet1")
wb.create_sheet("Sheet2")
wb.create_sheet("Sheet3")
wb.create_sheet("Sheet4")
wb.save("create_workbook.xlsx")
默认创建给定名称的工作表，创建出来工作表的顺序都是插入到最后面的
因为工作簿是新创建的，所以默认带有一个Sheet的工作表
插入工作表
通过wb.create_sheet(title,index)插入工作表
其实就是创建工作表，只是创建时，通过指定index，来指定插入的位置
PS:起始按照第0位，第1位，第2位，...，倒数为倒数第0位，倒数第1位，倒数第2位，...，这样去对照下表理解
index
插入的位置
wb.create_sheet("MySheet0", 0)
wb.create_sheet("MySheet1", 1)
wb.create_sheet("MySheet2", 2)
wb.create_sheet("MySheet3", 3)
wb.create_sheet("MySheet-1", -1)
wb.create_sheet("MySheet-2", -2)
wb.create_sheet("MySheet-3", -3)
wb.create_sheet("MySheet-last", None)
wb.save("create_workbook.xlsx")
打开已存在的工作簿
通过load_workbook()打开已存在的xlsx或xlsm文件
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
sheet_names = wb.sheetnames
for name in sheet_names:
    print(f"name:{name}")
获取所有的工作表名称
通过wb.sheetnames获取所有的工作表名称
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
sheet_names = wb.sheetnames
for name in sheet_names:
    print(f"name:{name}")
选中工作表
要操作一个工作表，首先要选中它，有以下几种方式选中工作表
wb.active
通过wb.active选中工作表
通过ws.title获取工作表的名称
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
ws: Worksheet = wb.active
print(f"类型是:{ws}")
print(f"title:{ws.title}")
wb[sheetname]
通过wb[工作表名称]获取工作表
通过ws.title获取工作表的名称
遍历工作簿获取工作表
可以通过遍历工作簿的方式，获取工作表
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
for sheet in wb:
    ws: Worksheet = sheet
    # print(f"类型是:{ws}")
    print(f"title:{ws.title}")
修改工作表
选中工作表后，就可以修改工作表的各种属性了
通过ws.title="xxx"修改工作表名称
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
ws: Worksheet = wb["MySheet0"]
print(f"原来的名称:{ws.title}")
ws.title = "UpdateSheet"
print(f"修改后的名称:{ws.title}")
wb.save("update.xlsx")
修改名称背景
通过ws.sheet_properties.tabColor = "xxxx"修改工作表名称背景颜色
xxxx使用RRGGBB颜色，可以来这里复制张贴HTML颜色代码表 (rapidtables.org)
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
filename = "insert_workbook.xlsx"
wb: Workbook = load_workbook(filename)
ws: Worksheet = wb["MySheet0"]
ws.sheet_properties.tabColor = "F08080"
wb.save("update.xlsx")
访问单个单元格
可以通过以下几种方式访问单个单元格
通过键的方式
通过ws['键名']的方式访问，获取到的对象类型是Cell
通过Cell.value获取到真正的值
from openpyxl import load_workbook
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
v: Cell = ws['A1']
print(v.value)
v: Cell = ws['A100']
print(v.value)
v: Cell = ws['AA1']
print(v.value)
v: Cell = ws['AB1000']
print(v.value)
通过.cell方法
通过ws.cell(row,column)的方式访问，获取到的对象类型是Cell
row和column都是大于0的整数，即最小是(1,1)
通过Cell.value获取到真正的值
from openpyxl import load_workbook
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
v: Cell = ws.cell(1, 1)
print(v.value)
v: Cell = ws.cell(14, 27)
print(v.value)
v: Cell = ws.cell(100, 28)
print(v.value)
访问大量单元格
可以通过以下几种方式访问大量单元格
访问某一列
通过ws["列名"]访问某一列
列名可以是A，B，C，AA，AB，...
返回是元祖，元祖内是Cell对象
通过Cell.value获取到真正的值
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws['A']
for cell in a:
    print(cell.value)
访问某几列
通过ws["列名:列名"]的形式可以访问多列，返回是以列组成的元祖，元素类型还是Cell
通过Cell.value获取到真正的值
传递的列名可以是字母，可以是数字
列名是字母的示例
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws['A:B']
for cell in a:
    print(cell)
a = ws['A:AB']
for cell in a:
    print(cell)
列名是数字的示例
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws['1:2']
for cell in a:
    print(cell)
a = ws['1:28']
for cell in a:
    print(cell)
访问多行多列
通过ws.iter_rows 的形式可以访问多行多列，返回的是Cell迭代器
返回的数据按行排序，即顺序是A1,B1,C1.....A2,B2,C2,...,A3,B3,C3
通过Cell.value获取到真正的值
示例：访问1-10行，A-AB列
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=28)
for one in a:
    print(one)
访问多列多行
和访问多行多列一样，只是排序方式为列优先
通过ws.iter_cols的形式可以访问多行多列，返回的是Cell迭代器
返回的数据按行排序，即顺序是A1,A2,A3,....,B1,B2,B3,...,C1,C2,C3,...
通过Cell.value获取到真正的值
示例：访问1-10行，A-AB列
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws.iter_cols(min_row=1, max_row=10, min_col=1, max_col=28)
for one in a:
    print(one)
访问某一行
在访问多行多列的方式中，把行固定，就是访问某一行
示例：访问第2行
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws.iter_rows(min_row=2, max_row=2, min_col=1, max_col=28)
for one in a:
    print(one)
访问所有的数据
通过ws.rows遍历所有的数据
遍历出来的数据以行优先排列，即A1,B1,C1.....A2,B2,C2,...,A3,B3,C3
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws.rows
for one in a:
    print(one)
仅访问工作表的值
访问单个单元格和大量单元格，返回的都是Cell对象，Cell对象就是单元格，可以通过Cell对象，获取单元格的属性，例如：颜色，背景，边框等。
如果只想访问单元格的值，而不关心单元格的其他属性，则可以这样获取
通过ws.values获取所有的值
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws.values
for one in a:
    print(one)
通过访问多行多列或访问多列多行时，传递参数values_only=True即可
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=28,values_only=True)
for one in a:
    print(one)
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
a = ws.iter_cols(min_row=1, max_row=10, min_col=1, max_col=28,values_only=True)
for one in a:
    print(one)
选中单元格cell后，可以修改它的数据
通过cell.value="xxx"修改单元格的值
通过ws.cell(row, cloumn, value)修改单元格的值
from openpyxl import load_workbook
from openpyxl.cell import Cell
from openpyxl.worksheet.worksheet import Worksheet
wb = load_workbook("src.xlsx")
ws: Worksheet = wb['Sheet']
cell: Cell = ws['A1']
print(f'A1原始的值{cell.value}')
cell.value = 100
print(f'A1修改后的值{cell.value}')
print(f"C2原始的值:{ws.cell(2, 3).value}")
ws.cell(2, 3, 200)
print(f"C2修改后的值:{ws.cell(2, 3).value}")
wb.save("update.xlsx")
保存到文件
通过wb.save()保存Workbook对象到文件
from openpyxl import Workbook
wb = Workbook()
wb.save("create_workbook.xlsx")
from openpyxl import load_workbook
wb = load_workbook("src.xlsx")
wb.save("update.xlsx")
指定属性 template=True 将工作表保存为模板
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
wb: Workbook = load_workbook("src.xlsx")
wb.template = True
wb.save("template.xltm")
指定属性 template=False将模板文件保存为普通文件
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
wb: Workbook = load_workbook("template.xltm")
wb.template = False
wb.save("new.xlsx")
需要保存为后缀名相同的文件
打开xlsm需要传递参数keep_vba=True
模板文件后缀名应该为xltm
添加一行数据
通过ws.append()添加一行数据
添加的数据在所有数据的最后面
可以理解为就是列表添加数据
from openpyxl import load_workbook
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
wb: Workbook = load_workbook("src.xlsx")
ws: Worksheet = wb.active
for i in range(5):
    ws.append(range(10))
wb.save("a.xlsx")
数字转换为字母
把数字转成字母，例如1就是A列，2就是B列，C就是C列,26就是Z列
from openpyxl.utils import get_column_letter
print(f"1对应的列是{get_column_letter(1)}")
print(f"25对应的列是{get_column_letter(25)}")
print(f"26对应的列是{get_column_letter(26)}")
print(f"30对应的列是{get_column_letter(30)}")
print(f"42对应的列是{get_column_letter(42)}")
通过ws.insert_rows(index, amount)插入空行
index表示插入的位置
amount表示插入的行数
示例：插入到第3行，插入2行空行
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb: Workbook = load_workbook("src.xlsx")
ws: Worksheet = wb.active
for one in ws.values:
    print(one)
ws.insert_rows(3, 2)
for one in ws.values:
    print(one)
通过ws.insert_cols(index, amount)插入空列
index表示插入的位置
amount表示插入的列数
示例：插入到第三列，插入2列空列
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb: Workbook = load_workbook("src.xlsx")
ws: Worksheet = wb.active
for one in ws.values:
    print(one)
ws.insert_cols(3, 2)
for one in ws.values:
    print(one)
wb.save("a.xlsx")
删除行和删除列
通过ws.delete_rows(index,amount)删除行
通过ws.delete_cols(index,amount)删除列
index表示要删除的行或列位置
amount表示要删除的行数或列数
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
wb: Workbook = load_workbook("src.xlsx")
ws: Worksheet = wb.active
for one in ws.values:
    print(one)
ws.delete_rows(2,2)
ws.delete_cols(2,2)
wb.save("a.xlsx")
教程 — openpyxl 3.0.7 文档 (openpyxl-chinese-docs.readthedocs.io)