Script/工具库文件/excelToJson.py

#!/usr/bin/env python
# coding: utf-8
import os.path
import sys
import codecs
import xlrd
import shutil
import re
from excelToLua import FloatToString,StringToString,exception,int2index
excelDir="../策划脚本/".decode('utf8')
jsonDir="../json/".decode('utf8')
g_objName=""

def haveNext(table, col, row, max_cols):
    for c in range(col, max_cols):
        objType = str(table.cell_value(row, c));
        if(objType.find("@") == -1 or objType.find("_") != 0):
            return 1
    return 0


def table2json(table, jsonfilename):
    nrows = table.nrows
    ncols = table.ncols
    if nrows <= 3:
        return
    f = codecs.open(jsonfilename,"w","utf-8")
    f.write(u"[\n")
    for r in range(nrows):
        # if r == 2:
            # continue
        f.write(u"\t[ ")
        for c in range(ncols):
            strCellValue = u""
            objName = str(table.cell_value(1,c))
            global g_objName
            g_objName= objName
            objType = str(table.cell_value(2,c))
            objStr = table.cell_value(r,c)
            if(objName.find("@") >= 0 or objName.find("_") == 0):
                continue
            if r <= 2:
                strCellValue = u"\""  + objStr + u"\""
            else:
                if cmp(objType, "NUMBER") == 0 or cmp(objType, "FLOAT") == 0:
                    strCellValue = FloatToString(objStr)
                    if strCellValue == False:
                        print u"错误位置\t"+str(r+1)+":"+str(table.cell_value(r,1))+"\t"+int2index(c)+":"+str(table.cell_value(0,c))
                        exception()
                else:
                    strvalue = StringToString(objStr).strip();
                    strvalue = "".join(strvalue.split("\n"))
                    strCellValue = u"\""  + strvalue.replace("\\", "\\\\") + u"\""

            if c < ncols-1:
                isNext = haveNext(table, c+1, 1, ncols)
                if isNext != 0:
                    strCellValue += u", "
            f.write(strCellValue)
        f.write(u" ]")
        if r <= 1 or (r == 2 and nrows > 3) or (r > 2 and r < nrows-1):
            f.write(u",")
        f.write(u"\n")
    f.write(u"]")
    f.close()
    return

def excelToJson(excelFileName,sheets):
    path = excelFileName.split('.')[0]
    childDir = jsonDir+path
    if not os.path.exists(childDir) :
        os.makedirs(childDir)

    data = xlrd.open_workbook(excelDir+excelFileName)
    for table in data.sheets():
        if table.name in sheets:
            print 'trans :' , excelFileName,table.name
            destfilename = os.path.join(jsonDir, path + "/" + table.name + ".json")
            try:
                table2json(table,destfilename)
            except Exception,e:  
                print Exception,":",e
                exception()

if __name__ == '__main__':
    reload(sys)
    sys.setdefaultencoding( "utf-8" )
    if os.path.isdir(jsonDir): 
        shutil.rmtree(jsonDir)
    os.makedirs(jsonDir)
    file = open("JsonConfig.txt") 
    r = re.compile('(\t|\n|\r)')
    sheetMap = dict()
    while True:
        lines = file.readlines(1000)
        if not lines:
            break
        for line in lines:
            if line.startswith('#'):
                continue
            line = r.sub('',line)
            if line == "":
                continue
            x = line.split(':')
            if len(x)<2:
                print 'error:',line
                file.close()
                exception()
            else:
                if x[0] not in sheetMap:
                    sheetMap[x[0]]=[]
                sheetMap[x[0]].append(x[1])
    file.close()
    for (k,v) in sheetMap.items():
        excelToJson(k,v)