轻松学会openpyxl库，Python处理Excel有如神助-阿里云开发者社区

1.Python处理Excel的意义

Excel是Windows环境下流行的、强大的电子表格应用。无论是在工作中还是学习中。我们几乎都在不间断的使用Excel来记录或处理一些数据。例如：可能有一个无聊的任务，需要从一个电子表格拷贝数据，粘贴到另一表格。

或者可能需要从几千上万行中挑选几行，根据各种条件稍作修改。或者需要查看几百分部门预算电子表格，寻找其中指定内容。正是这种无聊无脑的电子表格任务，如果让人工来完成，则无论是时间成本或者精力成本都不是一件好事情，我们可以通过python来完成

python处理Excel方式：

openpyxl

环境安装：

这是官网，显示目前最新版本

pip install openpyxl

#如果报错，需要第三方支持：  
pip install lxml
pip install pillow

#查看openpyxl版本
import openpyxl
print(openpyxl.__version__)

2.Excel表格中的一些基本定义

首先，让我们来看一些基本定义：

工作簿：一个 Excel 电子表格文档称为一个工作簿，一个工作簿保存在扩展名为.xlsx 的文件中

sheet表：每个工作簿可以包含多个表(也称为工作表)

活动表：用户当前查看的表(或关闭 Excel 前最后查看的表)，称为活动表 active

单元格：每个表都有一些列(地址是从 A 开始的字母)和一些行(地址是从 1 开始的数字)。在特定行和列的方格称为单元格。每个单元格都包含一个数字或文本值。

3.读取Excel表格

用openpyxl模块打开Excel表格。
import openpyxl
import os

# print(openpyxl.__version__)

os.chdir(r'C:\Users\dell\Desktop')   
#读取excel表，返回一个workbook对象


wb = openpyxl.load_workbook("服务器磁盘分区优化.xlsx")

（1）- 获取工作簿的sheet表的名称，有多个sheet，以列表方式存储

print(wb.sheetnames)

我们对照下表

（2）- 获取指定的sheet对象，这种方式只有在openpyxl3.0以上版本才能使用

print(wb["Sheet1"])

（3）- 获取活动表

（4）- 从表中取得单元格

- 有了 Worksheet 对象后，就可以按名字访问 Cell 对象
    - 属性：
        - value:cell中存储的值
        - row：行索引
        - column：列索引
        - coordinate：坐标

cell = sheet['A4']    #创建一个cell对象
print(cell.value)     #拿到cell的值
print(cell.row)       #拿到cell所在行
print(cell.column)    #拿到cell所在列
print(cell.coordinate)#拿到cell所在坐标

用字母来指定列，这在程序中可能有点奇怪，特别是在 Z 列之后，列开时使用两个字母:AA、AB、AC 等。

作为替代，在调用表的 cell()方法时，可以传入整数作为 row 和 column 关键字参数，也可以得到一个单元格。第一行或第一列的整数是 1，不是 0。

#获取指定行和列的值

print(sheet.cell(row=1,column=2).value)

（5）- 从工作表中取得行和列

- 可以将 Worksheet 对象进行切片操作，从而取得电子表格中一行、一列或一个矩形区域中的所有 Cell 对象。

逐行拿到数据:

for cell_row in sheet['A2':'E6']:
    for cell in cell_row:
        print(cell.coordinate,cell.value)

要访问特定行或列的单元格的值，也可以利用 Worksheet 对象的 rows 和 columns属性。
sheet.columns是获取所有的列，得到的是生成器，需要用list转换一下，里面每个元祖是一列

#获取所有的行，每个元祖是一行

print(list(sheet.rows))

print(list(sheet.columns)[0])  #获取第一列的单元格对象

获取每个单元格的值

for cell in list(sheet.columns)[0]:
    print(cell.value)

获取工作表中的最大行和最大列的数量

max_row
max_column

print(sheet.max_row,sheet.max_column)

（6）- 案例

项目：2010 年美国人口普查数据自动化处理

在这个项目中，你要编写一个脚本，从人口普查电子表格文件中读取数据，并在几秒钟内计算出每个县的统计值（可以根据县的名称快速计算出县的总人口和普查区的数量）。

下面是程序要做的事:

从 Excel 电子表格中读取数据。

计算每个县中普查区的数目。

计算每个县的总人口。打印结果。

这意味着代码需要完成下列任务:

用 openpyxl 模块打开 Excel 文档并读取单元格。

计算所有普查区和人口数据，将它保存到一个数据结构中。

利用 pprint 模块，将该数据结构写入一个扩展名为.py 的文本文件。

数据说明：

