使用Python库openpyxl处理Excel

从网页爬下来的大量数据需要excel来清洗
成堆的科学实验数据需要导入到excel分析
领导安排的工作需要使用excel重复性操作
… …
作为一名伪程序员,怎么能不利用Python来为我效劳呢?

第三方库对比

环境配置对比

以上所有库都支持Python2和Python3,其中xlutils仅支持xls文件,即2003以下版本;win32com与DataNitro仅支持Windows。

文档操作对比

由于设计模式的不同,导致基本的新建文件、修改文件、保存文件等功能在不同的库中存在着一定差异,比如xlsxwriter并不支持打开或修改现有文件,xlwings不支持对新建文件的命名等等,DataNitro作为excel插件依托于excel本身的操作。

性能对比

分别使用不同库进行添加及读取 1000行 * 700列 数据操作,得到所用时间,重复操作取平均值。另外在不同的电脑配置,不同的环境下结果肯定会有出入,数据仅供参考。

openpyxl使用

通过对比,最终我选择了openpyxl,并使用pip安装它:

1
pip install openpyxl

创建、保存工作簿及工作表

无需事先在文件系统上创建文件即可开始使用openpyxl。

1
2
3
4
#导入模块
from openpyxl import Workbook
#实例化Workbook类
wb = Workbook()

一个工作簿建立时至少有一张工作表,使用openpyxl.workbook.Workbook.active()激活:

1
ws = wb.active

或者使用Workbook.create_sheet()创建新的工作表:

1
2
3
4
#在最后插入新工作表
ws1 = wb.create_sheet("Mysheet1")
#在指定位置插入新工作表,起始为0
ws2 = wb.create_sheet("Mysheet2", 0)

工作表创建时会自动为其命名,并按顺序编号(Sheet,Sheet1,Sheet2,…)。可以随时使用Worksheet.title属性更改表名:

1
ws.title = "Mysheet3"

可以将表名作为工作簿的一个键来激活某一工作表:

1
ws3 = wb["Mysheet3"]

可以使用Workbook.sheetname属性查看工作簿的所有工作表的名称:

1
2
print(wb.sheetnames)
#['Sheet2', 'New Title', 'Sheet1']

也可以遍历所有工作表:

1
2
for sheet in wb:
print(sheet.title)

可以使用Workbook.copy_worksheet() 方法创建工作表的副本:

1
2
source = wb.active
target = wb.copy_worksheet(source)

最后,使用Workbook.save()方法保存工作簿:

1
2
wb = Workbook()
wb.save('save.xlsx')

这里要注意,这个操作将覆盖现有同名文件而不发出警告。

载入现有工作簿

可以使用openpyxl.load_workbook()打开现有工作簿(如果原文件有一些图片或者图标,则保存的时候可能会导致图片丢失):

1
2
3
4
5
#read_only默认false
from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx', read_only=True)
print(wb2.sheetnames)
#['Sheet2', 'New Title', 'Sheet1']

以下标志可以在load_workbook中使用:

  • guess_types:将在读取单元格时启用或禁用类型推断,默认禁用。
  • data_only:控制具有公式的单元格是具有公式(False)还是上次Excel读取工作表时存储的值(True),默认具有公式(False)。data_only=True状态下打开,如果最后用save()函数保存了,则原xlsx文件中,公式会被替换为常数结果或空值。而data_only=False状态下打开,最后用save()函数保存了的话,原xlsx文件也会只剩下data_only=False的那套值(即公式),另一套(data_only=True)的值会丢失,如想重新获得两套值,则仍旧需要用Excel程序打开该文件并保存。
  • keep_vba:控制是否保留任何Visual Basic元素。如果保留它们仍然不可编辑,默认否。
1
2
3
4
5
6
7
8
9
10
11
12
13
wb = load_workbook('e:\\sample.xlsx')

wb.guess_types = True
ws=wb.active
ws["D1"]="12%"
print(ws["D1"].value)
#结果为小数

wb.guess_types = False
ws=wb.active
ws["D1"]="12%"
print(ws["D1"].value)
#结果为百分数

处理单一单元格

可以直接使用单元格的坐标,或者使用Worksheet.cell()方法:

1
2
3
4
5
#使用坐标获取
c = ws['A4']
print(c)
#Worksheet.cell()方法获取'A4'并赋值
d = ws.cell(row=4, column=1, value=10)

可以使用单元格坐标进行写入,也可以使用append方法逐行写入:

1
2
ws['A1'] = 123
ws.append([1, 2, 3])

