文章目录
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 列中的单元格将自动更新。
- 每一行代表一次单独的销售。列分别是销售产品的类型(A)、产品每磅的价格(B)、销售的磅数©,以及这次销售的总收入(D)。
我们先看下表结构
-
现在假设 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 个字符。列宽为零或行高为零,将使单元格隐藏。
- 行的高度可以设置为 0 到 409 之间的整数或浮点值。这个值表示高度的点数。
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)
执行生成图表