unity python 简单的 excel to json
小工程
简单的excel to json
参考：https://github.com/zhang00lei/UnityEditorTools
修改了python 实现：1.支持单个excel多sheet的导出 2.支持id为string或int模式
1 # -*- coding:utf-8 -*- 2 3 import sys, os, re 4 import openpyxl 5 6 curpath = os.path.dirname(os.path.abspath(sys.argv[0])) 7 8 # 将数据导出到tgt_lua_path 9 def toInt(val): 10 if isinstance(val, str): 11 v = int(val) 12 else: 13 v = int(val) 14 return v; 15 16 # 将数据导出到tgt_lua_path 17 def excel2lua(src_excel_path, tgt_lua_path): 18 excel_data_src = openpyxl.load_workbook(src_excel_path, data_only=True) 19 excel_sheet = excel_data_src.worksheets[0] 20 if src_excel_path.endswith('CommonConfig.xlsx'): 21 lua_export_file = open(tgt_lua_path, 'w',encoding='utf-8') 22 lua_export_file.write('---this file is generate by tools,do not modify it.\n') 23 lua_export_file.write('---@class CommonConfig\n') 24 lua_export_file.write('local CommonConfig = {}\n') 25 for row in range(1, excel_sheet.max_row): 26 field_name = excel_sheet.cell(row+1,1).value 27 field_note = excel_sheet.cell(row+1,2).value 28 field_type = excel_sheet.cell(row+1,3).value 29 field_value = excel_sheet.cell(row+1,4).value 30 if field_type == 'float' or field_type == 'int' or field_type == 'number': 31 field_type = 'number' 32 elif field_type == 'boolean': 33 if not field_value: 34 field_value = 'false' 35 else: 36 field_value = 'true' 37 38 lua_export_file.write('---@field {0} {1} @{2}\n'.format(field_name, field_type,field_note)) 39 if field_type == 'string': 40 lua_export_file.write("CommonConfig.{0} = \"{1}\"\n".format(field_name,field_value)) 41 else: 42 lua_export_file.write("CommonConfig.{0} = {1}\n".format(field_name,field_value)) 43 lua_export_file.write('return CommonConfig') 44 lua_export_file.close() 45 print('exported CommonConfig') 46 return 47 # export to lua file 48 lua_export_file = open(tgt_lua_path, 'w',encoding='utf-8') 49 searchObj = re.search(r'([^\\/:*?"<>|\r\n]+)\.\w+$', tgt_lua_path, re.M | re.I) 50 lua_table_name = searchObj.group(1) 51 lua_table_name = lua_table_name.replace('.lua','') 52 lua_export_file.write('local %s = {\n' % lua_table_name) 53 for index in range(0,len(excel_data_src.worksheets)): 54 excel_sheet_one = excel_data_src.worksheets[index] 55 sheet_name = excel_data_src.sheetnames[index] 56 57 # excel data dict 58 excel_data_dict = {} 59 60 # col desc 61 col_desc_list = [] 62 63 # col name list 64 col_name_list = [] 65 66 # col val type list 67 col_val_type_list = [] 68 69 # 第一行是表名 70 # 第二行是所有列的描述 71 for col in range(0, excel_sheet_one.max_column): 72 cell = excel_sheet_one.cell(2, col + 1) 73 col_desc_list.append(str(cell.value)) 74 75 # 遍历第三行的所有列 保存字段名 76 for col in range(0, excel_sheet_one.max_column): 77 cell = excel_sheet_one.cell(3, col + 1) 78 if cell.value: 79 col_name_list.append(str(cell.value)) 80 elif cell.data_type != "s": 81 print("found a invalid col name in col [%d] !~" % (col + 1)) 82 83 # 遍历第四行的所有列 保存数据类型 84 for col in range(0, excel_sheet_one.max_column): 85 cell = excel_sheet_one.cell(4, col + 1) 86 if cell.value: 87 col_val_type_list.append(str(cell.value)) 88 elif cell.data_type != "s": 89 print("found a invalid col val type in col [%d] !~" % (col + 1)) 90 91 # 剔除表头、字段名和字段类型所在行 92 # 从第五行开始遍历 构造行数据 93 for row in range(4, excel_sheet_one.max_row): 94 # 保存数据索引 默认第一列为id 95 cell_id = excel_sheet_one.cell(row + 1, 1) 96 97 # assert cell_id.data_type == 2, "found a invalid id in row [%d] !~" % (row) 98 # 检查id的唯一性 99 if not cell_id.value: 100 continue; 101 102 if cell_id.value in excel_data_dict: 103 print('[warning] duplicated data id: "%d", all previous value will be ignored!~' % (cell_id.value)) 104 105 # row data list 106 row_data_list = [] 107 108 # 保存每一行的所有数据 109 for col in range(0, len(col_name_list)): 110 cell = excel_sheet_one.cell(row + 1, col + 1) 111 k = col_name_list[col] 112 cell_val_type = col_val_type_list[col] 113 114 # print("row", row, "col", col) 115 # ignored the string that start with '_' 116 if str(k).startswith('#'): 117 continue 118 119 # 根据字段类型去调整数值 如果为空值 依据字段类型 填上默认值 120 if cell_val_type == 'string': 121 if not cell.value: 122 v = '\"\"' 123 else: 124 v = '\"%s\"' % (str(cell.value)) 125 elif cell_val_type == 'int': 126 if not cell.value: 127 v = 0 128 else: 129 v = toInt(cell.value) 130 elif cell_val_type == 'float': 131 if not cell.value: 132 v = 0 133 else: 134 v = float(cell.value) 135 elif cell_val_type == 'bool': 136 if not cell.value: 137 v = 'false' 138 else: 139 v = 'true' 140 elif cell_val_type == 'table': 141 if not cell.value: 142 v = '{}' 143 else: 144 v = cell.value 145 else: 146 v = cell.value 147 148 # 加入列表 149 row_data_list.append([k, v]) 150 151 # 保存id 和 row data 152 excel_data_dict[cell_id.value] = row_data_list 153 154 lua_export_file.write(' %s = {\n' % sheet_name) 155 156 # 遍历excel数据字典 按格式写入 157 for k, v in excel_data_dict.items(): 158 #lua_export_file.write(' [%d] = {\n' % toInt(k)) 159 if isinstance(k, str): 160 lua_export_file.write(' ["%s"] = {\n' % (k)) 161 else: 162 lua_export_file.write(' [%d] = {\n' % (k)) 163 #lua_export_file.write(' {0} ={\n'.format(k)) 164 for row_data in v: 165 lua_export_file.write(' {0} = {1},\n'.format(row_data[0], row_data[1])) 166 lua_export_file.write(' },\n') 167 lua_export_file.write(' },\n') 168 169 170 lua_export_file.write('}\n') 171 lua_export_file.write('return %s' % lua_table_name) 172 173 lua_export_file.close() 174 175 print('[excel] %d row data exported !~ %s' % (excel_sheet.max_row, os.path.basename(tgt_lua_path))) 176 177 178 if __name__ == '__main__': 179 if len(sys.argv) < 3: 180 print('python excel2lua.py <excel_input_path> <lua_output_path>') 181 exit(1) 182 excel2lua(os.path.join(curpath, sys.argv[1]), os.path.join(curpath, sys.argv[2])) 183 184 # test hero.xlsx 185 # curpath = "E:/craftclient/ConfigData/Excel2Lua" 186 # excelPath = curpath + "/excel/CommonConfig.xlsx"; 187 # luaPath = curpath + "/lua/CommonConfig.lua"; 188 # excel2lua(excelPath, luaPath) 189 190 exit(0)
