python实现excel读写数据

时间:2022-11-07 16:40:10

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
#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文件中

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
#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()

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:https://blog.csdn.net/henni_719/article/details/78280968