Python编程让繁琐的工作自动化（2）-处理Excel电子表格

最新推荐文章于 2024-04-10 07:08:41 发布

阿苏尔

最新推荐文章于 2024-04-10 07:08:41 发布

阅读量1.6w

点赞数 8

分类专栏： python

本文链接：https://blog.csdn.net/u010132497/article/details/85037189

版权

python 专栏收录该内容

36 篇文章 1 订阅

订阅专栏

一个 Excel 电子表格文档称为一个工作簿。一个工作簿保存在扩展名为.xlsx 的文件中。每个工作簿可以包含多个表（也称为工作表）。用户当前查看的表（或关闭 Excel 前最后查看的表），称为活动表。每个表都有一些列（地址是从 A 开始的字母）和一些行（地址是从 1 开始的数字）。在特定行和列的方格称为单元格。每个单元格都包含一个数字或文本值。单元格形成的网格和数据构成了表。

常用操作

import openpyxl
import os//更改目录需要导入os模块
os.getcwd()
#修改目录要加r或者将\改为\\，否则会异常
os.chdir(r'c:\Users\Administrator\Desktop')
wb = openpyxl.load_workbook('exsample.xlsx')
#类型为workbook
type(wb) 
#获取工作簿中所有表名的列表
wb.get_sheet_names() 
#每个表由一个worksheet对象表示，通过传递表名字符串可以获得表对象
sheet = wb.get_sheet_by_name('Sheet3') 
#类型为worksheet
type(sheet)
sheet.title
#获取活动工作表
anotherSheet = wb.get_active_sheet() 
#按名字访问Cell对象
sheet['A1'] 
#Cell对象有value属性，row、column、coordinate属性，提供单元格的位置信息。
c = sheet['A1'].value
c.coordinate
c.row
c.column
#除了用字母来表示列还可以传递row和column关键字参数，第一行或者第一列的整数是1不是0
#下面通过控制步长2来获取奇数行的值
for i in range(1, 8, 2): 
        print(i, sheet.cell(row=i, column=2).value) 
#获取表的大小
sheet.get_highest_row()
sheet.get_highest_column()  
#列字母和数字的转换
from openpyxl.cell import get_column_letter, column_index_from_string 
get_column_letter(1) ->A
column_index_from_string('A') ->1
#从表中取得行和列
for rowOfCellObjects in sheet['A1':'C3']: 
    for cellObj in rowOfCellObjects: 
        print(cellObj.coordinate, cellObj.value) 
print('--- END OF ROW ---') 

#写入Excel文档
#创建并保存Excel文档
wb = openpyxl.load_workbook('example.xlsx') 
sheet = wb.get_active_sheet() 
sheet.title = 'Spam Spam Spam' 
wb.save('example_copy.xlsx') #保存副本，不会修改源文件
#创建和删除工作表
wb.create_sheet() 
wb.create_sheet(index=0, title='First Sheet') 
wb.remove_sheet(wb.get_sheet_by_name('Middle Sheet')) 
 #将值写入单元格
sheet['A1'] = 'Hello world!'

#设置单元格的字体风格，需要从openpyxl.styles模块导入Font()和Style()函数
from openpyxl.styles import Font, Style 
italic24Font = Font(size=24, italic=True) #斜体
styleObj = Style(font=italic24Font) 
sheet['A'].style = styleObj 
sheet['A1'] = 'Hello world!' 
#Font对象的style属性影响文本在单元格中的显示方式。要设置字体风格属性，就向Font()函数传入关键字参数。
#关键字参数有name：字符串，如'Times New Roman'，size，bold：True/False，italic：True/False

#公式
#公式以一个等号开始，可以配置单元格，让它包含通过其他单元格计算得到的值。
sheet['A1'] = 200 
sheet['A2'] = 300 
sheet['A3'] = '=SUM(A1:A2)' 
#通过 load_workbook()的 data_only 关键字参数设置显示公式或者显示公式的结果
#显示公式
sheet['A3'].value ->'=SUM(A1:A2)' 
#显示结果
wbDataOnly = openpyxl.load_workbook('writeFormula.xlsx', data_only=True) 
sheet = wbDataOnly.get_active_sheet() 
sheet['A3'].value ->500
#尽管Excel提供了一些公式，但是对于复杂的计算来说，很难搞清楚Excel公式干了什么，相比python代码的可读性好得多。

