使用python对excel表的操作（基础版~）|"朝闻道"知识分享大赛

[花墩墩]

这是我参加朝闻道知识分享大赛的第二篇文章

目录

前言  

基本操作  

openpyxl介绍  

获取当前活动表  

通过sheet名称获取表 

获取表格尺寸  

获取指定单元格的数据  

根据行列位置获取指定单元格内容  

获取指定区间的单元格数据  

 获取列的数据  

获取行的数据  

获取A、B两列的数据  

按行获取单元格内容  

按列获取单元格内容  

获取表内容的最大行数和最大列数   

将sheet表对应单元格内容进行修改赋值  

将列表内容写入对应sheet表内容后面  

 按行、列插入单元格,从idx开始,插入单元格数量   

按行、列删除单元格,从idx开始,插入单元格数量   

将(C9:D10)区域的东西插入到行列值范围,正整数为向下或向右、负整数为向左或向上  

创建新的sheet表  

给对应sheet表更改sheet名称  

将sheet表的内容复制到新表中  

删除sheet表  

更改sheet表名称

---

前言  

最近工作需要写一些简单的脚本，中间设计到Python对excel表的数据操作...卡了半天没写出来...特定写一篇博客恶补...

基本操作  

excel表里面的东西我就不复述了，相信大家都知道。
先对举例的excel表做个介绍，介绍如图所示~  
 

openpyxl介绍  

openpyxl是一个用于读写Excel文件（.xlsx）的Python库。它支持访问和修改Excel文件中的工作表、单元格、图表等元素。使用openpyxl库，你可以创建、编辑、保存和读取Excel文件，以及执行一系列与Excel文件相关的操作。  

 

获取当前活动表  

 

• 代码  

  

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)
    # 获取当前活动表
    sheet = workbook.active
    print(sheet)

 

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek2">

通过sheet名称获取表 

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">

获取表格尺寸  

 

• 代码  

  

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)

    # 获取表格尺寸
    print(geek2.dimensions)

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
A1:B5

获取指定单元格的数据  

 

• 代码  

  

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 获取指定单元格的数据
    cell1 = geek2['A1']
    cell2 = geek2['B1']
    print(cell1.value)
    print(cell2.value)

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
1
a

根据行列位置获取指定单元格内容  

 

• 代码  

     

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 根据行列位置获取指定单元格内容
    cell3 = geek2.cell(row=1, column=1)
    print(cell3.value)

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
1

 

获取指定区间的单元格数据  

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 获取指定区间的单元格数据
    cell4 = geek2['A1:A5']
    print(cell4)
    for i in cell4:
        for j in i:
            print(j.value)

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
((<Cell 'Geek2'.A1>,), (<Cell 'Geek2'.A2>,), (<Cell 'Geek2'.A3>,), (<Cell 'Geek2'.A4>,), (<Cell 'Geek2'.A5>,))
1
2
3
4
5

 获取列的数据  

 

• 代码  

    

# 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 获取列的数据
    cell5 = geek2['A']
    print(cell5)
    for i in cell5:
        print(i.value)

 

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
(<Cell 'Geek2'.A1>, <Cell 'Geek2'.A2>, <Cell 'Geek2'.A3>, <Cell 'Geek2'.A4>, <Cell 'Geek2'.A5>)
1
2
3
4
5

获取行的数据  

• 代码  

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 获取行的数据
    cell6 = geek2['1']
    print(cell6)
    for i in cell6:
        print(i.value)

 

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
(<Cell 'Geek2'.A1>, <Cell 'Geek2'.B1>)
1
a

 

获取A、B两列的数据  

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 获取A、B两列的数据
    cell7 = geek2['A:B']
    print(cell7)
    for i in cell7:
        for j in i:
            print(j.value)

 

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
((<Cell 'Geek2'.A1>, <Cell 'Geek2'.A2>, <Cell 'Geek2'.A3>, <Cell 'Geek2'.A4>, <Cell 'Geek2'.A5>), (<Cell 'Geek2'.B1>, <Cell 'Geek2'.B2>, <Cell 'Geek2'.B3>, <Cell 'Geek2'.B4>, <Cell 'Geek2'.B5>))
1
2
3
4
5
a
b
c
d
e

 

