openpyxl模块_python自动化办公系列 | python对阵excel—openpyxl模块

看了好多代码，目前为止都是散乱的分享，接下来将整理作为专题，进行系统化的一个分享整理，也是我自己学习的过程。第一个的系统化的分享专题——python办公自动化。代码后面的#所表示的是注释，对本行代码进行解释的内容，用#号开头是让程序理解这是注释不需要运行的意思。

本模板旨在分享和解读完整代码，只要你有安装配置好python环境，在pycharm里安装相应的第三方文件库，黏贴代码即可运行，我会尽量在代码后面都进行标注解读。我们以实用型为目的学习。编程类学习，从模仿中掌握突破。需要python教学视频和资料的在公众号菜单栏获取，有任何问题欢迎公众号后台联系我或加我微信。python安装教程

注：import后导入的模块需要单独安装，有些是自带的，但是要实现更复杂的功能一般是安装第三方模块。安装方法：

1.win+r，调出命令提示窗口，输入cmd再按回车键。
2.输入安装指令(电脑要联网，推荐使用国内镜像网站，安装更快，不然很慢)

Python pip安装第三方库的国内镜像

Windows系统下，一般情况下使用pip在DOS界面安装python第三方库时，经常会遇到超时的问题，导致第三方库无法顺利安装，此时就需要国内镜像源的帮助了。

使用方法如下：

例如：pip install -i https://pypi.tuna.tsinghua.edu.cn/simple xxx(这里的xxx是模块名)，这样就会从清华这边的镜像去安装pyspider库。

1

模块安装和基础操作，

前面有介绍过这个模块，但是整理的比较乱，所以再发一次。以下的操作是基本的数据拆分定位处理，对下一步的操作打下基础。

安装命令:

* pip install openpyxl

1)Excel 表格述语

这里需要大家仔细查看图中的每一项内容，知道什么是“行(row)、列(column)”？什 么是“格子(cell)”？什么是“sheet 表”？

提一下，一个Excel文件，就相当于一个“工作簿”(workbook)，一个“工作簿”里面可以包含多个“工作表(sheet)”

用下面这个表格进行实例操作

2)获取工作簿对象

import openpyxl#获取 工作簿对象workbook=openpyxl.load_workbook("DataSource\Economics.xlsx")#与xlrd 模块的区别#wokrbook=xlrd.open_workbook(""DataSource\Economics.xls)

3)获取所有工作表名

我们知道一个工作簿里面可以含有多个工作表，当我们获取“工作簿对象”后，可以接着来获取工作表对象，可以通过“索引”的方式获得，也可以通过“表名”的方式获得。

#获取工作簿 workbook的所有工作表shenames=workbook.get_sheet_names()print(shenames)  #['各省市', '测试表']#在xlrd模块中为 sheetnames=workbook.sheet_names()#使用上述语句会发出警告：DeprecationWarning: Call to deprecated function get_sheet_names (Use wb.sheetnames).#说明 get_sheet_names已经被弃用 可以改用 wb.sheetnames 方法shenames=workbook.sheetnamesprint(shenames)  #['各省市', '测试表']

‍

4)获取工作表对象

上一小节获取的工作表名，可以被应用在这一节中，用来获取工作表对象。

#获得工作簿的表名后，就可以获得表对象worksheet=workbook.get_sheet_by_name("各省市")print(worksheet)  ##使用上述语句同样弹出警告：DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).#改写成如下格式worksheet=workbook["各省市"]print(worksheet)  ##还可以通过如下写法获得表对象worksheet1=workbook[shenames[1]]print(worksheet1)  #

5)根据索引方式获取工作表对象

上一小节获取工作表对象的方式，实际上是通过“表名”来获取，我们可以通过更方便的方式，即通过“索引”方式获取工作表对象。 

#还可以通过索引方式获取表对象worksheet=workbook.worksheets[0]print(worksheet) ##也可以用如下方式#获取当前活跃的worksheet,默认就是第一个worksheetws = workbook.active

6)获取工作表的属性

得到工作表对象后，可以获取工作表的相应属性，包括“表名”、“行数”、“列数”

#经过上述操作，我们已经获得了第一个“表”的“表对象“，接下来可以对表对象进行操作name=worksheet.title  #获取表名print(name)  #各省市#在xlrd中为worksheet.name#获取该表相应的行数和列数rows=worksheet.max_rowcolumns=worksheet.max_columnprint(rows,columns)  #32 13#在xlrd中为 worksheet.nrows  worksheet.ncols

