Python如何读写Excel文件-使用xlrd/xlwt模块
-
时间: 2020-08-18 整理: qiyuan
安装和导入
1.模块介绍
在 python 中使用 xlrd/xlwt 和 openpyxl 模块可以对Excel电子表格(xls、xlsx文件)进行读写等操作. 本篇以 python3 为基础,以 xlrd/xlwt 模块为“学习和研究”对象,对 xlrd/xlwt 中常见的用法进行梳理和记录.
2.模块安装
pip install xlrd
pip install xlwt
3.模块导入
import xlrd import xlwt
xlrd模块
1.语法说明
import xlrd # 导入xlrd模块 1.打开excel文件,获取文件内容 excel = '/Users/usr/Downloads/TEMP/DVT.xlsx' data = xlrd.open_workbook(excel) data.nsheets # 获取该excel文件中包含的sheet的数量 data.sheets() # 返回该excel文件中所有sheet对象组成的列表 data.sheet_names() # 返回该excel文件中所有sheet名称组成的列表 data.sheet_names()[index] # 获取excel文件中指定索引的sheet的名称 data.sheet_loaded(sheet_name or index) # 检查某个sheet是否导入完毕 2.获取某个sheet数据 table = data.sheets()[index] # 根据sheet索引获取sheet内容 table = data.sheet_by_index(index) # 根据sheet索引获取sheet内容 table = data.sheet_by_name(sheet_name) # 根据sheet名称获取sheet内容 table.name # 获取sheet名称 3.操作行、列、单元格 # 行的操作 table.nrows # 获取该sheet中的有效行数 table.row(rowx) # 返回由该行中所有单元格对象组成的列表 table.row_slice(rowx) # 返回由该列中所有的单元格对象组成的列表 table.row_types(rowx,start_colx=0,end_colx=None) # 返回由该行中所有单元格的数据类型组成的列表 table.row_values(rowx,start_colx=0, end_colx=None) # 返回由该行中所有单元格数据组成的列表 table.row_len(rowx) # 返回该列的有效单元格长度 # 列的操作 table.ncols # 获取该sheet中的有效列数 table.col(colx,start_rowx=0,end_rowx=None) table.col_slice(colx,start_rowx=0,end_rowx=None) table.col_types(colx,start_rowx=0,end_rows=None) table.col_values(colx,start_rowx=0,end_rows=None) # 单元格的操作 table.cell(rowx,colx) # 返回单元格对象 table.cell_value(rowx,colx) # 返回单元格中的数据 table.cell(rowx,colx).value table.row(rowx)[index].value table.col(colx)[index].value table.cell_type(rowx,colx) # 返回单元格中的数据类型 sheet2.cell(rowx,colx).ctype table.row(rowx)[index].ctype table.col(colx)[index].ctype 4.获取单元格内容为特定类型方式 # ctype: 0 empty,1 string,2 number,3 date,4 boolean,5 error,6 blank # 获取单元格内容为date格式 from datetime import datetime,date if sheet1.cell(3,6).ctype == 3: cell_value = sheet1.cell(3,6).value) date_value = xlrd.xldate_as_tuple(cell_value, data.datemode) date_value_str = date(*data_value[:3]) date_value_str = date(*data_value[:3]).strftime('%Y/%m/%d') # 获取单元格内容为number(int)格式 if sheet1.cell(3,5).ctype == 2: cell_value = sheet1.cell(3,5).value num_value = int(cell_value) 5.获取合并单元格的内容 data = xlrd.open_workbook(filename, formattinng_info=True) sheet1 = data.sheet_by_name('OTA_02') sheet1.merged_cells # 返回: (row,row_range,col,col_range) # 总结规律: 获取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) for v in merge_value: cell_value = sheet1.cell(v[0],v[1]).value print(cell_value) 6.打开包含中文字符的文件名和sheet名时报错的解决办法 # 1.使用open()函数,xlrd.open_workbook()函数打开文件,文件名若包含中文,会报错找不到这个文件或目录 # 2.获取sheet时,若包含中文,也会报错 file = open(filename,'rb') # 打开文件 workbook = xlrd.open_workbook(filename) # 打开excel文件 sheet = workbook.sheet_by_name(sheetname) # 获取sheet # 解决方案: # a.对参数进行转码即可,如: filename = filename.decode('utf-8') # b.也试过unicode函数,不过,在ride中运行时出现了报错,不推荐 filename = unicode(filename,'utf-8')
2.示例
BOOK_LIST.xlsx : Sheet1
使用 xlrd 模块读取表格内容:
# -*- coding: utf-8 -*- import xlrd from datetime import datetime, date def read_excel(): data = xlrd.open_workbook(r'/media/psf/Home/Downloads/TEMP/BOOK_LIST.xlsx') print('Sheet列表:', data.sheet_names(), '数量:', data.nsheets) table = data.sheets()[0] # table = data.sheet_by_index(0) # table = data.sheet_by_name('Sheet1') print('Sheet名称:', table.name, '行数:', table.nrows, '列数:', table.ncols) print('-------- 按行显示Sheet内容: --------') for rowx in range(0, table.nrows): print('第{}行:'.format(rowx + 1), table.row_values(rowx)) # print('第{}行:'.format(rowx + 1), table.row_types(rowx)) print('-------- 按列显示Sheet内容: --------') for colx in range(0, table.ncols): print('第{}列:'.format(colx + 1), table.col_values(colx)) # print('第{}列:'.format(colx + 1), table.col_types(colx)) print('-------- 按行显示Sheet所有单元格对象: --------') for rowx in range(0, table.nrows): print('第{}行:'.format(rowx + 1), table.row(rowx)) print('-------- 按列显示Sheet所有单元格对象: --------') for colx in range(0, table.ncols): print('第{}列:'.format(colx + 1), table.col(colx)) print('-------- 获取合并单元格内容: --------') # 获取合并单元格内容 print('合并单元格的索引和范围:', table.merged_cells) for (rowx,row_range,colx,col_range) in table.merged_cells: print('合并单元格的内容:',table.cell(rowx,colx).value,'(','合并单元格宽:',col_range,'高:',row_range,')') print('-------- 获取/转换单元格内容为特定类型: --------') # 获取单元格内容为int类型 print('转换前:', table.col(0,1,None)) print('转换后', end=': ') for cellobj in table.col(0,1,None): if cellobj.ctype == 2: cell_value = cellobj.value num_value = int(cell_value) print(num_value, end=', ') else: print(cellobj.value, end=', ') print('') # 获取单元格内容为date格式 print('转换前:', table.col(6,1,None)) print('转换后', end=': ') for cellobj in table.col(6,1,None): if cellobj.ctype == 3: cell_value = cellobj.value date_value = xlrd.xldate_as_tuple(cell_value, data.datemode) data_value_obj = date(*date_value[:3]) data_value_str = date(*date_value[:3]).strftime('%Y/%m/%d') print(data_value_str,end=', ') else: print(cellobj.value, end=', ') if __name__ == '__main__': read_excel()
运行结果:
(提示: 如果图片不清晰影响阅读,可鼠标右键点击图片选择 ‘在新标签页中打开图片’ 查看高清大图,或选择 ‘图片存储为...’ 到本地查看)
xlwt模块
1.语法说明
import xlwt # 导入模块 1.创建Workbook对象 wb = xlwt.Workbook() 2.新增sheet ws = wb.add_sheet('sheet') ws = wb.add_sheet('联系人', cell_overwrite_ok=True) 3.写入数据 ws.write(0,0,'Hello') ws.write(2,0,100) ws.write(2,1,200) ws.write(2,2,xlwt.Formula("A3+B3")) ws.write(4,2,'技术部') ws.write(4,5,'186777233') ws.write(4,6,'wangwu@163.com') ws.write_merge(21,21,0,1,u'Summary') # 合并单元格 4.设定单元格样式、列宽 ws.col(0).width = 200*30 5.保存工作薄 wb.save('/Users/usr/Downloads/TEMP/example.xls')
2.示例
BOOK_LIST.xlsx : Sheet2
使用 xlwt 模块创建新的表格并写入数据:
# -*- coding: utf-8 -*- import xlwt from datetime import datetime,date def set_style(name,height,bold=False): style = xlwt,XFStyle() font = xlwt.Font() font.name = name font.blod = blod font.color_index = 4 font.height = height # borders = xlwt.Borders() # borsers.left = 6 # borders.right = 6 # borders.top = 6 # borders.bottom = 6 style.font = font # style.borders = borders return style def write_excel(): f = xlwt.Workbook() sheet1 = f.add_sheet(u'sheet1',cell_voerwrite_ok=True) row0 = [u'业务',u'状态',u'北京',u'上海',u'广州',u'深圳',u'状态小计',u'合计'] columnn0 = [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)) i,j = 1,0 while i < 4*len(column0) and j < len(column0): sheet1.write_merge(i,i+3,0,0,column[j],set_style('Arial',220,True)) sheet1.write_merge(i,i+3,7,7) i += 4 j += 1 sheet1.write_merage(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__': write_excel()
--the end--
