python处理Excel实现自动化办公教学(含实战)【一】
相关文章:
python处理Excel实现自动化办公教学(含实战)【一】
python处理Excel实现自动化办公教学(含实战)【二】
python处理Excel实现自动化办公教学(数据筛选、公式操作、单元格拆分合并、冻结窗口、图表绘制等)【三】
相关码源:B站python资料.zip-数据挖掘文档类资源-CSDN下载
1.环境安装
- python处理Excel的方式
- openpyxl
- 环境安装:
- 模块官网:openpyxl · PyPI
- 最新版本:
pip install -U openpyxl==3.0.7 / pip install openpyxl==3.0.7
- 三方支持:
pip install lxml
pip install pillow
- 首先,让我们来看一些基本定义:
- 工作簿:一个 Excel 电子表格文档称为一个工作簿,一个 工作簿保存在扩展名为.xlsx 的文件中
- sheet表:每个工作簿可以包含多个表(也称为工作表)
- 活动表:用户当前查看的表(或关闭 Excel 前最后查看的表),称为活动表
- 单元格:每个表都有一些列(地址是从 A 开始的字母)和一些行(地址是从 1 开始的数 字)。在特定行和列的方格称为单元格。每个单元格都包含一个数字或文本值。
2.读取Excel表格
实例表格如下:
import openpyxl
用 openpyxl 模块打开 Excel 文档
#创建了一个指定的工作簿对象
wb = openpyxl.load_workbook(filename='./data/test.xlsx')
获取工作簿的sheet表的名称
wb.get_sheet_names()
获取指定的sheet对象
sheet = wb.get_sheet_by_name('基本信息') #
sheet = wb.get_sheet_by_name('也就是某个sheet')
获取活动表
wb.get_active_sheet()
- 从表中取得单元格
- 有了 Worksheet 对象后,就可以按名字访问 Cell 对象
- 属性:
- value:cell中存储的值
- row:行索引
- column:列索引
- coordinate:坐标
cell = sheet['A4'] #A列第三个
cell.value
cell.row
cell.column
cell.coordinate
print上述得到:
bobo2;4;1;A4
用字母来指定列,这在程序中可能有点奇怪,特别是在 Z 列之后,列开时使用 两个字母:AA、AB、AC 等。作为替代,在调用表的 cell()方法时,可以传入整数 作为 row 和 column 关键字参数,也可以得到一个单元格。第一行或第一列的整数 是 1,不是 0。
sheet.cell(row=1,column=2).value
'age'
- 从工作表中取得行和列
- 可以将 Worksheet 对象进行切片操作,从而取得电子表格中一行、一列或一个矩形区域中的所有 Cell 对象。
for cell_row in sheet['A2':'E6']:
for cell in cell_row:
print(cell.coordinate,cell.value)
- 要访问特定行或列的单元格的值,也可以利用 Worksheet 对象的 rows 和 columns属性。
sheet.columns[0]
#第一列
(<Cell 基本信息.A1>,
<Cell 基本信息.A2>,
<Cell 基本信息.A3>,
<Cell 基本信息.A4>,
<Cell 基本信息.A5>,
<Cell 基本信息.A6>,
<Cell 基本信息.A7>)
for cell in list(sheet.columns)[0]:
print(cell.value)
name
bobo
bobo1
bobo2
bobo3
bobo4
bobo5
获取工作表中的最大行和最大列的数量
print(sheet.max_ row,sheet.max column)
7 6
3.项目实战
项目:2010 年美国人口普查数据自动化处理
- 在这个项目中,你要编写一个脚本,从人口普查电子表格文件中读取数据,并在几秒钟内计算出每个县的统计值(可以根据县的名称快速计算出县的总人口和普查区的数量)。
- 下面是程序要做的事:
- 从 Excel 电子表格中读取数据。
- 计算每个县中普查区的数目。
- 计算每个县的总人口。 打印结果。
- 这意味着代码需要完成下列任务:
- 用 openpyxl 模块打开 Excel 文档并读取单元格。
- 计算所有普查区和人口数据,将它保存到一个数据结构中。
- 利用 pprint 模块,将该数据结构写入一个扩展名为.py 的文本文件。
- 数据说明:
- censuspopdata.xlsx 电子表格中只有一张表,名为'Population by Census Tract'。
- 每一行都保存了一个普查区的数据。
- 列分别是普查区的编号(A),州的简称(B),县的名称(C),普查区的人口(D)。
- 注意:一个县会设定多个普查区,D列表示县中所有普查区对应每一个普查区的人口数量
- print()和pprint()都是python的打印模块,功能基本一样,唯一的区别就是pprint()模块打印出来的数据结构更加完整,每行为一个数据结构,更加方便阅读打印输出结果。特别是对于特别长的数据打印,print()输出结果都在一行,不方便查看,而pprint()采用分行打印输出,所以对于数据结构比较复杂、数据长度较长的数据,适合采用pprint()打印方式。当然,一般情况多数采用print()。
countyData结构:【构建要求形式】
如果前面的字典保存在 countyData 中,下面的表达式求值结果如下:
代码:openpyxl 2.x版本
import openpyxl, pprint
print('Opening workbook...')
wb = openpyxl.load_workbook('data/censuspopdata.xlsx')
sheet = wb.get_sheet_by_name('Population by Census Tract') #sheet[0]
#countyData将包含你计算的每个县的总人口和普查区数目。但在它里面存储任何东西之前,你应该确定它内部的数据结构。
countyData = {}
print('Reading rows...')
for row in range(2, sheet.get_highest_row() + 1): #sheet.get_highest_row() 获取最大行数
state = sheet['B' + str(row)].value #获取值
county = sheet['C' + str(row)].value
pop = sheet['D' + str(row)].value
countyData.setdefault(state, {}) #{state:{}} #setdefault给字典添加键值
#在给后续添加键值
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...')
#写入py文件
resultFile = open('census2010.py', 'w')
#使用 pprint.pformat()函数,将变量字典的值作为一个巨大的字符串, 写入文件 census2010.py
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')
Opening workbook...
Reading rows...
Writing results...
Done.
将countyData输出到文本文件 census2010.py,你就通过 Python 程序生成了一个 Python 程序!这样做的好处是现在可以导入 census2010.py,就像任何其他 Python 模块一样。
import os
import census2010
#查看AK州Anchorage县的人口普查数据
census2010.allData['AK']['Anchorage']
{'pop': 291826, 'tracts': 55}
#增加可读性:
anchoragePop = census2010.allData['AK']['Anchorage']['pop']
print('The 2010 population of Anchorage was ' + str(anchoragePop))
The 2010 population of Anchorage was 291826
openpyxl 3.0版本以上版本升级书写简介点:
import openpyxl, pprint
print('Opening workbook...')
wb = openpyxl.load_workbook('data/censuspopdata.xlsx')
#sheet = wb.get_sheet_by_name('Population by Census Tract') #sheet[0]
sheet = wb['Population by Census Tract']
#countyData将包含你计算的每个县的总人口和普查区数目。但在它里面存储任何东西之前,你应该确定它内部的数据结构。
countyData = {}
print('Reading rows...')
#for row in range(2, sheet.get_highest_row() + 1): #sheet.get_highest_row() 获取最大行数
for row in range(2, sheet.max_row + 1): #新版本只能sheet.max_row获取最大化
state = sheet['B' + str(row)].value #获取值
county = sheet['C' + str(row)].value
pop = sheet['D' + str(row)].value
countyData.setdefault(state, {}) #{state:{}} #setdefault给字典添加键值
#在给后续添加键值
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...')
#写入py文件
resultFile = open('census2010.py', 'w')
#使用 pprint.pformat()函数,将变量字典的值作为一个巨大的字符串, 写入文件 census2010.py
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')