转自：http://www.u3d8.com/?p=1860

本文主要是介绍自己前几天做的工具，是可以批量将Excel转成Lua和Json格式

工具操作比较简单，这里不再介绍操作方法

注：目前支持的Excel格式为.xls文件，如果是.xlsx文件，记得使用之前转下格式

操作界面：

主要代码：

import Excel
import os
import threading
import cgitb
 
keyRow = 0
valueTypeRow = 1
luaOrJsonRow = 2
descriptionRow = 3
valueRowBegin = 4
 
 
convertType = {
    'Ignore': 0,
    'Lua': 1,
    'Json': 2,
    'All': 3,
}
 
 
class Execute():
    def do(self,mainController, excelFolderPath, luaFolderPath, jsonFolderPath):
        cgitb.enable(format = 'text')
        print('************************** 开始 ********************************')
        print("Excel:"+ excelFolderPath)
        print("Lua:"+ luaFolderPath)
        print("Json:"+ jsonFolderPath)
        self.mainController = mainController
        self.excelFolderPath = excelFolderPath
        self.luaFolderPath = luaFolderPath
        self.jsonFolderPath = jsonFolderPath
        self.mainController.setConfirmState(False)
 
        # self.thread = threading.Thread(target=self.doThread)
        # self.thread.setDaemon(True)
        # self.thread.start()
        self.doThread()
 
    def doThread(self):
        print()
        self.progressValue = 0
        self.mainController.mainView.progressBar.setValue(self.progressValue)
        self.getExcelFiles()
        self.toLua()
        self.toJson()
        print()
        print('************************** 完成 ********************************')
        self.mainController.setConfirmState(True)
        # self.mainController.finishBox()
 
    def getExcelFiles(self):
        self.excelFiles = []
        for parent, _dirNames, _fileNames in os.walk(self.excelFolderPath):
            for fileName in _fileNames:
                if(fileName.split('.')[-1] == "xls"):
                    self.excelFiles.append(self.excelFolderPath + "/" + fileName)
        print('\n************** Excel Files ***************')
        print(self.excelFiles)
 
    def toLua(self):
        print('\n************** ToLua ***************')
        for excelFile in self.excelFiles:
            sheetNames = Excel.getSheetNames(excelFile)
            for sheetName in sheetNames:
                print('开始转换Lua：'+ sheetName)
                luaData = sheetName + "={"
                keys = Excel.readRow(excelFile, sheetName, keyRow)
                valueTypes = Excel.readRow(excelFile, sheetName, valueTypeRow)
                luaOrJsons = Excel.readRow(excelFile, sheetName, luaOrJsonRow)
                excelData = Excel.readAll(excelFile, sheetName)
                lastValidTypeIndex = 0;
                for i in range(0, len(luaOrJsons)):
                    if(str(luaOrJsons[i]) == str(convertType['Lua']) or str(luaOrJsons[i]) == str(convertType['All'])):
                        lastValidTypeIndex = i
                validKeys = []
                for i in range(valueRowBegin+1, len(excelData)):
                    for j in range(0, len(excelData[i])):
                        if(excelData[i][0] == None or excelData[i][0] == ""): continue
                        if(luaOrJsons[j] == None or luaOrJsons[j] == ""): continue
                        if(str(luaOrJsons[j]) != str(convertType['Lua']) and str(luaOrJsons[j]) != str(convertType['All'])): continue
                        if(j == 0):
                            if(valueTypes[0] == 'int'):
                                luaData += '\n\t[' + str(excelData[i][j]) + '] = {'
                            else:
                                luaData += '\n\t["' + str(excelData[i][j]) + '"] = {'
 
                        if(valueTypes[j] == 'int'):
                            luaData += '\n\t\t' + keys[j] + ' = ' + str(excelData[i][j]) + ','
                        elif(valueTypes[j] == 'float'):
                            luaData += '\n\t\t' + keys[j] + ' = ' + str(excelData[i][j]) + ','
                        elif(valueTypes[j] == 'double'):
                            luaData += '\n\t\t' + keys[j] + ' = ' + str(excelData[i][j]) + ','
                        elif(valueTypes[j] == 'string'):
                            luaData += '\n\t\t' + keys[j] + ' = "' + str(excelData[i][j]) + '",'
                        elif(valueTypes[j] == 'array'):
                            luaData += '\n\t\t' + keys[j] + ' = {' + str(excelData[i][j]) + '},'
                        else:
                            luaData += '\n\t\t' + keys[j] + ' = ' + str(excelData[i][j]) + ','
 
                        if(j == lastValidTypeIndex):
                            luaData += '\n\t},'
 
                        if(validKeys.__contains__(excelData[i][0]) == False):
                            validKeys.append(excelData[i][0])
                luaData += '\n}'
 
                luaData += '\n' + sheetName + '.keys={'
                for i in range(0, len(validKeys)):
                    if (valueTypes[0] == 'int'):
                        luaData += str(int(validKeys[i]))
                    else:
                        luaData += str(validKeys[i])
                    if (i < len(validKeys)-1):
                        luaData += ','
                    else:
                        luaData += '}'
 
                print('转换完成Lua：'+ sheetName)
                if not os.path.exists(self.luaFolderPath):
                    os.makedirs(self.luaFolderPath)
                file = open(self.luaFolderPath + "/" + sheetName + '.lua', 'w')
                file.write(luaData)
                file.close()
 
                self.progressValue += 100 / (len(sheetNames) * 2)
                self.mainController.mainView.progressBar.setValue(self.progressValue)
 
    def toJson(self):
        print('\n************** ToJson ***************')
        for excelFile in self.excelFiles:
            sheetNames = Excel.getSheetNames(excelFile)
            for sheetName in sheetNames:
                print('开始转换Json：' + sheetName)
                jsonData = "{"
                jsonData += '\n\t' + '"datas":['
                keys = Excel.readRow(excelFile, sheetName, keyRow)
                valueTypes = Excel.readRow(excelFile, sheetName, valueTypeRow)
                luaOrJsons = Excel.readRow(excelFile, sheetName, luaOrJsonRow)
                excelData = Excel.readAll(excelFile, sheetName)
                lastValidTypeIndex = 0
                for i in range(0, len(luaOrJsons)):
                    if (str(luaOrJsons[i]) == str(convertType['Json']) or str(luaOrJsons[i]) == str(convertType['All'])):
                        lastValidTypeIndex = i
                validKeys = []
                for i in range(valueRowBegin + 1, len(excelData)):
                    for j in range(0, len(excelData[i])):
                        if (excelData[i][0] == None or excelData[i][0] == ""): continue
                        if (luaOrJsons[j] == None or luaOrJsons[j] == ""): continue
                        if (str(luaOrJsons[j]) != str(convertType['Json']) and str(luaOrJsons[j]) != str(
                            convertType['All'])): continue
                        if (j == 0):
                            jsonData += '\n\t\t{'
 
                        if (valueTypes[j] == 'int'):
                            jsonData += '\n\t\t\t"' + keys[j] + '" : ' + str(excelData[i][j])
                        elif (valueTypes[j] == 'float'):
                            jsonData += '\n\t\t\t"' + keys[j] + '" : ' + str(excelData[i][j])
                        elif (valueTypes[j] == 'double'):
                            jsonData += '\n\t\t\t"' + keys[j] + '" : ' + str(excelData[i][j])
                        elif (valueTypes[j] == 'string'):
                            jsonData += '\n\t\t\t"' + keys[j] + '" : "' + str(excelData[i][j]) + '"'
                        elif (valueTypes[j] == 'array'):
                            jsonData += '\n\t\t\t"' + keys[j] + '" : [' + str(excelData[i][j]) + ']'
                        else:
                            jsonData += '\n\t\t\t"' + keys[j] + '" : ' + str(excelData[i][j])
 
                        if (j == lastValidTypeIndex):
                            jsonData += '\n\t\t}'
                            if(i != len(excelData)-1):
                                jsonData += ','
                        else:
                            jsonData += ','
 
                        if (validKeys.__contains__(excelData[i][0]) == False):
                            validKeys.append(excelData[i][0])
 
                jsonData += '\n\t' + ']'
                jsonData += '\n' + '}'
 
                print('转换完成Json：' + sheetName)
                if not os.path.exists(self.jsonFolderPath):
                    os.makedirs(self.jsonFolderPath)
                file = open(self.jsonFolderPath + "/" + sheetName + '.json', 'w')
                file.write(jsonData)
                file.close()
 
                self.progressValue += 100 / (len(sheetNames) * 2)
                self.mainController.mainView.progressBar.setValue(self.progressValue)
 
    def toJson2(self):
        print('\n************** ToJson ***************')
        for excelFile in self.excelFiles:
            sheetNames = Excel.getSheetNames(excelFile)
            for sheetName in sheetNames:
                print('开始转换Json：' + sheetName)
                jsonData = {}
                keys = Excel.readRow(excelFile, sheetName, keyRow)
                valueTypes = Excel.readRow(excelFile, sheetName, valueTypeRow)
                luaOrJsons = Excel.readRow(excelFile, sheetName, luaOrJsonRow)
                excelData = Excel.readAll(excelFile, sheetName)
                for i in range(valueRowBegin + 1, len(excelData)):
                    key = excelData[i][0]
                    jsonData[key] = {}
                    for j in range(0, len(excelData[i])):
                        if (excelData[i][0] == None or excelData[i][0] == ""): continue
                        if (luaOrJsons[j] == None or luaOrJsons[j] == ""): continue
                        if (str(luaOrJsons[j]) != str(convertType['Json']) and str(luaOrJsons[j]) != str(
                            convertType['All'])): continue
                        jsonData[key][keys[j]] = excelData[i][j]
 
                jsonData = json.dumps(jsonData)
                print('转换完成Json：' + sheetName)
                if not os.path.exists(self.jsonFolderPath):
                    os.makedirs(self.jsonFolderPath)
                file = open(self.jsonFolderPath + "/" + sheetName + '.json', 'w')
                file.write(jsonData)
                file.close()
 
                self.progressValue += 100 / (len(sheetNames) * 2)
                self.mainController.mainView.progressBar.setValue(self.progressValue)