#!/usr/bin/python
# -*- coding: utf-8 -*-
# 这段代码主要的功能是把excel表格转换成utf-8格式的json文件
# lastdate:2011-8-15 14:21 version 1.1

"""
https://github.com/zdhsoft/excel2json
pip install pyinstaller
pyinstaller -F excel2json.py
"""

import os
import sys
import codecs
from shutil import copy
import json
import xlrd  # http://pypi.python.org/pypi/xlrd
from collections import OrderedDict
from enum import Enum
import types
import argparse
import datetime
import hashlib
import platform;
from colorama import Fore, Back, Style, init



# 缓存目录的路径
CACHE_PATH = "__cache"
# 客户端输出目录
ClientPath = "Client"
# 服务器端输出目录
ServerPath = "Server"

# 字段名所在行
FIELD_ROW = 0
# 数据类型所在行
TYPE_ROW = 1
# 数据类别所在行
CATEGORY_ROW = 2
# 数据开始行
DATA_ROW = 4

# 导出分类
CATEGORY_UNKNOW = 0
CATEGORY_CLIENT = 1
CATEGORY_SERVER = 2
CATEGORY_BOTH = 3

class CategoryType(Enum):
    UNKNOW = 0
    CLIENT = 1
    SERVER = 2
    BOTH   = 3

AccessCategoryType = {'C': CategoryType.CLIENT, 'S': CategoryType.SERVER, 'C&S': CategoryType.BOTH}

def is_in_category_type(t1, t2):
    if t1 == 'C' and (t2 == 'C' or t2 == 'C&S'):
        return True
    if t1 == 'S' and (t2 == 'S' or t2 == 'C&S'):
        return True
    return False

# 数据类型枚举
class DataType(Enum):
    NONE = 0
    INT = 1
    FLOAT = 2
    BOOL = 3
    STRING = 4
    ARRAY = 5
    OBJECT = 6

AccessDataType = {'int': DataType.INT, 'float': DataType.FLOAT, 'bool': DataType.BOOL,'string': DataType.STRING,'array': DataType.ARRAY, 'object': DataType.OBJECT}

# 字符串转类型枚举
def convertDataType(colType):
    if not colType in AccessDataType:
        return DataType.NONE
    return AccessDataType[colType]

# 表格类型
class TabType(Enum):
    NONE = 0
    TAB_ARRAY = 1   # array类型
    TAB_MAP = 2     # map类型 
    TAB_KV = 3      # kv类型

AccessTabType = {'array': TabType.TAB_ARRAY, 'map': TabType.TAB_MAP, 'kv': TabType.TAB_KV}


# 导出类型
class ExportType(Enum):
    NONE = 0
    JSON = 1
    LUA = 2
    TS = 3

AccessExportType = {'.json': ExportType.JSON, '.lua': ExportType.LUA, '.ts': ExportType.TS}

# 校验导出类型是否合法
def check_export(suffix):

    pass

# kv 类型默认导出列
DefaultKVFields = {'key':0,'value':1,'type':2,'required':3,'desc':4}


def hump2underline(hump_str):
    """
    驼峰形式字符串转成下划线形式
    :param hump_str: 驼峰形式字符串
    :return: 字母全小写的下划线形式字符串
    """
    # 匹配正则，匹配小写字母和大写字母的分界位置
    p = re.compile(r'([a-z]|\d)([A-Z])')
    # 这里第二个参数使用了正则分组的后向引用
    sub = re.sub(p, r'\1_\2', hump_str).lower()
    return sub


def underline2hump(underline_str):
    """
    下划线形式字符串转成驼峰形式
    :param underline_str: 下划线形式字符串
    :return: 驼峰形式字符串
    """
    # 这里re.sub()函数第二个替换参数用到了一个匿名回调函数，回调函数的参数x为一个匹配对象，返回值为一个处理后的字符串
    sub = re.sub(r'(_\w)', lambda x: x.group(1)[1].upper(), underline_str)
    return sub


def json_hump2underline(hump_json_str):
    """
    把一个json字符串中的所有字段名都从驼峰形式替换成下划线形式。
    注意点：因为考虑到json可能具有多层嵌套的复杂结构，所以这里直接采用正则文本替换的方式进行处理，而不是采用把json转成字典再进行处理的方式
    :param hump_json_str: 字段名为驼峰形式的json字符串
    :return: 字段名为下划线形式的json字符串
    """
    # 从json字符串中匹配字段名的正则
    # 注：这里的字段名只考虑由英文字母、数字、下划线组成
    attr_ptn = re.compile(r'"\s*(\w+)\s*"\s*:')
    # 使用hump2underline函数作为re.sub函数第二个参数的回调函数
    sub = re.sub(attr_ptn, lambda x: '"' + hump2underline(x.group(1)) + '" :', hump_json_str)
    return sub

class ComplexEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime.datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, date):
            return obj.strftime('%Y-%m-%d')
        else:
            return json.JSONEncoder.default(self, obj)


# 打印错误
def printError(errStr):
    print (Back.BLUE + "**************************************************")
    print (Fore.RED + errStr)
    print (Back.BLUE + "**************************************************")

# 获取文件md5
def getFileMD5(filePath):
    if not (os.path.exists(filePath)):
        return ""
    srcFile = open(filePath, "rb")
    srcData = srcFile.read()
    srcFile.close()

    m = hashlib.md5()
    m.update(srcData)
    return m.hexdigest()

# 删除文件
def removeFile(filePath):
    if os.path.exists(filePath):
        os.remove(filePath)

# 拷贝文件
def copyFile(src, dst):
    removeFile(dst)
    copy(src, dst)


def spaceString(layer):
    lua_str = ""
    for i in range(0, layer):
        lua_str += '\t'
    return lua_str


def objToLuaString(data, layer=0):
    d_type = type(data)
    if d_type is list:
        lua_str = "{\n"
        lua_str += spaceString(layer+1)
        for i in range(0, len(data)):
            lua_str += ("[" + str(i+1) + "] = ")
            lua_str += dicToLuaString(data[i], layer+1)
            if i < len(data)-1:
                lua_str += ','
        lua_str += '\n'
        lua_str += spaceString(layer)
        lua_str += '}'
        return lua_str
    elif d_type is dict:
        lua_str = ''
        lua_str += "{\n"
        data_len = len(data)
        data_count = 0
        for k, v in data.items():
            data_count += 1
            lua_str += spaceString(layer+1)
            if type(k) is int:
                lua_str += '[' + str(k) + ']'
            else:
                lua_str += '["' + str(k) + '"]'
            lua_str += ' = '
            try:
                lua_str += dicToLuaString(v, layer + 1)
                if data_count < data_len:
                    lua_str += ',\n'

            except :
                print('error in ', k, v)
                raise
        lua_str += '\n'
        lua_str += spaceString(layer)
        lua_str += '}'
        return lua_str
    else:
        print(d_type, 'is error')
        return None


def dicToLuaString(data, layer=0):
    d_type = type(data)
    if d_type is type(None):
        return 'nil'
    elif d_type is bytes or d_type is str or d_type is str:
        return "'" + data + "'"
    elif d_type is bool:
        if data:
            return 'true'
        else:
            return 'false'
    elif d_type is int or d_type is int or d_type is float:
        return str(data)
    elif d_type is list:
        lua_str = "{\n"
        lua_str += spaceString(layer+1)
        for i in range(0, len(data)):
            lua_str += ("[" + str(i+1) + "] = ")
            lua_str += dicToLuaString(data[i], layer+1)
            if i < len(data)-1:
                lua_str += ','
        lua_str += '\n'
        lua_str += spaceString(layer)
        lua_str += '}'
        return lua_str
    elif d_type is dict:
        lua_str = ''
        lua_str += "\n"
        lua_str += spaceString(layer)
        lua_str += "{\n"
        data_len = len(data)
        data_count = 0
        for k, v in data.items():
            data_count += 1
            lua_str += spaceString(layer+1)
            if type(k) is int:
                lua_str += '[' + str(k) + ']'
            else:
                lua_str += '["' + str(k) + '"]'
            lua_str += ' = '
            try:
                lua_str += dicToLuaString(v, layer + 1)
                if data_count < data_len:
                    lua_str += ',\n'

            except e:
                print ('error in ', k, v)
                raise
        lua_str += '\n'
        lua_str += spaceString(layer)
        lua_str += '}'
        return lua_str
    else:
        print(d_type, 'is error')
        return None


def IsJson(strValue):
    try:
        obj = json.loads(strValue, encoding='utf-8')
        d_type = type(obj)
        if d_type is list:
            return True
        elif d_type is dict:
            return True
        return False
    except ValueError:
        return False
    return False


