python get sheet_python读写excel

#coding=utf-8

importxlrdimportxlwtimporttracebackfrom xlutils.copy importcopyimportsys

reload(sys)#需要加上，否则会报UnicodeEncodeError: 'ascii' codec can't encode characters in position 0-1: ordinal not in range(128)

sys.setdefaultencoding('utf-8')classExcelAction:'''只支持xls格式'''

deftransCode(self, filename, sheetname):

filename= filename.decode('utf-8')

sheetname= sheetname.decode('utf-8')returnfilename, sheetnamedefread_excel(self, filename, sheetname):'''读取excel'''filename, sheetname=self.transCode(filename, sheetname)

workbook= xlrd.open_workbook(filename) #获得工作薄

sheet = workbook.sheet_by_name(sheetname) #获得sheet

rows = sheet.nrows #文件总行数

list =[]print u'-------文件内容-------'

for i inrange(0, rows):

line= sheet.row_values(i) #获得一行的值，返回列表

list.append(line)#避免打印包含中文的列表时变成unicode

print '[' + ','.join("'" + str(element) + "'" for element in line) + ']'

print u'-----------------------'

returnlistdef write_excel(self, filename, sheetname, row, col, value, type=0):'''修改excel'''filename, sheetname=self.transCode(filename, sheetname)#转成整形是因为要在ride中使用，ride把参数传过来默认是字符串，除非这样传${1}

row =int(row)

col=int(col)

type=int(type)#formatting_info=True保存之前数据的格式

rb = xlrd.open_workbook(filename, formatting_info=True)

wb=copy(rb)

sheet=wb.get_sheet(sheetname)#设置样式，写入的文字为红色加粗

style = xlwt.easyxf('font: bold 1, color red;')if type == 1:

sheet.write(row, col, value, style)else:

sheet.write(row, col, value)

wb.save(filename)defaddSheet(self, filename, sheetname, row, valueList):'''写入excel,一次写一行'''filename, sheetname=self.transCode(filename, sheetname)

wb=xlwt.Workbook(filename)#其实会覆盖第一个sheet页

sheet =wb.add_sheet(sheetname)for i inrange(len(valueList)):#需要转码

valueList[i] = str(valueList[i]).decode('utf-8')

sheet.write(row, i, valueList[i])

wb.save(filename)if __name__ == '__main__':

ea=ExcelAction()

filename= r'G:\测试数据\最后的骑士.xls'sheetname= '第1页'list=ea.read_excel(filename, sheetname)#ea.write_excel(filename, sheetname, 0, 0, 'Optimus Prime')

#ea.write_excel(filename, sheetname, 0, 1, 'Megatron', 1)

#valueList = ['阿杜 - 烂好人', '阿杜 - 一诺千年', 'Coldplay - Hypnotised', 'Ruth B. - Superficial Love', '杨宗纬、张碧晨 - 凉凉']

#ea.addSheet(filename, '第2页', 0, valueList)