获取单元格的值只需使用value属性:

1
print(ws['A3'].value)

处理批量单元格

可以通过仅指定行或列坐标来获取单行或单列的对象,也可以通过切片访问多行或多列:

1
2
3
4
5
6
#指定单一坐标
colA = ws['A']
row2 = ws[2]
#切片
col_range = ws['C:D']
row_range = ws[5:10]

想要获取批量单元格的值,就要使用value属性进行遍历:

1
2
3
4
5
6
7
8
#操作多列
for column in ws["A:C"]:
for cell in column:
print(cell.value)
#操作多行
for row in ws[1:3]:
for cell in row:
print(cell.value)

使用Worksheet.iter_rows()和Worksheet.iter_cols()方法获取某一块区域单元格,区别是一个按行返回,一个按列返回:

1
2
3
4
5
6
7
#添加参数values_only=True,可以只获取值
for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3, values_only=True):
for cell in row:
print(cell.value)
for col in ws.iter_cols(min_row=1, min_col=1, max_col=3, max_row=3, values_only=True):
for cell in col:
print(cell)

也可以使用该方法获取任意单元格对象:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
rows=[]
for row in ws.iter_rows():
rows.append(row)
print(rows) #所有行
print(rows[0]) #获取第一行
print(rows[-1]) #获取最后一行
print(rows[0][0]) #获取第一行第一列的单元格对象
print(rows[len(rows)-1][len(rows[0])-1]) #获取最后一行最后一列的单元格对象

cols=[]
for col in ws.iter_cols():
cols.append(col)
print(cols) #所有列
print(cols[0]) #获取第一列
print(cols[-1]) #获取最后一列
print(cols[0][0]) #获取第一列第一行的单元格对象
print(cols[len(cols)-1][len(cols[0])-1]) #获取最后一列最后一行的单元格对象

如果仅想获取所有行或所有列也可以使用rows、columns属性:

1
2
3
4
5
6
7
8
#获取所有行
for row in ws.rows:
for cell in row:
print(cell)
#获取所有列
for col in ws.columns:
for cell in col:
print(cell)

单元格特殊格式

写入时间格式字符串:

1
2
3
4
5
6
import datetime
import time
#写入当前时间
ws['A3'] = datetime.datetime.now()
#写入自定义格式的时间
ws['A4'] = time.strftime("%Y年%m月%d日 %H时%M分%S秒", time.localtime())

使用公式:

1
2
3
4
5
ws["A4"] = "=SUM(1, 1)"
ws["A5"] = "=SUM(A1:A3)"
print(ws["A4"].value)
print(ws["A5"].value)
#打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值

可以使用openpyxl.utils检查公式名称:

1
2
3
from openpyxl.utils import FORMULAE
print("HEX2DEC" in FORMULAE)
#True

合并/拆分单元格

1
2
3
4
5
ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')
# 或者
ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)

合并后的单元格,脚本单独执行拆分操作会报错,需要重新执行合并操作再拆分。

单元格样式

使用openpyxl.styles设定单元格格式,包括以下几类:

  • Font:来设置文字的大小,颜色和下划线等
1
2
3
4
from openpyxl.styles import Font
font = Font(u'宋体',size = 11,bold=True,italic=True,strike=True,color='000000')
ws.['A1'].font = font
#设置字体为“宋体”,大小为11,bold为加粗,italic为斜体,strike为删除线,颜色为黑色
  • PatternFill:填充图案和渐变色
1
2
3
4
5
6
7
8
9
10
11
12
from openpyxl.styles import PatternFill
fill = PatternFill("solid", fgColor="1874CD")
ws.['B1'].fill = fill
#'solid'代表纯色填充,fgColor 是对应的颜色代码:[http://tool.oschina.net/commons?type=3](http://tool.oschina.net/commons?type=3)。
#Excel2010中的标准色如下:
黑色 = 000000 白色 = FFFFFF
深红 = C00000 红色 = FF0000
橙色 = FFC000 黄色 = FFFF00
浅绿 = 92D050 绿色 = 00B050
浅蓝 = 00B0F0 蓝色 = 0070C0
深蓝 = 002060 紫色 = 7030A0
#注意,官方文档中写明,fill_type若没有特别指定类型,则后续的参数都无效。
  • Border:单元格的边框
