{"id":1860,"date":"2018-10-31T10:34:55","date_gmt":"2018-10-31T02:34:55","guid":{"rendered":"http:\/\/39.100.100.179\/?p=1860"},"modified":"2020-11-02T18:25:09","modified_gmt":"2020-11-02T10:25:09","slug":"%e4%bd%bf%e7%94%a8python%e5%ae%9e%e7%8e%b0excel%e8%bd%aclua%e3%80%81json","status":"publish","type":"post","link":"http:\/\/www.u3d8.com\/?p=1860","title":{"rendered":"\u4f7f\u7528Python\u5b9e\u73b0Excel\u8f6cLua\u3001Json"},"content":{"rendered":"<p>\u672c\u6587\u4e3b\u8981\u662f\u4ecb\u7ecd\u81ea\u5df1\u524d\u51e0\u5929\u505a\u7684\u5de5\u5177\uff0c\u662f\u53ef\u4ee5\u6279\u91cf\u5c06Excel\u8f6c\u6210Lua\u548cJson\u683c\u5f0f<\/p>\n<p>\u5de5\u5177\u64cd\u4f5c\u6bd4\u8f83\u7b80\u5355\uff0c\u8fd9\u91cc\u4e0d\u518d\u4ecb\u7ecd\u64cd\u4f5c\u65b9\u6cd5<\/p>\n<p>\u6ce8\uff1a\u76ee\u524d\u652f\u6301\u7684Excel\u683c\u5f0f\u4e3a.xls\u6587\u4ef6\uff0c\u5982\u679c\u662f.xlsx\u6587\u4ef6\uff0c\u8bb0\u5f97\u4f7f\u7528\u4e4b\u524d\u8f6c\u4e0b\u683c\u5f0f<\/p>\n<h2>\u64cd\u4f5c\u754c\u9762\uff1a<\/h2>\n<p><img title=\"\u4f7f\u7528Python\u5b9e\u73b0Excel\u8f6cLua\u3001Json - \u7b2c1\u5f20  | u3d8\u6280\u672f\u5206\u4eab\" alt=\"\u4f7f\u7528Python\u5b9e\u73b0Excel\u8f6cLua\u3001Json - \u7b2c1\u5f20  | u3d8\u6280\u672f\u5206\u4eab\" loading=\"lazy\" class=\"size-large wp-image-1863 alignnone\" src=\"http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/10\/QQ\u622a\u56fe20181031102805-1-1024x645.jpg\" alt=\"\" width=\"640\" height=\"403\" srcset=\"http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/10\/QQ\u622a\u56fe20181031102805-1-1024x645.jpg 1024w, http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/10\/QQ\u622a\u56fe20181031102805-1-300x189.jpg 300w, http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/10\/QQ\u622a\u56fe20181031102805-1-768x484.jpg 768w, http:\/\/www.u3d8.com\/wp-content\/uploads\/2018\/10\/QQ\u622a\u56fe20181031102805-1.jpg 1078w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<h2>\u4e3b\u8981\u4ee3\u7801\uff1a<\/h2>\n<pre class=\"lang:c# decode:true  \">import Excel\nimport os\nimport threading\nimport cgitb\n\nkeyRow = 0\nvalueTypeRow = 1\nluaOrJsonRow = 2\ndescriptionRow = 3\nvalueRowBegin = 4\n\n\nconvertType = {\n    'Ignore': 0,\n    'Lua': 1,\n    'Json': 2,\n    'All': 3,\n}\n\n\nclass Execute():\n    def do(self,mainController, excelFolderPath, luaFolderPath, jsonFolderPath):\n        cgitb.enable(format = 'text')\n        print('************************** \u5f00\u59cb ********************************')\n        print(\"Excel:\"+ excelFolderPath)\n        print(\"Lua:\"+ luaFolderPath)\n        print(\"Json:\"+ jsonFolderPath)\n        self.mainController = mainController\n        self.excelFolderPath = excelFolderPath\n        self.luaFolderPath = luaFolderPath\n        self.jsonFolderPath = jsonFolderPath\n        self.mainController.setConfirmState(False)\n\n        # self.thread = threading.Thread(target=self.doThread)\n        # self.thread.setDaemon(True)\n        # self.thread.start()\n        self.doThread()\n\n    def doThread(self):\n        print()\n        self.progressValue = 0\n        self.mainController.mainView.progressBar.setValue(self.progressValue)\n        self.getExcelFiles()\n        self.toLua()\n        self.toJson()\n        print()\n        print('************************** \u5b8c\u6210 ********************************')\n        self.mainController.setConfirmState(True)\n        # self.mainController.finishBox()\n\n    def getExcelFiles(self):\n        self.excelFiles = []\n        for parent, _dirNames, _fileNames in os.walk(self.excelFolderPath):\n            for fileName in _fileNames:\n                if(fileName.split('.')[-1] == \"xls\" or fileName.split('.')[-1] == \"xlsx\"):\n                    self.excelFiles.append(self.excelFolderPath + \"\/\" + fileName)\n        print('\\n************** Excel Files ***************')\n        print(self.excelFiles)\n\n    def toLua(self):\n        print('\\n************** ToLua ***************')\n        for excelFile in self.excelFiles:\n            sheetNames = Excel.getSheetNames(excelFile)\n            for sheetName in sheetNames:\n                print('\u5f00\u59cb\u8f6c\u6362Lua\uff1a'+ sheetName)\n                luaData = sheetName + \"={\"\n                keys = Excel.readRow(excelFile, sheetName, keyRow)\n                valueTypes = Excel.readRow(excelFile, sheetName, valueTypeRow)\n                luaOrJsons = Excel.readRow(excelFile, sheetName, luaOrJsonRow)\n                excelData = Excel.readAll(excelFile, sheetName)\n                lastValidTypeIndex = 0;\n                for i in range(0, len(luaOrJsons)):\n                    if(str(luaOrJsons[i]) == str(convertType['Lua']) or str(luaOrJsons[i]) == str(convertType['All'])):\n                        lastValidTypeIndex = i\n                validKeys = []\n                for i in range(valueRowBegin+1, len(excelData)):\n                    for j in range(0, len(excelData[i])):\n                        if(excelData[i][0] == None or excelData[i][0] == \"\"): continue\n                        if(luaOrJsons[j] == None or luaOrJsons[j] == \"\"): continue\n                        if(str(luaOrJsons[j]) != str(convertType['Lua']) and str(luaOrJsons[j]) != str(convertType['All'])): continue\n                        if(j == 0):\n                            if(valueTypes[0] == 'int'):\n                                luaData += '\\n\\t[' + str(excelData[i][j]) + '] = {'\n                            else:\n                                luaData += '\\n\\t[\"' + str(excelData[i][j]) + '\"] = {'\n\n                        if(valueTypes[j] == 'int'):\n                            luaData += '\\n\\t\\t' + keys[j] + ' = ' + str(excelData[i][j]) + ','\n                        elif(valueTypes[j] == 'float'):\n                            luaData += '\\n\\t\\t' + keys[j] + ' = ' + str(excelData[i][j]) + ','\n                        elif(valueTypes[j] == 'double'):\n                            luaData += '\\n\\t\\t' + keys[j] + ' = ' + str(excelData[i][j]) + ','\n                        elif(valueTypes[j] == 'string'):\n                            luaData += '\\n\\t\\t' + keys[j] + ' = \"' + str(excelData[i][j]) + '\",'\n                        elif(valueTypes[j] == 'array'):\n                            luaData += '\\n\\t\\t' + keys[j] + ' = {' + str(excelData[i][j]) + '},'\n                        else:\n                            luaData += '\\n\\t\\t' + keys[j] + ' = ' + str(excelData[i][j]) + ','\n\n                        if(j == lastValidTypeIndex):\n                            luaData += '\\n\\t},'\n\n                        if(validKeys.__contains__(excelData[i][0]) == False):\n                            validKeys.append(excelData[i][0])\n                luaData += '\\n}'\n\n                luaData += '\\n' + sheetName + '.keys={'\n                for i in range(0, len(validKeys)):\n                    if (valueTypes[0] == 'int'):\n                        luaData += str(int(validKeys[i]))\n                    else:\n                        luaData += '\"' + str(validKeys[i]) + '\"'\n                    if (i &lt; len(validKeys)-1):\n                        luaData += ','\n                luaData += '}'\n\n                print('\u8f6c\u6362\u5b8c\u6210Lua\uff1a'+ sheetName)\n                if len(validKeys):\n                    if not os.path.exists(self.luaFolderPath):\n                        os.makedirs(self.luaFolderPath)\n                    file = open(self.luaFolderPath + \"\/\" + sheetName + '.lua', 'w', encoding='utf-8')\n                    file.write(luaData)\n                    file.close()\n\n                self.progressValue += 100 \/ (len(sheetNames) * 2)\n                self.mainController.mainView.progressBar.setValue(self.progressValue)\n\n    def toJson(self):\n        print('\\n************** ToJson ***************')\n        for excelFile in self.excelFiles:\n            sheetNames = Excel.getSheetNames(excelFile)\n            for sheetName in sheetNames:\n                print('\u5f00\u59cb\u8f6c\u6362Json\uff1a' + sheetName)\n                jsonData = \"{\"\n                jsonData += '\\n\\t' + '\"datas\":['\n                keys = Excel.readRow(excelFile, sheetName, keyRow)\n                valueTypes = Excel.readRow(excelFile, sheetName, valueTypeRow)\n                luaOrJsons = Excel.readRow(excelFile, sheetName, luaOrJsonRow)\n                excelData = Excel.readAll(excelFile, sheetName)\n                lastValidTypeIndex = 0\n                for i in range(0, len(luaOrJsons)):\n                    if (str(luaOrJsons[i]) == str(convertType['Json']) or str(luaOrJsons[i]) == str(convertType['All'])):\n                        lastValidTypeIndex = i\n                validKeys = []\n                for i in range(valueRowBegin + 1, len(excelData)):\n                    for j in range(0, len(excelData[i])):\n                        if (excelData[i][0] == None or excelData[i][0] == \"\"): continue\n                        if (luaOrJsons[j] == None or luaOrJsons[j] == \"\"): continue\n                        if (str(luaOrJsons[j]) != str(convertType['Json']) and str(luaOrJsons[j]) != str(\n                            convertType['All'])): continue\n                        if (j == 0):\n                            jsonData += '\\n\\t\\t{'\n\n                        if (valueTypes[j] == 'int'):\n                            jsonData += '\\n\\t\\t\\t\"' + keys[j] + '\" : ' + str(excelData[i][j])\n                        elif (valueTypes[j] == 'float'):\n                            jsonData += '\\n\\t\\t\\t\"' + keys[j] + '\" : ' + str(excelData[i][j])\n                        elif (valueTypes[j] == 'double'):\n                            jsonData += '\\n\\t\\t\\t\"' + keys[j] + '\" : ' + str(excelData[i][j])\n                        elif (valueTypes[j] == 'string'):\n                            jsonData += '\\n\\t\\t\\t\"' + keys[j] + '\" : \"' + str(excelData[i][j]) + '\"'\n                        elif (valueTypes[j] == 'array'):\n                            jsonData += '\\n\\t\\t\\t\"' + keys[j] + '\" : [' + str(excelData[i][j]) + ']'\n                        else:\n                            jsonData += '\\n\\t\\t\\t\"' + keys[j] + '\" : ' + str(excelData[i][j])\n\n                        if (j == lastValidTypeIndex):\n                            jsonData += '\\n\\t\\t}'\n                            if(i != len(excelData)-1 and excelData[i + 1][0] != ''):\n                                jsonData += ','\n                        else:\n                            jsonData += ','\n\n                        if (validKeys.__contains__(excelData[i][0]) == False):\n                            validKeys.append(excelData[i][0])\n\n                jsonData += '\\n\\t' + ']'\n                jsonData += '\\n' + '}'\n\n                print('\u8f6c\u6362\u5b8c\u6210Json\uff1a' + sheetName)\n                if len(validKeys):\n                    if not os.path.exists(self.jsonFolderPath):\n                        os.makedirs(self.jsonFolderPath)\n                    file = open(self.jsonFolderPath + \"\/\" + sheetName + '.json', 'w', encoding='utf-8')\n                    file.write(jsonData)\n                    file.close()\n\n                self.progressValue += 100 \/ (len(sheetNames) * 2)\n                self.mainController.mainView.progressBar.setValue(self.progressValue)\n\n    def toJson2(self):\n        print('\\n************** ToJson ***************')\n        for excelFile in self.excelFiles:\n            sheetNames = Excel.getSheetNames(excelFile)\n            for sheetName in sheetNames:\n                print('\u5f00\u59cb\u8f6c\u6362Json\uff1a' + sheetName)\n                jsonData = {}\n                keys = Excel.readRow(excelFile, sheetName, keyRow)\n                valueTypes = Excel.readRow(excelFile, sheetName, valueTypeRow)\n                luaOrJsons = Excel.readRow(excelFile, sheetName, luaOrJsonRow)\n                excelData = Excel.readAll(excelFile, sheetName)\n                for i in range(valueRowBegin + 1, len(excelData)):\n                    key = excelData[i][0]\n                    jsonData[key] = {}\n                    for j in range(0, len(excelData[i])):\n                        if (excelData[i][0] == None or excelData[i][0] == \"\"): continue\n                        if (luaOrJsons[j] == None or luaOrJsons[j] == \"\"): continue\n                        if (str(luaOrJsons[j]) != str(convertType['Json']) and str(luaOrJsons[j]) != str(\n                            convertType['All'])): continue\n                        jsonData[key][keys[j]] = excelData[i][j]\n\n                jsonData = json.dumps(jsonData)\n                print('\u8f6c\u6362\u5b8c\u6210Json\uff1a' + sheetName)\n                if not os.path.exists(self.jsonFolderPath):\n                    os.makedirs(self.jsonFolderPath)\n                file = open(self.jsonFolderPath + \"\/\" + sheetName + '.json', 'w', encoding='utf-8')\n                file.write(jsonData)\n                file.close()\n\n                self.progressValue += 100 \/ (len(sheetNames) * 2)\n                self.mainController.mainView.progressBar.setValue(self.progressValue)\n<\/pre>\n<p>\u00a0<\/p>\n<h2>\u5de5\u5177\u4e0b\u8f7d<\/h2>\n<p>\u94fe\u63a5: <a href=\"https:\/\/pan.baidu.com\/s\/12SXpaXhbDP5HKICLm87eHA\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/pan.baidu.com\/s\/12SXpaXhbDP5HKICLm87eHA<\/a> \u63d0\u53d6\u7801: hjxh<\/p>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u672c\u6587\u4e3b\u8981\u662f\u4ecb\u7ecd\u81ea\u5df1\u524d\u51e0\u5929\u505a\u7684\u5de5\u5177\uff0c\u662f\u53ef\u4ee5\u6279\u91cf\u5c06Excel\u8f6c\u6210Lua\u548cJson\u683c\u5f0f &hellip; <a href=\"http:\/\/www.u3d8.com\/?p=1860\">\u7ee7\u7eed\u9605\u8bfb <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":1863,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[308],"tags":[385,384],"_links":{"self":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/1860"}],"collection":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1860"}],"version-history":[{"count":23,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/1860\/revisions"}],"predecessor-version":[{"id":2189,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/posts\/1860\/revisions\/2189"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=\/wp\/v2\/media\/1863"}],"wp:attachment":[{"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1860"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1860"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.u3d8.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1860"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}