第13章：处理Excel电子表格（笔记）

13.1：Excel文档

13.2：安装openpyxl模块

pip install --user --Uopenpuxl==2.6.2

 这是安装2.6.2版本的，比较新的版本与学习的书籍的信息有一点不兼容

13.3：读取Excel文档

13.3.1：用openpyxl模块打开Excel文档

import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
type(wb)

load_workbook()函数接收文件名返回workbook数据类型，打开文件

13.3.2：从工作簿中取得工资表

import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
print(wb.sheetnames)
# ['Sheet1']

sheet = wb['Sheet1']
print(sheet)
# <Worksheet "Sheet1">

print(sheet.title)
# Sheet1

anotherSheet = wb.active
print(anotherSheet)
# <Worksheet "Sheet1">

sheetnames属性可以取得工作簿中所有的表名
去一个表可以直接使用带方括号：wb['Sheet1']
active属性可以获取工作簿的活动表
title属性可以获取它的名称

13.3.3：从表中取得单元格

import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
sheet = wb['Sheet1']
print(sheet['A1'])
print(sheet['A1'].value)
c = sheet['b1']
print(c.value)
print('Row %s, Column %s is %s' % (c.row, c.column, c.value))
print('Cell %s is %s' % (c.coordinate, c.value))

 

 Cell对象有一个value属性，它包含这个单元格的值，row、column和coordinate属性提供单元格位置信息

import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
sheet = wb['Sheet1']
print(sheet.cell(row=1, column=2))
print(sheet.cell(row=1, column=2).value)
for i in range(1, 8, 2):
    print(i, sheet.cell(row=i, column=2).value)

使用cell方法传入row值和column值，可以得到单元格的Cell对象和指定sheet['B1']一样

 

import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
sheet = wb['Sheet1']
print(sheet.max_row)
print(sheet.max_column)

 max_row和max_column属性来确定表的大小

13.3.4：列字母和数字之间的转换

import openpyxl
from openpyxl.utils import column_index_from_string, get_column_letter

wb = openpyxl.load_workbook("example.xlsx")
sheet = wb['Sheet1']
print(get_column_letter(1))
print(get_column_letter(2))
print(get_column_letter(27))
print(get_column_letter(900))

print(get_column_letter(sheet.max_column))
print(column_index_from_string('a'))
print(column_index_from_string('aa'))

get_column_letter()函数传入整数可以知道其整数对应的列，column_index_from_string()函数正好相反

13.3.5：从表中取得行和列

import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
sheet = wb['Sheet1']
print(tuple(sheet['a1':'c3']))
for rowOfCellObjects in sheet['a1':'c3']:
    for cellObj in rowOfCellObjects:
        print(cellObj.coordinate, cellObj.value)
    print("---END OF ROW---")

 指明要取的矩形框范围比如：sheet['a1':'c3']然后用循环嵌套把值输出来

import openpyxl

wb = openpyxl.load_workbook("example.xlsx")
sheet = wb.active
print(list(sheet.columns)[1])
for cellObj in list(sheet.columns)[1]:
    print(cellObj.value)

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

import openpyxl, pprint

wb = openpyxl.load_workbook("censuspopdata.xlsx")
sheet = wb.active
countyData = {}

for row in range(2, sheet.max_row+1):
    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)

