Python 操作Excel-openpyxl模块使用

时间：2025-03-25 07:20:53

openpyxl 的用法实例

1.1 Openpyxl 库的安装使用

openpyxl 模块是一个读写 Excel 2010 文档的 Python 库，如果要处理更早格式的 Excel 文 档，需要用到额外的库，openpyxl 是一个比较综合的工具，能够同时读取和修改 Excel 文档。 其他很多的与 Excel 相关的项目基本只支持读或者写 Excel 一种功能。新建、读取、保存工 作簿。

1.2 Excel 的新建、读取、保存

1.2.1 新建保存工作簿(覆盖创建)
新建：openpyxl.Workbook()，注意这个的 W 是大写的(本人吃过亏)，可以设置 write_only=True/False 的读写方式，默认是可写。 保存：('工作簿名.xlsx')

    

  1. 

    

    
from openpyxl import Workbook
    

    2. 

  2. 

    

    wb=Workbook() # 新建工作簿

    

    3. 

  3. 

    

    ('我的工作簿.xlsx') # 相对路径保存工作簿

    

    4.

每个 workbook 创建后，默认会存在一个 sheet。也可以自行创建新的 sheet。

1.2.2 读取保存工作簿

读取工作簿：
openpyxl.load_workbook('工作簿名.xlsx')，注意以下相关参数的设置。
read_only=False/True False 表示可以读、写，True 表示只能读、不能写。
guess_types=False/True False 表示转换数据，True 表示不能转换数据。
data_only=False/True False 表示序单元格的真实信息，True 表示只读取值。

    

  1. 

    

    
from openpyxl import load_workbook
    

    2. 

  2. 

    

    wb=load_workbook('成绩表-') # 读取 工作簿

    

    3. 

  3. 

    

    ('成绩表-') # 保 存工作簿

    

    4.

1.2.3 实例(批量建新工作簿)

    

  1. 

    

    
from openpyxl import Workbook 
    

    2. 

  2. 

    

    
for m in range(1,13): 
    

    3. 

  3. 

    

        wb = Workbook()  # 新建工作簿

    

    4. 

  4. 

    

        ('%d 月.xlsx'%m)# 保存工作簿

    

    5.
1.3 工作表对象的获取方法
1.3.1 工作表获取方式:

获取当前活动工作表的：

sheet =

以索引值方式获取工作表：

sheet= [2]

以工作表名获取： wb['工作表名']，注意，此表达方式为切片显示，所以没有成员提示。很少用

sheet = wb["Sheet-甲乙-甲乙"]

循环工作表：很好用，一般用sheetnames

wb =

获取所有工作表名：

wb4 =

获取指定工作表名

wb7 = [2].title()

修改工作表名称

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    wb = openpyxl.load_workbook('我的工作簿.xlsx')
    

    4. 

  4. 

    

    # wb[[1]].title = '南京'
    

    5. 

  5. 

    

    sh_name =   # 获取所有sheet

    

    6. 

  6. 

    

    sh = wb[sh_name[1]]
    

    7. 

  7. 

    

     = "南京"

    

    8. 

  8. 

    

    sheet = [1].title()
    

    9. 

  9. 

    

    ('我的工作簿.xlsx')
    

    10. 

  10. 

    

    
print(sheet)
    

    11.
1.3.2 实例(批量修改工作表名)
    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    wb = openpyxl.load_workbook('各年业绩表.xlsx')
    

    4. 

  4. 

    

    
for sh in :
    

    5. 

  5. 

    

         =  + '-芝华公司'

    

    6. 

  6. 

    

    ('各年业绩表(修改后).xlsx')
    

    7.
1.4 工作表的新建、复制、删除
1.4.1 新建工作表

可以在新建的工作簿中新建工作表(在新建工作簿时，会默认新建一个工作表)。也能在已经存在的工作簿中新建工作表。

新建工作表时的默认工作表名：

wb.create_sheet()  #默认工作表名为 Sheet1、 Sheet2、 Sheet3……
wb8 = wb.create_sheet('工作表名',指定位置)
1.4.2 复制工作表
wb.copy_worksheet([3])
1.4.3 删除工作表

