【干货】建议收藏! ! !全网最全的Python.openpyxl *** 作Excel数据

概述Python *** 作 Excel 存取数据1. openpyxl 模块1.1 安装 openpyxl 模块1.2 Excel 文件的三个对象1.2.1 Workbook 对象1.2.2 Worksheet 对象1.3.3 Cell2. Python *** 作 Excel2.1 Python *** 作 Excel 之读取打开本地工作簿，获取所有工作表名称创建工作表根据工作表名称获取工作表获取单元格对象及单元格的值获取单元格的行、列及值读取整行、整列及部分几行1. openpyxl 模块对比其它编程语言，我们都知道

Python *** 作 Excel 存取数据 1. openpyxl 模块1.1 安装 openpyxl 模块1.2 Excel 文件的三个对象1.2.1 Workbook 对象1.2.2 Worksheet 对象1.3.3 Cell2. Python *** 作 Excel2.1 Python *** 作 Excel 之读取2.1.1 打开本地工作簿，获取所有工作表名称2.1.2 创建工作表2.1.3 根据工作表名称获取工作表2.1.4 获取单元格对象及单元格的值2.1.5 获取单元格的行、列及值2.1.6 读取整行、整列及部分几行2.1.7 使用 iter_rows()逐行读取2.1.8 部分行部分列切片读取2.1.9 列数字与字母的对应转换2.2 Python *** 作 Excel 之写2.2.1 创建、删除工作表2.2.2 使用 list 写入2.2.3 使用 range 方式写入2.2.4 使用 cell()方法写入3. Python *** 作 Excel 之修改样式3. 1 修改字体样式3. 2 设置单元格公式3.2 设置行高和列宽3.3 合并单元格3.4 拆分单元格4. Python *** 作 Excel 之图表4.1 饼图4.2 条形图和柱形图4.3 气泡图4.4 散点图

1. openpyxl 模块

对比其它编程语言，我们都知道Python最大的优势是代码简单，有丰富的第三方开源库供开发者使用。伴随着近几年数据分析的热度，Python也成为最受欢迎的编程语言之一。而对于数据的读取和存储，对于普通人来讲，除了数据库之外，最常见的就是微软的Excel。

openpyxl 模块是一个读写 Excel 2010 文档的 Python 库，如果要处理更早格式的 Excel 文档，需要用到额外的库，openpyxl 是一个比较综合的工具，能够同时读取和修改 Excel 文档。

1.1 安装 openpyxl 模块

要想使用 openpyxl 模块，必须先安装此模块。直接使用 pip 就可以进行安装，命令如下：

pip install openpyxl

1.2 Excel 文件的三个对象

想要 *** 作 Excel 首先要了解 Excel 基本概念，Excel 中列以字母命名，行以数字命名， 比如左上角第一个单元格的坐标为 A1，下面的为 A2，右边的 B1。

openpyxl 中有三个不同层次的类，Workbook 是对工作簿的抽象，Worksheet 是对表格的抽象，Cell 是对单元格的抽象，每一个类都包含了许多属性和方法。

打开或者创建一个 Excel 需要创建一个 Workbook 对象。获取一个表则需要先创建一个 Workbook 对象，然后使用该对象的方法来得到一个 Worksheet 对象。如果要获取表中的数据，那么得到 Worksheet 对象以后再从中获取代表单元格的 Cell 对象。

1.2.1 Workbook 对象

一个 Workbook 对象代表一个 Excel 文档，因此在 *** 作 Excel 之前，都应该先创建一个 Workbook 对象。对于创建一个新的 Excel 文档，直接进行 Workbook 类的调用即可，对于一 个已经存在的 Excel 文档，可以使用 openpyxl 模块的 load_workbook 函数进行读取，该函数 包涵多个参数，但只有 filename 参数为必传参数。filename 是一个文件名，也可以是一个打开的文件对象。

创建 Workbook 对象

