python 读写excel,基于win32com实现

 1 #注意,初始化 pip install pypiwin32
# 重新下载命令 : python -m pip install --upgrade pypiwin32 --force-reinstall
2 #如果是离线,则需要到pypiwin32官网下载文件,放入Python安装目录的Script目录下,再执行pip install pypiwin32 3 #!/usr/bin/python 4 import os 5 from win32com import client 6 7 pwd = os.getcwd() 8 excelApp = client.Dispatch("Excel.Application") 9 excelApp.Visible = False 10 11 class Employee: 12 '对象说明' 13 # 参数1 14 param1='' 15 #定义对象构造函数 16 def __init__(self,list): 17 self.param1 = list[0] 18 19 #nrows_1 = info_1.Rows.Count 20 #ncols_1 = info_1.Columns.Count 21 22 print ("Hello, Python!") 23 24 #打开excel文件 25 def openExcel(filePath): 26 excelBook = excelApp.Workbooks.Open(filePath) 27 return excelBook 28 #选择excel的Sheet 29 def openSheet(excelBook,sheetIndex): 30 excelSheet = excelBook.Worksheets(sheetIndex) 31 info = excelSheet.UsedRange 32 nrows = info.Rows.Count # sheet1行数 33 ncols = info.Columns.Count # sheet1列数 34 print("rows all:",nrows) 35 print("column all:",ncols) 36 return excelSheet 37 #读取excel Sheet中的内容 38 def readSheetDatas(excelSheet,startRow,endRow,startColumn,endColumn): 39 list = [] 40 while(startRow <= endRow): 41 columbList = [] 42 while(startColumn <= endColumn): 43 columbList.append(excelSheet.Cells(startRow,startColumn).Value) 44 print("row:",startRow,",column:",startColumn,",value:",excelSheet.Cells(startRow,startColumn).Value) 45 startColumn += 1 46 #将内容换成对象进行保存 47 emp = Employee(columbList) 48 list.append(emp) 49 startRow += 1 50 startColumn = 1 51 return list 52 #关闭exel 53 def closeExcel(excelBook): 54 excelBook.Close(True) 55 return 56 #对excel的sheet进行赋值 57 def setValue(excelSheet,rowIndex,columnIndex,value): 58 excelSheet.Cells(rowIndex, columnIndex).Value = value 59 return 60 #excel 另存,直接存则是 excelBook.Save() 61 def saveAsFile(excelBook,filePath): 62 try: 63 excelBook.SaveAs(filePath) 64 except: 65 closeExcel(excelBook) 66 else: 67 closeExcel(excelBook) 68 return 69 70 excelBook = openExcel("E:/test.xlsx") 71 72 excelSheet = openSheet(excelBook,1) 73 # read datas 74 list = readSheetDatas(excelSheet,4,18,1,14) 75 76 for emp in list : 77 print("参数1:",int(emp.param1)) 78 #如果是float或者double,比如excel显示的7,而获取的时候为7.0就需要类型强制转换 79 #转换为string类型 80 tmp = "参数1:"+str(int(emp.param1)) 81 print("tmp:",tmp) 82 #保存地址 83 #pwd 类似于 linux的指令,为当前执行python文件的路径 84 savePath = pwd + "\\test\\1.xlsx" 85 saveAsFile(excelBookWrite,savePath) 86 print("cycle for obj...") 87 print("cycle for end...") 88 89 90 91 #close table 92 closeExcel(excelBook) 93 94 #close excel file 95 excelApp.Quit()

 

posted @ 2020-12-28 10:28  SimpleSmile  阅读(573)  评论(0编辑  收藏  举报