python--excelReader_skybeauty_新浪博客

# coding=utf-8
__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 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,cellValue=0,cellType=XL_CELL_NUMBER):
        self.cellValue = cellValue;
        self.cellType = cellType;

    def GetKey(self):
        return self.cellKey;

    def GetValue(self):
        return self.cellValue;

    def GetType(self):
        return self.cellType

#excel每行的数据
class RowInfo:
    def __init__(self,rowIndex):
        self.rowCells =[] ;
        self.rowIndex = rowIndex;

    def SetRowInfo(self,cellKey,cellValue,cellType):
        cellInfo = self.CreateRowCellByKey(cellKey);
        cellInfo.SetCellInfo(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,cellKey,cellValue,cellType):
        rowInfo = self.CreateRowByIndex(rowIndex);
        rowInfo.SetRowInfo(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;
        print("ReadExcel construct success");

    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 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 findKeyRow(self,sheetV,startKeyType):
        rowIndex = 0;
        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(sheetKeyRow+1,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 = "%s"�llValue
                    elif cellType ==XL_CELL_NUMBER: