Python处理excel的强大工具

Python实现自动化办公、自动化测试数据驱动，都离不开对excel的操作，下面简单介绍下，如何使用Python的openpyxl库处理excel文档。

首先，Python处理excel的第三方库有：xlrd，xlwt，openpyxl等。

其中xlrd只能读excel，xlwt只能写excel，所以今天我们就重点了解一下openpyxl，它既可以读写数据，还能操作工作表，比如生成柱状图等。

安装openpyxl

pip install openpyxl

当然，也可以用国内镜像安装：

pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple

Excel基本概念

·工作簿(workbook)：一个 Excel 文档；·工作表(sheet)：一个工作簿可以包含多个表, 如：sheet1， sheet2等;·列(column): 列地址是从 A 开始的;·行(row): 行地址是从 1 开始的;·单元格(cell)：指定行和列的格；

Excel操作

Excel不管读写都是“三板斧”：加载workbook，打开sheet，操作cell

现有工作簿“学生表.xlsx”如下图：

1.首先导入模块：

from openpyxl import load_workbook

2.加载工作簿：

可以使用相对路径也可使用绝对路径

wb = load_workbook("学生表.xlsx")wb = load_workbook("D:\Python_exercises\学生表.xlsx")

3.打开工作表

加载工作簿后，我们要打开一个工作表：

#打开工作表两种方式：#方式一：通过工作表名称打开工作表sheet=wb["sheet1"]#方式二：获取活跃的工作表sheet=wb.active    #['sheet1']

顺带介绍一下工作表的其他操作：

#获取所有的工作表wb.sheetnames    #['sheet1']#修改工作表名称sheet.title="students"#获取工作表名称sheet.title    #students

4.单元格常用操作

加载工作簿，打开工作表后就可以操作单元格了，以下是对单元格操作的介绍：

获取单元格（指定行，指定列）

sheet.cell(2,3)    #<Cell 'students'.C2>sheet["C2"]    #<Cell 'students'.C2>

如果要获取单元格的值，使用value属性：

#方式一sheet.cell(2,3).value    #60#方式二sheet["C2"].value    #60

往单元格（指定行，指定列）中写入值

#方式一sheet.cell(2,4).value="及格"#方式二sheet["D3"]="及格"#方式三sheet.cell(4,4,"良好")#保存工作簿wb.save("学生表.xlsx")

获取工作表的最大行和最大列

#获取最大行：sheet.max_row    #8#获取最大列：sheet.max_column    #4

获取多个单元格

方法一：切片方法，结果为一个元组#获取A1到A3的单元格cell1=sheet['A1:A3']#获取第一行和第二行的单元格cell2=sheet[1:2]#获取A列到C列的单元格cell3=sheet['A:C']

方法二：iter_rows 和 iter_cols 方法，此方法得到的是一个可迭代序列#获取行数1-2且列数1-4的单元格，逐行读取cell4=sheet.iter_rows(min_row=1, max_row=2, min_col=1, max_col=4)for cell in cell4:print(cell)# 上面获取的是单元格，如果想得到单元格的值，只需加个参数cell4=sheet.iter_rows(min_row=1, max_row=2, min_col=1, max_col=4,values_only=True)#获取列数1-4且行数1-2的单元格，逐列读取cell5=sheet.iter_cols(min_col=1, max_col=4,min_row=1, max_row=2)for cell in cell5:print(cell)#同样，想获取单元格的值，加参数cell5=sheet.iter_cols(min_col=1, max_col=4,min_row=1, max_row=2,values_only=True)

5.插入/删除行、列

#插入行sheet.insert_rows(idx=数字编号,amount=要插入行数)#删除行sheet.delete_rows(idx=数字编号,amount=要插入行数)

#从第二行开始插入三行sheet.insert_rows(idx=2,amount=3)

#第2行开始删除3行sheet.delete_rows(idx=2,amount=3)#插入列sheet.insert_cols(idx=数字编号,amount=要插入列数)#删除列sheet.delete_cols(idx=数字编号,amount=要插入列数)

生成折线图和柱状图

现有Excel文档“销售数据.xlsx"，表中数据内容如下：

现在我们在excel中生成折线图，结果如下：

我们还可以生成柱状图：

代码如下：

from openpyxl import load_workbook# 生成折线图from openpyxl.chart import LineChart,Referencewb=load_workbook("销售数据.xlsx")sheet=wb.activechart=LineChart()# 图的标题chart.title="手机销售数据统计"# y轴标题chart.y_axis.title="销量（单位：万台）"# x轴标题chart.x_axis.title="季度"# 数据来源data=Reference(worksheet=sheet,min_row=2,max_row=4,min_col=1,max_col=5)# 设定X轴项目名称,项目名称来自第一行的第二列至第五行categories=Reference(sheet,min_col=2,min_row=1,max_col=5,max_row=1)# 给折线图添加数据，数据源中有系列名称，系列名称来自行，第一行为系列名称chart.add_data(data,from_rows=True,titles_from_data=True)chart.set_categories(categories)sheet.add_chart(chart,"B6")wb.save("销售数据.xlsx")

from openpyxl import load_workbook#生成柱状图from openpyxl.chart import BarChart,Referencewb=load_workbook("销售数据.xlsx")sheet=wb.activebc=BarChart()# 图的标题bc.title="手机销售数据统计"# y轴标题bc.y_axis.title="销量（单位：万台）"# x轴标题bc.x_axis.title="季度"# 数据来源bc_data=Reference(worksheet=sheet,min_row=2,max_row=4,min_col=1,max_col=5)# 设定X轴项目名称,项目名称来自第一行的第二列至第五行bc_cat=Reference(sheet,min_col=2,min_row=1,max_col=5,max_row=1)# 给柱状图添加数据，数据源中有系列名称，系列名称来自行，第一行为系列名称bc.add_data(bc_data,from_rows=True,titles_from_data=True)bc.set_categories(bc_cat)sheet.add_chart(bc,"B6")wb.save("销售数据.xlsx")