(工作表)

([2])

1.5 关于工作表的实例应用

1.5.1 实例应用(批量新建12个月工作表)
    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    wb = ()  # 新建工作簿

    

    4. 

  4. 

    

    
for m in range(1, 13):
    

    5. 

  5. 

    

        wb.create_sheet('%d 月' % m)  # 新建月份工作表

    

    6. 

  6. 

    

    (wb['Sheet'])  # 删除 指定工作表

    

    7. 

  7. 

    

    ('2022 年计划表.xlsx')  # 保存工作簿。

    

    8.
1.5.2 实例应用(删除不符合条件的工作表)
    

  1. 

    

    
import openpyxl 
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('2021 年.xlsx')  # 读取 工作簿

    

    3. 

  3. 

    

    
for sh in wb: # 循环 工作簿中的工作表

    

    4. 

  4. 

    

        if ('-')[0]!='北京': # 判断 工作表是否不等于北京

    

    5. 

  5. 

    

              (sh) # 删除 工作表

    

    6. 

  6. 

    

    ('北京.xlsx') # 保 存工作簿

    

    7.
1.5.3 实例应用(批量复制工作表)
    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    wb = openpyxl.load_workbook('模板.xlsx')
    

    4. 

  4. 

    

    
for m in range(1, 13):
    

    5. 

  5. 

    

        wb.copy_worksheet(wb['测试']).title = '%d 月' % m
    

    6. 

  6. 

    

        (wb['测试'])
    

    7. 

  7. 

    

     
    

    8. 

  8. 

    

    ('2018 年各月表格.xlsx')
    

    9.

以上是处理工作表的方式

以下是处理单元格的方法

1.6 单元格信息获取
1.6.1 单元格数据获取

A1 表示法：工作表['A1']，R1C1 表示法：工作表.cell(行号,列号)

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

     
    

    3. 

  3. 

    

    wb = openpyxl.load_workbook("各年业绩表.xlsx")
    

    4. 

  4. 

    

    ws = [0]   #不能使sheetnames

    

    5. 

  5. 

    

    sh2 = ws["a1"].value    #必须是小写

    

    6. 

  6. 

    

    
print(sh2)
    

    7. 

  7. 

    

    sh = (1,1).value
    

    8. 

  8. 

    

    
print(sh)
    

    9.
1.6.2 实例应用(汇总各表各单元格数据)
    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb = openpyxl.load_workbook('各年业绩表.xlsx')
    

    3. 

  3. 

    

    
print(sum([s['b14'].value for s in wb]))
    

    4. 

  4. 

    

    
print(sum([(14,2).value for s in wb]))
    

    5.
1.7 单元格区域信息获取
1.7.1 单元格区域数据获取

1.工作表['起始单元格': '终止单元格']或工作表['起始单元格: 终止单元格']，此方法是按行读取的数据。

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb = openpyxl.load_workbook('')
    

    3. 

  3. 

    

    sh = wb["天峰集团"]
    

    4. 

  4. 

    

    s = sh["a1":"a2"]
    

    5. 

  5. 

    

    
print(s)
    

    6. 

  6. 

    

    ("")
    

    7.

2.工作表['起始行号': '结束行号']或者工作表['起始行号: 结束行号']，此方法是按行读取的数据。

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb = openpyxl.load_workbook('')
    

    3. 

  3. 

    

    sh = wb["天峰集团"]
    

    4. 

  4. 

    

    s = sh["1:3"]
    

    5. 

  5. 

    

    
print(s)
    

    6. 

  6. 

    

    ("")
    

    7.

3.工作表['起始列号': '结束列号']或者工作表['起始列号: 结束列号']，
此方法是按列读取的数据。

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb = openpyxl.load_workbook('')
    

    3. 

  3. 

    

    sh = wb["天峰集团"]
    

    4. 

  4. 

    

    s = sh["a:w"]
    

    5. 

  5. 

    

    
print(s)
    

    6. 

  6. 

    

    ("")
    

    7.