import openpyxl excel = openpyxl.Workbook() # 创建本地工作簿 excel = openpyxl.load_workbook("abc.xlsx") # 加载本地已存在的工作簿 #  *** 作工作簿完毕后需要保存工作簿 excel.save("workbook_test.xlsx")

Workbook 对象提供了很多属性和方法，其中，大部分方法都与 sheet 有关，常用属性和方法如表所示：

1.2.2 Worksheet 对象

通过 Worksheet 对象获取表格的属性，得到单元格中的数据，修改表格中的内容。 openpyxl 提供了非常灵活的方式来访问表格中的单元格和数据，常用的 Worksheet 属性和方法如表所示：

Worksheet 对象常用属性：

Worksheet 对象常用方法：

1.3.3 Cell

Cell 对象比较简单，常用的属性如表所示：

2. Python *** 作 Excel 2.1 Python *** 作 Excel 之读取 2.1.1 打开本地工作簿，获取所有工作表名称

# 获取所有的工作表名称import openpyxlwb = openpyxl.load_workbook('excelTest.xlsx')# 获取所有的工作表名称print(wb.sheetnames)# 获取当前激活的工作表print(wb.active.Title)# 通过工作簿获取for s in wb:    print(s.Title)

2.1.2 创建工作表

import openpyxl wb = openpyxl.load_workbook('excelTest.xlsx') #创建工作表 mySheet = wb.create_sheet('mySheet') print(wb.sheetnames) #遍历获取工作表的名称 for sheet in wb: 	print(sheet.Title)

2.1.3 根据工作表名称获取工作表

import openpyxl wb = openpyxl.load_workbook('excelTest.xlsx') # 根据工作表名称获取工作表，DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).# 会产生弃用警告，wb['my_sheet']，可以避免# my_sheet = wb.get_sheet_by_name('my_sheet')#根据名称获取表单 sheet3 = wb.get_sheet_by_name('Sheet2') #或者 sheet4 = wb['mySheet']

2.1.4 获取单元格对象及单元格的值

import openpyxl wb = openpyxl.load_workbook('excelTest.xlsx') #获取当前激活的工作表 ws = wb.active #返回的是工作表对象 print(ws) #<Worksheet "Sheet1"> #获取 Cell 对象及单元格的值 print(ws['A1']) #<Cell 'Sheet1'.A1> print(ws['A1'].value)

2.1.5 获取单元格的行、列及值

# 获取单元格对象的行列和值import openpyxl# 获取工作簿对象wb = openpyxl.load_workbook('excelTest.xlsx')# 获取sheetws = wb.active# 根据单元格名称获取单元格对象c = ws['C3']print('row:{},column:{},value:{}'.format(c.row, c.column, c.value))print('coordinate:', c.coordinate)print('ws.cell()获取单元格的值')print(ws.cell(row=2, column=2).value)print('循环遍历获取：')for r in ws.rows:    for c in r:        print(c.value, end='\t\t')    print()

2.1.6 读取整行、整列及部分几行

# 读取整行整列及部分行import openpyxl# 获取工作簿wb = openpyxl.load_workbook('excelTest.xlsx')# 获取sheetws = wb.activeprint('获取一整行')rows = ws[2]print(rows)# 遍历所有单元格for r in rows:    print(r.value, end='\t')print('获取整列')columns = ws['B']print(columns)for c in columns:    print(c.value, end='\t')print('获取部分行 对行进行切片')row_range = ws[2:3]print(row_range)for r in row_range:    for c in r:        print(c.value, end='\t')    print()columns_range = ws['A':'C']print(columns_range)for col in columns_range:    for c in col:        print(c.value, end='\t\t')    print()

2.1.7 使用 iter_rows()逐行读取