def IsInt(strValue):
    try:
        int(strValue)
    except ValueError:
        return False
    return True


def IsFloat(strValue):
    try:
        float(strValue)
    except ValueError:
        return False
    return True


def FloatToString(aFloat):
    if type(aFloat) != float:
        return u""
    strTemp = str(aFloat)
    strList = strTemp.split(".")
    if len(strList) == 1:
        return strTemp
    else:
        if strList[1] == "0":
            return u""+strList[0]
        else:
            return u""+strTemp


def ParseExtType(paramExtType):
    strList = paramExtType.split(":")
    retType = {}
    if len(strList) == 2:
        retType["key"] = strList[0]
        retType["type"] = strList[1]
    else:
        retType["key"] = ""
        retType["type"] = ""
    return retType


# 查找第1个非要求的字符串的下标
def findFirstNot(str, begin, substr):
    for i in range(begin, len(str)):
        if substr.find(str[i]) == -1:
            return i
    return -1




# 解析filter字符串，返回变量数组
def parseFilterKey(filter):
    ret = []
    begin = 0
    while True:
        index = filter.find("$", begin)
        if index >= 0:
            index += 1
            end = findFirstNot(
                filter, index, "1234567890abcdefghijklmnopqrstuvwxyz_ABC DEFGHIJKLMNOPQRSTUVWXYZ")
            key = filter[index:end]
            ret.append(key)
            begin = end
        else:
            return ret


# 读取字段列表
def readFields(paramFields):
    # mapField = {}
    mapField = OrderedDict()
    strList = paramFields.split(",")
    i = 0
    for f in strList:
        mapField[f] = i
        i += 1

    return mapField


# def CellToString(paramCell):
#     strCellValue = u""
#     if type(paramCell) == unicode:
#         strCellValue = paramCell
#     elif type(paramCell) == float:
#         strCellValue = FloatToString(paramCell)
#     else:
#         strCellValue = str(paramCell)
#     return strCellValue


def IsEmptyLine(paramTable, paramRow, paramFieldCount):
    linecnt = 0
    for i in range(paramFieldCount-1):
        v = paramTable.cell_value(paramRow, i)
        if type(v) == str:
            v = v
        elif type(v) == float:
            v = FloatToString(v)
        else:
            v = str(v)
        linecnt += len(v)
        if linecnt > 0:
            return False

    if linecnt == 0:
        return True
    else:
        return False


def IsEmptyGrid(data):
    if len(str(data)) == 0:
        return True
    else:
        return False


# 数据解析
def DealData(dataType, data):
    if data == "null":
        return True, data

    ok = False
    strValue = u""

    if dataType == DataType.INT:
        # 值为空，返回默认值
        if IsEmptyGrid(data):
            return True, u"0"
        # 这里读进来的整形 带.0
        if type(data) != float:
            return ok, strValue
        strValue = FloatToString(data)
        if IsInt(strValue) == False:
            return ok, strValue
        strValue = str(strValue)
    elif dataType == DataType.FLOAT:
        # 值为空，返回默认值
        if IsEmptyGrid(data):
            return True, u"0"
        if type(data) != float:
            return ok, strValue
        strValue = FloatToString(data)
    elif dataType == DataType.BOOL:
        # 值为空，返回默认值
        if IsEmptyGrid(data):
            return True, u"false"
        if data == 0:
            strValue = u"false"
        elif data == 1:
            strValue = u"true"
        else:
            return ok, strValue
    elif dataType == DataType.STRING:
        # 值为空，返回默认值
        if type(data) == "":
            return True, u"\"\""
        if type(data) == float:
            data = FloatToString(data)
        if type(data) != str:
            return ok, strValue
        # strValue = data.replace(u"\\", u"\\\\").replace(u"\"", u"\\\"")
        # strValue = strValue.replace(u"\n", u"")
        strValue = "\"" + data + u"\""
    elif dataType == DataType.ARRAY:
        ok = True
        strValue = json.dumps(json.loads(data, object_pairs_hook=OrderedDict))
    elif dataType == DataType.OBJECT:
        obj = data.replace(u"\\", u"")
        if IsJson(obj) == False:
            return ok, strValue
        strValue = obj
    else:
        return ok, strValue

    return True, strValue

