python解析excel解析读取excel工具，主要用于跟excel文件相关的处理

# coding=utf-8 Date = 2016 / 8 / 17; __author__ = 'rtc' #useAge: #import excelReader #reader = excelReader.getReader(); #reader.Read(excelPath); #excelInfo = reader.GetExcelInfoByPathAndSheetName(excelPath,"Main"); #excelRows = excelInfo.GetExcelRows(); #for row in excelRows: # rowCells = row.GetRowCells(); # for cell in rowCells: # print("key = %s,v = %s,type=%s"%(cell.GetKey(),cell.GetValue(),cell.GetType())); import os import xlrd import xlwt import sys reload(sys) sys.setdefaultencoding('utf8'); # 根据标准Excel数据类型定义Cell FUN = range(1); CELL_TYPE = FUN ( XL_CELL_EMPTY, XL_CELL_TEXT, XL_CELL_NUMBER, XL_CELL_DATE, XL_CELL_BOOLEAN, XL_CELL_ERROR, XL_CELL_BLANK, # for use in debugging, gathering stats, etc ) = range(7); curFilePath = os.path.realpath(__file__); curDir = os.path.dirname(curFilePath); wrongFilePath = os.path.join(curDir, "wrongList"); # 存储每个excel的数据 excelList = []; # 存储未读取成功的excel数据 wrongList = []; # key所在的行的cell的数据类型标志 defaultStartKeyType = XL_CELL_TEXT; defaultSheetName = "Main"; readerInstance = None; ERRORTYPE = FUN ( FILENOTEXIST, FILEINFONOTFOUND, ) = range(2); errorMsg = ["excelFile does not exist", "can not find excelInfo"] class LogInfo: def __init__(self, msg, path): self.msg = msg; self.path = path; def GetMsg(self): return self.msg; def GetFilePath(self): return self.path; # 每个cell的数据已经key class CellInfo: def __init__(self, cellKey): self.cellKey = cellKey; def SetCellInfo(self, colIndex,cellValue=0, cellType=XL_CELL_NUMBER): self.cellValue = cellValue; self.cellType = cellType; self.colIndex = colIndex; def GetKey(self): return self.cellKey; def GetValue(self): return self.cellValue; def SetValue(self,value): self.cellValue = value def GetType(self): return self.cellType def GetColIndex(self): return self.colIndex; # excel每行的数据 class RowInfo: def __init__(self, rowIndex): self.rowCells = []; self.rowIndex = rowIndex; def SetRowInfo(self, colIndex,cellKey, cellValue, cellType): cellInfo = self.CreateRowCellByKey(cellKey); cellInfo.SetCellInfo(colIndex,cellValue, cellType); def GetRowCells(self): return self.rowCells; def GetRowIndex(self): return self.rowIndex; def CreateRowCellByKey(self, cellKey): rowCell = self.GetRowCellByKey(cellKey); if rowCell == None: rowCell = CellInfo(cellKey); self.rowCells.append(rowCell); return rowCell; def GetRowCellByKey(self, cellKey): rowCell = None; for i in self.rowCells: if i.GetKey() == cellKey: rowCell = i; break; return rowCell; # 每个excel文件存储的所有行数据 class ExcelInfo: def __init__(self, filePath, sheetName=defaultSheetName): self.rows = []; self.filePath = filePath; self.sheetName = sheetName; def SetExcelInfo(self, rowIndex,colIndex, cellKey, cellValue, cellType): rowInfo = self.CreateRowByIndex(rowIndex); rowInfo.SetRowInfo(colIndex,cellKey, cellValue, cellType) def GetExcelRows(self): return self.rows; def GetFilePath(self): return self.filePath; def GetSheetName(self): return self.sheetName; def CreateRowByIndex(self, rowIndex): row = self.GetRowByIndex(rowIndex); if row == None: row = RowInfo(rowIndex); self.rows.append(row); return row; def GetRowByIndex(self, rowIndex): row = None; for i in self.rows: if i.GetRowIndex() == rowIndex: row = i; break; return row; class ReadExcel: def __init__(self): self.excelList = []; self.excelPath = ""; self.sheetName = defaultSheetName; self.startKeyType = defaultStartKeyType; self.startKeyRow = None; print("ReadExcel construct success"); #默认从0开始 def SetKeyRow(self,keyRow): self.keyRow = keyRow; def Read(self, excelPath, startKeyType=defaultStartKeyType, sheetName=defaultSheetName): self.excelPath = excelPath; self.startKeyType = startKeyType; self.sheetName = sheetName; print("start read file::" + excelPath); self.readExcelFile(self.excelPath, self.sheetName, self.startKeyType); def ClearExcelData(self,excelPath): for i in self.excelList: if i.GetFilePath() == excelPath: self.excelList.remove(i) def GetExcelInfoByPathAndSheetName(self, excelPath, sheetName=defaultSheetName, isReadAgain=True): excelInfo = None; if not excelPath: print("error file path not exist:: %s" % excelPath); return excelInfo; for i in self.excelList: if (i.GetFilePath() == excelPath) and (i.GetSheetName() == sheetName): excelInfo = i; break; if isReadAgain and excelInfo == None: startKeyType = 10; print("not find excel info by path and sheetName ::excelPath=%s,sheetName=%s" % (excelPath, sheetName)); print("start read again by change startKeyType:: startKeyType=%s" % startKeyType); self.Read(excelPath, startKeyType, sheetName); return self.GetExcelInfoByPathAndSheetName(excelPath, sheetName, False); return excelInfo; def getKeyRow(self): return self.keyRow def findKeyRow(self, sheetV, startKeyType): rowIndex = 0; if self.getKeyRow() !=None: return self.getKeyRow(); startKeyType = self.getStartKeyRow(startKeyType) for row in range(1, sheetV.nrows): cellType = sheetV.cell_type(row, 0); if cellType == startKeyType: rowIndex = row; break; print("startKeyRow=%s" % rowIndex); return rowIndex # 读取excel每行数据 def readFileItemFromExcelSheet(self, sheetV, excelPath, startKeyType, sheetName): sheetKeyRow = self.findKeyRow(sheetV, startKeyType); excelInfo = self.createExcelInfo(excelPath, sheetName); for row in range(0, sheetV.nrows): if sheetV.cell_value(row, 0) == "": continue for col in range(sheetV.ncols): key = sheetV.cell_value(sheetKeyRow, col); if self.checkIfKeyValid(key): cellType = sheetV.cell_type(row, col); cellValue = sheetV.cell_value(row, col); if cellType == XL_CELL_TEXT: if cellValue == "": cellValue = "" cellValue = str(cellValue); elif cellType == XL_CELL_NUMBER: if cellValue == "": cellValue = 0; cellValue = (int)(cellValue) else: if cellValue == "": cellValue = 0; cellValue = cellValue; if cellValue != "": self.insertExcelInfo(excelInfo, row, col, key, cellValue, cellType); def readExcelFile(self, excelPath, sheetName, startKeyType): sheetName = self.getSheetName(sheetName); if self.checkIfExcelFileCanRead(excelPath): excelData = xlrd.open_workbook(excelPath); for sheetN in excel