#调整行和列
#Worksheet 对象有 row_dimensions 和 column_dimensions 属性，控制行高和列宽。
#行的高度可以设置为 0 到 409 之间的整数或浮点值。这个值表示高度的点数。一点等于 1/72 英寸。默认的行高是 12.75。
#列宽可以设置为 0到 255 之间的整数或浮点数。这个值表示使用默认字体大小时（11 点），单元格可以显示的字符数。
#默认的列宽是 8.43 个字符。列宽为零或行高为零，将使单元格隐藏。 
sheet.row_dimensions[1].height = 70 
sheet.column_dimensions['B'].width = 20 
#合并和拆分单元格，要设置这些合并后单元格的值，只要设置这一组合并单元格左上角的单元格的值。
sheet.merge_cells('A1:D3') 
sheet['A1'] = 'Twelve cells merged together.' 
sheet.unmerge_cells('A1:D3') 
#冻结窗格
#对于太大而不能一屏显示的电子表格，“冻结”顶部的几行或最左边的几列，是很有帮助的。
#。在 OpenPyXL 中，每个 Worksheet 对象都有一个 freeze_panes属性，可以设置为一个 Cell 对象或一个单元格坐标的字符串。
#请注意，单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结。
sheet.freeze_panes = 'A2'  #冻结行 1 
sheet.freeze_panes = 'B1'  #冻结列 A 
sheet.freeze_panes = 'C1'  #冻结列 A 和列 B 
sheet.freeze_panes = 'C2'  #冻结行 1 和列 A 和列 B 
sheet.freeze_panes = 'A1'  #没有冻结窗格
sheet.freeze_panes = None  #没有冻结窗格

#图表
#个人认为利用python来画excel图表倒不如直接利用excel来画图了，如果后期有需要可以在来查阅这部分内容
#另外，在 OpenPyXL 的当前版本中（2.1.4），load_workbook()不会加载 Excel文件中的图表。即使 Excel 文件包含图表，加载的 Workbook 对象也不会包含它们。
#如果加载一个 Workbook 对象，然后马上保存到同样的.xlsx 文件名中，实际上就会删除其中的图表。

项目：从电子表格中读取数据

假定你有一张电子表格的数据，来自于 2010 年美国人口普查。你有一个无聊的任
务，要遍历表中的几千行，计算总的人口，以及每个县的普查区的数目（普查区就是一
个地理区域，是为人口普查而定义的）。每行表示一个人口普查区。我们将这个电子表格文件命名为 censuspopdata.xlsx，可以从 http://nostarch.com/automatestuff/下载它。它的内容如图 12-2 所示。
在这里插入图片描述
尽管 Excel 是要能够计算多个选中单元格的和，你仍然需要选中 3000 个以上县
的单元格。即使手工计算一个县的人口只需要几秒钟，整张电子表格也需要几个小
时时间。
在这个项目中，你要编写一个脚本，从人口普查电子表格文件中读取数据，并
在几秒钟内计算出每个县的统计值。
下面是程序要做的事：
• 从 Excel 电子表格中读取数据。
• 计算每个县中普查区的数目。
• 计算每个县的总人口。
• 打印结果。
这意味着代码需要完成下列任务：
• 用 openpyxl 模块打开 Excel 文档并读取单元格。
• 计算所有普查区和人口数据，将它保存到一个数据结构中。
• 利用 pprint 模块，将该数据结构写入一个扩展名为.py 的文本文件。

#! python3 
# readCensusExcel.py - Tabulates population and number of census tracts for 
# each county. 
#第1步：读取电子表格数据 
import openpyxl, pprint 
print('Opening workbook...') 
wb = openpyxl.load_workbook('censuspopdata.xlsx') 
sheet = wb.get_sheet_by_name('Population by Census Tract') 
#数据保存在字典里，最终结果应该是这样：{'AK': {'Aleutians East': {'pop': 3141, 'tracts': 1}...},
#州映射一个字典，其中键是县名，键值是另一个字典，分别为普查区tract和人口pop
countyData = {} 
 
#第2步：填充数据结构
print('Reading rows...') 
for row in range(2, sheet.get_highest_row() + 1): 
    # Each row in the spreadsheet has data for one census tract. 
    State  = sheet['B' + str(row)].value 
    county = sheet['C' + str(row)].value 
    pop    = sheet['D' + str(row)].value 
	#设置键值为默认值，这样保证键存在才能在后面引用
	countyData.setdefault(state, {})
	countyData[state].setdefault(county, {'tracts': 0, 'pop': 0}) 
	#更改数据
	countyData[state][county]['tracts'] += 1 
	countyData[state][county]['pop'] += int(pop) 
