本文介绍以下使用python库实现对excel的读写。主要思路就是通过xlrd读取excel数据进行处理、通过openpyxl进行数据的写入。
（python有很多处理excel的库，这里只是记录其中一种方式。后面会系统学习pandas）

参考：https://www.jb51.net/article/165629.htm
https://www.jb51.net/article/205141.htm#_label0

xlrd的使用

  1. 安装

在pycharm中的控制台输入：pip install xlrd即可

  1. 操作流程
  1. 导入模块
1
import xlrd as xr
  1. 打开工作簿
1
2
# filename是文件的路径名称
workbook = xlrd.open_workbook(filename=r'C:\Users\Windows10\Desktop\xlsx文件.xlsx')
  1. 获取需要操作的sheet表格（有三种方法）
1
2
3
4
5
6
7
8

通过索引获取：--------------
# 获取第一个sheet表格
table = workbook.sheets()[0]

通过sheet名称获取：---------
# 通过sheet名称获取
table = workbook.sheet_by_name(sheet_name='Sheet1')

补充：获取excel所有sheel名称

1
2
# 获取工作薄中所有的sheet名称
names = workbook.sheet_names()
  1. 行列操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

# 获取sheet中有效行数
row = table.nrows
print(row)

# 获取sheet中有效列数
col = table.ncols
print(col)

# 返回该行的有效单元格长度
num = table.row_len(0)
print(num)

# rowx表示是获取第几行的数据
# start_col表示从索引为多少开始，end_colx表示从索引为多少结束，
# end_colx为None表示结束没有限制
# 获取指定行中的数据并以列表的形式返回
table_list = table.row_values(rowx=0, start_colx=0, end_colx=None)
print(table_list)

# colx表示是获取第几列的数据
# start_rowx表示从索引为多少开始，end_rowx表示从索引为多少结束，
# end_rowx为None表示结束没有限制
# 获取指定列中的数据并以列表的形式返回
table_list = table.col_values(colx=0, start_rowx=0, end_rowx=None)
print(table_list)

#返回由该列中所有的单元格对象组成的列表
print(table.row(0)) 
#返回由该行中所有的单元格对象组成的列表
print(table.row_slice(0)) 
#返回由该行中所有单元格的数据类型组成的列表
print(table.row_types(0, start_colx=0, end_colx=None)) 

#返回由该列中所有的单元格对象组成的列表
print(table.col(0, start_rowx=0, end_rowx=None)) 
#返回由该列中所有的单元格对象组成的列表
print(table.col_slice(0, start_rowx=0, end_rowx=None)) 
#返回由该列中所有单元格的数据类型组成的列表
print(table.col_types(0, start_rowx=0, end_rowx=None))

  1. 单元格操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 获取指定单元格内的值
value = table.cell_value(rowx=0, colx=1)
print(value)

# 获取单元格内的组成对象和数据
value = table.cell(rowx=0, colx=1)
print(value)

# 获取单元格的数据类型
# python读取excel中单元格的内容返回的有5种类型。ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error。即date的ctype=3，这时需要使用xlrd的xldate_as_tuple来处理为date格式，先判断表格的ctype=3时xldate才能开始操作。
value = table.cell_type(rowx=0, colx=1)
print(value)

openpyxl的使用

特点概述

openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，xls和xlsx之间转换容易 注意：如果文字编码是“gb2312” 读取后就会显示乱码，请先转成Unicode。

  1. 起始值：1，1

  2. openpyxl 支持直接横纵坐标访问，如A1,B2..

1
ws['A4'] = 4 #直接赋值

具体操作

先理清openpyxl的操作对象

  • workbook： 工作簿，一个excel文件包含多个sheet。
  • sheet：工作表，一个workbook有多个，表名识别，如“sheet1”,“sheet2”等。
  • cell： 单元格，存储数据对象
  1. 新建表
1
2
3
4
5
6
7
8
9
10
11
12
13
wb = Workbook(encoding='UTF-8')

# 使用_active_sheet_index属性, 默认会设置0，也就是第一个worksheet。
ws = wb.active

# 创建worksheets，通过 openpyxl.workbook.Workbook.create_sheet() 方法：
ws = wb.create_sheet("Mysheet") #插入到最后(default)
#或者
 ws = wb.create_sheet("Mysheet", 0) #插入到最开始的位置
 
