#Copyright (c) 2006-2007 SunMedia technology (Chengdu) co. Ltd. 
#All right reserved
#Author:jin.hu
#date:2009/09/15
import re
import os
import sys
import time
import string
import codecs
import xlrd
import xlwt
from types import *

class ParserXLS:
    def __init__ (self):
        self.xlsfile = None
        self.sheet = None
        self.columnNumber = 0
        self.rowNumber = 0
        self.netStructNumber = 0
        self.TPStructNumber = 0
        self.serviceStructNumber = 0
        
        self.satelliteData = []
        self.TPData = []
        self.serviceData = []

        self.tpFlag = []

        self.coulumnIndex = 0
 
    def setNetNumber(self, netNumber):
        self.netStructNumber = netNumber
    def setTPNumber(self, TPNumber):
        self.TPStructNumber = TPNumber
    def setServiceNumber(self, serviceNumber):
        self.serviceStructNumber = serviceNumber

    def parserXLS (self, filePath):
        self.xlsfile = xlrd.open_workbook(filePath,formatting_info=True)
        self.setSheet('DB Tool')
        self.getCurSheetCols()
        self.getCurSheetRows()
 
    def setSheet (self, sheetName):
        self.sheet = self.xlsfile.sheet_by_name(sheetName)
        if self.getCurSheetCols() == 0:
            return 0
    
    def parserXLSData(self):
        for row in range(1, self.rowNumber):
            self.parserNetData(row)
            self.parserTPData(row)
            self.parserServiceData(row)

    def parserNetData(self, row):
        singleNetData = []
        colunm = 0
        for colunm in range(self.netStructNumber):
            singleNetData.append(self.getString(row, colunm))
        if singleNetData not in self.satelliteData:
            self.satelliteData.append(singleNetData)
    def parserTPData(self, row):
        singleTPData = []
        bAdd = 0
        for colunm in range(self.netStructNumber, self.netStructNumber+self.TPStructNumber):
            if self.getString(row, colunm) != '':
                bAdd = 1
            singleTPData.append(self.getString(row, colunm))
        singleTPFlag = []
        for colunm in range(self.netStructNumber+self.TPStructNumber):
            singleTPFlag.append(self.getString(row, colunm))
        if singleTPFlag not in self.tpFlag and bAdd == 1:
            self.tpFlag.append(singleTPFlag)
            self.TPData.append(singleTPData)
    def parserServiceData(self, row):
        singleServiceData = []
        bAdd = 0
        for colunm in range(self.netStructNumber+self.TPStructNumber, self.columnNumber):
            if self.getString(row, colunm) != '':
                bAdd = 1
            singleServiceData.append(self.getString(row, colunm))
        if bAdd == 1:
            self.serviceData.append(singleServiceData)

    def getSatData(self):
        return self.satelliteData
    def getTPData(self):
        return self.TPData
    def getServiceData(self):
        return self.serviceData

    def getElementName (self, col):
        elementName = self.sheet.cell_value(0, col)
        return elementName

    def getColumn (self, languageName):
        for i in range(self.getCurSheetCols()):
            if self.getLanguageName(i) == languageName:
                return i
        return -1

    def getCurSheetCols (self):
        self.columnNumber = self.sheet.ncols

    def getCurSheetRows (self):
        self.rowNumber = self.sheet.nrows
	
    def getString (self, nRow, nCol):
        value = self.sheet.cell_value(nRow, nCol)
        if type(value) is not UnicodeType and type(value) is not StringType:
            #because the excel file number is float type
            value = int(value)
            value = str(value)
        return value

    def getStringID (self, nRow):
        return self.getString(nRow, 0)

class ExportXLS:
    def __init__ (self, sheetName):
        self.mergedCount = 0
        self.workBook = xlwt.Workbook('utf8')
        self.sheetBook = self.workBook.add_sheet(sheetName)
        
    def writeColunmName(self, columnIndex, columnName):
        self.sheetBook.write(0, columnIndex, columnName)
    
    def writeColunmValue(self, columnIndex, rowIndex, rowValue):
        self.sheetBook.write(rowIndex, columnIndex, rowValue)

    def generateFile (self, path):
        self.workBook.save(path)
        self.row = 1
        self.column = 1

def parserXLS():
    c = ParserXLS()
    c.parserXLS('E:\\DBTool\\test.xls.xls')
    c.setNetNumber(10)
    c.setTPNumber(11)
    c.setServiceNumber(14)
    c.parserXLSData()
    for i in  c.serviceData:
        print i
    #print c.TPData
    #print c.serviceData

def test2 ():
    c = ParserXLS('E:\\python script\\a.xls')
    c.parserXLS()
    c.setSheet('Language')
    c.getLanguage('USE_ENGLISH_OSD')
    c.getLanguage('USE_SCHINESE_OSD')
    c.getLanguage('USE_TCHINESE_OSD')
    c.getLanguage('USE_FRENCH_OSD')
    c.getLanguage('USE_DEUTSCH_OSD')
    print c.getStringActive(42)
    print c.getStringGroupID(42)
    print c.getStringID(42)
    c.creatFile('E:\\python script\\a.c')
    c.writeData('ff')

if __name__ == '__main__':
    testSDX()