之前写过一篇关于获取excel数据进行迭代的方法,今天补充上写入的方法。由于我用的是Python3,不兼容xlutils,所以无法使用copy excel的方式来写入。这里使用xlwt3创建excel后,将原有的excel数据输入到新建的excel,写入->保存->移除原有excel。
1.首先安装xlwt3,下载链接:https://pypi.python.org/pypi/xlwt3/0.1.2
2.code:
import os
import xlrd
import xlwt3
class ExcelUtil(object):
def __init__(self, excelPath, sheetName):
self.path = excelPath
self.name = sheetName
self.data = None
self.table = None
self.row = None
self.rowNum = None
self.colNum = None
self.curRowNo = 1
def openExcel(self):
self.data = xlrd.open_workbook(self.path, formatting_info=True)
self.table = self.data.sheet_by_name(self.name)
self.row = self.table.row_values(0)
self.rowNum = self.table.nrows
self.colNum = self.table.ncols
def next(self):
self.openExcel()
r = []
try:
while self.hasNext():
s = {}
col = self.table.row_values(self.curRowNo)
i = self.colNum
for x in range(i):
s[self.row[x]] = col[x]
r.append(s)
self.curRowNo += 1
self.curRowNo = 1
except Exception as ex:
print(ex)
return r
def hasNext(self):
if self.rowNum != 0 and self.rowNum > self.curRowNo :
return True
else:
return False
def write(self, titleName, value):
self.openExcel()
columnNum = self.getColNum(titleName)
book = xlwt3.Workbook(encoding='utf-8', style_compression=0)
#cell_overwrite_ok=True参数是为了可以修改原来的cell
sheet = book.add_sheet(self.name, cell_overwrite_ok=True)
#将原来的excel数据添加到新建的excel中
for x in range(self.rowNum):
row = self.table.row_values(x)
for i in range(self.colNum):
sheet.write(x, i, row[i])
#写入当前迭代那一行的excel
if self.hasNext():
sheet.write(self.curRowNo, columnNum, value)
self.curRowNo += 1
os.remove(self.path)
book.save(self.path)
def getColNum(self, titleName):
titleColNum = -1
try:
for i in range(len(self.row)):
if self.row[i] == titleName:
titleColNum = i
break
else:
continue
except Exception as ex:
print(ex)
return titleColNum
3.这里执行xlwt3的write方法时,会提示:ValueError: ‘init’ in slots conflicts with class variable。
打开Python35\Lib\site-packages\xlwt3\formula.py文件,将其中的
slots = [“init“, “__s”, “__parser”, “__sheet_refs”, “__xcall_refs”]
修改为
slots = [ “__s”, “__parser”, “__sheet_refs”, “__xcall_refs”]
这时再执行就OK了!