import openpyxl# 获取工作簿wb = openpyxl.load_workbook('excelTest.xlsx')# 获取当前激活态的sheetws = wb.activeprint('总共{}，总共{}列'.format(ws.max_row, ws.max_column))print(ws.iter_rows())  # generator object 获取一个生成器对象# 获取所有行所有列for row in ws.iter_rows():    for cell in row:        print(cell.value, end='\t\t')    print()print('读取部分行 部分列')for row in ws.iter_rows(min_row=2, max_row=3, min_col=2, max_col=2):    for cell in row:        print(cell.value, end='\t\t')    print()print()print('iter.cols()')print(ws.iter_cols())for cell in ws.iter_cols():    for c in cell:        print(c.value, end='\t\t')    print()print()print('ws.iter_rows()设置最小列、最大列、最小行和最大行读取'.center(20,'*'))for cell in ws.iter_cols(min_row=2, max_col=2, max_row=3):    for c in cell:        print(c.value, end='\t\t')    print()print()

2.1.8 部分行部分列切片读取

import openpyxl wb = openpyxl.load_workbook('excelTest.xlsx') ws = wb.active print('共{}行， 共{}列'.format(ws.max_row,ws.max_column)) print('部分行部分列切片读取'.center(20,'*')) cell_range = ws['A1:C3'] for rowObject in cell_range: 	for cellObject in rowObject:	     print(cellObject.coordinate,cellObject.value,end='\t') 	print()

2.1.9 列数字与字母的对应转换

# 列字母与数字的对象转化from openpyxl.utils import get_column_letter, column_index_from_stringprint('第2列对象的字母:', get_column_letter(2))print('第12列对应的字母:', get_column_letter(12))print('字母DR对应的数字:', column_index_from_string('DR'))print('字母D对应的数字:', column_index_from_string('D'))

2.2 Python *** 作 Excel 之写 2.2.1 创建、删除工作表

# 创建WorkSheet及删除import openpyxl# 创建工作簿wb = openpyxl.Workbook()ws = wb.activeprint('默认工作表名称', ws.Title)# 设置名称ws.Title = 'mySheet'print(ws.Title)# 获取工作簿的所有sheetnamesprint(wb.sheetnames)  # 返回所有sheet名称 列表对象print(wb.get_sheet_names())print('创建工作表')wb.create_sheet(index=1, Title='One Sheet')wb.create_sheet(index=2, Title='Two Sheet')wb.create_sheet(index=3, Title='Three Sheet')print(wb.sheetnames)print('删除工作表')wb.remove_sheet(wb['Two Sheet'])  # Deprecating Waring：弃用wb.remove(wb['Two Sheet'])print(wb.sheetnames)

2.2.2 使用 List 写入

# 写入数据到excel中import openpyxlwb = openpyxl.Workbook()ws = wb.create_sheet(Title='使用List写入')print(wb.sheetnames)print('使用List写入')rows = [['Now1', 'Now2', 'Now3', 'Now3'],        [12, 54, 23, 26],        [11, 43, 55, 13],        [54, 7672, 333, 433],        [1, 2, 3, 4],        ]print(rows)# 按行写入for row in rows:    ws.append(row)

2.2.3 使用 range 方式写入

import openpyxl #向工作单元写内容 wb = openpyxl.Workbook() ws2 = wb.create_sheet('range names')ws = wb.create_sheet('RangeSheet')for i in range(1, 41):    ws.append(range(16))

2.2.4 使用 cell()方法写入

# 根据cell()写值ws = wb.create_sheet('Cell Sheet')from openpyxl.utils import get_column_letter  # 根据当前列获取该列名for row in range(10, 21):    for col in range(5, 16):        ws.cell(row=row, column=col, value=get_column_letter(col))  # 根据当前列获取该列名，作为valuewb.save('写入数据.xlsx')

3. Python *** 作 Excel 之修改样式 3. 1 修改字体样式

# 修改样式import openpyxlfrom openpyxl.styles import colors, Font# 获取工作簿wb = openpyxl.Workbook()ws = wb.active# 更改sheet的namews.Title = '修改字体样式'# 修改单元格字体样式c3_Font = Font(name='宋体', size=24, italic=True, )ws['C3'].Font = c3_Fontws['C3'] = '宋体 24 italic'  # italic：斜体的;斜体字;a5_Font = Font('微软雅黑', size=18, bold=True, color=colors.BLUE)ws['A5'].Font = a5_Fontws['A5'] = '微软雅黑 18 bold blue'wb.save('style_excelTest.xlsx')

