excel_to_json_one.py 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661
  1. # -*- coding: utf-8 -*-
  2. '''
  3. 一、支持的表头
  4. 单值Key-Value形式的:【KV】、【MAP】;即第2行是key,第3行是类型,第5行是value
  5. 如:{a=1, b=3, ...}
  6. 单值Key-Value形式的:【K-V】;即第1列是key,第2列是类型,第4列是value
  7. 如:{a=1, b=3, ...}
  8. 对象列表形式的:【V】;即第1列
  9. 如:{1, 2, 3, ...}
  10. 对象Key-Value形式的:【M】;
  11. 如:{[1]={a=1, b=3, ...}, [2]={a=1, b=3, ...}}
  12. 单值列表形式的:【LIST】、【L】。
  13. 如:{{a=1, b=3, ...}, {a=1, b=3, ...}}
  14. 推荐使用:
  15. 单值Key-Value形式的:【K-V】
  16. 对象列表形式的:【V】
  17. 对象Key-Value形式的:【M】
  18. 单值列表形式的:【L】
  19. 二、支持的数据类型:
  20. 整数:int;
  21. 整数字符串:intstring;
  22. 布尔值:bool、boolean。为true的值:"TRUE"、"true"、"T"、"1"、1:
  23. 字符串:text、string;
  24. 浮点型:double、float;
  25. lua表达式:table、lua;
  26. 如:{{id=101,count=2222}}
  27. 其它:直接用文本输出。
  28. '''
  29. import os
  30. import sys
  31. import commands
  32. import json
  33. import shutil
  34. import ConfigParser
  35. import json
  36. import math
  37. from optparse import OptionParser
  38. from xlrd import open_workbook
  39. reload(sys)
  40. sys.setdefaultencoding('utf-8')
  41. class ConfigParserEx(ConfigParser.ConfigParser):
  42. def __init__(self,defaults=None):
  43. ConfigParser.ConfigParser.__init__(self,defaults=None)
  44. def optionxform(self, optionstr):
  45. return optionstr
  46. def load_config(path):
  47. ''' 读取配置表
  48. '''
  49. config = ConfigParserEx()
  50. config.read(path)
  51. return config
  52. kSpace = " "
  53. class ExcaleToLuaClass(object):
  54. def __init__(self):
  55. self.currentPyPath = sys.path[0]
  56. self.configPath = None
  57. self.errorMap = []
  58. def start(self):
  59. print("开始转换...")
  60. print("++++ 检查参数...")
  61. ok = self.check_arguments()
  62. if ok:
  63. print("---- 检查参数正确!!!")
  64. else:
  65. print("---- 检查参数错误!!!")
  66. return
  67. print("++++ 加载配置...")
  68. ok = self.load_arguments_config()
  69. if ok:
  70. print("---- 加载配置正确!!!")
  71. else:
  72. print("---- 加载配置错误!!!")
  73. return
  74. print("++++ 转换...")
  75. ok = self.loop_exchange()
  76. if ok:
  77. print("---- 转换正确!!!")
  78. else:
  79. print("---- 转换错误!!!")
  80. return
  81. for errorName in self.errorMap:
  82. print(json.dumps("\033[31m---- 错误配置:" + errorName + "\033[0m").decode("unicode-escape"))
  83. def check_arguments(self):
  84. ''' 检查参数
  85. '''
  86. parser = OptionParser()
  87. parser.add_option("-s", action="store", type="string", dest="source", help="source path")
  88. parser.add_option("-l", action="store", type="string", dest="list", help="source subdirectory list")
  89. parser.add_option("-o", action="store", type ="string", dest="output", help="output path")
  90. parser.add_option("-t", action="store", type ="string", dest="type", help="type")
  91. # 不必输入,固定是输出json格式
  92. # parser.add_option("--json", action="store_true", dest="outputJson", help="output json")
  93. (options, args) = parser.parse_args()
  94. ok = True
  95. sourcePath = options.source
  96. if options.source == None:
  97. ok = False
  98. print("-s config path is not set")
  99. subListStr = options.list
  100. subList = [""]
  101. if options.list != None:
  102. subList = str.split(subListStr, ",")
  103. outputPath = options.output
  104. if options.output == None:
  105. #如果输出目录没有写,则在源目录中新建一个output目录作为输出目录
  106. outputPath = os.path.join(cur_path, "out")
  107. onlyClient = False
  108. if options.type == "c":
  109. onlyClient = True
  110. else:
  111. onlyClient = False
  112. onlyServer = False
  113. if options.type == "s":
  114. onlyServer = True
  115. else:
  116. onlyServer = False
  117. outputJson = True # options.outputJson
  118. print "sourcePath", sourcePath
  119. print "subList", subList
  120. print "outputPath", outputPath
  121. if outputJson:
  122. print "输出 json格式"
  123. else:
  124. print "输出 lua格式"
  125. if not os.path.exists(outputPath):
  126. os.makedirs(outputPath)
  127. self.excelRootPath = sourcePath
  128. self.excelDirList = subList
  129. self.jsonRootPath = outputPath
  130. if onlyClient:
  131. self.mode = "CLIENT"
  132. elif onlyServer:
  133. self.mode = "SERVER"
  134. else:
  135. self.mode = "ALL"
  136. self.outputJson = outputJson
  137. return True
  138. def load_arguments_config(self):
  139. ''' 加载参数
  140. '''
  141. return True
  142. def loop_exchange(self):
  143. ''' 循环转换
  144. '''
  145. excelRootPath = self.excelRootPath
  146. excelDirList = self.excelDirList
  147. jsonRootPath = self.jsonRootPath
  148. mode = self.mode
  149. oneJson = {}
  150. for i in range(0, len(excelDirList)):
  151. excelDir = excelDirList[i]
  152. sourcePath = os.path.join(excelRootPath, excelDir)
  153. outputPath = os.path.join(jsonRootPath)
  154. if not os.path.exists(outputPath):
  155. os.makedirs(outputPath)
  156. dirList = []
  157. # 填充所有目录和文件名称
  158. for dirpath, dirnames, filenames in os.walk(sourcePath):
  159. for filename in filenames:
  160. dirList.append(os.path.join(dirpath, filename))
  161. # 过滤文件
  162. for filePath in dirList:
  163. name, ext = os.path.splitext(filePath)
  164. if ext == ".xlsx":
  165. if name.find("~$") < 0 and name.find(".~") < 0:
  166. # 批量创建
  167. self.excel2lua(filePath, outputPath, mode, oneJson)
  168. jsonStr = json.dumps(oneJson, sort_keys=True, separators=(',', ':'), ensure_ascii=False)
  169. jsonStr = jsonStr.decode("utf-8")
  170. outputFilePath = os.path.join(outputPath, "AllConfig.json")
  171. output = open(outputFilePath , "w")
  172. output.write(jsonStr)
  173. output.close()
  174. return True
  175. # 生成lua文件
  176. def excel2lua(self, filePath, outputPath, mode, oneJson):
  177. ''' 转换
  178. '''
  179. excelData = self.openExecl(filePath)
  180. # 遍历所有的sheet
  181. for sheet in excelData.sheets():
  182. name = sheet.name
  183. nrows = sheet.nrows
  184. ncols = sheet.ncols
  185. #print(nrows, ncols)#行列数
  186. #row 1 作为lua表的属性用
  187. row_1 = sheet.row_values(0)
  188. dataMode = row_1[0]
  189. excelMode = row_1[1]
  190. if dataMode == "" or excelMode == "":
  191. # self.errorMap.append(filePath)
  192. continue
  193. if mode != "ALL":
  194. if mode == "CLIENT" and (excelMode != "CLIENT" and excelMode != "ALL"):
  195. # 只需要导出客户端需要的,该表不是客户端需要的,过掉
  196. continue
  197. elif mode == "SERVER" and (excelMode != "SERVER" and excelMode != "ALL"):
  198. # 只需要导出客户端需要的,该表不是客户端需要的,过掉
  199. continue
  200. outputFilePath = os.path.join(outputPath, name + ".lua")
  201. if dataMode == "K-V":
  202. self.outputColKeyValue(sheet, outputFilePath)
  203. elif dataMode == "V":
  204. self.outputList(sheet, outputFilePath)
  205. elif dataMode == "M":
  206. self.outputObjMap(sheet, outputFilePath)
  207. elif dataMode == "L":
  208. self.outputObjList(sheet, outputFilePath)
  209. # 下面是兼容旧版本的标签
  210. elif dataMode == "KV" or dataMode == "MAP":
  211. self.outputKeyValue(sheet, outputFilePath)
  212. elif dataMode == "LIST":
  213. self.outputObjList(sheet, outputFilePath)
  214. else:
  215. continue
  216. if self.outputJson:
  217. jsonData = self.lua2json(outputPath, name)
  218. oneJson[name] = jsonData
  219. # lua转json
  220. def lua2json(self, outputPath, name):
  221. cmdFormat = 'lua -e \" \
  222. local cjson = require([[cjson]]); \
  223. package.path = [[{0}/?.lua;]] .. package.path; \
  224. local function updateKey(tab) \
  225. if type(tab) ~= [[table]] then \
  226. return tab; \
  227. end \
  228. local count = 0; \
  229. for key, value in pairs(tab) do \
  230. count = count + 1; \
  231. end \
  232. local ret = {{}}; \
  233. if count == #tab then \
  234. for i, value in ipairs(tab) do \
  235. ret[i] = updateKey(value); \
  236. end \
  237. else \
  238. for key, value in pairs(tab) do \
  239. ret[tostring(key)] = updateKey(value); \
  240. end \
  241. end \
  242. return ret; \
  243. end \
  244. local fileName = [[{1}]]; \
  245. local config = require(fileName); \
  246. config = updateKey(config); \
  247. local jsonStr = cjson.encode(config); \
  248. print(jsonStr); \
  249. \"'
  250. cmd = cmdFormat.format(outputPath, name)
  251. content = commands.getoutput(cmd)
  252. data = json.loads(content)
  253. luaOutputFilePath = os.path.join(outputPath, name + ".lua")
  254. os.remove(luaOutputFilePath)
  255. return data
  256. # 打开execl
  257. def openExecl(self, filePath):
  258. try:
  259. print("openExecl file name : " + filePath)
  260. data = open_workbook(filePath)
  261. return data
  262. except Exception,e:
  263. print "openExecl error:" + str(e)
  264. def cellValue(self, value, typeStr):
  265. ret = ""
  266. if value == "":
  267. return ""
  268. if value == "无" and typeStr != "text":
  269. return ""
  270. if typeStr == "int":
  271. retInt = float(value)
  272. ret = str(int(math.floor(retInt + 0.5)))
  273. elif typeStr == "intstring":
  274. if type(value) == type(0.1):
  275. retInt = float(value)
  276. ret = "\'" + str(int(math.floor(retInt + 0.5))) + "\'"
  277. else:
  278. ret = "\'" + str(value) + "\'"
  279. elif typeStr == "bool" or typeStr == "boolean":
  280. if value == "TRUE" or value == "true" or value == "T" or value == "1" or value == 1:
  281. ret = "true"
  282. else:
  283. ret = "false"
  284. elif typeStr == "text" or typeStr == "string":
  285. ret = "\'" + str(value) + "\'"
  286. elif typeStr == "double" or typeStr == "float":
  287. ret = str(value)
  288. elif typeStr == "table" or typeStr == "lua":
  289. ret = str(value)
  290. else:
  291. ret = str(value)
  292. return ret
  293. # 以Map形式输出 {a=1, b=3, ...}
  294. def outputColKeyValue(self, sheet, outputFilePath):
  295. name = sheet.name
  296. nrows = sheet.nrows
  297. ncols = sheet.ncols
  298. #print(nrows, ncols)#行列数
  299. output = open(outputFilePath, "w")
  300. output.write("local root = {")
  301. # 从第2行开始读
  302. start_rr = 1
  303. #第一行需要特殊处理
  304. firstLine = True
  305. for x in xrange(start_rr, nrows):
  306. onRow = sheet.row_values(x)
  307. keyStr = onRow[0]
  308. # 忽略key是空的列
  309. if keyStr == "":
  310. continue
  311. valueType = onRow[1]
  312. value = onRow[3]
  313. valueStr = self.cellValue(value, valueType)
  314. # 忽略空值的属性
  315. if valueStr == "":
  316. continue
  317. if firstLine:
  318. output.write("\n" + kSpace + keyStr + " = " + valueStr)
  319. firstLine = False
  320. else:
  321. output.write(",\n" + kSpace + keyStr + " = " + valueStr)
  322. output.write("\n}\nreturn root")
  323. output.close()
  324. # 以List形式输出 {1, 2, 3, ...}
  325. def outputList(self, sheet, outputFilePath):
  326. nrows = sheet.nrows
  327. ncols = sheet.ncols
  328. #print(nrows, ncols)#行列数
  329. # 第3行是数据类型的声明
  330. typeRow = sheet.row_values(2)
  331. # 因为是list,就一个类型
  332. valueType = typeRow[0]
  333. output = open(outputFilePath, "w")
  334. output.write("local root = {")
  335. # 从第5行开始读
  336. start_rr = 4
  337. #第一行需要特殊处理
  338. firstLine = True
  339. for x in xrange(start_rr, nrows):
  340. onRow = sheet.row_values(x)
  341. value = onRow[0]
  342. valueStr = self.cellValue(value, valueType)
  343. if firstLine:
  344. output.write("\n" + kSpace + valueStr)
  345. firstLine = False
  346. else:
  347. output.write(",\n" + kSpace + valueStr)
  348. output.write("\n}\nreturn root")
  349. output.close()
  350. # 以Map形式输出 {[1]={a=1, b=3, ...}, [2]={a=1, b=3, ...}}
  351. def outputObjMap(self, sheet, outputFilePath):
  352. name = sheet.name
  353. nrows = sheet.nrows
  354. ncols = sheet.ncols
  355. #print(nrows, ncols)#行列数
  356. # 支持合并单元格
  357. merge = {}
  358. for (rlow, rhigh, clow, chigh) in sheet.merged_cells:
  359. for r in xrange(rlow, rhigh):
  360. if not merge.get(r):
  361. merge[r] = {}
  362. for c in xrange(clow, chigh):
  363. merge[r][c] = sheet.row_values(rlow)[clow]
  364. #第1行的第3个单元格声明哪个作为key
  365. row_1 = sheet.row_values(0)
  366. key = row_1[2]
  367. # 第2行是key名称
  368. keyRow = sheet.row_values(1)
  369. # 第3行是数据类型的声明
  370. typeRow = sheet.row_values(2)
  371. output = open(outputFilePath, "w")
  372. output.write("local root = {")
  373. # 从第0列开始读
  374. start_cc = 0
  375. # 从第5行开始读
  376. start_rr = 4
  377. #第一行需要特殊处理
  378. firstLine = True
  379. for x in xrange(start_rr, nrows):
  380. objStr = "{"
  381. keyValue = ""
  382. # 读取一行
  383. oneRow = sheet.row_values(x)
  384. mergeRow = merge.get(x)
  385. # 第一个属性需要特殊处理
  386. firstProperty = True
  387. for cc in xrange(start_cc, ncols):
  388. keyStr = keyRow[cc]
  389. # 忽略key是空的列
  390. if keyStr == "":
  391. continue
  392. value = oneRow[cc]
  393. valueType = typeRow[cc]
  394. if mergeRow:
  395. mergeCell = mergeRow.get(cc)
  396. if mergeCell != None:
  397. value = mergeCell
  398. value = self.cellValue(value, valueType)
  399. if value == "":
  400. continue
  401. if firstProperty:
  402. objStr = objStr + "\n" + kSpace + kSpace + keyStr + " = " + value
  403. firstProperty = False
  404. else:
  405. objStr = objStr + ",\n" + kSpace + kSpace + keyStr + " = " + value
  406. if keyStr == key:
  407. keyValue = value
  408. if firstLine:
  409. objStr = "\n" + kSpace + "[" + keyValue + "] = " + objStr + "\n" + kSpace + "}"
  410. firstLine = False
  411. else:
  412. objStr = ",\n" + kSpace + "[" + keyValue + "] = " + objStr + "\n" + kSpace + "}"
  413. output.write(objStr)
  414. output.write("\n}\nreturn root")
  415. output.close()
  416. # 以Map形式输出 {{a=1, b=3, ...}, {a=1, b=3, ...}}
  417. def outputObjList(self, sheet, outputFilePath):
  418. name = sheet.name
  419. nrows = sheet.nrows
  420. ncols = sheet.ncols
  421. #print(nrows, ncols)#行列数
  422. # 支持合并单元格
  423. merge = {}
  424. for (rlow, rhigh, clow, chigh) in sheet.merged_cells:
  425. for r in xrange(rlow, rhigh):
  426. if not merge.get(r):
  427. merge[r] = {}
  428. for c in xrange(clow, chigh):
  429. merge[r][c] = sheet.row_values(rlow)[clow]
  430. # 第2行是key名称
  431. keyRow = sheet.row_values(1)
  432. # 第3行是数据类型的声明
  433. typeRow = sheet.row_values(2)
  434. output = open(outputFilePath, "w")
  435. output.write("local root = {")
  436. # 从第0列开始读
  437. start_cc = 0
  438. # 从第5行开始读
  439. start_rr = 4
  440. #第一行需要特殊处理
  441. firstLine = True
  442. for x in xrange(start_rr, nrows):
  443. if firstLine:
  444. output.write("\n" + kSpace + "{")
  445. firstLine = False
  446. else:
  447. output.write(",\n" + kSpace + "{")
  448. # 读取一行
  449. onRow = sheet.row_values(x)
  450. mergeRow = merge.get(x)
  451. # 第一个属性需要特殊处理
  452. firstProperty = True
  453. for cc in xrange(start_cc, ncols):
  454. keyStr = keyRow[cc]
  455. # 忽略key是空的列
  456. if keyStr == "":
  457. continue
  458. value = onRow[cc]
  459. valueType = typeRow[cc]
  460. if mergeRow:
  461. mergeCell = mergeRow.get(cc)
  462. if mergeCell != None:
  463. value = mergeCell
  464. valueStr = self.cellValue(value, valueType)
  465. # 忽略空值的属性
  466. if valueStr == "":
  467. continue
  468. if firstProperty:
  469. output.write("\n" + kSpace + kSpace + keyStr + " = " + valueStr)
  470. firstProperty = False
  471. else:
  472. output.write(",\n" + kSpace + kSpace + keyStr + " = " + valueStr)
  473. output.write("\n" + kSpace + "}")
  474. output.write("\n}\nreturn root")
  475. output.close()
  476. # 以Map形式输出 {a=1, b=3, ...}
  477. def outputKeyValue(self, sheet, outputFilePath):
  478. name = sheet.name
  479. nrows = sheet.nrows
  480. ncols = sheet.ncols
  481. #print(nrows, ncols)#行列数
  482. # 支持合并单元格
  483. merge = {}
  484. for (rlow, rhigh, clow, chigh) in sheet.merged_cells:
  485. for r in xrange(rlow, rhigh):
  486. if not merge.get(r):
  487. merge[r] = {}
  488. for c in xrange(clow, chigh):
  489. merge[r][c] = sheet.row_values(rlow)[clow]
  490. # 第2行是key名称
  491. keyRow = sheet.row_values(1)
  492. # 第3行是数据类型的声明
  493. typeRow = sheet.row_values(2)
  494. # 第3行是值
  495. valueRow = sheet.row_values(4)
  496. mergeRow = merge.get(4)
  497. output = open(outputFilePath, "w")
  498. output.write("local root = {")
  499. # 从第0列开始读
  500. start_cc = 0
  501. #第一行需要特殊处理
  502. firstLine = True
  503. for cc in xrange(start_cc, ncols):
  504. keyStr = keyRow[cc]
  505. # 忽略key是空的列
  506. if keyStr == "":
  507. continue
  508. value = valueRow[cc]
  509. valueType = typeRow[cc]
  510. if mergeRow:
  511. mergeCell = mergeRow.get(cc)
  512. if mergeCell != None:
  513. value = mergeCell
  514. valueStr = self.cellValue(value, valueType)
  515. # 忽略空值的属性
  516. if valueStr == "":
  517. continue
  518. if firstLine:
  519. output.write("\n" + kSpace + keyStr + " = " + valueStr)
  520. firstLine = False
  521. else:
  522. output.write(",\n" + kSpace + keyStr + " = " + valueStr)
  523. output.write("\n}\nreturn root")
  524. output.close()
  525. def main():
  526. ExcaleToLuaClass().start()
  527. if __name__ == '__main__':
  528. main()