4.获取(按行)指定工作表所有已用数据：
list([索引值].values)

print([[ for c in row] for row in ws['a1:d3']])
1.7.2 实例应用

按行求和(方法 1)

    

  1. 

    

    import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=
    

    4. 

  4. 

    

    rngs=ws['a2:e71']
    

    5. 

  5. 

    

    print(['%s-%d'%(row[0].value,sum([c.value for c in row][1:])) for row in rngs])
    

    6.

按行求和(方法 )

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=
    

    4. 

  4. 

    

    
print(['%s-%d'%(row[0],sum(row[1:])) for row in list()[1:]])
    

    5.

按列统计平均值

    

  1. 

    

    import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=
    

    4. 

  4. 

    

     
    

    5. 

  5. 

    

    f=[sum(l)/len(l) for l in list(zip(*list()[1:]))[1:]]
    

    6. 

  6. 

    

    n=[ for c in ws['1']][1:]
    

    7. 

  7. 

    

    
print(['%s-%.2f'%c for c in list(zip(n,f))])
    

    8. 

  8. 

    

     
    

    9. 

  9. 

    

    
print(['%s-%.2f'%(l[0],sum(l[1:])/len(l[1:])) for l in list(zip(*list()))[1:]])
    

    10.
1.8 行列信息获取
1.8.1 行列信息获取

按行获取工作表使用区域数据：

按列获取工作表使用区域数据：
获取工作表中最小行号：worksheet.min_row
获取工作表中最小列号：worksheet.min_column
获取工作表中最大行号：worksheet.max_row
获取工作表中最大列号：worksheet.max_column
获取单元格的行号：
获取单元格的列号： iter
方法获取指定区域：
1.按行获取指定工作表单元格区域：worksheet.iter_rows(……)
2.按列获取指定工作表单元格区域：worksheet.iter_cols(……)
可以通过 min_row、min_col、max_col、max_row 这几个参数进行单元格区域的控制

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=
    

    4. 

  4. 

    

    minr=ws.min_row
    

    5. 

  5. 

    

    minc=ws.min_column
    

    6. 

  6. 

    

    maxr=ws.max_row
    

    7. 

  7. 

    

    maxc=ws.max_column
    

    8. 

  8. 

    

    rngs=ws.iter_rows(min_row=minr+1,min_col=minc+2,max_row=maxr-1,max_col=maxc-1)
    

    9. 

  9. 

    

    col=ws.iter_cols(min_row=minr+1,min_col=minc+1,max_row=maxr-1,max_col=minc+1)
    

    10. 

  10. 

    

    total=([sum([ for v in row]) for row in rngs])
    

    11. 

  11. 

    

    cp=([[ for v in row] for row in col][0])
    

    12. 

  12. 

    

    
print(list(zip(cp,total)))
    

    13.
1.8.2 实例应用
    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=[0]
    

    4. 

  4. 

    

    
for row in ws.iter_rows(min_row=36,min_col=2,max_col=4,max_row=40):
    

    5. 

  5. 

    

        print([ for c in row])
    

    6. 

    

  1. 

    

    import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=[0]
    

    4. 

  4. 

    

    for row in list()[1:]:
    

    5. 

  5. 

    

    
    l=[v.value for v in row]
    

    6. 

  6. 

    

        print(l[0],sum(l[1:]))
    

    7. 

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=[0]
    

    4. 

  4. 

    

    
for col in list()[1:]:
    

    5. 

  5. 

    

        l=[ for v in col]
    

    6. 

  6. 

    

        print(l[0],max(l[1:]))
    

    7. 

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=[0]
    

    4. 

  4. 

    

    total=[sum([ for c in row]) for row in ws.iter_rows(min_col=2,min_row=2)]
    

    5. 

  5. 

    

    name=[ for c in ws['a']][1:]
    

    6. 

  6. 

    

    
print(list(zip(name,total)))
    

    7. 

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=[0]
    

    4. 

  4. 

    

    total=[sum([ for c in col]) for col in ws.iter_cols(min_col=2,min_row=2)]
    

    5. 

  5. 

    

    name=[ for c in ws[1][1:]]
    

    6. 

  6. 

    

    
