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)
执行生成图表