resultFile = open('census2010.py', 'w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()

13.5：写入Excel文档

13.5.1：创建并保存Excel文档

import openpyxl

wb = openpyxl.Workbook()
print(wb.sheetnames)
sheet = wb.active
print(sheet.title)
sheet.title = 'Span'
print(wb.sheetnames)

 Workbook函数可以创建一个新的Excel，但以上代码电子表格不会保存需要调用save()方法

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'Span'
wb.save('test.xlsx')

save()方法用于保存Excel文件

13.5.2：创建和删除工作表

import openpyxl

wb = openpyxl.Workbook()
print(wb.sheetnames)
print(wb.create_sheet())
print(wb.sheetnames)
wb.create_sheet(index=0, title='First Sheet')
print(wb.sheetnames)
del wb['Sheet']
print(wb.sheetnames)

 

create_sheet()方法创建一个新的工作表，位置默认在最后。index和title关键参数指定新的工作表的索引和名。
del操作符可以删除一个工作表

13.5.3：将值写入单元格

import openpyxl

wb = openpyxl.Workbook()
sheet = wb['Sheet']
sheet['a1'] = 'Hello world!'
print(sheet['a1'].value)

 将值写入单元格和普通的赋值差不多，知道单元格的坐标字符串后将值赋给其对象

13.6：项目：更新电子表格

import openpyxl

wb =openpyxl.load_workbook("produceSales.xlsx")
sheet = wb['Sheet']

PRICE_UPDATES ={
    'Garlic': 3.07,
    'Celery': 1.19,
    'Lemon': 1.27
}
for rowNum in range(2, sheet.max_row):
    produceName = sheet.cell(row=rowNum, column=1)
    if produceName in PRICE_UPDATES:
        sheet.cell(row=rowNum, column=1).value = PRICE_UPDATES[produceName]
wb.save('updatedProduceSales.xlsx')

13.7：设置单元格的字体风格

import openpyxl
from openpyxl.styles import Font

wb = openpyxl.Workbook()
sheet = wb['Sheet']
italic24Font = Font(size=24, italic=True)   # italic设置斜体
sheet['a1'].font = italic24Font
sheet['a1'] = 'Hello world!'
wb.save('styles.xlsx')

Font()定义字体风格，将所需的风格按参数填入

13.8：Font对象

Font()函数的关键字参数

关键字参数	数据类型	描述
name	字符串	设置字体名称
size	整型	设置字体大小
bold	布尔型	是否为粗体
italic	布尔型	是否为斜体

import openpyxl
from openpyxl.styles import Font

wb = openpyxl.Workbook()
sheet = wb['Sheet']
fontObj1 = Font(name='Times New Roman', bold=True)   # italic设置斜体
sheet['a1'].font = fontObj1
sheet['a1'] = 'Times New Roman'

fontObj2 = Font(size=24, italic=True)   # italic设置斜体
sheet['b3'].font = fontObj2
sheet['b3'] = '24 pt Italic'

wb.save('styles.xlsx')

13.9：公式

用编程的方式在单元格中添加公式就和赋值一样

import openpyxl

wb = openpyxl.Workbook()
sheet = wb['Sheet']
sheet['a1'] = 200
sheet['a2'] = 300
sheet['a3'] = '=SUM(A1:A2)'
wb.save('writeFormula.xlsx')

13.10：调整行和列

13.10.1：设置行高和列宽

import openpyxl

wb = openpyxl.Workbook()
sheet = wb['Sheet']
sheet['a1'] = 'Tall row'
sheet['b2'] = 'Wide column'
sheet.row_dimensions[1].height = 70
sheet.column_dimensions['b'].width = 20
wb.save('dimensions.xlsx')

Worksheet对象有row_dimensions和column_dimensions属性分别控制行高和列宽。

行高可设高度为0到409之间的整数或浮点数默认行高为12.75。

列宽可设0到255之间的整数和浮点数，默认列宽为8.43个字符

13.10.2：合并和拆分单元格

import openpyxl

wb = openpyxl.load_workbook('merged.xlsx')
sheet = wb['Sheet']
sheet.merge_cells('a1:d3')
sheet.merge_cells('c5:d5')
sheet.unmerge_cells('a1:d3')
wb.save('merged.xlsx')

merge_cells()方法可以将一个矩形区域的单元格合并为一个单元格。

unmerge——cells()方法可以将一个合并矩形区域的单元格拆分为独立的单元格

13.10.3：冻结窗口

Worksheet对象有一个freeze_panes属性，可以设置一个Cell对象或一个单元格坐标的字符串，设置后单元格上边的所有行和列都会被冻结，其他的行和列不会被冻结

import openpyxl

wb = openpyxl.load_workbook('produceSales.xlsx')
sheet = wb['Sheet']
sheet.freeze_panes = 'a2'
wb.save('freezeExample.xlsx')

13.11：图表

要创建图表，需要以下事情：

1.  从一个矩形区域选择单元格来创建一个Reference对象
2. 通过传入Reference对象来创建一个Series对象
3. 创建一个Chart对象
4. 将Series对象添加到Chart对象
5. 可选地设置Chart对象的drawing.top、drawing.left、drawing.width和drawing.height属性
6. 将Chart对象添加到Worksheet对象

 

import openpyxl
from openpyxl.chart import Reference, Series, BarChart

wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1, 11):
    sheet['a' + str(i)] = i

refObj = Reference(sheet, min_col=1, min_row=1, max_row=10, max_col=1)
seriesObj = Series(refObj, title='First series')

chartObj = BarChart()
chartObj.title = 'My Chart'
chartObj.append(seriesObj)

sheet.add_chart(chartObj, 'c5')
wb.save('sampleChart.xlsx')