# 创建的sheet的名称会自动创建，按照sheet，sheet1，sheet2自动增长，通过title属性可以修改其名称
ws.title = "New Title"
ws = wb.create_sheet(title="Pip")

注：也可以打开已有的excel文件

1
2
xfile = openpyxl.load_workbook('D:\\Tencent Files\\95239002\\聚类求和.xlsx')
sheet = xfile.get_sheet_by_name("总的数据")
  1. 单元格赋值
1
2
3
4
#设定单元格的值，三种方式
sheet.cell(row=2,column=5).value=99
sheet.cell(row=3,column=5,value=100)
ws['A4'] = 4 #write
  1. 逐行写
1
2
3
4
5
ws.append(iterable)
#添加一行到当前sheet的最底部（即逐行追加从第一行开始） iterable必须是list,tuple,dict,range,generator类型的。 1,如果是list,将list从头到尾顺序添加。 2，如果是dict,按照相应的键添加相应的键值。
ws.append([‘This is A1', ‘This is B1', ‘This is C1'])
ws.append({‘A' : ‘This is A1', ‘C' : ‘This is C1'})
ws.append({1 : ‘This is A1', 3 : ‘This is C1'})
  1. 读表操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#打开文件：
from openpyxl import load_workbook
excel=load_workbook('E:/test.xlsx')
#获取sheet：
table = excel.get_sheet_by_name('Sheet1')  #通过表名获取 
#获取行数和列数：
rows=table.max_row  #获取行数
cols=table.max_column  #获取列数
#获取单元格值：
Data=table.cell(row=row,column=col).value #获取表格内容，是从第一行第一列是从1开始的，注意不要丢掉 .value

#通过名字
  ws = wb["frequency"] 
  #等同于 ws2 = wb.get_sheet_by_name('frequency')
  #不知道名字用index
  sheet_names = wb.get_sheet_names()
  ws = wb.get_sheet_by_name(sheet_names[index])# index为0为第一张表 
#或者
  ws =wb.active
  # 等同于 ws = wb.get_active_sheet() #通过_active_sheet_index设定读取的表，默认0读第一个表
  #活动表表名
  wb.get_active_sheet().title
  
#逐行读
    ws.iter_rows(range_string=None, row_offset=0, column_offset=0): range-string(string)-单元格的范围：例如('A1:C4') row_offset-添加行 column_offset-添加列 
  # 返回一个生成器, 注意取值时要用value,例如：
  for row in ws.iter_rows('A1:C2'):
    for cell in row:
      print cell
  #读指定行、指定列:
  rows=ws.rows#row是可迭代的 
  columns=ws.columns#column是可迭代的
  #打印第n行数据 
  print rows[n]#不需要用.value 
  print columns[n]#不需要用.value

具体例子：聚类求和

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
import xlrd as xr
import openpyxl
xlsx = xr.open_workbook("D:\\Tencent Files\\95239002\\聚类求和.xlsx")
ret1 = xlsx.sheets()[0]
sheet1_col1 = ret1.col_values(0)
sheet1_col2 = ret1.col_values(1)
sheet1_col3 = ["result"]
i, j = 1, 2
k = 1
while True:
    if j<len(sheet1_col2) and sheet1_col2[i]==sheet1_col2[j]:
        k+=1
        j+=1
        continue
    if k==1:
        sheet1_col3.append(round(sheet1_col1[i], 6))
    else:
        sheet1_col3.append(round(sum(sheet1_col1[i:j]), 6))
        for m in range(k-1):
            sheet1_col3.append("same")
        k = 1
    i = j
    j = i+1
    if j>=len(sheet1_col2):
        sheet1_col3.append(round(sheet1_col1[i], 6))
        break
print(sheet1_col3)

xfile = openpyxl.load_workbook('D:\\Tencent Files\\95239002\\聚类求和.xlsx')
sheet = xfile.get_sheet_by_name("总的数据")
for i in range(1, len(sheet1_col3)+1):
    sheet.cell(i, 3, sheet1_col3[i-1])
xfile.save("聚类求和3.xlsx")