3. 2 设置单元格公式

import openpyxl from openpyxl.styles import Font from openpyxl.styles import colors wb = openpyxl.Workbook() ws = wb.active# 设置单元格公式ws = wb.create_sheet('设置单元格公式')ws['B1'] = 100ws['B2'] = 99ws['B3'].Font = a5_Fontws['B3'] = '=SUM(B1:B2)'

3.2 设置行高和列宽

import openpyxl from openpyxl.styles import Font from openpyxl.styles import colors wb = openpyxl.Workbook() ws = wb.active #设置行高和列宽 ws = wb.create_sheet('dimesions') ws['A1'] = 'Tall row' ws.row_dimensions[1].height = 70 ws['B2'] = 'WIDe column' ws.column_dimensions['B'].wIDth = 20 wb.save('style_excelTest.xlsx')

3.3 合并单元格

import openpyxl from openpyxl.styles import Font from openpyxl.styles import colors wb = openpyxl.Workbook()#合并单元格 ws = wb.create_sheet('merged') ws.merge_cells('A1:D3') ws['A1'] = 'Twelve cells merged together' ws.merge_cells('C5:D5') ws['C5'] ='Two merged cellswb.save('style_excelTest.xlsx')

3.4 拆分单元格

import openpyxl from openpyxl.styles import Font from openpyxl.styles import colors wb = openpyxl.Workbook() ws = wb.active #拆分单元格 ws = wb.copy_worksheet(wb.get_sheet_by_name('merged')) ws.Title = 'unmerged' ws.unmerge_cells('A1:D3') ws.unmerge_cells('C5:D5') wb.save('style_excelTest.xlsx')

4. Python *** 作 Excel 之图表 4.1 饼图

饼图将数据绘制为一个圆的切片，每个切片代表整个百分比。切片按顺时针方向绘制， 圆的顶部为 0°。

# 绘制饼图import openpyxlfrom openpyxl.chart import PIEChart, Reference, barChart, BubbleChart, ScatterChart  # Reference：图标所用信息from openpyxl.chart import SerIEs# 准备数据rows = [    ['PIE', 'Sold'],    ['Apple', 50],    ['Cherry', 30],    ['Pumpkin', 10],    ['Chocolate', 40]]# 将数据写入excel# 创建工作簿wb = openpyxl.Workbook()ws = wb.activews.Title = 'PIE Charts'for row in rows:    ws.append(row)# 绘制饼图pIE_chart = PIEChart()# 设置标题pIE_chart.Title = 'PIE sold by category'# 进行分类category = Reference(ws, min_col=1, min_row=2, max_row=5)data = Reference(ws, max_row=5)  # 数据所在第2列# 需要先添加数据再设置种类介绍# 添加数据pIE_chart.add_data(data)# 设置所分类别pIE_chart.set_categorIEs(category)# 在excel添加饼图ws.add_chart(pIE_chart, 'D1')  # 在D1位置绘制饼图# 保存wb.save('char_excel_text.xlsx')

4.2 条形图和柱形图

在条形图中，值被绘制为水平条或垂直列。可以通过 type 属性来设置。

绘制垂直的条形图则使用如下：

chart1.type = 'col

绘制成水平条形图示例如下：

chart1.type = 'bar

