python+xls:常用操作、从电子表格中读取数据、更新一个电子表格

一个 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
文件的程序,将非常有用。这样的程序可以完成下列任务:
• 从一个电子表格读取数据,写入其他电子表格的某些部分
• 从网站、文本文件或剪贴板读取数据,将它写入电子表格。
• 自动清理电子表格中的数据。例如,可以利用正则表达式,读取多种格式的电
话号码,将它们转换成单一的标准格式。
————————————————

发布了492 篇原创文章 · 获赞 37 · 访问量 11万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览