学习分享

使用python对excel表的操作（基础版~）

前言

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

基本操作

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

openpyxl 介绍

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

获取当前活动表

  • 代码
1
2
3
4
5
6
7
# 加载使用的excel表
   workbook = openpyxl.load_workbook('./result/test1.xlsx')
   # 获取所有sheet名称
   print(workbook.sheetnames)
   # 获取当前活动表
   sheet = workbook.active
   print(sheet)
  • 结果
1
2
['Geek1', 'Geek2']
<Worksheet "Geek2">

通过 sheet 名称获取表

  • 代码
1
2
3
4
5
6
7
8
9
10
# 加载使用的excel表
   workbook = openpyxl.load_workbook('./result/test1.xlsx')
   # 获取所有sheet名称
   print(workbook.sheetnames)

   # 通过sheet名称获取表
   geek1 = workbook['Geek1']
   print(geek1)
   geek2 = workbook['Geek2']
   print(geek2)
  • 结果
1
2
3
['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">

获取表格尺寸

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
# 加载使用的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)
  • 结果
1
2
3
4
['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
A1:B5

获取指定单元格的数据

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 加载使用的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)
  • 结果
1
2
3
4
5
['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
1
a

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

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 加载使用的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)
  • 结果
1
2
3
4
['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
1

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

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 加载使用的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)
  • 结果
1
2
3
4
5
6
7
8
9
['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

获取列的数据

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 加载使用的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)
  • 结果
1
2
3
4
5
6
7
8
9
['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

获取行的数据

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 加载使用的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)
  • 结果
1
2
3
4
5
6
['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
(<Cell 'Geek2'.A1>, <Cell 'Geek2'.B1>)
1
a

获取 A、B 两列的数据

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 加载使用的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)
  • 结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
['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

按行获取单元格内容

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 加载使用的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)
  • 结果
1
2
3
4
5
6
7
8
9
['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
1
a
2
b
3
c

按列获取单元格内容

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 加载使用的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)
  • 结果
1
2
3
4
5
6
7
8
9
['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
1
2
3
a
b
c

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

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 加载使用的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)
  • 结果
1
2
3
4
5
['Geek1', 'Geek2']
<Worksheet "Geek1">
<Worksheet "Geek2">
5
2

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

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 加载使用的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 表内容后面

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 加载使用的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 开始，插入单元格数量

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 加载使用的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 开始，插入单元格数量

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 加载使用的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) 区域的东西插入到行列值范围，正整数为向下或向右、负整数为向左或向上

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 加载使用的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 表

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 加载使用的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 名称

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 加载使用的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 表的内容复制到新表中

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 加载使用的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 表

  • 代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 加载使用的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 表名称

  • 代码
1
2
3
4
5
6
# 更改sheet表名称  
   workbook = openpyxl.Workbook()
   test2 = workbook.active

   test2.title = "test2测试"
   workbook.save('./result/test2.xlsx')
访问量次 | 访客人 |