python--操作excel表格，openpyxl模块

简介

openpyxl是一个非常强大的读写 Excel 2010 xlsx/xlsm/xltx/xltm 的 Python 库，简单易用，功能广泛，单元格格式/图片/表格/公式/筛选/批注/文件保护等等功能应有尽有

官方文档

https://openpyxl.readthedocs.io/en/stable/

openpyxl的3个对象

• workbook：相当于excel文件，我们创建和删除的excel文件
• sheet：相当于文件中的一张工作表，每个excel文件中都至少有一张sheet
• cell: 单元格，基本数据存储单元

下载

pip install openpyxl

操作

参照官网教程：https://openpyxl.readthedocs.io/en/stable/tutorial.html

获取文件内容

from openpyxl import load_workbook
wb = load_workbook(filename = 'C:\\Users\\51963\\Desktop\\1.xlsx')

print(wb.sheetnames)       #获取workbook中的sheets列表
print(wb["Sheet1"]["A2"].value)     #获取Sheet1表中的A2位置的值（.value获取值）
print(wb["Sheet1"].cell(column=1,row=2).value)     #获取Sheet1表中的A2位置的值（.value获取值）
print(wb["Sheet1"]["A1":"C3"])      #以两层元组输出Sheet1表（内层不同列A、B、C，外层不同行1、2、3）
for rows in wb["Sheet1"]["A1":"C3"]:   #for循环输出Sheet1表A1到C5的值
    for cells in rows:
        print(cells.value,end='\t')   #用print(end='')不换行
    print('')   #用于换行

----------------输出如下---------
['Sheet1', 'Sheet2', 'Sheet3']
1
1
((<Cell 'Sheet1'.A1>, <Cell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>), (<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>), (<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>))
1	2	3	
1	2	3	
1	2	3	

获取单个cell的方法

print(wb["Sheet1"]["A2"].value)     #获取Sheet1表中的A2位置的值（.value获取值）
print(wb["Sheet1"].cell(column=1,row=2).value)     #获取Sheet1表中的A2位置的值（.value获取值）

获取多个cell的方法

print(wb["Sheet1"]["B"])         #只获取B列；""双引号不能省略，因为"B"是字符串不是变量
print(wb["Sheet1"]["A":"C"])     #只获取ABC三列
print(wb["Sheet1"]["1":"3"])     #只获取123三行

cell_range = ws['A1':'C2']       #获取A1到C2区域

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):    #ws.iter_rows方法
    for cell in row:
        print(cell)

for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):    #ws.iter_cols方法
    for cell in col:
        print(cell)

获取sheet相关信息

print(ws.max_row)      #最大行
print(ws.max_column)   #最大列
print(ws.dimensions)   #已启用单元格范围
print(ws.encoding)     #编码类型
print(ws.sheet_format) #对象信息

变更文件内容

修改不save保存则只运行在内存，不会落地保存

工作表sheet的修改

from openpyxl import load_workbook
from openpyxl import Workbook

wb = load_workbook(filename = 'C:\\Users\\51963\\Desktop\\1.xlsx')
print(wb.sheetnames)

ws1 = wb.create_sheet("Mysheet1")   # 在最后位置插入（默认）
ws2 = wb.create_sheet("Mysheet2", 0)  # 在第一个位置插入
ws3 = wb.create_sheet("Mysheet3", -1)  # 在倒数第二个位置插入
print(wb.sheetnames)
ws3.title = "new title"    #worksheet.title可以给sheet重命名
print(wb.sheetnames)
new_ws2 = wb.copy_worksheet(ws2)    #copy sheet
print(wb.sheetnames)
wb.move_sheet(new_ws2,-2)   #移动sheet
print(wb.sheetnames)
wb.remove(new_ws2)          #删除sheet
print(wb.sheetnames)
del wb["Sheet1"]         #删除sheet
print(wb.sheetnames)

