Python编程快速上手12章处理 Excel 电子表格（上）

• 安装 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 支持利用工作表中单元格的数据，创建条形图、折线图、散点图和饼图。