7)按行或列方式获取表中的数据

要想以行方式或者列方式，获取整个工作表的内容，我们需要使用到以下两个生成器：

sheet.rows，这是一个生成器，里面是每一行数据，每一行数据由一个元组类型包裹。

sheet.columns，同上，里面是每一列数据。

for row in worksheet.rows:    for cell in row:        print(cell.value,end=" ")    print()"""各省市 工资性收入 家庭经营纯收入 财产性收入 转移性收入 食品 衣着 居住 家庭设备及服务 ……北京市 5047.4 1957.1 678.8 592.2 1879.0 451.6 859.4 303.5 698.1 844.1 575.8 113.1 ……天津市 3247.9 2707.4 126.4 146.3 1212.6 265.3 664.4 122.4 441.3 315.6 263.2 56.1 …………"""for col in worksheet.columns:    for cell in col:        print(cell.value,end=" ")    print()'''各省市 北京市 天津市 河北省 山西省 内蒙古自治区 辽宁省 吉林省 黑龙江省 上海市 江苏省 浙江省 ……工资性收入 5047.4 3247.9 1514.7 1374.3 590.7 1499.5 605.1 654.9 6686.0 3104.8 3575.1 ……家庭经营纯收入 1957.1 2707.4 2039.6 1622.9 2406.2 2210.8 2556.7 2521.5 767.7 2271.4  …………'''

我们可以通过查看sheet.rows 里面的具体格式，来更好的理解代码

for row in worksheet.rows:    print(row)'''(, , , , ,……(, , , , , …………'''#可知，需要二次迭代for row in worksheet.rows:    for cell in row:        print(cell,end=" ")    print()'''…… …………'''#还需要cell.valuefor row in worksheet.rows:    for cell in row:        print(cell.value,end=" ")    print()

8)获取特定行或特定列的数据

上述方法可以迭代输出表的所有内容，但是如果要获取特定的行或列的内容呢？我们可以想到的是用“索引”的方式，但是sheet.rows是生成器类型，不能使用索引。所以我们将其转换为list之后再使用索引，例如用list(sheet.rows)[3]来获取第四行的tuple对象。

#输出特定的行for cell in list(worksheet.rows)[3]:  #获取第四行的数据    print(cell.value,end=" ")print()#河北省 1514.7 2039.6 107.7 139.8 915.5 167.9 531.7 115.8 285.7 265.4 166.3 47.0#输出特定的列for cell in list(worksheet.columns)[2]:  #获取第三列的数据    print(cell.value,end=" ")print()#家庭经营纯收入 1957.1 2707.4 2039.6 1622.9 2406.2 2210.8 2556.7 2521.5 767.7 2271.4 3084.3……#已经转换成list类型，自然是从0开始计数。for col in worksheet.columns:    for cell in col:        print(cell.value,end=" ")    print()'''各省市 北京市 天津市 河北省 山西省 内蒙古自治区 辽宁省 吉林省 黑龙江省 上海市 江苏省 浙江省 ……工资性收入 5047.4 3247.9 1514.7 1374.3 590.7 1499.5 605.1 654.9 6686.0 3104.8 3575.1 ……家庭经营纯收入 1957.1 2707.4 2039.6 1622.9 2406.2 2210.8 2556.7 2521.5 767.7 2271.4  …………'''

9)获取某一块的数据

有时候我们并不需要一整行或一整列内容，那么可以通过如下方式获取其中一小块的内容。

注意两种方式的区别，在第一种方式中，由于生成器被转换成了列表的形式，所以索引是从0开始计数的。

而第二种方式，行和列都是从1开始计数，这是和xlrd模块中最大的不同，在xlrd中行和列都是从0计数的，openpyxl之所这么做是为了和Excel表统一，因为在Excel表，就是从1开始计数。

for rows in list(worksheet.rows)[0:3]:    for cell in rows[0:3]:        print(cell.value,end=" ")    print()'''各省市 工资性收入 家庭经营纯收入 北京市 5047.4 1957.1 天津市 3247.9 2707.4 '''for i in range(1, 4):    for j in range(1, 4):        print(worksheet.cell(row=i, column=j).value,end=" ")    print()'''各省市 工资性收入 家庭经营纯收入 北京市 5047.4 1957.1 天津市 3247.9 2707.4 '''

