基于Python 第三方库实现 Excel读写

陈衍鹏

(广东电网有限责任公司 佛山供电局， 佛山 528000)

基于Python 第三方库实现 Excel读写

陈衍鹏

(广东电网有限责任公司 佛山供电局， 佛山 528000)

介绍了基于Python第三方库pyExcelerator与xlrd实现Excel读写的方法，着重介绍了xlrd读取Excel内容和 pyExcelerator修改与生成带格式 的 Excel 文件的实现细节。pyExcelerator与xlrd可以广泛应用于部署在非Windows操作系统下的应用程序，方便程序调用 Excel 报表信息，并进行二次编辑或者生成的操作，特别是一些系统自动导出 Excel 文件、网络游戏 Excel 报表等场合，具有极大的应用价值。

Python； pyExcelerator； xlrd； Excel

0 引言

Python 语言一种解释型、交互式、纯面向对象的脚本程序设计语言，它结合了多种不同语言最好的设计原则和思想，在软件开发的各个领域都得到了广泛的应用。Python 是一种跨平台的程序设计语言，在 Windows、Unix、Linux 甚至各种嵌入式操作系统中都得到支持，并且其生成的字节码具有平台无关性，可以在各种平台中不经修改而直接运行。Python语言的强大之处在于它有丰富和强大的类库，可以方便地处理工作中各种需求。对于简化程序的代码，起到了很大的作用！库很多，博大精深，这样形容python最恰当不过了。正因为其强大的库，让编程不再艰难。我们只需要调用库中的函数，而对于函数的具体实现，没有特殊需求，我们无需探究。这无疑是编码者的福音。其次，与C/C++相比，所有语句末尾无需再添加繁琐的“；”(分号)，因为每一行就是一个语句，并且使用简单而美观的缩进，规范了变量和语句的执行域，而摒弃了一贯使用的“{}”(花括号)作为界限，不仅使语句更加富有可读性，而且还为代码的编写者提供了良好的代码习惯。

Excel 是 Windows 平台下主流的电子表格处理软件，可以进行各种数据的处理、统计分析和辅助决策操作，广泛应用于管理、统计财经、金融等多个财务领域。实际应用中有时需要用编程生成脚本的方式对 Excel 文件进行自动化处理，比如生成 Excel 格式的报表、整合多个Excle文件的表格内容、从多个 Excel 文件提取相关信息进行重运算等。这些操作在 Windows 操作系统环境下可以通过 VB编程实现，但在Linux、Unix等其他操作系统环境下由于没有 VBA 运行环境而不能轻易实现，这只能通过直接读写 Excel 文件的方式进行操作。Python作为跨平台的语言，很多类库和组件是平台无关的，其中 pyExcelerator 与xlrd就是可以用来跨平台操作 Excel 文件的类库之一。文中详细介绍使用第三方库pyExcelerator与xlrd 读写 Excel 的方法。

1 pyExcelerator与xlrd库

pyExcelerator 是一个主要用于产生 Excel 文件的库，pyExcelerator 完全支持 UNICODE，并且支持各种格式设置，也提pyExcelerator 主要通过两个对象对 Excel 进行操作 ：Workbook 和 Worksheet，分别对应 Excel 的 Book 和 Sheet，一个 Workbook 可以包含多个 Worksheet。pyExcelerator读取数据不方便，因为它不提供表格的行数、列数、单元格数据类型等关键信息的访问接口，也不支持空数据的存在，因此你没法对它进行循环操作。它读取数据的机制是这样的：sheets=parse_xls(filename) 这个sheets返回的是一个列表，每一项是一张sheet的数据，每一项本身是一个二元组的数据结构(表名，单元格数据)，单元格数据又是一个字典结构，key是单元格的索引(i,j)，value是单元格的数据。所以，如果你想获取此excel文件的第N张工作表的第i行第j列的单元格数据，则应该是这样：sheets[n][1][(i,j)]。

