- 安装 openpyxl 模块
要导入 openpyxl 模块,否则会得到错误,NameError: name 'openpyxl’is not defined。 - 读取 Excel 文档
#example.xlsx
A B C
1 4/5/2015 1:34:02 PM Apples 73
2 4/5/2015 3:41:23 AM Cherries 85
3 4/6/2015 12:46:51 PM Pears 14
4 4/8/2015 8:59:43 AM Oranges 52
5 4/10/2015 2:07:00 AM Apples 152
6 4/10/2015 6:10:37 PM Bananas 23
7 4/10/2015 2:40:46 AM Strawberries 98
- 用 openpyxl 模块打开 Excel 文档
openpyxl.load_workbook()函数接受文件名,返回一个workbook 数据类型的值。
import openpyxl
#example.xlsx 需要在当前工作目录,才能显示出提示信息
wb = openpyxl.load_workbook('example.xlsx')
type(wb)
- 从工作簿中取得工作表
>>>import openpyxl
>>>wb = openpyxl.load_workbook('example.xlsx')
>>>wb.sheetnames
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet = wb['Sheet']
>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title
'Sheet3'
>>> anotherSheet = wb.active
>>> anotherSheet
<Worksheet "Sheet1">
- 从表中取得单元格
>>> import openpyxl
>>> wb = openpyxl.load_ _workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> sheet['A1']
<Cell Sheet1.A1>
>>> sheet['A1'].value
datetime.datetime(2015, 4, 5, 13, 34, 2)
>>> c = sheet['B1']
>>> c.value
'Apples'
>>> 'Row ' + str(c.row) + ', Column ' + c.column + ' is ' + c.value
'Row 1, Column B is Apples'
>>> 'Cell ' + c.coordinate + ' is ' + c.value
'Cell B1 is Apples'
>>> sheet['C1'].value
73
Cell 对象有一个 value 属性,不出意外,它包含这个单元格中保存的值。
Cell 对象也有 row、column 和 coordinate 属性,提供该单元格的位置信息。
>>> sheet.cell(row=1, column=2)
<Cell Sheet1.B1>
>>> sheet.cell(row=1, column=2).value
'Apples'
>>> for i in range(1, 8, 2):
print(i, sheet.cell(row=i, column=2).value)
1 Apples
3 Pears
5 Apples
7 Strawberries
可以看到,使用表的 cell()方法,传入 row=1 和 column=2,将得到单元格 B1的 Cell 对象,就像指定 sheet[‘B1’]一样。
然后,利用 cell()方法和它的关键字参数,就可以编写 for 循环,打印出一系列单元格的值。
可以通过 Worksheet 对象的 sheet.max_row和 sheet.max_column,确定表的大小。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.active
>>> sheet.max_row
7
>>> sheet.max_column
3
列字母和数字之间的转换
要从字母转换到数字,就调用 openpyxl.utils.column_index_from_string()函数。
要从数字转换到字母,就调用 openpyxl.utils.get_column_letter()函数。
>>> import openpyxl
>>> from openpyxl.utils import get_column_letter, column_index_from_string
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> get_column_letter(sheet.max_column)
'C'
>>> column_index_from_string('A')
1
>>> column_index_from_string('AA')
27
- 从表中取得行和列
可以将 Worksheet 对象切片,取得电子表格中一行、一列或一个矩形区域中的所有
Cell 对象。然后可以循环遍历这个切片中的所有单元格。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb['Sheet1']
>>> tuple\list(sheet['A1':'C3'])
((<Cell Sheet1.A1>, <Cell Sheet1.B1>, <Cell Sheet1.C1>), (<Cell Sheet1.A2>,
<Cell Sheet1.B2>, <Cell Sheet1.C2>), (<Cell Sheet1.A3>, <Cell Sheet1.B3>,
<Cell Sheet1.C3>))
>>> for rowObj in sheet['A1':'C3']:
for cellObj in rowObj:
#准确位置,打印位置的值
print(cellObj.coordinate, cellObj.value)
print('--- END OF ROW ---')
A1 2015-04-05 13:34:02
B1 Apples
C1 73
--- END OF ROW ---
A2 2015-04-05 03:41:23
B2 Cherries
C2 85
--- END OF ROW ---
A3 2015-04-06 12:46:51
B3 Pears
C3 14
--- END OF ROW ---
要访问特定行或列的单元格的值,也可以利用 Worksheet 对象的 rows 和 columns属性。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.active
#一共有7行,取第一行。list(sheet.rows)[1]
#一共有3列,取第一列。list(sheet.columns)[1]
>>> list(sheet.columns)[1]
(<Cell 'Sheet1'.B1>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.B4>,
<Cell 'Sheet1'.B5>, <Cell 'Sheet1'.B6>, <Cell 'Sheet1'.B7>)
>>> for cellObj in list(sheet.columns)[1]:
print(cellObj.value)
Apples
Cherries
Pears
Oranges
Apples
Bananas
Strawberries
要访问一个特定的元组,可以利用它在大的元组中的下标。
例如,要得到代表 B列的元组,可以用list(sheet.columns)[1]。
要得到代表A 列的元组,可以用 list(sheet.columns)[0]。
在得到了代表行或列的元组后,可以循环遍历它的对象,打印出它们的值。
工作簿、工作表、单元格
1.导入 openpyxl 模块。
2.调用 openpyxl.load_workbook()函数。
3.取得 Workbook 对象。
4.调用 sheet.active或 sheet[‘Sheet’]工作簿方法。
5.取得 Worksheet 对象。
6.使用索引或工作表的 cell()方法,带上 row 和 column 关键字参数。
7.取得 Cell 对象。
8.读取 Cell 对象的 value 属性。
项目:从电子表格中读取数据
假定你有一张电子表格的数据,来自于2010 年美国人口普查。你有一个无聊的任
务,要遍历表中的几千行,计算总的人口,以及每个县的普查区的数目(普查区就是一
个地理区域,是为人口普查而定义的)。每行表示一个人口普查区。
数据结构:
{‘AK’: {‘Aleutians East’: {‘pop’: 3141, ‘tracts’: 1},
‘Aleutians West’: {‘pop’: 5561, ‘tracts’: 2},
‘Anchorage’: {‘pop’: 291826, ‘tracts’: 55},
‘Bethel’: {‘pop’: 17013, ‘tracts’: 3},
‘Bristol Bay’: {‘pop’: 997, ‘tracts’: 1},
–snip–
import os,openpyxl,pprint
count = {}
wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb.active
#遍历行
for j in range(1,sheet.max_row):
#取出数据
state = sheet.cell(column=2,row=j).value
county = sheet.cell(column=3,row=j).value
pop = sheet.cell(column=4,row=j).value
#把数据写入dict
count.setdefault(state,{})
count[state].setdefault(county,{'pop':0,'tracts':0})
count[state][county]['pop'] += int(pop)
count[state][county]['tracts'] += 1
#把dict保存至文件
outputFile = open('output\\total.py','w')
outputFile.write('count = '+pprint.pformat(count))
outputFile.close()
写入 Excel 文档
- 创建并保存 Excel 文档
调用 openpyxl.Workbook()函数,创建一个新的空 Workbook 对象。
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.sheetnames
['Sheet']
>>> sheet = wb.active
>>> sheet.title
'Sheet'
>>> sheet.title = 'Spam Bacon Eggs Sheet'
>>> wb.sheetnames
['Spam Bacon Eggs Sheet']
当修改Workbook对象或它的工作表和单元格时,电子表格文件不会保存,除非你调
用 save()工作簿方法。
>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.active
>>> sheet.title = 'Spam Spam Spam'
>>> wb.save('example_ _copy.xlsx')
当你编辑从文件中加载的一个电子表格时应该有备份。
- 创建和删除工作表
利用 create_sheet() and del wb[‘Sheet’]方法,可以在工作簿中添加或删除工作表。
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> wb.sheetnames
['Sheet']
#创建表单
>>> wb.create_sheet()
<Worksheet "Sheet1">
#index索引,title名称
>>> wb.create_sheet(index=0, title='First Sheet')
>>>del wb['Sheet']#删除表单
- 将值写入单元格,很像将值写入字典中的键。
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
>>> sheet['A1'] = 'Hello world!'
>>> sheet['A1'].value
'Hello world!'
项目:更新一个电子表格xlsx
这个项目需要编写一个程序,更新产品销售电子表格中的单元格。程序将遍
历这个电子表格,找到特定类型的产品,并更新它们的价格。
列分别是销售产品的类型(A)、产品每磅的价格(B)、销售的磅数(C)(以及这次销售的总收入)。
需要更新的价格如下:apple,candy,cup = 3
import openpyxl,os
wb = openpyxl.load_workbook('FreezeExample.xlsx')
sheet = wb.active
produce = {'apple':3,
'candy':3,
'cup':3}
for row in range(2,sheet.max_row+1):
temp = sheet.cell(row=row,column=1).value
if temp in produce:
sheet.cell(row=row,column=2).value = produce[temp]
wb .save('output\\FreezeExample_change.xlsx')
- 设置单元格的字体风格
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook()
sheet = wb['Sheet']
italic24Font = Font(size = 24, italic = True)
sheet['A1'].font = italic24Font
sheet['A1'] = 'Hello world!'
wb.save('styled.xlsx')
- Font 对象
Font 对象的 style 属性影响文本在单元格中的显示方式。要设置字体风格属性,就
向 Font()函数传入关键字参数。
关键字参数----数据类型----描述
name-----------string---------字体名称,‘Calibri’
size-------------int-------------大小点数
bold-------------bool----------True 表示粗体
italic ------------bool ---------True 表示斜体
可以调用 Font()来创建一个 Font 对象,并将这个 Font 对象保存在一个变量中。
>>> import openpyxl
>>> from openpyxl.styles import Font
#创建新对象
>>> wb = openpyxl.Workbook()
>>> sheet = wb['Sheet']
#加入新样式和文本
>>> fontObj1 = Font(name='Times New Roman', bold=True)
>>> sheet['A1'].font = fontObj1
>>> sheet['A1'] = 'Bold Times New Roman'
#加入新样式和文本
>>> fontObj2 = Font(size=24, italic=True)
>>> sheet['B3'].font = fontObj2
>>> sheet['B3'] = '24 pt Italic'
>>> wb.save('styles.xlsx')
- 公式
公式以一个等号开始,可以配置单元格,让它包含通过其他单元格计算得到的
值。
#计算单元格 B1 到 B8 的和。
>>> 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('writeFormula.xlsx')
#data_only 关键字参数设置为 True。
>>> import openpyxl
>>> wbFormulas = openpyxl.load_workbook('writeFormula.xlsx')
>>> sheet = wbFormulas.active
>>> sheet['A3'].value
'=SUM(A1:A2)'
>>> wbDataOnly = openpyxl.load_workbook('writeFormula.xlsx', data_only=True)
>>> sheet = wbDataOnly.active
>>> sheet['A3'].value
500
- 设置行高和列宽
Worksheet 对象有 row_dimensions 和 column_dimensions 属性,控制行高和列宽。
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> sheet['A1'] = 'Tall row'
>>> sheet['B2'] = 'Wide column'
>>> sheet.row_dimensions[1].height = 70
>>> sheet.column_dimensions['B'].width = 20
>>> wb.save('dimensions.xlsx')
行高可以设置为 0到409 之间的整数或浮点值。
默认的行高是 12.75。
列宽可以设置为 0到255 之间的整数或浮点数。
默认的列宽是8.43 个字符。
列宽为零或行高为零,将使单元格隐藏。
- 合并和拆分单元格
利用 merge_cells()工作表方法,合并单元格。
unmerge_cells()工作表方法,拆分单元格。
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> sheet.merge_cells('A1:D3')
>>> sheet['A1'] = 'Twelve cells merged together.'
>>> sheet.merge_cells('C5:D5')
>>> sheet['C5'] = 'Two merged cells.'
>>> wb.save('merged.xlsx')
- 冻结窗格
freeze_panes 的设置 --------------冻结的行和列
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-------没有冻结窗格
>>> import openpyxl
>>> wb = openpyxl.load_workbook('produceSales.xlsx')
>>> sheet = wb.active
>>> sheet.freeze_panes = 'A2'
>>> wb.save('freezeExample.xlsx')
- 图表
openpyxl 支持利用工作表中单元格的数据,创建条形图、折线图、散点图和饼图。