censuspopdata.xlsx 电子表格中只有一张表，名为’Population by Census Tract’。

每一行都保存了一个普查区的数据。

列分别是普查区的编号(A)，州的简称(B)，县的名称©，普查区的人口(D)。

注意：一个县会设定多个普查区，D列表示县中所有普查区对应每一个普查区的人口数量

print()和pprint()都是python的打印模块，功能基本一样，唯一的区别就是pprint()模块打印出来的数据结构更加完整，每行为一个数据结构，更加方便阅读打印输出结果。

特别是对于特别长的数据打印，print()输出结果都在一行，不方便查看，而pprint()采用分行打印输出，所以对于数据结构比较复杂、数据长度较长的数据，适合采用pprint()打印方式。当然，一般情况多数采用print()。

我们可以将数据存放到一个大的字典

countyData结构：

我们先看下原始数据，7万多行

#案例：美国人口普查

import openpyxl, pprint
print('Opening workbook...')
#读取文件
wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\censuspopdata.xlsx')
#openpyxl3.0版本操作
#拿到表名, wb.get_sheet_names()这个方法已经被废弃了，现在使用wb.sheetnames
print(wb.sheetnames)
sheet = wb['Population by Census Tract']

countyData = {}
print('Reading rows...')

#openpyxl3.0版本操作
#从第二行开始拿数据，取到最大行
for row in range(2, sheet.max_row + 1):
    #str(row)是拿到列
    state = sheet['B' + str(row)].value
    county = sheet['C' + str(row)].value
    pop = sheet['D' + str(row)].value

    #先设置字典的默认值
    countyData.setdefault(state, {})  #{state:{}}
    countyData[state].setdefault(county, {'tracts': 0, 'pop': 0}) #{state:{county:{'tracts': 0, 'pop': 0}}}
    #只要州县的名字不变，就一直加，变了就从零开始
    countyData[state][county]['tracts'] += 1
    countyData[state][county]['pop'] += int(pop)
print('Writing results...')
resultFile = open('census2010.py', 'w')
#使用 pprint.pformat()函数，将变量字典的值作为一个巨大的字符串， 写入文件 census2010.py
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')

将countyData输出到文本文件 census2010.py，你就通过 Python 程序生成了一个 Python 程序!这样做的好处是现在可以导入 census2010.py，就像任何其他 Python 模块一样

import census2010
#查看AK州Anchorage县的人口普查数据
census2010.allData['AK']['Anchorage']

4.写入Excel文档

OpenPyXL 也提供了一些方法写入数据，这意味着你的程序可以创建和编辑电子表格文件。利用Python创建一个包含几千行数据的电子表格是非常简单的。

（1）- 调用 openpyxl.Workbook()函数，创建一个新的空 Workbook 对象

（2）- 获取工作表名称

print(wb.sheetnames)

（3）- 给工作表设置名称

sheet.title = '跟进记录表'

（4）- 保存工作表

#保存工作簿
wb.save(r'C:\Users\dell\Desktop\exceltest\第一个工作表.xlsx')

看下保存的表

5.创建和删除工作表

- 利用 create_sheet() and del语法，可以在工作簿中添加或删除工作表
    - 在工作簿中添加或删除工作表之后，记得调用 save()方法来保存变更。

（1）- 创建sheet

先打开工作簿

wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\第一个工作表.xlsx')
wb.create_sheet(title="销售记录")
#创建指定位置的工作表，index下标，从0开始，表示sheet的位置
wb.create_sheet(index=1,title="养殖技术")

#查看下所有工作表
print(wb.sheetnames)

（2）- 删除工作表

del wb["养殖技术"]

（3）- 将值写入单元格

- 将值写入单元格，很像将值写入字典中的键
- 如果你有单元格坐标的字符串，可以像字典的键一样，将它用于 Worksheet 对象，指定要写入的单元格。

sheet = wb['销售记录']
sheet['A1'] = '景浩'
sheet['B2'] = '你好'

#操作完记得保存工作簿
#执行保存记得关闭excel软件，不然错没权限
wb.save(r'C:\Users\dell\Desktop\exceltest\第一个工作表.xlsx')

（4）- 项目:更新一个电子表格

这个项目需要编写一个程序，更新产品销售电子表格中的单元格。程序将遍历这个电子表格，找到特定类型的产品，并更新它们的价格

数据说明：

每一行代表一次单独的销售。列分别是销售产品的类型(A)、产品每磅的价格(B)、销售的磅数©，以及这次销售的总收入（D）。

TOTAL列已经设置为 Excel公式，将每磅的成本乘以销售的磅数，并将结果取整到分。有了这个公式，如果列 B 或 C 发生变化，TOTAL 列中的单元格将自动更新。