‍

10)获取某一单元格的数据

#精确读取表格中的某一单元格content_A1= worksheet['A1'].valueprint(content_A1)content_A1=worksheet.cell(row=1,column=1).value#等同于 content_A1=worksheet.cell(1,1).valueprint(content_A1)#此处的行数和列数都是从1开始计数的，而在xlrd中是由0开始计数的

2

使用openpyxl模块对xlsx文件进行写操作

1)创建工作簿和获取工作表

同样的workbook=openpyxl.Workbook() 中“W”要大写。

# 导入xlwt模块import xlwt#创建一个Workbook对象，相当于创建了一个Excel文件book=xlwt.Workbook(encoding="utf-8",style_compression=0)#Workbook类初始化时有encoding和style_compression参数#encoding:设置字符编码，一般要这样设置：w = Workbook(encoding='utf-8')，就可以在excel中输出中文了。默认是ascii。#style_compression:表示是否压缩，不常用。0是不需要的意思

2) 创建工作表

创建完工作簿之后，可以在相应的工作簿中，创建工作表。

worksheet2 = workbook.create_sheet()   #默认插在工作簿末尾#worksheet2 = workbook.create_sheet(0)  #插入在工作簿的第一个位置worksheet2.title = "New Title"

3) 将数据写入工作表

#以下是我们要写入的数据Province=['北京市', '天津市', '河北省', '山西省', '内蒙古自治区', '辽宁省',          '吉林省', '黑龙江省', '上海市', '江苏省', '浙江省', '安徽省', '福建省',          '江西省', '山东省', '河南省', '湖北省', '湖南省', '广东省', '广西壮族自治区',          '海南省', '重庆市', '四川省', '贵州省', '云南省', '西藏自治区', '陕西省', '甘肃省',          '青海省', '宁夏回族自治区', '新疆维吾尔自治区']Income=['5047.4', '3247.9', '1514.7', '1374.3', '590.7', '1499.5', '605.1', '654.9',        '6686.0', '3104.8', '3575.1', '1184.1', '1855.5', '1441.3', '1671.5', '1022.7',        '1199.2', '1449.6', '2906.2', '972.3', '555.7', '1309.9', '1219.5', '715.5', '441.8',        '568.4', '848.3', '637.4', '653.3', '823.1', '254.1']Project=['各省市', '工资性收入', '家庭经营纯收入', '财产性收入', '转移性收入', '食品', '衣着',         '居住', '家庭设备及服务', '交通和通讯', '文教、娱乐用品及服务', '医疗保健', '其他商品及服务']#写入第一行数据，行号和列号都从1开始计数for i in range(len(Project)):    worksheet.cell(1, i+1,Project[i])#写入第一列数据，因为第一行已经有数据了，i+2for i in range(len(Province)):    worksheet.cell(i+2,1,Province[i])#写入第二列数据for i in range(len(Income)):    worksheet.cell(i+2,2,Income[i])

4) 保存工作簿

workbook.save(filename='DataSource\\myfile.xlsx')

结果如下

3

修改已经存在的工作簿(表)

1)插入一列数据

将第四节中最后保存的myfile.xlsx作为我们要修改的表格，我们计划在最前面插入一列“编号”，如下所示：

1. import openpyxlworkbook=openpyxl.load_workbook("DataSource\myfile.xlsx")worksheet=workbook.worksheets[0]#在第一列之前插入一列worksheet.insert_cols(1)  #for index,row in enumerate(worksheet.rows):    if index==0:        row[0].value="编号"  #每一行的一个row[0]就是第一列    else:        row[0].value=index#枚举出来是tuple类型，从0开始计数workbook.save(filename="DataSource\myfile.xlsx")

   执行出来的工作表test02如下所示

   

2)修改特定单元格

1. worksheet.cell(2,3,'0')worksheet["B2"]="Peking"

2)批量修改数据

批量修改数据就相当于写入，会自动覆盖。在上一节中已经有介绍，不再赘述。

还有sheet.append()方法，可以用来添加行

1. taiwan=[32,"台湾省"]worksheet.append(taiwan)

okok，今天的分享到此结束，欢迎各位三连。长按下方图片关注公众号。