定制化比較嚴重，按需更改

excel文件如下

?代碼

# -*- coding: utf-8 -*-
import oss2
import shutil
import sys
import xlwt
import xlrd
import json
from datetime import datetime, timedeltafile1 = "C:\\Users\\cxy\\Desktop\\generate.xls"
#打開表1
wb1 = xlrd.open_workbook(filename=file1)# 表1的sheet
sheet = wb1.sheet_by_index(1)
# 表1的sheet的總行數
rowNum = sheet.nrows
# 表1的sheet的總列數
colNum = sheet.ncolsjson_data = {}def getCellValue(row,col): # if row == 8:#     print(1)value = sheet.cell_value(row,col)if str(value).endswith(".0"):return str(value).split(".0")[0]# if type(value) == float:#     value = '%.2f' % sheet.cell_value(8,8)return value
def getRowValue(row):return sheet.row_values(row)
def getColValue(col,start_rowx=0, end_rowx=None):return sheet.col_values(col, start_rowx, end_rowx)
def isKeyRow(row):return any(s == '$$key$$' for s in sheet.row_values(row))
import redef name_convert_to_camel(name: str) -> str:"""下劃線轉駝峰(小駝峰)"""return re.sub(r'(_[a-z])', lambda x: x.group(1)[1].upper(), name)def arr_str_to_arr(value: str) -> str:"""數組字符串變成數組類型"""value = str(value)if re.match('^\[', value) and re.search('\]$', value):arr = value.strip("[]").split(",")temp = []for v in arr:temp.append({"name":add_oss_host(v)})return tempreturn add_oss_host(value)def add_oss_host(value: str) -> str:content = str(value)if content.endswith(".png") or content.endswith(".jpeg") or content.endswith(".mp4") or content.endswith(".jpg"):return "/s" + contentelse:return contentdef resolveData(cur_level,start_row,end_row,data):col_values = sheet.col_values(cur_level, start_row, end_row)cur_level_key_row = [i+start_row for i in range(0,len(col_values)) if col_values[i] == '$$key$$']# cur_level_key_row.append(rowNum)for index, row_index in enumerate(cur_level_key_row):key_row = getRowValue(row_index)key = key_row[cur_level+1]if str(key).endswith(".0"):key = str(key).split(".0")[0]type = key_row[cur_level+2]if isKeyRow(row_index+1):# 第一行就遇到keyif type == 'array':print("第"+(row_index+1)+"行格式不對")breakelse:temp = {}data[key] = tempif row_index+1<rowNum and isKeyRow(row_index+1):resolveData(cur_level + 1, row_index + 1, rowNum if (index + 1) >= len(cur_level_key_row) else cur_level_key_row[index + 1], temp)continuecolumn_row = getRowValue(row_index+1)column_name_arr = []for col_index in range(cur_level,colNum):column_name = column_row[col_index]if column_name == '':breakcolumn_name_arr.append(name_convert_to_camel(column_name))if type == 'object':temp = {}data[key] = tempfor i in range(0,len(column_name_arr)):temp[column_name_arr[i]] = arr_str_to_arr(getCellValue(row_index+2, i + cur_level) )if row_index+3<rowNum and isKeyRow(row_index+3):resolveData(cur_level + 1, row_index + 3, rowNum if (index + 1) >= len(cur_level_key_row) else cur_level_key_row[index + 1], temp)else:# [0, 18, 28]tempArr = []data[key] = tempArrarr_data_start = cur_level_key_row[index]+2arr_data_end = rowNum # 默認，下面會改next_key_index_temp = rowNumif (index + 1) < len(cur_level_key_row):next_key_index_temp = cur_level_key_row[index + 1]if cur_level == 0:arr_data_end = next_key_index_tempelse:for i_temp in range(arr_data_start, next_key_index_temp):if i_temp == rowNum-1:breakif (getCellValue(i_temp, cur_level-1) == '' and getCellValue(i_temp+1, cur_level-1) != '') or (isKeyRow(i_temp+1) and getCellValue(i_temp+1, cur_level) == '$$key$$'):arr_data_end = i_temp+1breakfor chi_row_index in range(arr_data_start, arr_data_end):colValue = getCellValue(chi_row_index, cur_level)if colValue == '':continuetemp = {}tempArr.append(temp)for i in range(0,len(column_name_arr)):temp[column_name_arr[i]] = arr_str_to_arr(getCellValue(chi_row_index, i + cur_level) )if chi_row_index + 1 < rowNum and isKeyRow(chi_row_index + 1):parentColValues = getColValue(cur_level, chi_row_index + 1, rowNum)not_empty_index = next((j for j, v in enumerate(parentColValues) if v), len(parentColValues))resolveData(cur_level + 1, chi_row_index + 1, chi_row_index + not_empty_index + 1, temp)cur_level = 0
start_row = 0
end_row = rowNum
json_data = {}resolveData(cur_level,start_row,end_row,json_data)print(json.dumps(json_data, ensure_ascii=False))target = "C:\\Users\\cxy\\Desktop\\generate_target.json"
with open(target, "w", encoding='utf-8') as f:json.dump(json_data, f, indent=4, ensure_ascii=False)