XLRD模块是纯Python编写的Excel表格处理接口模块，相比pyExcelerator的读取操作更加便捷和简单。在完成excel文件打开后，可以sheet_by_index()函数获取对应的sheet，再通过cell_value(x,y)函数获取对应单元格的值。当然也可以通过col_values(i)获取整列数据，返回的数据是列表型，使用sh.nrows或sh.ncols获取行数和列数。因此，xlrd库提供了更为快捷的excel读取方式，单该库不能实现excel的写操作。

2 读取 Excel 文件

2.1 使用pyExcelerator读取excel文件

pyExcelerator 最主要的特色在于其能够灵活产生各种带格 式的 Excel 文件，但也能够读取已经存在的Excel文件，只不过读取起来不方便。

pyExcelerator读取Excel 文件只需使用函数：parse_xls。该函数在 ImportXLS.py 文件中定义，从函数声明中观察：

parse_xls(filename, encoding = None)。

Filename指向需要打开的Excel 文件， encoding规定Excel 文件使用的编码方法。返回值为一个列表，每条数据存放一个 Sheet 的数据，它是一个二元组 (sheets[n]，{(row,col)：value})，其中单元格数据又是一个字典，键值就是单元格的索引(row，col)。如果某个单元格无数据，那么就不存在这个值。假如book1.xls 文件一共有 3 个 sheet，名称分别为SheetA、SheetB、SheetC；SheetA 数据为 A1=11，A2=12，A3=13；SheetB 数据为 A1=21，A2 为空，A3=23；SheetC 为空。用pyExcelerator 读取方法如下：

from pyExcelerator import *

sheets = parse_xls ('book1.xls')

print sheets

print sheets提供了操作 Excel 打印数据的接口。最重要的是 pyExcelerator 是一个跨平台的模块，并不需要在Windows操作系统下和COM服务器环境也能正常执行。这对于需要在如Linux、Unix 环境下操作Excel的程序来说是个很友好的操作方式，比如能够很好地应用于运行在 Linux 下却需要动态产生Excel文件，同时能够把数据返回到浏览器端的Web程序。

得到的结果：

[

(u'SheetA',{(2, 0): 13.0,(1, 0): 12.0,(0, 0) : 11.0}) ,

(u'SheetB',{(2, 0): 23.0,(0, 0): 21.0}),

(u'SheetC',{})

]

可以看出，由于 SheetB 的A2不存在，所以得到SheetB数据中不存在(1，0) 单元格。所以，在使用返回的数据时，一定要先添加判断条件，确定单元格是否存在再去读取，否则会出现 KeyError 的报错。

3.2 使用xlrd读取excel文件

Xlrd主要是提供快捷的读取方式，可以通过读取文件，得到表格，通过表格读取单元格，不需要通过字典的键值对来回去单元格的value，并且可以通过nrows和ncols来进行表格的遍历，获取这个表格的原数据。

如一个excel存在三张表，sheet1、sheet2、sheet3，sheet1中的单元格内容为：A1=11，A2=12，A3=13，读取excel的过程代码如下：

import xlrd

wb = xlrd.open_workbook('book.xls') #打开文件

sh = wb.sheet_by_index(0) #获得工作表的方法1

sh = wb.sheet_by_name('sheet1') #获得工作表的方法2

row_count=sh.nrows #获得行数

col_count=sh.ncols #获得列数

for i in range(0,col_count):

for j range(0,row_count):

print cellA1Value = sh.cell_value(i, j) #获得单元格数据

得到结果：

11

12

13

从上述结果来看，open_workbook()函数读取了需要打开的excel文件，并且获取了excel对象。sheet_by_index()和sheet_by_name()两个函数是读取工作表的两个方式，可以通过位置和表名来进行定位。通过nrows和ncols来获取当前表格的行数和列数，并且通过遍历的方式来打印单元格的数据。由于遍历单元格过程中，如果单元格不存在只时，会返回None，因此不存在error的情况，有效保护程序的运行。

个人推荐使用xlrd读取excel文件，由于不涉及过多的数据结构和字典，无需进行多余的判断操作，并且读取过程和代码结构清晰，便于代码的阅读，降低代码维护难度。

3 写入 Excel 文件

