python操作EXCEL的操作实例源码(读数据、写数据)

来源：互联网发布：乐其网络编辑：程序博客网时间：2024/06/04 18:42

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

#coding=utf8#导入读excel的操作库import xlrdclass 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=utf8from readCSV import readCSVimport xlwtclass 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()

阅读全文

0 0