# 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