python-操作excel数据文件
1、excel文件操作
读文件xlrd模块:
# -*- coding: utf-8 -*- import xlrd workbook = xlrd.open_workbook('D:\\workspace\\eclipse-python\\test\\myexcel.xls') sheetname=workbook.sheet_names()#抓取所有sheet页的名称 print 'myexcel is ',sheetname[0],sheetname[1],sheetname[2] #定位到sheet1 worksheet1=workbook.sheet_by_name(u'【工作月报】') #遍历sheet1中所有行row num_rows=worksheet1.nrows print u'总行数=',num_rows for i in range(num_rows): row=worksheet1.row_values(i) print u'获取的第',i,u'行的数据是',row[0],row[1],row[2] #遍历sheet1中所有列col num_cols=worksheet1.ncols print u'总列数=',num_cols for j in range(num_cols): col=worksheet1.col_values(j) print u'获取的第',i,u'列的数据是',col[0],col[1],col[2],col[3],col[4] #遍历sheet1中所有单元格cell for rown in range(num_rows): for coln in range(num_cols): cell = worksheet1.cell_value(rown,coln) print cell
写文件xlwt:
#coding=utf-8 #新建excel文件并写入数据 import xlwt #创建workbook和sheet对象 wb=xlwt.Workbook(encoding='utf-8') #注意W大写 ws1=wb.add_sheet('sheet1',cell_overwrite_ok=True) ws2=wb.add_sheet('sheet2',cell_overwrite_ok=True) #向sheet中写入数据 ws1.write(0,0,'姓名') ws1.write(0,1,'年龄') ws2.write(0,0,'姓名') ws2.write(1,2,'籍贯') #保存该excel文件,有同名文件时直接覆盖 wb.save('D:\\workspace\\eclipse-python\\test\\test.xls') print u'excel文件创建完毕!'
过滤excel文件xlutils:
#coding=utf-8 ############ #修改文件数据,新增sheet ############ import xlrd from xlrd import open_workbook import xlutils.copy #打开一个已经存在的workbook rb=open_workbook('D:\\workspace\\eclipse-python\\test\\myexcel.xls') wb=xlutils.copy.copy(rb) ws=wb.get_sheet(0) #获取sheet对象 ws.write(1,1,'changed!!') #写入数据 wb.add_sheet('seed',cell_overwrite_ok=True) #新增1个sheet #利用保存时同名覆盖达到修改excel文件的目的,注意未被修改的内容保持不变 wb.save('D:\\workspace\\eclipse-python\\test\\myexcel.xls')