結果

{
? ? "userInfos": {
? ? ? ? "userPic": "https://oss.shop.sxmu.com/test/dzpt/頭像20.jpeg",
? ? ? ? "nickName": "墨雨無痕",
? ? ? ? "level": "42",
? ? ? ? "userId": "66606024",
? ? ? ? "gaming_career": [
? ? ? ? ? ? {
? ? ? ? ? ? ? ? "gamePic": "https://oss.shop.sxmu.com/test/dzpt/mszb.jpeg",
? ? ? ? ? ? ? ? "gameName": "魔獸爭霸3",
? ? ? ? ? ? ? ? "level": "42",
? ? ? ? ? ? ? ? "score": "96800",
? ? ? ? ? ? ? ? "gaming_career": [
? ? ? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? ? ? "modePic": "https://oss.shop.sxmu.com/test/dzpt/ch.png",
? ? ? ? ? ? ? ? ? ? ? ? "modeName": "澄海3C",
? ? ? ? ? ? ? ? ? ? ? ? "score": "56800",
? ? ? ? ? ? ? ? ? ? ? ? "gameTimes": "1000"
? ? ? ? ? ? ? ? ? ? },
? ? ? ? ? ? ? ? ? ? {
? ? ? ? ? ? ? ? ? ? ? ? "modePic": "https://oss.shop.sxmu.com/test/dzpt/DOTA6.83.png",
? ? ? ? ? ? ? ? ? ? ? ? "modeName": "DOTA 6.83",
? ? ? ? ? ? ? ? ? ? ? ? "score": "40000",
? ? ? ? ? ? ? ? ? ? ? ? "gameTimes": "650"
? ? ? ? ? ? ? ? ? ? }
? ? ? ? ? ? ? ? ]
? ? ? ? ? ? },
? ? ? ? ? ? {
? ? ? ? ? ? ? ? "gamePic": "https://oss.shop.sxmu.com/test/dzpt/cs.jpg",
? ? ? ? ? ? ? ? "gameName": "CS",
? ? ? ? ? ? ? ? "level": "38",
? ? ? ? ? ? ? ? "score": "76500"
? ? ? ? ? ? }
? ? ? ? ]
? ? },
? ? "player_list": {
? ? ? ? "star_list": [
? ? ? ? ? ? {
? ? ? ? ? ? ? ? "userPic": "https://oss.shop.sxmu.com/test/dzpt/頭像11.jpeg",
? ? ? ? ? ? ? ? "nickName": "阿海",
? ? ? ? ? ? ? ? "userDesc": "喜歡跳不上的b小，架不住的a1，最愛的沙鷹，放不開也抓不住。",
? ? ? ? ? ? ? ? "online": "0"
? ? ? ? ? ? },
? ? ? ? ? ? {
? ? ? ? ? ? ? ? "userPic": "https://oss.shop.sxmu.com/test/dzpt/頭像12.jpeg",
? ? ? ? ? ? ? ? "nickName": "醉念",
? ? ? ? ? ? ? ? "userDesc": "只不過游戲而已，玩的再牛逼又如何",
? ? ? ? ? ? ? ? "online": "0"
? ? ? ? ? ? }
? ? ? ? ],
? ? ? ? "fans_list": [
? ? ? ? ? ? {
? ? ? ? ? ? ? ? "userPic": "https://oss.shop.sxmu.com/test/dzpt/頭像16.jpeg",
? ? ? ? ? ? ? ? "nickName": "亡夢",
? ? ? ? ? ? ? ? "userDesc": "遺憾與殘局皆，與神明畫過押。",
? ? ? ? ? ? ? ? "online": "0"
? ? ? ? ? ? }
? ? ? ? ]
? ? }
}