美文网首页
python 操作excel文件的库xlrd,xlwt的使用de

python 操作excel文件的库xlrd,xlwt的使用de

作者: LeoceanY桑 | 来源:发表于2016-08-01 00:58 被阅读559次

注释较为全面,参照注释使用即可。

库的安装: 从官网下载源码后,解压,cmd进到对应目录下,执行 python setup.py install 即可。

注意:1. xlrd 只能对文件进行读。2. xlwt 只能写。3. 两者的文件对象不能直接通用。

'''

# -*- coding: utf-8 -*-

import xlrd

import xlwt

from datetime import date,datetime

def edit_and_sava_as_excel():

workbook = xlrd.open_workbook(r'C:\Users\LiYang\Desktop\1.xls')  # 获取所有sheet

print workbook.sheet_names() # [u'Sheet1', u'Sheet2']

sheet1_name = workbook.sheet_names()[0]

sheet1 = workbook.sheet_by_name('Sheet1')

print sheet1.name," row="+str(sheet1.nrows)," clo="+str(sheet1.ncols)

wbk2 = xlwt.Workbook()

sheet2 = wbk2.add_sheet('Sheet2')

for i in range(2,sheet1.nrows):

row = sheet1.row_values(i)    # 获取第i行内容

if row[2] == u'\u5973\u751f':  # 如果是女生

row[3] = 'F'+row[3]

for j in range(4,7):

if row[j] != u'':

row[j] = 'M'+row[j]

else:

row[3] = 'M'+row[3]

for j in range(4,7):

if row[j] != u'':

row[j] = 'F'+row[j]

for j in range(0,7):

sheet2.write(i,j,row[j])

wbk2.save(r'C:\Users\LiYang\Desktop\2.xls')

def read_excel():  # 打开文件

workbook = xlrd.open_workbook(r'C:\Users\LiYang\Desktop\2.xls')  # 获取所有sheet

print workbook.sheet_names() # [u'Sheet1', u'Sheet2']

sheet1_name = workbook.sheet_names()[0]

# 根据sheet索引或者名称获取sheet内容

#sheet2 = workbook.sheet_by_index(1) # sheet索引从0开始

sheet1 = workbook.sheet_by_name('Sheet2')

# sheet的名称,行数,列数

print sheet1.name," row="+str(sheet1.nrows)," clo="+str(sheet1.ncols)

# 获取整行和整列的值(数组)

#rows = sheet1.row_values(3) # 获取第四行内容

cols4 = sheet1.col_values(3) # 获取四列内容

cols5 = sheet1.col_values(4)

cols6 = sheet1.col_values(5)

cols7 = sheet1.col_values(6)

print cols4

count=1

#从姓名列一个一个处理

for name in cols4:

print ""

print str(count)+" "+name

index = cols4.index(name)

print name + " select "+sheet1.cell(index,4).value.encode('utf-8')\

+"  "+sheet1.cell(index,5).value.encode('utf-8')\

+"  "+sheet1.cell(index,6).value.encode('utf-8')

count = count + 1

row = sheet1.row_values(cols4.index(name))                      #取出自己所在的整列元素

if name in cols5 and name != u'':

a = sheet1.cell(cols5.index(name),3).value                      #找到谁把你选为了五星

print "and " +a.encode('utf-8')+" select "+name

if a in row:

print a+" and "+name+" select each other!!!!!!!!!!!!!!!!!"

if name in cols6 and name != u'':

a = sheet1.cell(cols6.index(name),3).value                      #找到谁把你选为了四星

print "and " +a.encode('utf-8')+" select "+name

if a in row:

print a+" and "+name+" select each other!!!!!!!!!!!!!!!!!"

if name in cols7 and name != u'':

a = sheet1.cell(cols7.index(name),3).value                      #找到谁把你选为了三星

print "and " +a.encode('utf-8')+" select "+name

if a in row:

print a+" and "+name+" select each other!!!!!!!!!!!!!!!!!!"

# print rows[0].encode('utf-8'),rows[1].encode('utf-8')

# 获取单元格内容

#print sheet1.cell(1,0).value.encode('utf-8')

#print sheet1.cell_value(1,0).encode('utf-8')

#print sheet1.row(1)[0].value.encode('utf-8')

# 获取单元格内容的数据类型

#print sheet1.cell(1,0).ctype

if __name__ == '__main__':

#edit_and_sava_as_excel()

read_excel()

'''

相关文章

网友评论

      本文标题:python 操作excel文件的库xlrd,xlwt的使用de

      本文链接:https://www.haomeiwen.com/subject/eyjxsttx.html