# 表格转array
def table2Array(srcPath, category, table, filename, suffix,  mapTable, desc):
   # 如果有缓存文件则使用缓存文件
    md5 = getFileMD5(srcPath)
    cacheFile = os.path.join(CACHE_PATH, filename + "_" + md5 + suffix)
    dstFilePath = ""
    if category == "C":
        dstFilePath = os.path.join(ClientPath, filename + suffix)
    else:
        dstFilePath = os.path.join(ServerPath, filename + suffix)
    if os.path.exists(cacheFile):
        copy(cacheFile, dstFilePath)
        print("copy cache file " + filename + suffix + " to dist dir ")
        return True

    nrows = table.nrows
    ncols = table.ncols

    # 头部加上"[\n"
    json_str = u"[\n"
    rs = 0
    for r in range(DATA_ROW, nrows):
        if IsEmptyLine(table, r, ncols):  # 跳过空行
            continue
        strTmp = u"\t{ "
        i = 0

        for c in range(ncols):
            # 根据列名校验是否是要导出的列
            title = table.cell_value(FIELD_ROW, c)
            title = title.replace(u"\n", u"").replace(u"\"", u"").replace(u" ", u"")
            if not title in mapTable:
                continue

            # 校验列的数据类型
            dataType = DataType.INT
            colType = table.cell_value(TYPE_ROW, c).replace(u" ", "")
            if "array" in colType:
                dataType = DataType.ARRAY
            elif "map" in colType:
                dataType = DataType.MAP
            else:
                if not colType in AccessDataType:
                    printError(u"错误 Table2Array data type error on make " + filename + "row:" + str(r+1) + "col:" + str(c+1) + "title:" + title + "type:" + colType)
                    sys.exit(1)
                else:
                    dataType = AccessDataType[colType]

            # 取数据
            data = table.cell_value(r, c)

            # 数据校验转换
            ok, strValue = DealData(dataType, data)
            if ok == False:
                printError(u"错误 data format error on make " + filename +"row:" + str(r+1) + "col:" + str(c+1) + "title:" + title)
                sys.exit(1)

            if i > 0:
                strTmp += u", " + u"\"" + title + u"\":" + strValue
            else:
                strTmp += u"" + u"\"" + title + u"\":" + strValue

            i += 1

        # 当前行结束
        strTmp += u" }"
        if rs > 0:  # 不是第1行
            strTmp = ",\n" + strTmp
        rs += 1
        json_str += strTmp

    # 尾部加上"\n]"
    json_str += u"\n]"
    json_str += u"\n"

    # 打开输出文件
    dir = os.path.dirname(dstFilePath)
    if dir and not os.path.exists(dir):
        os.makedirs(dir)
    f = codecs.open(dstFilePath, "w", "utf-8")
    if suffix == ".json":
        f.write(json.dumps(json.loads(json_str,	 object_pairs_hook=OrderedDict),
                           ensure_ascii=False, indent=4).replace(u"\\\\", u"\\"))
    elif suffix == ".lua":
        if len(desc) > 0:
            f.write("--[[\n")
            f.write(str(desc))
            f.write("\n]]--\n")
        f.write("return " + objToLuaString(json.loads(json_str)))
    f.close()

    # 备份一份缓存文件
    copyFile(dstFilePath, cacheFile)
    print("Create ", dstFilePath, " OK")
    return

