使用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相关的操作

#获取sheet
table_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).value
table.cell_value(i,j)
table.row(i)[j].value
table.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 xlrd
data = xlrd.open_workbook('D:/demo.xlsx')   #打开一个工作簿
table = data.sheets()[0]    #打开第一个sheet

print('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	单元格锁定，公式隐藏

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

字体格式：

#创建字体格式font0
font0 = 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_ACC
font0.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…

单元格对齐：

#创建格式alignment0
alignment0 = 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

边框格式：

#创建格式borders0
borders0 = 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）

背景格式：

#创建格式pattern0
pattern0 = xlwt.Pattern()

#设置背景阴影
pattern0.pattern = xlwt.Pattern.SOLID_PATTERN   #May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12   

#设置背景色，序号对应同字体颜色. 
pattern0.pattern_fore_colour = 5

应用：

#创建格式style0
style0 = xlwt.XFStyle()

#分别将以上设置的属性赋值
style0.font = font0
style0.slignment = alignment0
style0.borders = borders0
style0.pattern = pattern0

#写入表格时应用
sheet1.write(0,0,label = 'abc',style = style0)

又一个小例子：

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

import xlwt

def AlignmentStyle(horz_type,vert_type):
    alignment = xlwt.Alignment()
    alignment.horz = horz_type
    alignment.vert = vert_type
    return alignment

def 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

# 生成工作簿及sheet
f = 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)

#保存文件为.xls
f.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的用法及例子。

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