import openpyxlfrom openpyxl.chart import PIEChart, ScatterChart  # Reference：图标所用信息from openpyxl.chart import SerIEs# 绘制柱状图# 创建工作表ws = wb.create_sheet('bar Chart')# 准备数据rows = [    ('Number', 'Batch1', 'Batch2'),    (2, 10, 30),    (3, 40, 60),    (4, 50, 70),    (5, 20, 10),    (6, 40),    (7,]# 添加数据for row in rows:    ws.append(row)# 绘制柱状图bar_chart = barChart()bar_chart.type = 'col'  # col垂直、水平柱状图 barbar_chart.Title = 'bar Chart'bar_chart.style = 10  # 设置颜色，10的对比度最强，红色与蓝色# 设置横轴纵轴标题bar_chart.x_axis.Title = 'Sample length(mm)'bar_chart.y_axis.Title = 'Test number'# 设置分类category = Reference(ws, max_row=7)# 获取数据data = Reference(ws, max_col=3, min_row=1, max_row=7)# 柱状图对象添加数据bar_chart.add_data(data, Titles_from_data=True)  # Titles_from_data=True：根据来源设置数据标题# 设置分类bar_chart.set_categorIEs(category)# 工作页绘制柱状图，并指定位置ws.add_chart(bar_chart, 'E1')# 保存wb.save('char_excel_text.xlsx')

4.3 气泡图

气泡图类似于散点图，但使用第三维来确定气泡的大小。图表可以包括多个系列。

import openpyxlfrom openpyxl.chart import PIEChart, ScatterChart  # Reference：图标所用信息from openpyxl.chart import SerIEs# 绘制气泡图ws = wb.create_sheet('Bubble Chart')# 设置数据rows = [    ('Number of Products', 'Sales in USD', 'Marked share'),    (14, 12200, 15),    (20, 60000, 33),    (18, 2440,    (22, 3200, 42),    (),    (12, 8200, 18),    (15, 50000,    (19, 22400,    (25, 25000, 50),]# 添加数据for row in rows:    ws.append(row)# 获取气泡图对象bubble_chart = BubbleChart()bubble_chart.style = 10  # 设置颜色# 添加一组数据xvalues = Reference(ws, max_row=5)yvalues = Reference(ws, max_row=5)size = Reference(ws, min_col=3, max_row=5)# 创建SerIEs对象serIEs = SerIEs(values=yvalues, xvalues=xvalues, zvalues=size, Title='2013')bubble_chart.serIEs.append(serIEs)# 添加一组数据xvalues = Reference(ws, min_row=7, max_row=10)yvalues = Reference(ws, max_row=10)size = Reference(ws, max_row=10)serIEs = SerIEs(values=yvalues, Title='2014')bubble_chart.serIEs.append(serIEs)# 添加气泡表ws.add_chart(bubble_chart, 'E1')# 保存wb.save('char_excel_text.xlsx')

4.4 散点图

散点图或 xy 图类似于某些折线图。主要的区别是一个系列的值相对于另一个系列。当值无序时，这很有用。

import openpyxlfrom openpyxl.chart import PIEChart, ScatterChart  # Reference：图标所用信息from openpyxl.chart import SerIEs# 绘制散点图ws = wb.create_sheet('Scatter Chart')rows = [    ['Size', 'Batch2'],    [2, 25],    [3,    [4,    [5, 30,    [6, 25, 35],    [7, 40],]for row in rows:    ws.append(row)# 绘制散点图scatter_chart = ScatterChart()# 设置标题scatter_chart.Title = 'Scatter Chart'# 设置颜色scatter_chart.style = 13# 设置x轴y轴标题scatter_chart.x_axis.Title = 'Size'scatter_chart.y_axis.Title = 'Percentage'# 创建x轴的数据来源xvalues = Reference(ws, max_row=7)# 创建yvaluesfor i in range(2, 4):    yvalues = Reference(ws, min_col=i, max_row=7)    serIEs = SerIEs(yvalues, Title_from_data=True)    scatter_chart.serIEs.append(serIEs)# 将散点图添加到ws工作表中ws.add_chart(scatter_chart, 'E1')# 保存工作簿wb.save('charts.xlsx')

感谢SXT，感谢努力，加油！

总结

以上是内存溢出为你收集整理的【干货】建议收藏! ! !全网最全的Python.openpyxl *** 作Excel数据全部内容，希望文章能够帮你解决【干货】建议收藏! ! !全网最全的Python.openpyxl *** 作Excel数据所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。