按行获取单元格内容  

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 按行获取单元格内容
    for i in geek2.iter_rows(min_row=1, max_row=3, min_col=1, max_col=2):
        for j in i:
            print(j.value)

 

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
1
a
2
b
3
c

按列获取单元格内容  

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 按列获取单元格内容
    for i in geek2.iter_cols(min_row=1, max_row=3, min_col=1, max_col=2):
        for j in i:
            print(j.value)

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
1
2
3
a
b
c

获取表内容的最大行数和最大列数   

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 获取表内容的最大行数
    rows = geek2.max_row
    
    # 获取表内容的最大列数
    columns = geek2.max_column
    print(rows)
    print(columns)

• 结果  

['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
5
2

将sheet表对应单元格内容进行修改赋值  

 

• 代码      

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 将sheet表对应单元格内容进行修改赋值
    geek2['A1'] = '数字'
    geek2['B1'] = '字母'
    workbook.save('./result/test1.xlsx')

• 结果  

 

将列表内容写入对应sheet表内容后面  

 

• 代码  

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 将列表内容写入对应sheet表内容后面
    data = [
        ['数字1', '字母1'],
        ['数字2', '字母2'],
        ['数字3', '字母3']
    ]

    for row in data:
        geek2.append(row)
    workbook.save('./result/test1.xlsx')

• 结果  

 

 按行、列插入单元格,从idx开始,插入单元格数量   

 

• 代码  

   

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 按行插入单元格,从idx开始,插入单元格数量
    geek2.insert_rows(idx=2, amount=2)
    
    # 按列插入单元格,从idx开始,插入单元格数量
    geek2.insert_cols(idx=1, amount=1)
    workbook.save('./result/test1.xlsx')

• 结果  

 

 

按行、列删除单元格,从idx开始,插入单元格数量   

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 和插入同理
    geek2.delete_rows(idx=2, amount=2)
    geek2.delete_cols(idx=1, amount=1)
    workbook.save('./result/test1.xlsx')

• 结果  

 

将(C9:D10)区域的东西插入到行列值范围,正整数为向下或向右、负整数为向左或向上  

 

• 代码      

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 将C9:D10区域的东西插入到行列值范围,正整数为向下或向右、负整数为向左或向上
    geek2.move_range('C9:D10', rows=0, cols=-2)
    workbook.save('./result/test1.xlsx')

 

• 结果  

  

  

创建新的sheet表  

 

• 代码  

     

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    #  创建新的sheet表
    workbook.create_sheet('geek3')
    print(workbook.sheetnames)
    workbook.save('./result/test1.xlsx')

 

给对应sheet表更改sheet名称  

 

• 代码  

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 给对应sheet表更改sheet名称
    geek2.title = 'ssss'
    print(workbook.sheetnames)
    workbook.save('./result/test1.xlsx')

 

将sheet表的内容复制到新表中  

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 将sheet表的内容复制到新表中
    workbook.copy_worksheet(geek2)
    workbook.save('./result/copy.xlsx')

 

• 结果  

 

删除sheet表  

 

• 代码  

    

    # 加载使用的excel表
    workbook = openpyxl.load_workbook('./result/test1.xlsx')
    # 获取所有sheet名称
    print(workbook.sheetnames)

    # 通过sheet名称获取表
    geek1 = workbook['Geek1']
    print(geek1)
    geek2 = workbook['Geek2']
    print(geek2)
    
    # 删除sheet表
    workbook = openpyxl.load_workbook('./result/copy.xlsx')
    print(workbook.sheetnames)
    geek1 = workbook['Geek1']
    print(geek1)
    workbook.remove(geek1)
    workbook.save('./result/copy.xlsx')

更改sheet表名称

 

• 代码  

  

    # 更改sheet表名称  
    workbook = openpyxl.Workbook()
    test2 = workbook.active

    test2.title = "test2测试"
    workbook.save('./result/test2.xlsx')