-----------------输出如下--------
['Sheet1', 'Sheet2', 'Sheet3']
['Mysheet2', 'Sheet1', 'Sheet2', 'Sheet3', 'Mysheet3', 'Mysheet1']
['Mysheet2', 'Sheet1', 'Sheet2', 'Sheet3', 'new title', 'Mysheet1']
['Mysheet2', 'Sheet1', 'Sheet2', 'Sheet3', 'new title', 'Mysheet1', 'Mysheet2 Copy']
['Mysheet2', 'Sheet1', 'Sheet2', 'Sheet3', 'Mysheet2 Copy', 'new title', 'Mysheet1']
['Mysheet2', 'Sheet1', 'Sheet2', 'Sheet3', 'new title', 'Mysheet1']
['Mysheet2', 'Sheet2', 'Sheet3', 'new title', 'Mysheet1']

cell值的修改

append追加

from openpyxl import load_workbook
from openpyxl import Workbook
wb = load_workbook(filename = 'C:\\Users\\51963\\Desktop\\1.xlsx')

ws = wb.active

for rows in ws["A1":"A3"]:   #for循环输出查看
    for cells in rows:
        print(cells.value,end='\t')
    print('')

print(ws.max_row)        #输出表最大行，因为前面for循环查到3行，所以最大行为3

ws["A1"] = 'hello word'    #修改A1值为helloword
print(ws['A1'].value)      #输出A1值

for rows in range(1,11):       #定义追加输入行数10行
    ws.append(range(1,11))     #定义每行数据为1-10

print(ws.max_row)      #查询最大行，append追加从最大行max_row下一行开始追加，所以最大行为3+10=13

for rows in ws["A1":"L14"]:   #for循环输出
    for cells in rows:
        print(cells.value,end='\t')
    print('')

--------------输出如下---------
None	
None	
None	
3
hello word
13
hello word	None	None	None	None	None	None	None	None	None	None	None	
None	None	None	None	None	None	None	None	None	None	None	None	
None	None	None	None	None	None	None	None	None	None	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
1	2	3	4	5	6	7	8	9	10	None	None	
None	None	None	None	None	None	None	None	None	None	None	None	

根据cell.value = 精确赋值

from openpyxl import load_workbook
from openpyxl import Workbook
wb = load_workbook(filename = 'C:\\Users\\51963\\Desktop\\1.xlsx')

ws = wb.active

for rows in ws["A1":"A3"]:   #for循环赋值
    for cells in rows:
        cells.value = 1


for rows in ws["A1":"B4"]:   #for循环输出查看
    for cells in rows:
        print(cells.value,end='\t')
    print('')
---------------输出如下--------
1	None	
1	None	
1	None	
None	None

根据worksheet.cell精确赋值

from openpyxl import load_workbook
from openpyxl import Workbook
wb = load_workbook(filename = 'C:\\Users\\51963\\Desktop\\1.xlsx')

ws = wb.active


for x in range(1,11):
     ws.cell(row=x,column=1,value=1)     #根据worksheet.cell精确赋值


for rows in ws.iter_rows(max_row=10,min_row=1,min_col=1,max_col=2):   #for循环输出
    for cells in rows:
        print(cells.value,end='\t')
    print('')
--------------输出如下----------
1	None	
1	None	
1	None	
1	None	
1	None	
1	None	
1	None	
1	None	
1	None	
1	None

cells块移动

>>> ws.move_range("D4:F10", rows=-1, cols=2)    #这会将区域中的单元格向上移动一行，向右移动两列;单元格将覆盖任何现有单元格;

>>> ws.move_range("G4:H10", rows=1, cols=1, translate=True)   #这会将范围内公式中的相对引用移动一行和一列

save保存文件

>>> wb = Workbook()
>>> wb.save('C:\\Users\\51963\\Desktop\\balances.xlsx')

#save操作将覆盖现有文件，而不会发出警告；所以建议save成一个新文档对原文档没有变动；