# 表格转map
def table2Map(srcPath, category, table, filename, suffix,  mapTable, desc):
   # 如果有缓存文件则使用缓存文件
    md5 = getFileMD5(srcPath)
    cacheFile = os.path.join(CACHE_PATH, filename + "_" + md5 + suffix)
    dstFilePath = ""
    if category == "C":
        dstFilePath = os.path.join(ClientPath, filename + suffix)
    else:
        dstFilePath = os.path.join(ServerPath, filename + suffix)
    if os.path.exists(cacheFile):
        copy(cacheFile, dstFilePath)
        print("copy cache file " + filename + suffix + " to dist dir ")
        return True

    nrows = table.nrows
    ncols = table.ncols
    json_str = u"{\n"
    keyIndex = 0
    rs = 0
    for r in range(DATA_ROW, nrows):
        if IsEmptyLine(table, r, ncols):  
            continue # 跳过空行

        i = 0

        keyValue = table.cell_value(r, keyIndex)
        if type(keyValue) == float:
            keyValue = FloatToString(keyValue)
        else:
            keyValue = str(keyValue)
        strTmp = u"\t\""+str(keyValue) + "\": { "


        for c in range(ncols):
            # 根据列名校验是否是要导出的列
            title = table.cell_value(FIELD_ROW, c)
            title = title.replace(u"\n", u"").replace(u"\"", u"").replace(u" ", u"")

            # 校验是否是要导出的列
            if not title in mapTable:
                continue

            # 校验列的数据类型
            dataType = DataType.INT
            colType = table.cell_value(TYPE_ROW, c).replace(u" ", u"")
            if "array" in colType:
                dataType = DataType.ARRAY
            elif "map" in colType:
                dataType = DataType.MAP
            else:
                if not colType in AccessDataType:
                    printError(u"错误 Table2Map data type error on make " + filename + "row:" + str(r+1) + "col:" + str(c+1) + "title:" + title + "type:" + colType)
                    sys.exit(1)
                else:
                    dataType = AccessDataType[colType]

            # 取数据
            data = table.cell_value(r, c)

            # 数据校验转换
            ok, strValue = DealData(dataType, data)
            if ok == False:
                printError(u"错误 data format error on make " + filename + "row:" + str(r+1) + "col:" + str(c+1) + "title:" + title)
                sys.exit(1)

            if i > 0:
                delm = u", "
            else:
                delm = u""

            strTmp += delm + u"\"" + title + u"\":" + strValue
            i += 1

        strTmp += u" }"
        if rs > 0:  # 不是第1行
            strTmp = u",\n" + strTmp

        rs += 1
        json_str += strTmp

    json_str += u"\n}"
    json_str += u"\n"
    
    # 打开输出文件
    dir = os.path.dirname(dstFilePath)
    if dir and not os.path.exists(dir):
        os.makedirs(dir)
    f = codecs.open(dstFilePath, "w", "utf-8")
    if suffix == ".json":
        f.write(json.dumps(json.loads(json_str,	 object_pairs_hook=OrderedDict),
                           ensure_ascii=False, indent=4).replace(u"\\\\", u"\\"))
    elif suffix == ".lua":
        if len(desc) > 0:
            f.write("--[[\n")
            f.write(str(desc))
            f.write("\n]]--\n")
        f.write("return " + objToLuaString(json.loads(json_str)))
    f.close()

   # 备份一份缓存文件
    copyFile(dstFilePath, cacheFile)

    print ("Create ", dstFilePath, " OK")
    return



# 表格转key:value 表，2列(key:value) 或 3列(key:value:type)
def table2KeyValue(srcPath, category, table, filename, suffix, fields, desc):
   # 如果有缓存文件则使用缓存文件
    md5 = getFileMD5(srcPath)
    cacheFile = os.path.join(CACHE_PATH, filename + "_" + md5 + suffix)
    dstFilePath = ""
    if category == "C":
        dstFilePath = os.path.join(ClientPath, filename + suffix)
    else:
        dstFilePath = os.path.join(ServerPath, filename + suffix)
    if os.path.exists(cacheFile):
        copy(cacheFile, dstFilePath)
        print("copy cache file " + filename + suffix + " to dist dir ")
        return True

    # 校验要导出的列的个数
    if not (len(fields) == 2 or len(fields) == 5):
        return False

    nrows = table.nrows # 行数
    ncols = table.ncols # 列数

    hasMap = (len(fields) > 0)
    json_str = u"{\n"
 
    # for k,v in fields.items():
    #     pass

    # 计算要导出的列索引
    colMap = OrderedDict()
    for c in range(ncols):
        # 根据列名校验是否是要导出的列
        title = table.cell_value(FIELD_ROW, c)
        title = title.replace(u"\n", u"").replace( u"\"", u"").replace(u" ", u"")
        if not title in fields:
            continue
        colMap[fields[title]] = c

    result = OrderedDict()

    rs = 0
    for r in range(DATA_ROW, nrows):
        if IsEmptyLine(table, r, ncols):  # 跳过空行
            continue

        # 取出key
        # i = 0
        keyValue = table.cell_value(r, colMap[0])
        if len(keyValue) <= 0:
            continue

        # 取value 值
        data = table.cell_value(r, colMap[1])
        
        # 取出value 类型
        colType = table.cell_value(TYPE_ROW, colMap[1]).replace(u" ", u"")
        if len(colMap) >= 3:
            colType = table.cell_value(r, colMap[2])

        dataType = convertDataType(colType)
        if dataType == DataType.NONE:
            printError(u"错误 Table2KeyValue data type error on make " + filename + "row:" + str(r+1) + "col:" + str(c+1) + "title:" + title + "type:" + colType)
            sys.exit(1)
            return


        # 数据校验转换
        ok, strValue = DealData(dataType, data)
        if ok == False:
            printError(u"错误 data format error on make " + filename + "row:" + str(r+1) + "col:" + str(c+1) + "title:" + title)
            sys.exit(1)

        # 拼接字符串
        strTmp = u"\t\""+keyValue + "\":" + strValue
        if rs > 0:  # 不是第1行
            strTmp = u",\n" + strTmp
        rs += 1
        json_str += strTmp

    json_str += u"\n}"
    json_str += u"\n"

    # 打开输出文件
    dir = os.path.dirname(dstFilePath)
    if dir and not os.path.exists(dir):
        os.makedirs(dir)
    f = codecs.open(dstFilePath, "w", "utf-8")
    if suffix == ".json":
        f.write(json.dumps(json.loads(json_str,	 object_pairs_hook=OrderedDict), ensure_ascii=False, indent=4).replace(u"\\\\", u"\\"))
    elif suffix == ".lua":
        if len(desc) > 0:
            f.write("--[[\n")
            f.write(str(desc))
            f.write("\n]]--\n")
        f.write("return " + objToLuaString(json.loads(json_str)))
    f.close()

   # 备份一份缓存文件
    copyFile(dstFilePath, cacheFile)

    print("Create ", dstFilePath, " OK")
    return


