Python读写Excel

xlrd模块读取excel

处理流程

1. 导入模块2. 打开Excel文件读取数据3. 获取一个工作表sheet = book.sheets()[0]          #通过索引顺序获取sheet = book.sheet_by_index(0) #通过索引顺序获取sheet = book.sheet_by_name(u'Sheet1')#通过名称获取4.获取整行和整列的值（数组）sheet.row_values(i)sheet.col_values(i)5.获取行数和列数nrows = sheet.nrowsncols = sheet.ncols6.循环行列表数据for i in range(nrows ):  print table.row_values(i)7.获取单元格cell_A1 = table.cell(0,0).value cell_A1 = table.row(0)[0].valuecell_C4 = table.cell(2,3).valuecell_A2 = table.col(2)[3].value8.简单的写入row = 0 col = 0 # 类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 errorctype = 1 value = '单元格的值' xf = 0 # 扩展的格式化 table.put_cell(row, col, ctype, value, xf) table.cell(0,0)       #单元格的值'table.cell(0,0).value #单元格的值'

xlwt模块写入excel

处理流程

1. 导入xlwt模块2. 新建一个excel文件file = xlwt.Workbook()3. 新建一个sheettable = file.add_sheet('sheet name')4. 写入数据table.write(行,列,value)table.write(0,0,'test')如果对一个单元格重复操作，会引发returns error:# Exception: Attempt to overwrite cell:# sheetname=u'sheet 1' rowx=0 colx=0所以在打开时加cell_overwrite_ok=True解决table = file.add_sheet('sheet name',cell_overwrite_ok=True)5. 保存文件file.save('demo.xls')6.其它使用stylestyle = xlwt.XFStyle() #初始化样式font = xlwt.Font()     #为样式创建字体font.name = 'Times New Roman'font.bold = Truestyle.font = font    #为样式设置字体table.write(0, 0, 'some bold Times text', style) # 使用样式xlwt 允许单元格或者整行地设置格式。还可以添加链接以及公式。可以阅读源代码，那里有例子：dates.py, 展示如何设置不同的数据格式hyperlinks.py, 展示如何创建超链接 (hint: you need to use a formula)merged.py, 展示如何合并格子row_styles.py, 展示如何应用Style到整行格子中.

示例demo

# -*- coding:utf-8 -*-#!/usr/bin/python'''Script Name     : parsexls.pyAuthor          : svoidCreated            : 2015-02-25Last Modified    : Version            : 1.0Modifications    : Description    : 处理读写Excel'''import xlrd     #读取Excelimport xlwt     #写入Excelimport codecsimport os'''Description        :读取Excel内容并保存到文本文件中@param textfilename:写入文本文件@param xlsname     :读取Excel文件@return 无'''def readxls(excelName,textfilename):  #打开Excel文件读取数据  book = xlrd.open_workbook(excelName)  for i in range(len(book.sheets())):    sheet = book.sheets()[i]    nrows = sheet.nrows     #读取工作页行数    ncols = sheet.ncols     #读取工作页列数    for i in range(nrows):      info = ''      for j in range(len(sheet.row_values(i))):        if isinstance(sheet.row_values(i)[j],(int,float,complex)):          info += str(int(sheet.row_values(i)[j]))+'\t'        else:          info += sheet.row_values(i)[j]+'\t'      print(info)      saveFile(textfilename,info)'''Description    : 将数据按行保存到文件中@param savefilename:保存文件内容文本文件@param data :需要保存的数据@return 无'''def saveFile(savefilename,data):  f=codecs.open(savefilename,"a","utf-8")  f.writelines(data)  f.writelines('\n')  f.close()'''Description    : 文本转换成Excel的函数@param filename:输入文本文件@param excelName :输出Excel文件@return 无'''def txt2xls(filename,excelName):  f = codecs.open(filename,'r','gbk')  x = 0  y = 0  xls = xlwt.Workbook()  sheet = xls.add_sheet('个人信息',cell_overwrite_ok=True)  while True:    line = f.readline()    #读取文本行内容    print(line)    if not line :      break    for i in line.split('\t'):      item = i.strip()      sheet.write(x,y,item)      y += 1    x += 1    y = 0  f.close()   #读取完成之后关闭文件  xls.save(excelName)if __name__ == '__main__':  readxls("D:\\test.xlsx","D:\\test.txt")  txt2xls("D:\\test1.txt","D:\\test1.xls")

整理自网络

Svoid
2015-02-25