Python实现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_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官网。希望该文章对你有所帮助，哈哈哈哈哈哈~ 感谢阅读！

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。

Excel安装库的对比

安装

基本操作

1.引入库

2.工作簿

创建或者加载工作簿

获取当前工作表

3.工作表

创建工作表和获取工作表

获取所有工作表

删除工作表

复制工作表

过滤和排序

使用公式

4.单元格

访问单元格

​ 合并单元格

拆分单元格

单元格数据

单元格遍历

单元格样式

转化为pandas

6.图表生成

柱状图

圆饼图

折线图

散点图

7.保存工作簿

总结

合并单元格