使用Python xlrd与xlwt模块读写Excel

  最近处理一些Excel表格，遇到从大量表格的同一位置提取数值的情况，手动复制效率极低，还容易出现遗漏的情况，所以试着用Python中xlrd与xlwt模块实现了一下，接下来就简单分享一下用xlrd，xlwt来实现Excel读写操作的方法。

Ecxel的读取（xlrd）

首先通过pip安装xlrd：

>> pip install xlrd

或手动安装（https://pypi.python.org/pypi/xlrd）

xlrd的基本操作

#导入模块import xlrd#打开一个工作簿data = xlrd.open_workbook('D:/demo.xlsx')

获取sheet相关的操作

#获取sheettable_list = data.sheets()        #获取sheet对象的列表table_name = data.sheet_names()   #获取sheet name的列表table = data.sheets()[i]          #按索引获取sheet对象table = data.sheet_by_index(i)    #通过检索获取第i个sheet对象，作用和data.sheets()[i]相同table = data.sheet_by_name('sheet2')    #通过name获取名称为“sheet2”的对象#返回sheet的名称、最大行数、最大列数name = table.name()nrows = table.nrows()ncols = table.ncols()

获取表格中值的相关操作

#获取整行、整列数据（返回为列表）rows = table.row_values(i)    #返回第i行值的列表cols = table.col_values(j)    #返回第j列值的列表#获取单元格(i,j)的值（i，j均从0开始计数）table.cell(i,j).valuetable.cell_value(i,j)table.row(i)[j].valuetable.col(j)[i].value#获取单元格中值的属性table.cell(i,j).ctype#返回值如下ctype:0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error#日期读取方法datetuple = xlrd.xldate_as_tuple(table.cell(i,j).value,0)     #转化为元组datetuple = xlrd.xldate_as_tuple(table.cell(i,j).value,1)     #转化为datetime对象

一个小栗子：

  下面来试着读取这份表中的数据

图1. 成绩单

import xlrddata = xlrd.open_workbook('D:/demo.xlsx')   #打开一个工作簿table = data.sheets()[0]    #打开第一个sheetprint('Name of sheet:',table.name)      #输出sheet的名称print('The number of rows:',table.nrows)    #输出行数for i in range(table.ncols-1):      #按顺序打印第一列数据    print(table.cell(i,0).value)

运行结果：

Name of sheet: 成绩单
The number of rows: 5
姓名

小明
小红
李华

结果显示所有需要的值都可以读取出来，但仔细观察会发现“姓名”和“小明”之间有一个空值，我们打印A2的对象

print(table.cell(1,0))

结果为：

empty:”

这是因为表中第一列前两个单元格是合并的，而xlrd读取合并单元格时只会读入其中第一个值，其他当做空值读入，读表时需要注意。

Ecxel的写入（xlwt）

安装xlwt package

>> pip install xlwt

或者（https://pypi.python.org/pypi/xlwt/）

xlwt的基本操作

#导入xlwt模块import xlwt#创建一个工作簿f = xlwt.Workbook()#创建一个sheet，cell_overwrite_ok默认为False，当设置为True时，覆盖已有数据不会报错sheet1 = f.add_sheet('A Demo'，cell_overwrite_ok=False)

数据写入

#写入单元格sheet1.write(r,c,label = '',style = style0)

其中r，c分别为行、列号，均从0开始计数；label = 后面为值选项，可以输入整型、浮点型数值变量，字符串，bool值，时间戳等值或变量；style = 为可选项，用于设置单元格字体、对齐、背景及边框等附加属性，后面会详细介绍(“label = ”和“style = ”可同时省略，即输入：sheet1.write(r,c,value,style0))。

#按公式写入单元格sheet1.write(i,j,xlwt.Formula('A1+A2')      #将A1与A2相加后写入(i,j)sheet1.write(i,j,xlwt.Formula('SUM(A1,A2,A3)'))     #A1，A2，A3求和后写入(i,j)sheet1.write(i,j,Formula('HYPERLINK("http://www.google.com";"Google")'))    #插入超链接#合并单元格write_merge(r,r+l,c,c+h,label = '',style = style)    #r,c为起始行列，l,h为跨行列数

写入完成后保存文件，需要注意xlwt只支持Excel2007之前的版本，即保存时应以.xls为后缀，如果存为.xlsx文件打开时会出错。

#保存文件，以.xls为后缀f.save('D:/demo.xls')

xlwt的格式控制

在写入单元格时，有style的可选项，用于控制单元格格式，其中包括六组选项：

