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 openpyxlwb = openpyxl.load_workbook("example.xlsx")
type(wb)
load_workbook()函数接收文件名返回workbook数据类型，打开文件
13.3.2：从工作簿中取得工资表
import openpyxlwb = openpyxl.load_workbook("example.xlsx")
print(wb.sheetnames)
# ['Sheet1']sheet = wb['Sheet1']
print(sheet)
# <Worksheet "Sheet1">print(sheet.title)
# Sheet1anotherSheet = wb.active
print(anotherSheet)
# <Worksheet "Sheet1">sheetnames属性可以取得工作簿中所有的表名 去一个表可以直接使用带方括号：wb['Sheet1'] active属性可以获取工作簿的活动表 title属性可以获取它的名称
13.3.3：从表中取得单元格
import openpyxlwb = 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 openpyxlwb = 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 openpyxlwb = 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_letterwb = 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 openpyxlwb = 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 openpyxlwb = 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, pprintwb = openpyxl.load_workbook("censuspopdata.xlsx")
sheet = wb.active
countyData = {}for row in range(2, sheet.max_row+1):state = sheet['b' + str(row)].valuecounty = sheet['c' + str(row)].valuepop = sheet['d' + str(row)].valuecountyData.setdefault(state, {})countyData[state].setdefault(county, {'tracts': 0, 'pop': 0})countyData[state][county]['tracts'] += 1countyData[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 openpyxlwb = openpyxl.Workbook()
print(wb.sheetnames)
sheet = wb.active
print(sheet.title)
sheet.title = 'Span'
print(wb.sheetnames)
Workbook函数可以创建一个新的Excel，但以上代码电子表格不会保存需要调用save()方法
import openpyxlwb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'Span'
wb.save('test.xlsx')save()方法用于保存Excel文件
13.5.2：创建和删除工作表
import openpyxlwb = 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 openpyxlwb = openpyxl.Workbook()
sheet = wb['Sheet']
sheet['a1'] = 'Hello world!'
print(sheet['a1'].value)
将值写入单元格和普通的赋值差不多，知道单元格的坐标字符串后将值赋给其对象
13.6：项目：更新电子表格
import openpyxlwb =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 Fontwb = 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 Fontwb = 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 openpyxlwb = 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 openpyxlwb = 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 openpyxlwb = 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 openpyxlwb = 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, BarChartwb = openpyxl.Workbook()
sheet = wb.active
for i in range(1, 11):sheet['a' + str(i)] = irefObj = 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')
![[linux kernel]slub内存管理分析(7) MEMCG的影响与绕过](https://img-blog.csdnimg.cn/b42bfacfc4894c03a9f526c4041b23a7.gif#pic_center)