由于xlrd模块不提供excel文件的修改和生成操作，因此这里使用pyExcelerator 来完成生成的操作。pyExcelerator中有多个类用于写入 Excel 文件，其中常用的有 3 个：Workbook，Worksheet，XFStyle。Workbook 定义为一个 Excel 文件，Worksheet 定义为一个 Excel 文件中的一页，XFStyle用于确定写入单元格的格式。

3.1 生成Excel 文件

下面我们来演示生成 Excel 文件的过程，代码如下：

coding=utf-8

from pyExcelerator import *

wb = Workbook () # 生成 Workbook 对象

ws = wb.add_sheet ('Sheet1') # 生成Worksheet对象

ws.write (0, 0, 'Hello world') # 向Worksheet对象的 A1 单元写入数据‘Hello world’

w.save ('MyExcel.xls') # 将Workbook对象进行保存

首先，我们读取第一行代码，pyExcelerator使用workbook生成工作簿对象，然后调用add_sheet函数添加sheet表格，接下来使用Worksheet对象的write方法写入数据的，此函数定义：write (row, column, label="" , style=Style.XFStyle ())。row、column 是要写入数据的单元格的行、列坐标，从 0 开始，如果A1单元就是(0，0)，B2 单元就是(1，1)。Label 参数是要写入的具体内容。最后的style参数指定写入时的格式，关于这个style的使用，我们将在接下来内容进行讲解。

3.2 设置单元格字体

设置单元格的字体格式就要使用Font对象，代码如下：

font = Font ()

font.name = 'Arial'# 明确字体的名称

font.bold = True# 确定字体是否加粗

font.italic = True# 确定字体是否斜体

font.height = 200 # 设置字体的高度，200相当于10点高

font.struck_out = True# 设置是否在字之间划删除线

font.outline = False# 设置是否采用 outline 字体

font.shadow = False# 设置是否加阴影

font.colour_index = 2# 设置字体颜色的索引

font.escapement = font0.ESCAPEMENT_SUBSCRIPT# 指定字体的上、下标

font.underline = font0.UNDERLINE_SINGLE_ACC# 设置是否使用下划线

font.family = font0.FAMILY_NONE# 指定字体集

font.charset = font0.CHARSET_ANSI_CYRILLIC# 指定字符集

style = XFStyle ()

style.font = font

wb = Workbook ()

ws = wb.add_sheet('Sheet1')

ws.write(1, 1, 'Test', style)

wb.save('MyExcel.xls')

可以看到通过 Font 对象可以设置各种字体格式，大部分使用的解释都在注释中给出，其中比较特殊的两个用法是ESCAPEMENT用于设置字体的上下标， ESCAPEMENT_NONE表示不使用上下标 ， ESCAPEMENT_SUPERSCRIP表示使用上标，ESCAPEMENT_SUBSCRIPT表示使用下标。underline 用于设置字体的下划线，UNDERLINE_NONE表示不使用下划线， UNDERLINE_SINGLE 表示使用单下划线，UNDERLINE_SINGLE_ACC表示使用会计用单下划线， UNDERLINE_DOUBLE 表示使用双下划线， UNDERLINE_DOUBLE_ACC 表示使用会计用双下划线。另外，通过设置当前行上的某一个单元格的高度 (height)就可以间接地设置此行的行高。

3.3 设置列宽

wb = Workbook ()

ws = w.add_sheet ('Sheet1')

ws.write (1, 1, 'Hello World')

ws.col (1) .width = 8000# 8000:400点

w.save ('MyExcel.xls')

ws.col (colnum) 函数返回的是第colnum列的对象，我们可以对它的width属性来设置和修改此列的宽度。

3.4 设置单元格边框

使用pyExcelerator模块中的Borders对象，可以对每个单元格的边框进行设置。设置属性包括left(左边框)、right(有边框)、top(上边框)、bottom(底边框)、diag(对角线)，还有一些边框的颜色，如left_colour(左边框颜色)、right_colour(右边框颜色)、top_colour(上边框颜色)、bottom_colour(底边框颜色)、diag_colour(对角线颜色)。代码如下：

borders = Borders ()

borders.left = borders.DOUBLE # 设置左边框

borders.right = borders.DOUBLE # 设置右边框

borders.top = borders.NO_LINE # 设置上边框