1
2
3
4
5
6
7
8
from openpyxl.styles import Border,Side
border = Border(left=Side(border_style='thin',color='000000'),
right=Side(border_style='thin',color='000000'),
top=Side(border_style='thin',color='000000'),
bottom=Side(border_style='thin',color='000000'))
ws.['C1'].border = border
#left,right,top,bottom指左右上下边框。
#边框样式有:"'dashDot','dashDotDot','dashed','dotted','double','hair','medium','mediumDashDot','mediumDashDotDot','mediumDashed','slantDashDot','thick','thin'
  • Alignment:单元格的对齐方式等
1
2
3
4
5
6
from openpyxl.styles import Alignment
align = Alignment(horizontal='left',vertical='center',wrap_text=True)
ws.['D1'].alignment = align
#horizontal代表水平方向,可以左对齐left,还有居中center和右对齐right,分散对齐distributed,跨列居中centerContinuous,两端对齐justify,填充fill,常规general。
#vertical代表垂直方向,可以居中center,还可以靠上top,靠下bottom,两端对齐justify,分散对齐distributed。
#wrap_text代表自动换行,布尔类型参数,这个参数还可以写作wrapText。
  • Protection:写保护
1
2
from openpyxl.styles import Protection
protection = Protection(locked=True,hidden = True)

默认样式如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
font = Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000')
fill = PatternFill(fill_type=None,
start_color='FFFFFFFF',
end_color='FF000000')
#边框可选为:['hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin']
border = Border(left=Side(border_style=None,
color='FF000000'),
right=Side(border_style=None,
color='FF000000'),
top=Side(border_style=None,
color='FF000000'),
bottom=Side(border_style=None,
color='FF000000'),
diagonal=Side(border_style=None,
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style=None,
color='FF000000'),
vertical=Side(border_style=None,
color='FF000000'),
horizontal=Side(border_style=None,
color='FF000000')
)
alignment=Alignment(horizontal='general',
vertical='bottom',
text_rotation=0,
wrap_text=False,
shrink_to_fit=False,
indent=0)
number_format = 'General'
protection = Protection(locked=True,
hidden=False)

注意,以上几种样式(字体、填充、边框、位置和保护)实例一旦被创建实例的属性就不可更改,只能重新创建实例:

1
2
3
4
5
6
a1 = ws['A1']
ft = Font(color=colors.RED)
a1.font = ft
a1.font.italic = True #不会生效,样式实例一旦创建,实例的属性就不可改变
# 若要改变样式,必须新建样式实例
a1.font = Font(color=colors.RED, italic=True) # 创建字体实例(红色、斜体,其他属性默认)

样式可以被复制并加以修改:

1
2
ft1 = Font(name='Arial', size=14)
ft2 = ft1.copy(name="Tahoma") # 复制时指定字体为“Tahoma”,其他属性均复制自ft1

字体颜色里有一些常量可以直接调用:

1
2
3
4
from openpyxl.styles import Font
from openpyxl.styles import colors
font = Font(color=colors.RED)
font = Font(color="00FFBB00")

应用到单一单元格:

1
2
c = ws['A1']
c.font = Font(size=12)

应用到批量单元格:

1
2
3
4
col = ws.column_dimensions['A']
col.font = Font(bold=True)
row = ws.row_dimensions[1]
row.font = Font(underline="single")

应用到合并单元格即应用到左上角单元格。

1
2
3
4
5
6
7
8
ws.merge_cells('B2:F4')
top_left_cell = ws['B2']
thin = Side(border_style="thin", color="000000")
double = Side(border_style="double", color="ff0000")
top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)
top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD")
top_left_cell.font = Font(b=True, color="FF0000")
top_left_cell.alignment = Alignment(horizontal="center", vertical="center")

命名样式

创建命名样式,可以在工作簿中注册它,并快速使用:

1
2
3
4
5
6
from openpyxl.styles import NamedStyle, Font, Border, Side
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
ws['A1'].style = highlight

与单元格样式相比,命名样式是可变的。一旦为单元格指定了命名样式,对样式的其他更改将不会影响单元格。

过滤和排序

要添加过滤器,先定义范围,然后添加列和排序条件:

1
2
3
4
5
6
#定义需要过滤的单元格范围
ws.auto_filter.ref = "A1:B15"
#为指定列添加行筛选器,0代表以第一列为目标过滤,["Kiwi", "Apple", "Mango"]代表要显示的行,blank = True代表显示具有空白单元格的行,默认False。
ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"],blank = True)
#为指定范围的单元格添加排序条件,"B2:B15"代表排序列,descending=True表示降序,默认False。
ws.auto_filter.add_sort_condition("B2:B15", descending=True)

如果今后用到其他例如图表等功能,再来进行补充,目前来看用不到。

但愿我的博文能对您有所帮助~