python实现excel读写数据

本文实例为大家分享了python操作EXCEL的实例源码，供大家参考，具体内容如下

读EXCEL的操作：把excel的数据存储为字典类型

#coding=utf8 
 
#导入读excel的操作库 
import xlrd 
class GenExceptData(object): 
  def __init__(self): 
    try: 
      self.dataDic={} 
      #打开工作薄 
      self.wkbook= xlrd.open_workbook("Requirement.xls") 
      #获取工作表“requirement” 
      self.dataSheet=self.wkbook.sheet_by_name("requirement") 
      #把数据按 按照相应格式写入excel表中 
      self.readDataToDicl() 
      #保存文件 
    except Exception,e: 
      print "Read Excel error:",e 
  
  def readDataToDicl(self): 
    try: 
      nrows = self.dataSheet.nrows 
      ncols = self.dataSheet.ncols 
       
      print ncols ,nrows 
      try: 
        for rowNum in range(1,nrows):     
              #把数据的当前行的元素与上一行元素作比较 
              #如果不相等执行if语句 
              try: 
                 
                singleJson={} 
                propertyName=self.dataSheet.cell(rowNum,3).value 
                propertyValue=self.dataSheet.cell(rowNum,4).value 
                             
                if self.dataSheet.cell(rowNum,0).value and self.dataSheet.cell(rowNum,2).value: 
                  mdEvent=self.dataSheet.cell(rowNum,0).value 
                  singleJson["serviceId"]=self.dataSheet.cell(rowNum,2).value 
                singleJson[propertyName]=propertyValue                
                print singleJson 
                self.dataDic[mdEvent]=singleJson 
                singleJson.clear() 
                 
                 
              except Exception,e: 
                print "Get Data Error:",e 
      except Exception,e: 
        print "Reading Data Error:",e 
    except Exception,e: 
      print "Reading Data TO Dic Error:",e 
 
   
          
     
def test(): 
  GenExceptData() 
   
if __name__=="__main__": 
  test()

写EXCEL的操作：把csv文件的数据按照需求写入到excel文件中

#coding=utf8 
 
from readCSV import readCSV 
import xlwt 
 
class GenTestCase(): 
  def __init__(self,path="E:\\PythonDemo\\OperExcel\\Demo.csv"): 
    self.dataInfor=readCSV(path) 
    #创建工作薄 
    self.wkbook=xlwt.Workbook() 
    #创建表:“埋点需求” 
    self.dataSheet=self.wkbook.add_sheet("shellt") 
    self.creatHead() 
     
  def creatHead(self): 
    firstLine=[] 
    #创建表头 
    for index in range(len(firstLine)): 
      self.dataSheet.write(0,index,firstLine[index]) 
       
    dataBody=self.dataInfor.buffer 
    print dataBody.__len__() 
    currentrow=1 
    for rowNum in range(1,len(dataBody)): 
      for index in range(len(dataBody[rowNum])): 
        if rowNum>1: 
          if dataBody[rowNum-1][0]!=dataBody[rowNum][0] : 
            print currentrow,rowNum 
            if currentrow==1: 
              for cols in range(3): 
                cellValue=dataBody[currentrow][cols] 
                cellValue=cellValue.decode("gbk") 
                data=u"%s" %(cellValue) 
                self.dataSheet.write_merge(currentrow,rowNum-1,cols,cols,data) 
                 
              for cols in range(6,13): 
                cellValue=dataBody[currentrow][cols] 
                cellValue=cellValue.decode("gbk") 
                data=u"%s" %(cellValue) 
                self.dataSheet.write_merge(currentrow,rowNum-1,cols,cols,data)    
            else:   
              for cols in range(3): 
                cellValue=dataBody[currentrow][cols] 
                cellValue=cellValue.decode("gbk") 
                data=u"%s" %(cellValue) 
                self.dataSheet.write_merge(currentrow-1,rowNum-1,cols,cols,data) 
              for cols in range(6,12): 
                cellValue=dataBody[currentrow][cols] 
                cellValue=cellValue.decode("gbk") 
                data=u"%s" %(cellValue) 
                self.dataSheet.write_merge(currentrow-1,rowNum-1,cols,cols,data)                       
            currentrow=rowNum+1              
          break 
       
      for cols in range(3,6):        
        cellValue=dataBody[rowNum][cols] 
        cellValue=cellValue.decode("gbk") 
        data=u"%s" %(cellValue) 
        self.dataSheet.write(rowNum,cols,data) 
                 
    self.wkbook.save(r'reqq.xlsx') 
   
          
     
def test(): 
  GenTestCase() 
   
if __name__=="__main__": 
  test()