我们先看下表结构

现在假设 Garlic、Celery 和 Lemon 的价格输入的不正确。

这让你面对一项无聊的任务:遍历这个电子表格中的几万行，更新所有 garlic、celery 和 lemon 行中每磅的价格。

你不能简单地对价格查找替换，因为可能有其他的产品价格一样，你不希望错误地“更正”。

对于几万行数据，手工操作可能要几小时。但你可以编写程序，几秒钟内完成这个任务。

你的程序做下面的事情:

循环遍历所有行。

如果该行是 Garlic、Celery 或 Lemon，更新价格。

这意味着代码需要做下面的事情:

打开电子表格文件。

针对每一行，检查列 A 的值是不是 Celery、Garlic 或 Lemon。如果是，更新列 B 中的价格。将该电子表格保存为一个新文件(这样就不会丢失原来的电子表格，以防万一)。

需要更新的价格如下:

Celery 1.19

Garlic 3.07

Lemon 1.27

利用更新信息建立数据结构

这样硬编码产品和更新的价格有点不优雅。如果你需要用不同的价格，或针对不同的产品，再次更新这个电子表格，就必须修改很多代码。每次修改代码，都有引入缺陷的风险。

更灵活的解决方案，是将正确的价格信息保存在字典中，在编写代码时，利用这个数据结构,如果需要再次更新这个电子表格，只需要更新 PRICE_UPDATES字典，不用修改其他代码。

#打开工作簿
wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\produceSales.xlsx')

#查看工作表
print(wb.sheetnames)
#指定工作表
sheet = wb["Sheet"]
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27
                 }

#循环所有行
for rowNum in range(2,sheet.max_row+1):
    productName = sheet.cell(row=rowNum,column=1).value
    if productName in PRICE_UPDATES:
        sheet.cell(row=rowNum,column=2).value = PRICE_UPDATES[productName]
        #也可以这样写
        # sheet["B"+str(rowNum)].value = PRICE_UPDATES[productName]


#修改完保存，可以另存为其他文件名，防止将原数据覆盖
wb.save(r'C:\Users\dell\Desktop\exceltest\UpdatedproduceSales.xlsx')

6.设置单元格风格样式

为了定义单元格的字体风格，需要从 openpyxl.styles 模块导入 Font和PatternFill工具

（1）- 设置字体样式

#设置单元格风格样式
from openpyxl.styles import Font
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A3'] = '字体'
sheet['A3'].font = Font(name='楷体')
wb.save(r'C:\Users\dell\Desktop\exceltest\Styles.xlsx')

我们看下Font方法的参数

name指的是字体名称

sz是字体大小

color是字体颜色

italic=True 设置斜体

underline = “sigle” 单下划线

b =True 设置粗体

（2）- 设置字体颜色

设置字体颜色，值是十六进制

#设置字体颜色
from openpyxl.styles import Font
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A4'] = '我是有颜色的字体'
sheet['A4'].font = Font(color='8470FF')#RGB值：需要填写16进制
wb.save(r'C:\Users\dell\Desktop\exceltest\Styles.xlsx')

（3）- 设置cell的填充色

#设置cell的填充色
from openpyxl.styles import Font,PatternFill
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A4'] = '背景填充色'
sheet['A4'].fill = PatternFill(patternType='solid',fgColor='8470FF')#RGB值：需要填写16进制
wb.save(r'C:\Users\dell\Desktop\exceltest\Styles.xlsx')

参数说明:

patternType或fill_type：填充图案类型。

fgColor或start_color：图案前景色。当类型为solid时，前景色生效，背景色不生效

bgColor或end_color：图案背景色。

（4）- 设置cell的边框样式

- 需要使用Side，Boder类
- 步骤：
    - 1.创建Side对象，通过构造方法参数style和color设置其边的样式和颜色
    - 2.设置cell的border属性，给其赋值为Border对象，且设置其上下左右边框为哪一个Side

from openpyxl.styles import Side,Border
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['F4'] = '效果1'
sheet['F5'] = '效果2'
#常见的style值：dashDot，dashDotDot，dashed，dotted，sigle......
s1 = Side(style='thin',color='8470FF')
s2 = Side(style='double',color='ff0000')
sheet['F4'].border = Border(top=s1)
sheet['F5'].border = Border(top=s2,bottom=s1,left=s2,right=s1)