项目 属性 Number format 变量类型格式 Font 字体格式 Alignment 单元格对齐 Border 边框格式 Background 单元格背景 Protection 单元格锁定，公式隐藏

 
下面只详细介绍字体、对齐、边框及背景设置

字体格式：

#创建字体格式font0font0 = xlwt.Font()font0.name = 'Times New Roman'    #选择字体font0.colour_index = 2    #字体颜色，序号对应颜色如下所示font0.hight = 200        #字体大小font0.bold = True        #字体加粗font0.underline = xlwt.Font.UNDERLINE_NONE    #下划线  May be: UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACC（单元格扩展）, UNDERLINE_DOUBLE（双下划线）, UNDERLINE_DOUBLE_ACCfont0.italic = True        #斜体font0.struck_out = True    #删除线font0.escapement = xlwt.Font.ESCAPEMENT_NONE     #将内容设为上下标  May Be:ESCAPEMENT_SUPERSCRIPT,ESCAPEMENT_SUBSCRIPT

其中字体颜色序号对应为：

0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on…

单元格对齐：

#创建格式alignment0alignment0 = xlwt.Alignment()#水平对齐设置alignment0.horz = xlwt.Alignment.HORZ_CENTER   #May be:HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED#垂直对齐设置alignment0.vert = xlwt.Alignment.VERT_CENTER  #May be:VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED

边框格式：

#创建格式borders0borders0 = xlwt.Borders()#设置单元格左侧边框线条及颜色，同理可设置上部top，底部bottom及右侧right的边框属性borders0.left = xlwt.Borders.DASHED    #边框类型 May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.borders0.left_colour = 0x40     #边框颜色（0x00 : 0x40）

背景格式：

#创建格式pattern0pattern0 = xlwt.Pattern()#设置背景阴影pattern0.pattern = xlwt.Pattern.SOLID_PATTERN   #May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12   #设置背景色，序号对应同字体颜色. pattern0.pattern_fore_colour = 5

应用：

#创建格式style0style0 = xlwt.XFStyle()#分别将以上设置的属性赋值style0.font = font0style0.slignment = alignment0style0.borders = borders0style0.pattern = pattern0#写入表格时应用sheet1.write(0,0,label = 'abc',style = style0)

又一个小栗子：

使用xlwt写出图1所示的成绩单，代码如下：

import xlwtdef AlignmentStyle(horz_type,vert_type):    alignment = xlwt.Alignment()    alignment.horz = horz_type    alignment.vert = vert_type    return alignmentdef BordersStyle(line_type,line_colour):    borders = xlwt.Borders()    borders.top = line_type    borders.top_colour = line_colour    borders.left = line_type    borders.left_colour = line_colour    borders.bottom = line_type    borders.bottom_colour = line_colour    borders.right = line_type    borders.right_colour = line_colour    return borders# 生成工作簿及sheetf = xlwt.Workbook()sheet1 = f.add_sheet('成绩单',cell_overwrite_ok = True)# 设置单元格格式，其中字体及背景为默认style0 = xlwt.XFStyle()style0.alignment = AlignmentStyle(xlwt.Alignment.HORZ_CENTER,xlwt.Alignment.VERT_CENTER)style0.borders = BordersStyle(xlwt.Borders.THIN,0x00)# 信息列表subject_list = ['语文','思想品德','数学','科学']info_list = [['小明',22,80,85,90,77],['小红',23,91,88,95,90],['李华',24,75,70,98,100]]# 创建表头sheet1.write_merge(0,1,0,0,label = '姓名',style = style0)sheet1.write_merge(0,1,1,1,label = '学号',style = style0)sheet1.write_merge(0,0,2,3,label = '文科',style = style0)sheet1.write_merge(0,0,4,5,label = '理科',style = style0)# 写入学科（写入语句将"label ="及"style ="省略）for i in range(4):    sheet1.write(1,i+2,subject_list[i],style0)# 写入信息for obs in range(3):    for info in range(6):        sheet1.write(obs+2,info,info_list[obs][info],style0)#保存文件为.xlsf.save("C:/users/Vincent Wu/Desktop/score.xls")

生成结果为：

图2.成绩单2

综上，利用xlrd和xlwt可以很方便地对Excel进行读写操作，但由于xlwt只支持写入.xls格式文件，因此单个sheet最大行数限制为65535，当写入文件超过这一值时，会遇到如下报错：

ValueError: row index was 65536, not allowed by .xls format

此时该模块将不再适用，可以利用openpyxl代替之，后面再介绍openpyxl的用法及例子。

初次写博文，如有纰漏，还望指正！(●′ω`●)