def convertAttributeType(attributeType):
    if(attributeType == "int" or attributeType == "float"):
        return "number"
    else:
        return attributeType

# 表格转typescript
def table2TypeScript(table, filename, suffix, mapTable, desc):
    nrows = table.nrows
    ncols = table.ncols
    hasMap = (len(mapTable) > 0)

    json_str = u"export namespace " + filename.replace(".ts", "") + " {\n"
    json_str += u"\texport class Data {\n"

    # 处理基础属性
    for index in range(ncols):
        note = table.cell_value(0, index)
        attribute = table.cell_value(2, index).replace(u" ", u"")
        if not attribute in mapTable:
            continue

        attributeType = table.cell_value(1, index).replace(u" ", u"")
        if "array" in attributeType:
            strList = attributeType.split("|")
            json_str += (u"\t\t//" + note.replace(u"\t",
                                                  "").replace(u"\n", "").replace(u" ", "") + u"\n")
            json_str += (u"\t\t" + attribute + ": " +
                         json.loads(strList[1])["Type"] + u";\n\n")
        else:
            json_str += (u"\t\t//" + note.replace(u"\t",
                                                  "").replace(u"\n", "").replace(u" ", "") + u"\n")
            json_str += (u"\t\t" + attribute + ": " +
                         convertAttributeType(attributeType) + u";\n\n")

    json_str += u"\t};\n\n"

    # 处理非基础属性
    for index in range(ncols):
        note = table.cell_value(0, index)
        attribute = table.cell_value(2, index).replace(u" ", u"")
        if not attribute in mapTable:
            continue

        attributeType = table.cell_value(1, index).replace(u" ", u"")
        if "array" in attributeType:
            strList = attributeType.split("|")
            jsonMap = json.loads(strList[1])
            classType = ""
            for key in jsonMap:
                if key != "Type":
                    classType = key
                    break

            # 处理类名
            json_str += u"\texport class " + classType + "{\n"

            # 处理属性
            for key in jsonMap[classType]:
                json_str += u"\t\t" + key + ": " + \
                    jsonMap[classType][key] + ";\n"

            # 花括号收尾
            json_str += u"\t};\n\n"

    json_str += u"\tconst dataList: Data[] = [\n"

    for r in range(3, nrows):
        if IsEmptyLine(table, r, ncols):  # 跳过空行
            continue

        # # 过滤不需要的行
        # if len(filter) > 0:
        #     dic = {}
        #     for c in range(ncols):
        #         key = table.cell_value(2, c).replace(u" ", u"")
        #         value = table.cell_value(r, c)
        #         dic[key] = value

        #     if (eval(filter, {}, dic)) == False:
        #         continue

        json_str += u"\t\t{\n"

        for c in range(ncols):
            # 根据列名校验是否是要导出的列
            title = table.cell_value(2, c).replace(u" ", u"")
            title = title.replace(u"\n", u"").replace(u"\"", u"")
            if hasMap:
                if not title in mapTable:
                    continue
                else:
                    title = mapTable[title]

            # 校验列的数据类型
            dataType = DataType.INT
            colType = table.cell_value(1, c)
            if "array" in colType:
                dataType = DataType.ARRAY
            elif "map" in colType:
                dataType = DataType.MAP
            else:
                if not colType in AccessDataType:
                    print(Back.BLUE + "**************************************************")
                    print(Fore.RED + u"错误 Table2TypeScript data type error on make " + filename + "row:" + str(r+1) + "col:" + str(c+1) + "title:" + title + "type:" + colType)
                    print (Back.BLUE + "**************************************************")
                    sys.exit(1)
                else:
                    dataType = AccessDataType[colType]

            # 取数据
            data = table.cell_value(r, c)

            # 数据校验转换
            ok, strValue = DealData(dataType, data)
            if ok == False:
                print(Back.BLUE + "**************************************************")
                print(Fore.RED + u"错误 data format error on make " + filename + "row:" + str(r+1) + "col:" + str(c+1) + "title:" + title)
                print(Back.BLUE + "**************************************************")
                sys.exit(1)

            json_str += u"\t\t\t" + title + u": " + strValue + u",\n"

        # 当前行结束
        json_str += u"\t\t},\n"

    json_str += u"\t];\n\n"
    json_str += u"\texport const map: Map<number, Data> = new Map<number, Data>();\n"
    json_str += u"\tfor (const data of dataList) {\n"
    json_str += u"\t\tmap.set(data.id, data)\n"
    json_str += u"\t};\n\n"
    json_str += u"\texport const getValueWithID = function (id: number) {\n"
    json_str += u"\t\treturn map.get(id);\n"
    json_str += u"\t}\n\n"
    json_str += u"\texport const getSize = function () {\n"
    json_str += u"\t\treturn dataList.length;\n"
    json_str += u"\t}\n"
    json_str += u"};"

    # 打开输出文件
    dir = os.path.dirname(filename)
    if dir and not os.path.exists(dir):
        os.makedirs(dir)
    f = codecs.open(filename, "w", "utf-8")
    f.write(json_str)
    f.close()
    print("Create ", filename, " OK")
    return