print(list(zip(name,total)))
    

    7.

单元格的写入

1.9.1 单元格与区域数据写入

A1 表示法：工作表['A1']=值，R1C1 表示法：工作表.cell(行号,列号,值)

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=[1]
    

    4. 

  4. 

    

    s = ws['a1']=123

    

    5. 

  5. 

    

    (2,3,'我是中国人')
    

    6. 

  6. 

    

    (3,3).value='我是四川人'

    

    7. 

  7. 

    

    
print(s)
    

    8. 

  8. 

    

    ('')
    

    9.
1.9.2 实例应用(九九乘法表)
    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=()
    

    3. 

  3. 

    

    ws=
    

    4. 

  4. 

    

    ='九九表'

    

    5. 

  5. 

    

    
for x in range(1,10):
    

    6. 

  6. 

    

        for y in range(1,x+1):
    

    7. 

  7. 

    

            (x,y,f'{y}×{x}={x*y}')
    

    8. 

  8. 

    

    ('九九表.xlsx')
    

    9.
1.10 批量写入数据
1.10.1 按行写入数据 (追加)

在最后一行写入数据：工作表.append(列表)

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=[0]
    

    4. 

  4. 

    

    ws.append({'a':'张三','b':56,'c':'fdgsfg'})
    

    5. 

  5. 

    

    ('')
    

    6.

1.10.2 实例应用(九九乘法表)

    

  1. 

    

    wb=openpyxl.load_workbook('')
    

    2. 

  2. 

    

    ws=
    

    3. 

  3. 

    

    # for r in [['%d*%d=%d'%(y,x,x*y) for y in range(1,x+1)] for x in range(1,10)]:
    

    4. 

  4. 

    

    #     (r)
    

    5. 

  5. 

    

    # ws.delete_rows(1)
    

    6. 

  6. 

    

    # ('')
    

    7. 

  7. 

    

    
for row in ws['a1:c6']:
    

    8. 

  8. 

    

        for c in row:
    

    9. 

  9. 

    

            c.value=1

    

    10. 

  10. 

    

    ('')
    

    11.
1.11 循环方式批量写入数据
1.11.1 循环获取单元格对象，再写入

之前我们可以通过组合单元格来获取或者写入数据，但还有一种方法，就是直接循环单 元格区域来写入数据。与循环读取的表示方式基本相同，只是多了一个赋值。

    

  1. 

    

    
import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=[0]
    

    4. 

  4. 

    

    
for row in ws['a1:g9']:
    

    5. 

  5. 

    

        print(row)
    

    6. 

  6. 

    

        for c in row:
    

    7. 

  7. 

    

            print()
    

    8. 

  8. 

    

    ('')
    

    9.
1.11.2 实例应用(大于等于 90 分为优秀)
    

  1. 

    

    import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=
    

    4. 

  4. 

    

    rngs=("2:2")
    

    5. 

  5. 

    

    
for row in rngs:
    

    6. 

  6. 

    

        for c in row:
    

    7. 

  7. 

    

            if c.value>=90:
    

    8. 

  8. 

    

                c.value=str(c.value)+'(优秀)'

    

    9. 

  9. 

    

    ('')
    

    10.
1.11.3 实例应用(每个人的总分大于等于300为优秀)

最后加一列写优秀

    

  1. 

    

    import openpyxl
    

    2. 

  2. 

    

    wb=openpyxl.load_workbook('')
    

    3. 

  3. 

    

    ws=
    

    4. 

  4. 

    

    rngs=ws.iter_rows(min_row=2,min_col=2)
    

    5. 

  5. 

    

    
for row in rngs:
    

    6. 

  6. 

    

            sm=sum([c.value for c in row][0:4])
    

    7. 

  7. 

    

            if sm>=300:
    

    8. 

  8. 

    

                row[-1].value='优秀' #切片用的多

    

    9. 

  9. 

    

    ('')
    

    10.

相关文章