borders.bottom = borders.NO_LINE # 设置下边框

borders.diag = borders.DOUBLE # 设置对角线

borders.left_colour = 0x80 # 设置左边框颜色

borders.right_colour = 0x50 # 设置右边框颜色

borders.top_colour = 0x60 # 设置上边框颜色

borders.bottom_colour = 0x70 # 设置下边框颜色

borders.diag_colour = 0x90 # 设置对角线颜色

borders.need_diag1 = borders.NEED_DIAG1#设置是否显示左上->右下对角线

borders.need_diag2 = borders.NO_NEED_DIAG2#设置是否显示左下->右上对角线

style = XFStyle ()

style.borders = borders

wb = Workbook ()

ws = wb.add_sheet ('Sheet1')

ws.write (1, 1, 'Test borders! ', style)

wb.save ('MyExcel.xls')

可以看出Borders是格式 (XFStyle) 的一种，它的 left、 right、top、bottom、diag 属性分别设置左、右、上、下、对角五条线的类型，类型共有以下14 种：NO_LINE、THIN、MEDIUM、DASHED、DOTTED、THICK、DOUBLE、HAIR、MEDIUM_DASHED、THIN_DASH_DOTTED、MEDIDOTTED、THIN_DASH_DOT_DOTTED、MEDIUM_DASH_DOT_DOTTED、SLANTED_MEDIUM_DASH_DOTTED。其中 NO_LINE 表示不显示相应的边框线，其他值显示对应线形样式的边框。大家可以注意到left_colour等以是_colour结尾的属性是对应的线段的颜色的索引。need_diag1、need_diag2是用于设置是否需要对角线：NEED_DIAG1 (或 2)需要、NO_NEED_DIAG1 (或2)则表示不需要。其实diag属性只是设置对角线的线形，实际使用中并不多，只有设置 need_diag 属性为NEED才真正显示对角线，因此一般代码编写者都不会把该属性设置出来。

3.5 设置单元格底纹

使用pyExcelerator模块中的Pattern对象，可以设置单元格的底纹。设置的属性包括patteren(图案索引)、pattern_fore_colour(底纹的前景色)、pattern_back_colour(底纹的背景色)。

pattern = Pattern ()

pattern.pattern = 1 # 设置底纹的图案索引

pattern.pattern_fore_colour = 25 # 设置底纹的前景色

pattern.pattern_back_colour = 15 # 设置底纹的背景色

style = XFStyle ()

style.pattern = pattern

wb = Workbook ()

ws0 = wb.add_sheet ('Sheet2')

ws0.write (1, 1, 'Hello world! ', style)

wb.save ('MyExcel.xls')

Pattern的pattern属性表示底纹的图案索引，0指实心，1指75%灰色，2指50%灰色，以此类推，具体的设置值可以参考Excel自带的帮助文档。pattern_fore_colour、pattern_back_colour 分别表示底纹的前景色和背景色的颜色索引。

3.6 生成合并的单元格

生成合并单元格要使用模块中Worksheet 的write_merge、merge函数。我们来观察一下write_merge 和merge的定义：

write_merge ( r1, r2, c1, c2, label="" , style)

merge (r1, r2, c1, c2, style)

观察write_merge的定义，其中 r1，r2 指示需要进行单元格合并的起始行和终止行 (起始行一般以0为首计数)；c1、c2 指示要进行单元格合并的起始列和终止列 (起始列一般以0为首计数)；label 是写入的数据内容；style表示合并后单元格的格式，具体格式的设置可以参考之前所展示的例子。

观察merge的定义，可以看出 merge 是 write_merge 的特殊情况，merge 适用于写入内容为空的单元格合并操作。参考代码如下：

wb = Workbook ()

wb = wb.add_sheet ('Sheet3')

ws.write_merge (3, 3, 1, 5, 'Hello world!')#合并第4行，第2列到6列，并写入Hello world!。

ws.write_merge (4, 10, 1, 5, 'Hello world2!')#合并第5行到11行，第2列到6列，并写入Hello world2!。

ws.merge (12,15,1,5)#合并第13行到16行，第2列到 6 列，并写入空(None)。

wb.save ('MyExcel.xls')