wb.save(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')

（5）- 单元格的对齐方式

- 使用Alignment类

from openpyxl.styles import Alignment
import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')
sheet = wb['Sheet']
sheet["C1"] = "我是谁"
sheet["C2"] = "我是谁"
sheet["C3"] = "我是谁"
sheet["C4"] = "我是谁"   #这个默认，水平方向是靠左，垂直方向是底部
#horizontal--水平方向     vertical--垂直方向
c1 = sheet['C1'].alignment = Alignment(horizontal='right',vertical='center')   #水平方向靠右，垂直方向居中
c2 = sheet['C2'].alignment = Alignment(vertical='center',horizontal='center') #水平方向居中，垂直方向居中
c3 = sheet['C3'].alignment = Alignment(vertical='top')       #垂直方向顶部，水平方向默认居左

wb.save(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')

7.数据筛选

import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')
sheet = wb['数据筛选']
#创建筛选器对象：auto_filter
#ref：使得筛选器对象引用指定的区域
sheet.auto_filter.ref = 'A1:D7'
wb.save(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')

相当于我们操作office，点击了数据筛选

（1）- 设置筛选条件

import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')
sheet = wb['数据筛选']
#创建筛选器对象：auto_filter
#ref：使得筛选器对象引用指定的区域
sheet.auto_filter.ref = 'A1:D7'
#add_filter_column参数：参数1表示对指定区域那一列进行设置筛选条件，列号是从0开始。参数2：筛选条件内容
sheet.auto_filter.add_filter_column(1,['北京','深圳'])
wb.save(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')

这样执行后，虽然数据按条件筛选了，但是我们打开excel，看到的还是全部数据，此时需要我们手动点击一下筛选的其中一个，取消筛选，然后在点上，保存，才能显示出我们的筛选数据

我们看到数据筛选对了

需要我们手动点击其中一个，取消筛选，然后在勾上，即可实现我们想要的筛选

（2）- 排序

没啥用。不生效

import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')
sheet = wb['数据筛选']
sheet.auto_filter.ref = 'A1:D7'
#参数1：排序列。参数2：升降序
sheet.auto_filter.add_sort_condition(ref='D2:D7',descending=True)
wb.save(r'C:\Users\dell\Desktop\exceltest\cellBorder.xlsx')

执行完，把筛选器加上了，但是升降序没显示出来

#查看排序后的数据

for rowNum in range(2,sheet.max_row+1):
    # print(sheet["D"+str(rowNum)].value)
    #这种方式，行或列编号都是从1开始的，而且row和column的值都是数字
    print(sheet.cell(row=rowNum,column=4).value)

通过生成的表格查询也没生效

8.公式

利用 openpyxl 模块，用编程的方式在单元格中添加公式，就像添加普通的值一样。例如:

sheet[‘B9’] = ‘=SUM(B1:B8)’

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save(r'C:\Users\dell\Desktop\exceltest\sum.xlsx')

（1）- 读取公式结果

import openpyxl
#设置为只读模式，防止被修改
wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\sum.xlsx',read_only=True)
sheet = wb.active
#注意：如果返回的是None，则打开excel工作簿，将内容手动保存下即可，不方便，但是没有办法
print(sheet['A3'].value)

9.调整行和列

在 Excel 中，调整行和列的大小非常容易，只要点击并拖动行的边缘，或列的头部。
但如果你需要根据单元格的内容来设置行或列的大小，或者希望设置大量电子表格文件中的行列大小，编写 Python 程序来做就要快得多。
设置行高和列宽

Worksheet 对象有 row_dimensions 和 column_dimensions 属性，控制行高和列宽。

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 'Tall row'
sheet['B2'] = 'Wide column'
wb.save(r'C:\Users\dell\Desktop\exceltest\dimensions.xlsx')

默认生成的是这样的，会挤占别的行或列

#查看下dimensions

print(sheet.row_dimensions)
print(sheet.column_dimensions)

#设置行高，第二行行高
sheet.row_dimensions[2].height = 50   #50表示调整的单位

#设置列宽，A列
sheet.column_dimensions['A'].width = 50

wb.save(r'C:\Users\dell\Desktop\exceltest\dimensions.xlsx')

注意：

行的高度可以设置为 0 到 409 之间的整数或浮点值。这个值表示高度的点数。

一点等于 1/72 英寸。默认的行高是 12.75。

列宽可以设置为 0 到 255 之间的整数或浮点数。

默认的列宽是 8.43 个字符。列宽为零或行高为零，将使单元格隐藏。

10.合并和拆分单元格

利用 merge_cells()工作表方法，可以将一个矩形区域中的单元格合并为一个单元格。
要拆分单元格，就调用 unmerge_cells()工作表方法。

（1）- 合并单元格

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet.merge_cells('A1:D7') #合并  放置要合并的区域
sheet['A1'] = 'hello bobo'
wb.save(r'C:\Users\dell\Desktop\exceltest\merged.xlsx')

设置居中

#合并单元格

import openpyxl
from openpyxl.styles import Alignment

wb = openpyxl.Workbook()
sheet = wb.active
sheet.merge_cells('A1:D7') #合并
sheet['A1'] = 'hello bobo'
#设置居中
sheet["A1"].alignment = Alignment(vertical='center',horizontal='center')
wb.save(r'C:\Users\dell\Desktop\exceltest\merged.xlsx')

（2）- 拆分单元格

sheet.unmerge_cells('A1:D7')

11.冻结窗格

对于太大而不能一屏显示的电子表格，“冻结”顶部的几行或最左边的几列，是很有帮助的。

在 OpenPyXL 中，每个 Worksheet 对象都有一个 freeze_panes 属性，可以设置为一个 Cell 对象或一个单元格坐标的字符串。

请注意，单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结。

要解冻所有的单元格，就将 freeze_panes 设置为 None 或’A1’

#冻结

import openpyxl
wb = openpyxl.load_workbook(r'C:\Users\dell\Desktop\exceltest\produceSales.xlsx')
sheet = wb.active
#冻结第二行，冻结的数所在行的上面所有行，以及所在列的左边所有列
sheet.freeze_panes = "A3"
wb.save(r'C:\Users\dell\Desktop\exceltest\produceSales.xlsx')

12.绘制图表

（1）- openpyxl 支持利用工作表中单元格的数据，创建条形图、折线图、散点图和饼图。

要创建图表，需要做下列事情：

创建一个Reference对象，表示作用在图表中的数据区域
创建图表对象
往图表对象中添加数据
将图表添加到指定sheet中

import openpyxl
wb = openpyxl.load_workbook(filename=r'C:\Users\dell\Desktop\exceltest\sampleChart.xlsx')
sheet = wb.active

#1.创建一个Reference对象，表示作用在图表中的数据区域
values = openpyxl.chart.Reference(sheet,min_row=1,min_col=1,max_row=10,max_col=5)
#2.创建图表对象
chart = openpyxl.chart.BarChart()
chart.title = '我是柱状图'
chart.x_axis.title = '时间'
chart.y_axis.title = '销量'

#3.往图表对象中添加数据
chart.add_data(values)
#4.将图表添加到指定sheet中
sheet.add_chart(chart,'G1')   #将图标放在什么位置，指定列就行
wb.save(r'C:\Users\dell\Desktop\exceltest\sampleChart.xlsx')

我们可以调用 openpyxl.charts.BarChart()，创建一个条形图。也可以调用openpyxl.charts.LineChart()、openpyxl.charts.ScatterChart()和 openpyxl.charts.PieChart()，创建折线图、散点图和饼图。

（2）- 项目

假设公司积累了从2020-2030期间的北京总部和全国三个分公司所有产品的营收数据，现在需要对数据进行图例绘制，以便更好的展示整个公司的营收状况。

批量对这多个表数据进行绘制图表，每个工作簿有背景，广州，厦门等多个工作表

import openpyxl
from openpyxl.chart import Reference, BarChart
import os

for file_name in os.listdir(r'E:\baiduwangpan\Excel自动化办公\Excel自动化办公\data\data\Bobo公司2020-2030年营收报表'):
    if file_name != '.DS_Store':
        file_name = os.path.join(r'E:\baiduwangpan\Excel自动化办公\Excel自动化办公\data\data\Bobo公司2020-2030年营收报表', file_name)
        ex_file = openpyxl.load_workbook(file_name)
        sheet_names = ex_file.sheetnames
        for sheet_name in sheet_names:
            sheet_file = ex_file[sheet_name]
            #数据从第1行到32行，第一列到第四列
            data = Reference(sheet_file, min_col=2, min_row=1, max_row=32, max_col=4)
            bc = BarChart()
            bc.title = sheet_name
            bc.x_axis.title = '日期'
            bc.y_axis.title = '营收额'
            bc.add_data(data, titles_from_data=True)   #加上titles_from_data=True，可以指定每列数据的条形图颜色，如下图表的手机，彩妆，电脑。不加这个参数，默认如上面所示的 系列1，系列2......

            # 使用日期这一列作为x轴
            x_label = Reference(sheet_file, min_col=1, min_row=2, max_row=32)
            bc.set_categories(x_label)

            sheet_file.add_chart(bc, 'E5')
        ex_file.save(file_name)

执行生成图表

轻松学会openpyxl库，Python处理Excel有如神助