Python实现Excel办公自动化之openpyxl_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官网。希望该文章对你有所帮助，哈哈哈哈哈哈~ 感谢阅读！