#第3步：将结果写入文件
#打开一个新的文本文件并将countyData写入到py文件，这样census2010.py就保存了新的县的数据。
#readCensusExcel.py 程序就可以扔掉了，使用的时候是需要导入cesus2010.py即可。
print('Writing results...') 
resultFile = open('census2010.py', 'w') 
resultFile.write('allData = ' + pprint.pformat(countyData)) 
resultFile.close() 
print('Done.')

许多公司和组织机构使用 Excel 来保存各种类型的数据，电子表格会变得庞大，这
并不少见。解析 Excel 电子表格的程序都有类似的结构：它加载电子表格文件，准备一
些变量或数据结构，然后循环遍历电子表格中的每一行。这样的程序可以做下列事情：
• 比较一个电子表格中多行的数据。
• 打开多个 Excel 文件，跨电子表格比较数据。
• 检查电子表格是否有空行或无效的数据，如果有就警告。
• 从电子表格中读取数据，将它作为 Python 程序的输入。

项目2：更新一个电子表格

这个项目需要编写一个程序，更新产品销售电子表格中的单元格。程序将遍
历这个电子表格，找到特定类型的产品，并更新它们的价格。请从 http://nostarch.com/
automatestuff/下载这个电子表格。图 12-3 展示了这个电子表格。
在这里插入图片描述
每一行代表一次单独的销售。列分别是销售产品的类型（A）、产品每磅的价格
（B）、销售的磅数（C），以及这次销售的总收入。TOTAL 列设置为 Excel 公式，将
每磅的成本乘以销售的磅数，并将结果取整到分。有了这个公式，如果列 B 或 C 发
生变化，TOTAL 列中的单元格将自动更新。
现在假设 Garlic、Celery 和 Lemons 的价格输入的不正确。这让你面对一项无聊
的任务：遍历这个电子表格中的几千行，更新所有 garlic、celery 和 lemon 行中每磅
的价格。你不能简单地对价格查找替换，因为可能有其他的产品价格一样，你不希
望错误地“更正”。对于几千行数据，手工操作可能要几小时。但你可以编写程序，
几秒钟内完成这个任务。
你的程序做下面的事情：
• 循环遍历所有行。
• 如果该行是 Garlic、Celery 或 Lemons，更新价格。
这意味着代码需要做下面的事情：
• 打开电子表格文件。
• 针对每一行，检查列 A 的值是不是 Celery、Garlic 或 Lemon。
• 如果是，更新列 B 中的价格。
• 将该电子表格保存为一个新文件（这样就不会丢失原来的电子表格，以防万一）。

#! python3 
# updateProduce.py - Corrects costs in produce sales spreadsheet. 
 #第1步：利用更新信息建立数据结构
import openpyxl 
wb = openpyxl.load_workbook('produceSales.xlsx') 
sheet = wb.get_sheet_by_name('Sheet') 
# The produce types and their updated prices 
PRICE_UPDATES = {'Garlic': 3.07, 
                 'Celery': 1.19, 
                 'Lemon': 1.27} 
#第2步：检查所有行，更新不正确的价格
for rowNum in range(2, sheet.get_highest_row()):   # skip the first row 
    produceName = sheet.cell(row=rowNum, column=1).value 
    if produceName in PRICE_UPDATES: 
        sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName] 
    wb.save('updatedProduceSales.xlsx')

因为许多办公室职员一直在使用 Excel 电子表格，所以能够自动编辑和写入 Excel
文件的程序，将非常有用。这样的程序可以完成下列任务：
• 从一个电子表格读取数据，写入其他电子表格的某些部分
• 从网站、文本文件或剪贴板读取数据，将它写入电子表格。
• 自动清理电子表格中的数据。例如，可以利用正则表达式，读取多种格式的电
话号码，将它们转换成单一的标准格式。

阿苏尔

关注

8
点赞
踩
93

收藏

觉得还不错? 一键收藏
1
评论
Python编程让繁琐的工作自动化（2）-处理Excel电子表格

一个 Excel 电子表格文档称为一个工作簿。一个工作簿保存在扩展名为.xlsx 的文件中。每个工作簿可以包含多个表（也称为工作表）。用户当前查看的表（或关闭 Excel 前最后查看的表），称为活动表。每个表都有一些列（地址是从 A 开始的字母）和一些行（地址是从 1 开始的数字）。在特定行和列的方格称为单元格。每个单元格都包含一个数字或文本值。单元格形成的网格和数据构成了表。常用操作imp...
复制链接

扫一扫