# 根据表的结构推断表类型
def parse_tab():

    pass

# 解析单个tab标签页
def gen_tab(srcPath, category, tabType, destTable, destFileName, suffix, fields, strDesc):
    if suffix == ".json" or suffix == ".lua":
        if tabType == TabType.TAB_ARRAY:
            table2Array(srcPath, category, destTable, destFileName, suffix, fields, strDesc)
        elif tabType == TabType.TAB_MAP:
            table2Map(srcPath, category, destTable, destFileName, suffix, fields, strDesc)
        elif tabType == TabType.TAB_KV:
            table2KeyValue(srcPath, category, destTable, destFileName, suffix, fields, strDesc)
        else:
            printError(u"错误 type only support key:array,key:map,key:value format " + destFileName)
            sys.exit(1)
            return False
    elif suffix == ".ts":
        table2TypeScript(destTable, destFileName, suffix, fields, strDesc)
    else:
        printError(u"错误 only support (json, lua, ts), conf format " + destFileName)
        sys.exit(1)
        return False
    return True
    pass



# 解析单个excel
def gen_file(srcPath, export_category, export_suffix):
    # 校验是否有tablelist

    # 打开excel表
    book = xlrd.open_workbook(srcPath)

    # 检测表是否是空的
    sheetNames = book.sheet_names()
    if len(sheetNames) == 0:
        print("这是一张空的表: " + srcPath)
        return True


    if "tablelist" in sheetNames:
        # 读取 tablelist 导出配置页
        sheet = book.sheet_by_name(u"tablelist")
        for r in range(sheet.nrows-2):
            # 读目的table名(filename)
            destTableName = sheet.cell_value(r+2, 0)
            if destTableName not in sheetNames:
                printError(u"错误 export type not exist " + destFileName + " tab:" + destTableName)
                return False

            # 读输出文件名及文件扩展名
            destFileName = sheet.cell_value(r+2, 2)
            mainFile = destFileName[:destFileName.rfind(".")]
            suffix = destFileName[destFileName.rfind("."):].lower()
            if suffix not in AccessExportType:
                printError(u"错误 export type not exist " + destFileName + " tab:" + destTableName + " suffix:" + suffix)
                return False

            # 读导出的列(fields)
            strUseFields = sheet.cell_value(r+2, 3)
            fields = readFields(strUseFields)

            # 读导出分类 C、S、C&S
            category = sheet.cell_value(r+2, 4)
            if export_category != category:
                continue # 与导出的类型不相符

            # 读tab类型(type)
            strExtType = sheet.cell_value(r+2, 5)
            if strExtType not in AccessTabType:
                continue
            tabType = AccessTabType[strExtType]

            # 读出描述
            strDesc = sheet.cell_value(r+1, 6)
            print ("\nCreate " + destTableName + " ==> " + destFileName + " Starting...")

            # 读取目的table数据
            destTable = book.sheet_by_name(destTableName)

            # 单个标签页生成
            if not gen_tab(srcPath, category, tabType, destTable, mainFile, suffix, fields, strDesc):
                return False
        pass
    else:
        for sheetName in sheetNames:
            mainFile = sheetName
            suffix = export_suffix
            fields = OrderedDict()
            strDesc = ""
            tabType = TabType.TAB_ARRAY
            category  = export_category
            destTable = book.sheet_by_name(sheetName)

            i = 0
            for c in range(destTable.ncols):
                # 根据列名校验是否是要导出的列
                title = destTable.cell_value(FIELD_ROW, c)
                title = title.replace(u"\n", u"").replace(u"\"", u"").replace(u" ", u"")

                category = destTable.cell_value(CATEGORY_ROW, c).replace(u" ", u"")
                if not is_in_category_type(export_category, category):
                    continue
                fields[title] = i
                i += 1
                pass  
            
            title = destTable.cell_value(FIELD_ROW, 0).replace(u"\n", u"").replace(u"\"", u"").replace(u" ", u"")
            colType = destTable.cell_value(TYPE_ROW, 0).replace(u" ", u"")
            dataType = convertDataType(colType)
            if dataType == DataType.NONE:
                printError(u"错误 Table2KeyValue data type error on make " + srcPath + "row:" + str(r+1) + "col:" + str(c+1) + "title:" + title + "type:" + colType)
                sys.exit(1)
                
            if title == 'key' and dataType == DataType.STRING and len(fields) == 5:
                tabType = TabType.TAB_KV
            if title == 'id' and dataType == DataType.STRING and len(fields) > 2:
                tabType = TabType.TAB_MAP
            # 单个标签页生成
            if not gen_tab(srcPath, export_category, tabType, destTable, mainFile, suffix, fields, strDesc):
                return False
    return True
    pass




