Excel自动化操作神器 ---- openpyxl
目录
Excel安装库的对比
安装
基本操作
1.引入库
2.工作簿
创建或者加载工作簿
获取当前工作表
3.工作表
创建工作表和获取工作表
获取所有工作表
删除工作表
复制工作表
过滤和排序
使用公式
4.单元格
访问单元格
合并单元格
拆分单元格
单元格数据
单元格遍历
单元格样式
转化为pandas
6.图表生成
柱状图
圆饼图
折线图
散点图
7.保存工作簿
总结
Excel安装库的对比
在python中我们使用到的库有xlrd、xlwt、xlutils、xlwings、XlsxWriter、openpyxl 、pandas等，下面我给这个做一个详细的对比表，如下：
安装
安装命令：
pip install openpyxl
安装之后，可以在python中使用 import openpyxl 验证是否安装成功，没有报错就说明安装成功，反之安装失败。
基本操作
1.引入库
from openpyxl import Workbook,load_workbook
from openpyxl.styles import *
from openpyxl.chart import *
2.工作簿
在详细介绍工作簿相关的知识之前，我们把经常使用的属性和函数做个总结，如下：
创建或者加载工作簿
#创建和打开工作薄from openpyxl import Workbook,load_workbookwb = Workbook() #创建工作薄wb1 = load_workbook('test.xlsx') #打开已有工作薄获取当前工作表
from openpyxl import Workbook,load_workbookwb = Workbook() #创建工作薄ws = wb.active #获取当前工作表3.工作表
在详细介绍工作表相关的知识之前，我们把经常使用的属性和函数做个总结，如下：
创建工作表和获取工作表
#创建工作表和获取工作表 from openpyxl import Workbookwb = Workbook()ws = wb.create_sheet("sheet") #创建一个 sheet 名为 sheetws.title = "test" # 设置 sheet 标题ws1 = wb.create_sheet("sheet1", 0) # 创建一个 sheet，插入到最前面 默认插在后面ws1.title = "test1" # 设置 sheet 标题ws1.sheet_properties.tabColor = "1072BA" # 设置 sheet 标签背景色ws1 = wb['test'] # 获取 sheetws2 = wb.active #获取当前工作表wb.save('test.xlsx')获取所有工作表
import openpyxlwb = openpyxl.load_workbook('test.xlsx')sheets = wb.sheetnames #获取所有工作表print(sheets)for i in range(len(sheets)): #遍历所有工作表 sheet = wb[sheets[i]] print('第' + str(i + 1) + '个sheet: ' + sheet.title + ':') for r in range(1, sheet.max_row + 1): if r == 1: print(''.join([str(sheet.cell(row=r, column=c).value).ljust(17) for c in range(1, sheet.max_column + 1)])) else: print(''.join([str(sheet.cell(row=r, column=c).value).ljust(20) for c in range(1, sheet.max_column + 1)]))运行结果如下：
删除工作表
from openpyxl import Workbookwb = Workbook()ws = wb.create_sheet("test_1")ws.title = "test1"ws1 = wb.create_sheet("test_2")ws1.title = "test2"ws2 = wb.create_sheet("test_3")ws2.title = "test3"sheets = wb.sheetnamesprint(sheets)wb.remove(wb[sheets[1]]) #删除工作表wb.remove(wb["test2"]) #删除工作表del wb["test3"] #删除工作表复制工作表
import openpyxl src="test.xlsx"dest="dest.xlsx"wb = openpyxl.load_workbook(src)wsc1=wb.copy_worksheet(wb.worksheets[0])wsc2=wb.copy_worksheet(wb.worksheets[0])wsc1.title="test1"wb.save(dest)过滤和排序
from openpyxl import Workbookwb = Workbook()ws = wb.activedata = [ ["Fruit", "price"], ["banana", 15], ["Apple", 5], ["cherry", 50], ["pitaya", 3], ["Pear", 8],] # 按行写入数据for d in data: ws.append(d) # 设置筛选排序的单元格区域ws.auto_filter.ref = "A1:B6"# 筛选第1列里值是Apple/bananaws.auto_filter.add_filter_column(0, ["banana", "Apple"])# 按B列的值升序排序ws.auto_filter.add_sort_condition("B2:B6")wb.save("test1.xlsx")openpyxl也有过滤与排序功能，不过它只是完成了设置，并没有真正的实现过滤与排序。运行结果如下：
使用公式
from openpyxl import Workbookwb = Workbook()ws = wb.activedata = [ ["Fruit", "price"], ["banana", 15], ["Apple", 5], ["cherry", 50], ["pitaya", 3], ["Pear", 8], ["Sum", 0],]# 按行写入数据for d in data: ws.append(d)ws['B7'] = "=SUM(B2:B6)"wb.save("test1.xlsx")我们在操作Excel表格的时候经常使用到一些公式，如求和（SUM），条件判断（IF）等，而openpyxl也可以使用他们，它还有Tokenizer（解析公式），Translator（将公式从一个位置转移到另一个位置）等。上面例子代码运行结果如下：
4.单元格
在详细介绍单元格相关的知识之前，我们把经常使用的属性和函数做个总结，如下：
红色部分是单元格的样式属性相关的字段，通过导入库如下：
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protectionfrom openpyxl.styles import numbers访问单元格
from openpyxl import Workbookwb = Workbook()ws = wb.create_sheet("test_1")ws.title = "test1"print(ws["B"]) #访问某列单元格print(ws["2"]) #访问某行单元格print(ws["B2"]) #访问单元格print(ws.cell(1, 1)) #访问单元格print(ws["A:C"]) #访问多列单元格运行结果如下：
合并单元格
from openpyxl import Workbookwb = Workbook()ws = wb.create_sheet("test_1")ws.title = "test1"print("合并单元格前:")print(ws.merged_cells)print(ws.merged_cells.ranges)ws.merge_cells("A1:B1")ws.merge_cells(start_column=2,end_column=4,start_row=2,end_row=3)print("合并单元格后:")print(ws.merged_cells)print(ws.merged_cells.ranges)合并后的单元格，只会保留最上角的值，其他单元格的值全部为空(None)，运行结果如下：
拆分单元格
from openpyxl import Workbookwb = Workbook()ws = wb.create_sheet("test_1")ws.title = "test1"ws.merge_cells("A1:B1")ws.merge_cells(start_column=2,end_column=4,start_row=2,end_row=3)print("拆分单元格前:")print(ws.merged_cells)print(ws.merged_cells.ranges)ws.unmerge_cells('A1:B1')ws.unmerge_cells('B2:D3')print("拆分单元格后:")print(ws.merged_cells)print(ws.merged_cells.ranges)运行结果如下：
单元格数据
from openpyxl import Workbookwb = Workbook()ws = wb.create_sheet("test_1")ws.title = "test1"a = ws.cell(1, 1)a.value = 11print(a.value)单元格遍历
遍历单元格我们有三种方式：
1. 使用min_row,max_row和min_column，max_column
2.使用 rows或者columns
3.使用values
代码如下：
from openpyxl import Workbookwb = Workbook()ws = wb.create_sheet("test_1")ws.title = "test1"a = ws.cell(1, 1)a.value = 11ws.cell(2, 1).value = 12for i in ws.values: print(i)单元格样式
from openpyxl import Workbookfrom openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protectionfrom openpyxl.styles import numberswb = Workbook()ws = wb.create_sheet("test_1")ws.title = "test1"cell = ws.cell(1, 1)cell.value = 11cell.font = Font(name=u'宋体', size=12, bold=True, color='FF0000')cell.alignment = Alignment(horizontal='right')cell.fill = PatternFill(fill_type='solid', start_color='FF0000')cell.border = Border(left=Side(border_style='thin', color='FF0000'), right= Side(border_style='thin', color='FF0000'))cell.protection = Protection(locked=True, hidden=True)cell.number_format =numbers.FORMAT_PERCENTAGEprint("字体:" ,cell.font,)print("对齐:" ,cell.alignment)print("边框:" ,cell.border)print("填充:" ,cell.fill)print("数字格式:",cell.number_format)print("超链接:" ,cell.hyperlink)运行结果如下：
转化为pandas
from openpyxl import Workbookimport pandas as pdwb = Workbook()ws = wb.create_sheet("test_1")ws.title = "test1"cell = ws.cell(1, 1)cell.value = 11d = pd.DataFrame(ws.values)print(d)print("\n")for i in d.values: ws.append(i.tolist()) #向后追加数据#pandas转化为工作表数据for m in range(ws.min_column,ws.max_column+1): for n in range(ws.min_row,ws.max_row+1): print(m,n, ws.cell(n,m).value)运行结果如下：
6.图表生成
图表操作主要使用到PieChart, Reference, BarChart, BubbleChart, ScatterChart ,Series, DataLabelList, RichText, Paragraph, ParagraphProperties, CharacterProperties等
柱状图
# 绘制饼图import openpyxlfrom openpyxl import Workbookfrom openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart # Reference：图标所用信息from openpyxl.chart import Seriesfrom openpyxl.chart.label import DataLabelListfrom openpyxl.chart.text import RichTextfrom openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterPropertieswb = Workbook()ws = wb.activedata = [ ["Fruit", "price", "num"], ["banana", 15,1], ["Apple", 5,2], ["cherry", 50,4], ["pitaya", 3,2], ["Pear", 8,5],] # 按行写入数据for d in data: ws.append(d) ws.title = 'Pie Charts'# 绘制柱状图bar_chart = BarChart()bar_chart.type = 'col' # col垂直、水平柱状图 bar# 设置标题bar_chart.title = 'Fruit price Bar'# 进行分类category = Reference(ws, min_col=1, min_row=2, max_row=6)data = Reference(ws, min_col=2, min_row=1, max_row=6, max_col=3) # 数据所在第2列# 需要先添加数据再设置种类介绍# 添加数据bar_chart.add_data(data,titles_from_data=True)# 设置所分类别bar_chart.set_categories(category)# 设置横轴纵轴标题bar_chart.x_axis.title = 'Fruit'bar_chart.y_axis.title = 'price'bar_chart.style = 10 # 图表样式类型bar_chart.height = 10 # 图表高度bar_chart.width = 15 # 图表宽度s1 = bar_chart.series[0]s1.dLbls = DataLabelList()s1.dLbls.showCatName = True # 标签显示s1.dLbls.showVal = True # 数量显示axis = CharacterProperties(sz=900) # 图表中字体大小 *100s1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])# 在excel添加饼图ws.add_chart(bar_chart, 'D5') # 在D5位置绘制柱状图# 保存wb.save('fruit_price.xlsx')如果想生成三维图，可以使用BarChart3D，上面例子运行结果如下：
圆饼图
# 绘制饼图import openpyxlfrom openpyxl import Workbookfrom openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart # Reference：图标所用信息from openpyxl.chart import Seriesfrom openpyxl.chart.label import DataLabelListfrom openpyxl.chart.text import RichTextfrom openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterPropertieswb = Workbook()ws = wb.activedata = [ ["Fruit", "price"], ["banana", 15], ["Apple", 5], ["cherry", 50], ["pitaya", 3], ["Pear", 8],] # 按行写入数据for d in data: ws.append(d) ws.title = 'Pie Charts'# 绘制饼图pie_chart = PieChart()# 设置标题pie_chart.title = 'Fruit price category'# 进行分类category = Reference(ws, min_col=1, min_row=2, max_row=6)data = Reference(ws, min_col=2, min_row=2, max_row=6) # 数据所在第2列# 需要先添加数据再设置种类介绍# 添加数据pie_chart.add_data(data)# 设置所分类别pie_chart.set_categories(category)pie_chart.style = 10 # 图表样式类型pie_chart.height = 10 # 图表高度pie_chart.width = 15 # 图表宽度s1 = pie_chart.series[0]s1.dLbls = DataLabelList()s1.dLbls.showCatName = True # 标签显示#s1.dLbls.showVal = True # 数量显示s1.dLbls.showPercent = True # 百分比显示axis = CharacterProperties(sz=900) # 图表中字体大小 *100s1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])# 在excel添加饼图ws.add_chart(pie_chart, 'D5') # 在D5位置绘制饼图# 保存wb.save('fruit_price.xlsx')运行结果如下：
折线图
# 绘制饼图import openpyxlfrom openpyxl import Workbookfrom openpyxl.chart import LineChart,PieChart, Reference, BarChart, BubbleChart, ScatterChart # Reference：图标所用信息from openpyxl.chart import Seriesfrom openpyxl.chart.label import DataLabelListfrom openpyxl.chart.text import RichTextfrom openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterPropertieswb = Workbook()ws = wb.activedata = [ ["Fruit", "price", "num"], ["banana", 15,1], ["Apple", 5,2], ["cherry", 50,4], ["pitaya", 3,2], ["Pear", 8,5],] # 按行写入数据for d in data: ws.append(d) ws.title = 'Line Charts'# 绘制散点图line_chart = LineChart()# 设置标题line_chart.title = 'Fruit price Line'data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=6)line_chart.add_data(data, titles_from_data=True)line_chart.y_axis.title = 'price' # Y轴line_chart.x_axis.title = 'Fruit' # X轴line_chart.style = 10 # 图表样式类型line_chart.height = 10 # 图表高度line_chart.width = 15 # 图表宽度s1 = line_chart.series[0]s1.marker.symbol = "triangle" # triangle为三角形标记， 可选circle、dash、diamond、dot、picture、plus、square、star、triangle、x、autos1.marker.graphicalProperties.solidFill = "FF0000" # 填充颜色s1.marker.graphicalProperties.line.solidFill = "0000FF" # 边框颜色s1.smooth = True # 线条平滑s2 = line_chart.series[1]s2.graphicalProperties.line.solidFill = "00AAAA"s2.graphicalProperties.line.dashStyle = "sysDot" # 线条点状样式s2.graphicalProperties.line.width = 80000 # 线条大小，最大20116800EMUss2.smooth = True # 线条平滑# 将折线图添加到ws工作表中ws.add_chart(line_chart, 'D5') # 保存wb.save('fruit_price.xlsx')运行结果如下：
散点图
# 绘制饼图import openpyxlfrom openpyxl import Workbookfrom openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart # Reference：图标所用信息from openpyxl.chart import Seriesfrom openpyxl.chart.label import DataLabelListfrom openpyxl.chart.text import RichTextfrom openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterPropertieswb = Workbook()ws = wb.activedata = [ ["Fruit", "price", "num"], ["banana", 15,1], ["Apple", 5,2], ["cherry", 50,4], ["pitaya", 3,2], ["Pear", 8,5],] # 按行写入数据for d in data: ws.append(d) ws.title = 'Scatter Charts'# 绘制散点图scatter_chart = ScatterChart()# 设置标题scatter_chart.title = 'Fruit price Scatter'# 创建x轴的数据来源xvalues = Reference(ws, min_col=1, min_row=2, max_row=6)# 创建yvaluesfor i in range(2, 4): yvalues = Reference(ws, min_col=i, min_row=1, max_row=6) series = Series(yvalues, xvalues=xvalues, title_from_data=True) scatter_chart.series.append(series)# 设置横轴纵轴标题scatter_chart.x_axis.title = 'Fruit'scatter_chart.y_axis.title = 'price'scatter_chart.style = 10 # 图表样式类型scatter_chart.height = 10 # 图表高度scatter_chart.width = 15 # 图表宽度s1 = scatter_chart.series[0]s1.dLbls = DataLabelList()s1.dLbls.showCatName = True # 标签显示s1.dLbls.showVal = True # 数量显示axis = CharacterProperties(sz=900) # 图表中字体大小 *100s1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])# 将散点图添加到ws工作表中ws.add_chart(scatter_chart, 'D5')# 保存wb.save('fruit_price.xlsx')运行结果如下：
7.保存工作簿
wb.save(“test.xlsx”)
总结
本文章主要是介绍openpyxl对excel操作的常用方法和属性，如果你想更深入的理解openpyxl，我推荐你去看看openpyxl官网。希望该文章对你有所帮助，哈哈哈哈哈哈~ 感谢阅读！