3.7 插入图片

使用Workbook对象中的insert_bitmap函数进行图片的插入。代码如下：

wb = Workbook ()

ws = wb.add_sheet ('Image')

插入 Mypitcure.bmp 图片

ws.insert_bitmap (' Mypitcure.bmp ', 3, 3, 11, 11, 1, 1)

ws.insert_bitmap ( ' Mypitcure.bmp ', 11, 3) # 在单元格(11,3)插入Mypitcure.bmp图片

w.save ('MyExcel.xls')

insert_bitmap的定义为 insert_bitmap(filename, row, col, x= 0, y = 0, scale_x = 1, scale_y = 1)，filename为要插入图片的文件名或文件路径，由于pyExcelerator的当前版本较低，只支持插入位图文件 (bmp)；row、col 表示需要要插入图片的行列，x、y为插入的位置偏移量， 默认值为 0，不偏移；scale_x、scale_y 为横向、纵向的伸缩比例，默认值为1。

4 结语

本文给出使用基于Python环境下 pyExcelerator 、xlrd模块的读写 Excel 文件的方法，由于xlrd是专门的excel文件读取模块，在读取excel文件时，强烈推荐使用xlrd模块。然后本文着重介绍了修改和生成Excel文件的方法。由于pyExcelerator与xlrd可以跨平台使用，为Unix、Linux 操作系统下操作 Excel 文件提供了极为友好的环境，可以广泛应用于部署在非Windows操作系统下的应用程序，方便程序调用 Excel 报表信息，并进行二次编辑或者生成的操作，特别是一些系统自动导出 Excel 文件、网络游戏 Excel 报表等场合，具有极大的应用价值。

[1] [美]Christian, Benvenut 著，夏宏 闫江毓 黄景昌 译．深入理解Linux网络技术内幕 [M]．中国电力出版社，2009(1):978-7-5083-7964-7．

[2] [美] Paul Barry 著；林琪 郭静 等 译；Head First Python [M].中国电力出版社,2012(1).978-7-5123-2223-3.

[3] [美] Ryan Mitchell 著；陶俊杰 陈小莉 译. Python 网络数据采集 [M].人民邮电出版社,2016(01). 978-7-1154-1629-2.

[4] 安晓辉 著. QT Quick核心编程 [M].电子工业出版社,2015(01). 978-7-1212-4684-5.

[5] [美] Richard Blum, Christin Bresnahan 著；武海峰 译. Linux命令行与shell脚本编程大全(第2版) [M].人民邮电出版社,2012(2). 978-7-1152-8889-9.

[6] 马玉军，陈连山 著. Red Hat Enterprise Linux 6.5系统管理 [M].清华大学出版社,2014(01).

[7] Excel Home 著. 别怕，Excel VBA其实很简单 Excel VBA实战技巧精粹 [M].人民邮电出版社,2013(1).

[8] 赛贝尔资讯 著. Excel函数与公式速查手册 [M].清华大学出版社,2015(01). 978-7-30238471-7.

[9] [印] Shantanu Tushar, Sarath Lakshman 著；门佳 译. Linux Shell脚本攻略(第2版) [M].人民邮电出版社,2014(1). 978-7-115-33921-8.

[10] Excel Home 著.Excel2013函数与公式大全 [M]. 北京大学出版社,2016(1). 978-7-30126191-0.

Implementation of Reading and Writing for Excel Based on the Third Party Library of Phthon

Chen Yanpeng

(Foshan Power Supply Bureau, Guangdong Power Grid Limited Corporation, Foshan 528000, China)

This paper introduces a method based on Python third library pyExcelerator and xlrd to realize Excel opctions, and emphatically introduces the implementation details of reading Excel contents and producing excel files with format. PyExcelerator and xlrd library widely used on the deployment of applications on non windows operating system, for the program can easily reading and writing excel information, and the operation of correcting or creating, especially some system automatic export Excel files, network game excel report form and so on occasions, and has great application value.

Python; pyExcelerator; xlrd; Excel

陈衍鹏(1990-)，男，研究方向：信息开发和管理，数据库。

1007-757X(2017)08-0075-04

TG4

A

2017.04.30)