# 入口
if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='manual to this script')
    parser.add_argument('--input', type=str)
    parser.add_argument('--output', type=str)
    parser.add_argument('--category', type=str)
    parser.add_argument('--suffix', type=str)
    args = parser.parse_args()

    input_path = args.input   # 输入路径
    output_path = args.output # 输出路径
    category = args.category  # 导出分类 C || S
    suffix = args.suffix      # 导出扩展名  .json || .lua || .ts

    if category not in AccessCategoryType:
        printError(u"错误 args category invalid " + category)
        sys.exit(1)
    categoryType = AccessCategoryType[category]
    if categoryType == CategoryType.BOTH:
        printError(u"错误 args category invalid " + category)
        sys.exit(1)      

    if suffix not in AccessExportType:
        printError(u"错误 args suffix invalid " + suffix)
        sys.exit(1)    


    # 创建缓存目录
    if not os.path.exists(CACHE_PATH):
        os.makedirs(CACHE_PATH)

    # 创建输出目录
    ClientPath = os.path.join(output_path, ClientPath)
    ServerPath = os.path.join(output_path, ServerPath)
    if not os.path.exists(ClientPath):
        os.makedirs(ClientPath)
    if not os.path.exists(ServerPath):
        os.makedirs(ServerPath)

  
    # 遍历所有文件
    bSuccessed = True
    for path, dirs, files in os.walk(input_path):
        for f in files:
            srcPath = os.path.join(path, f)
            mainName = os.path.splitext(f)[0]
            extName = os.path.splitext(f)[1].lower()
            if (extName == ".xlsx" or extName == ".xls") and mainName[0] != '~':
                if not gen_file(srcPath, category, suffix):
                    bSuccessed = False
                    break

    if bSuccessed:
        print(Fore.GREEN + u"导出成功")
    else:
        print(Fore.RED + "导出失败!!!!!!!!!!!!")