Python Excel中xlrd和xlwt模块的方法
EXCEL的模块 xlrd，xlwt
xlrd模块实现对excel文件内容读取，xlwt模块实现对excel文件的写入。
模块的安装：
xlrd和xlwt模块不是自带模块。需要进行安装，安装方法不多说，有很多种通常用下面的这种方法安装简单方便。
pip install xlrd
pip install xlwt
xlrd模块的方法和属性：
excel文档名称为联系人.xls，内容如下：
- 打开excel文件并获取所有sheet(表格)
import xlrd
# 打开Excel文件读取数据
data = xlrd.open_workbook('联系人.xls')
sheet_name = data.sheet_names() # 获取所有sheet名称
print(sheet_name) # ['银行2', '银行3'] - 根据下标获取sheet名称
# 根据下标获取sheet名称 sheet2_name = data.sheet_names()[1] print(sheet2_name) # '银行3'
- 根据sheet索引或者名称获取sheet内容，同时获取sheet名称、行数、列数
# 根据sheet索引或者名称获取sheet内容，同时获取sheet名称、列数、行数 sheet2 = data.sheet_by_index(1) print('sheet2名称:{}\nsheet2列数: {}\nsheet2行数: {}'.format(sheet2.name, sheet2.ncols, sheet2.nrows)) # sheet2名称:银行3 # sheet2列数: 7 # sheet2行数: 5 sheet1 = data.sheet_by_name('银行2') print('sheet1名称:{}\nsheet1列数: {}\nsheet1行数: {}'.format(sheet1.name, sheet1.ncols, sheet1.nrows)) # sheet1名称:银行2 # sheet1列数: 8 # sheet1行数: 6
- 根据sheet名称获取整行和整列的值
# 根据sheet名称获取整行和整列的值 sheet1 = data.sheet_by_name('银行2') print(sheet1.row_values(3)) # ['', '张2', '开发', 'IT编码', 999.0, 133111.0, 41463.0, 'zhang2@164.com'] 日期2013/7/7，实际却显示为浮点数41463.0 print(sheet1.col_values(3)) # ['', '工作职责', '', 'IT编码', '网络维修', '']
- 获取指定单元格的内容
# 获取指定单元格的内容 print(sheet1.cell(1,0).value) # 第2 行1列内容：机构名称 print(sheet1.cell_value(1,0)) # 第2 行1列内容：机构名称 print(sheet1.row(1)[0].value) # 第2 行1列内容：机构名称
- 获取单元格内容的数据类型
# 获取单元格内容的数据类型 print(sheet1.cell(1,0).ctype) # 第2 行1列内容 ：机构名称为string类型 print(sheet1.cell(3,4).ctype) # 第4行5列内容：999 为number类型 print(sheet1.cell(3,6).ctype) # 第4 行7列内容：2013/7/8 为date类型 # 说明：ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
- 获取单元内容为日期类型的方式
from datetime import datetime,date if sheet1.cell(3,6).ctype == 3 : print(sheet1.cell(3, 6).value) # 41463.0 date_value = xlrd.xldate_as_tuple(sheet1.cell(3, 6).value, data.datemode) print(date_value) # (2013, 7, 8, 0, 0, 0) print(date(*date_value[:3])) # 2013-07-08 print(date(*date_value[:3]).strftime('%Y/%m/%d')) # 2013/07/08
- 获取单元内容为number的方式（转为整型）
if sheet1.cell(3, 5).ctype == 2: print(sheet1.cell(3, 5).value) # 133111.0 num_value = int(sheet1.cell(3, 5).value) print(num_value) # 133111
- 获取合并单元格的内容
需要merged_cells属性# 这里，需要在读取文件的时候添加个参数，将formatting_info参数设置为True，默认是False，否 # 则可能调用merged_cells属性获取到的是空值。 data = xlrd.open_workbook('联系人.xls',formatting_info=True) sheet1 = data.sheet_by_name('银行2') print(sheet1.merged_cells) # [(0, 1, 0, 8), (2, 6, 0, 1)] # merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range),其中[row,row_range)包括row, # 不包括row_range,col也是一样，下标从0开始。 #(0, 1, 0, 8) 表示1列-8列合并 (2, 6, 0, 1)表示3行-6行合并 # 分别获取合并2个单元格的内容： print(sheet1.cell(0,0).value) # 银行2 print(sheet1.cell_value(2, 0)) # 银行2
规律：获取merge_cells返回的row和col低位的索引即可！使用以下方法更加方便
merge_value = [] for (row,row_range,col,col_range) in sheet1.merged_cells: merge_value.append((row,col)) print(merge_value) # [(0, 0), (2, 0)] for v in merge_value: print(sheet1.cell(v[0], v[1]).value) # 银行2 # 银行2
xlwt模块的方法和属性：
-
写excel的难点可能不在构造一个workbook的本身，而是填充的数据，不过这不在范围内。在写excel的操作中也有棘手的问题，比如写入合并的单元格就是比较麻烦的，另外写入还有不同的样式。这些要看源码才能研究的透。
我"构思"了如下面的sheet1，即要用xlwt实现的东西：
代码如下：
''' 设置单元格样式 ''' def set_style(name,height,bold=False): style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体 font.name = name # 'Times New Roman' font.bold = bold font.color_index = 4 font.height = height # borders= xlwt.Borders() # borders.left= 6 # borders.right= 6 # borders.top= 6 # borders.bottom= 6 style.font = font # style.borders = borders return style #写excel def write_excel(): f = xlwt.Workbook() #创建工作簿 ''' 创建第一个sheet: sheet1 ''' sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet row0 = [u'业务',u'状态',u'北京',u'上海',u'广州',u'深圳',u'状态小计',u'合计'] column0 = [u'机票',u'船票',u'火车票',u'汽车票',u'其它'] status = [u'预订',u'出票',u'退票',u'业务小计'] #生成第一行 for i in range(0,len(row0)): sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True)) #生成第一列和最后一列(合并4行) i, j = 1, 0 while i < 4*len(column0) and j < len(column0): sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True)) #第一列 sheet1.write_merge(i,i+3,7,7) #最后一列"合计" i += 4 j += 1 sheet1.write_merge(21,21,0,1,u'合计',set_style('Times New Roman',220,True)) #生成第二列 i = 0 while i < 4*len(column0): for j in range(0,len(status)): sheet1.write(j+i+1,1,status[j]) i += 4 f.save('demo1.xlsx') #保存文件 if __name__ == '__main__': #generate_workbook() #read_excel() write_excel()
需要稍作解释的就是write_merge方法：
write_merge(x, x + m, y, w + n, string, sytle)
x表示行，y表示列，m表示跨行个数，n表示跨列个数，string表示要写入的单元格内容，style表示单元格样式。其中，x，y，w，h，都是以0开始计算的。
这个和xlrd中的读合并单元格的不太一样。
如上述：sheet1.write_merge(21,21,0,1,u'合计',set_style('Times New Roman',220,True))
即在22行合并第1,2列，合并后的单元格内容为"合计"，并设置了style。
如果需要创建多个sheet，则只要f.add_sheet即可。
如在上述write_excel函数里f.save('demo1.xlsx') 这句之前再创建一个sheet2，效果如下：
代码如下：
''' 创建第二个sheet: sheet2 ''' sheet2 = f.add_sheet(u'sheet2',cell_overwrite_ok=True) #创建sheet2 row0 = [u'姓名',u'年龄',u'出生日期',u'爱好',u'关系'] column0 = [u'小杰',u'小胖',u'小明',u'大神',u'大仙',u'小敏',u'无名'] #生成第一行 for i in range(0,len(row0)): sheet2.write(0,i,row0[i],set_style('Times New Roman',220,True)) #生成第一列 for i in range(0,len(column0)): sheet2.write(i+1,0,column0[i],set_style('Times New Roman',220)) sheet2.write(1,2,'1991/11/11') sheet2.write_merge(7,7,2,4,u'暂无') #合并列单元格 sheet2.write_merge(1,2,4,4,u'好朋友') #合并行单元格 f.save('demo1.xlsx') #保存文件
还可以添加超链接，如：
# 添加超链接 n= "HYPERLINK" sheet2.write_merge(9,9,2,8, xlwt.Formula(n +'("http://www.cnblogs.com/zhoujie";"jzhou\'s blog")'),set_style('Arial',300,True)) sheet2.write_merge(10,10, 2, 8, xlwt.Formula(n +'("mailto:zhoujie0111@126.com";"contact me")'),set_style('Arial',300,True))
完整代码：
# -*- coding: utf-8 -*- import xlwt import xlrd ''' 设置单元格样式 ''' def set_style(name,height,bold=False): style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体 font.name = name # 'Times New Roman' font.bold = bold #f.underline= Font.UNDERLINE_DOUBLE font.color_index = 4 font.height = height # borders= xlwt.Borders() # borders.left= 6 # borders.right= 6 # borders.top= 6 # borders.bottom= 6 style.font = font # style.borders = borders return style #读excel def read_excel(): # 打开文件 workbook = xlrd.open_workbook(r'F:\demo.xlsx') # 获取所有sheet print workbook.sheet_names() # [u'sheet1', u'sheet2'] sheet2_name = workbook.sheet_names()[1] # 根据sheet索引或者名称获取sheet内容 sheet2 = workbook.sheet_by_index(1) #sheet索引从0开始 sheet2 = workbook.sheet_by_name('sheet2') # sheet的名称，行数，列数 print sheet2.name,sheet2.nrows,sheet2.ncols # 获取整行和整列的值（数组） rows = sheet2.row_values(3) #获取第四行内容 cols = sheet2.col_values(2) #获取第三列内容 print rows print cols # 获取单元格内容 print sheet2.cell(1,0).value.encode('utf-8') print sheet2.cell_value(1,0).encode('utf-8') print sheet2.row(1)[0].value.encode('utf-8') # 获取单元格内容的数据类型 print sheet2.cell(1,0).ctype #写excel def write_excel(): f = xlwt.Workbook() #创建工作簿 ''' 创建第一个sheet: sheet1 ''' sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet row0 = [u'业务',u'状态',u'北京',u'上海',u'广州',u'深圳',u'状态小计',u'合计'] column0 = [u'机票',u'船票',u'火车票',u'汽车票',u'其它'] status = [u'预订',u'出票',u'退票',u'业务小计'] #生成第一行 for i in range(0,len(row0)): sheet1.write(0,i,row0[i],set_style('Times New Roman',220,True)) #生成第一列和最后一列(合并4行) i, j = 1, 0 while i < 4*len(column0) and j < len(column0): sheet1.write_merge(i,i+3,0,0,column0[j],set_style('Arial',220,True)) #第一列 sheet1.write_merge(i,i+3,7,7) #最后一列"合计" i += 4 j += 1 sheet1.write_merge(21,21,0,1,u'合计',set_style('Times New Roman',220,True)) #生成第二列 i = 0 while i < 4*len(column0): for j in range(0,len(status)): sheet1.write(j+i+1,1,status[j]) i += 4 ''' 创建第二个sheet: sheet2 ''' sheet2 = f.add_sheet(u'sheet2',cell_overwrite_ok=True) #创建sheet row0 = [u'姓名',u'年龄',u'出生日期',u'爱好',u'关系'] column0 = [u'小杰',u'小胖',u'小明',u'大神',u'大仙',u'小敏',u'无名'] #生成第一行 for i in range(0,len(row0)): sheet2.write(0,i,row0[i],set_style('Times New Roman',220,True)) #生成第一列 for i in range(0,len(column0)): sheet2.write(i+1,0,column0[i],set_style('Times New Roman',220)) sheet2.write(1,2,'1991/11/11') sheet2.write_merge(7,7,2,4,u'暂无') #合并列单元格 sheet2.write_merge(1,2,4,4,u'好朋友') #合并行单元格 # 插入图片 sheet2.insert_bitmap(r'F:\1.bmp',10,2) # 添加超链接 n= "HYPERLINK" sheet2.write_merge(9,9,2,8, xlwt.Formula(n +'("http://www.cnblogs.com/zhoujie";"jzhou\'s blog")'),set_style('Arial',300,True)) sheet2.write_merge(10,10, 2, 8, xlwt.Formula(n +'("mailto:zhoujie0111@126.com";"contact me")'),set_style('Arial',300,True)) f.save('demo1.xlsx') #保存文件 if __name__ == '__main__': read_excel() write_excel()
参考©：http://www.cnblogs.com/snake-hand/p/3153158.html
