目录
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_workbook
wb = Workbook() #创建工作薄
wb1 = load_workbook('test.xlsx') #打开已有工作薄获取当前工作表
from openpyxl import Workbook,load_workbook
wb = Workbook() #创建工作薄
ws = wb.active #获取当前工作表3.工作表
在详细介绍工作表相关的知识之前，我们把经常使用的属性和函数做个总结，如下：
创建工作表和获取工作表
#创建工作表和获取工作表
from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet("sheet") #创建一个 sheet 名为 sheet
ws.title = "test" # 设置 sheet 标题
ws1 = wb.create_sheet("sheet1", 0) # 创建一个 sheet，插入到最前面 默认插在后面
ws1.title = "test1" # 设置 sheet 标题
ws1.sheet_properties.tabColor = "1072BA" # 设置 sheet 标签背景色
ws1 = wb['test'] # 获取 sheet
ws2 = wb.active #获取当前工作表
wb.save('test.xlsx')获取所有工作表
import openpyxl
wb = 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 Workbook
wb = 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.sheetnames
print(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 Workbook
wb = Workbook()
ws = wb.active
data = [
["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/banana
ws.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 Workbook
wb = Workbook()
ws = wb.active
data = [
["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, Protection
from openpyxl.styles import numbers访问单元格
from openpyxl import Workbook
wb = 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 Workbook
wb = 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 Workbook
wb = 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 Workbook
wb = Workbook()
ws = wb.create_sheet("test_1")
ws.title = "test1"
a = ws.cell(1, 1)
a.value = 11
print(a.value)单元格遍历
遍历单元格我们有三种方式：
1. 使用min_row,max_row和min_column，max_column
2.使用 rows或者columns
3.使用values
代码如下：
from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet("test_1")
ws.title = "test1"
a = ws.cell(1, 1)
a.value = 11
ws.cell(2, 1).value = 12
for i in ws.values:
print(i)单元格样式
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protection
from openpyxl.styles import numbers
wb = Workbook()
ws = wb.create_sheet("test_1")
ws.title = "test1"
cell = ws.cell(1, 1)
cell.value = 11
cell.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_PERCENTAGE
print("字体:" ,cell.font,)
print("对齐:" ,cell.alignment)
print("边框:" ,cell.border)
print("填充:" ,cell.fill)
print("数字格式:",cell.number_format)
print("超链接:" ,cell.hyperlink)运行结果如下：
转化为pandas
from openpyxl import Workbook
import pandas as pd
wb = Workbook()
ws = wb.create_sheet("test_1")
ws.title = "test1"
cell = ws.cell(1, 1)
cell.value = 11
d = 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 openpyxl
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart
# Reference：图标所用信息
from openpyxl.chart import Series
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties
wb = Workbook()
ws = wb.active
data = [
["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) # 图表中字体大小 *100
s1.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 openpyxl
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart
# Reference：图标所用信息
from openpyxl.chart import Series
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties
wb = Workbook()
ws = wb.active
data = [
["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) # 图表中字体大小 *100
s1.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 openpyxl
from openpyxl import Workbook
from openpyxl.chart import LineChart,PieChart, Reference, BarChart, BubbleChart, ScatterChart
# Reference：图标所用信息
from openpyxl.chart import Series
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties
wb = Workbook()
ws = wb.active
data = [
["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、auto
s1.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 # 线条大小，最大20116800EMUs
s2.smooth = True # 线条平滑
# 将折线图添加到ws工作表中
ws.add_chart(line_chart, 'D5')
# 保存
wb.save('fruit_price.xlsx')运行结果如下：
散点图
# 绘制饼图
import openpyxl
from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference, BarChart, BubbleChart, ScatterChart
# Reference：图标所用信息
from openpyxl.chart import Series
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import Paragraph, ParagraphProperties, CharacterProperties
wb = Workbook()
ws = wb.active
data = [
["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)
# 创建yvalues
for 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) # 图表中字体大小 *100
s1.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官网。希望该文章对你有所帮助，哈哈哈哈哈哈~ 感谢阅读！
9520
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
