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:图表
要创建图表,需要以下事情:
- 从一个矩形区域选择单元格来创建一个Reference对象
- 通过传入Reference对象来创建一个Series对象
- 创建一个Chart对象
- 将Series对象添加到Chart对象
- 可选地设置Chart对象的drawing.top、drawing.left、drawing.width和drawing.height属性
- 将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')