如何处理JSON嵌套数组并导入Excel？解决JS字段undefined问题

你需要处理JSON中的嵌套数据并导入Excel，但当前代码中Login Time和Login Status字段显示undefined，相关信息如下：

原始JSON数据 #

"data": [
    {
      "character_id": "5785304882",
      "id": 289222,
      "login_status": {
        "created": "2022-09-21T19:52:04.394",
        "status": "Started Competition"
      },
      "name": "user_01",
      "team": "DA ATRAX ESPORTS"
    },
    {
      "character_id": "5670142641",
      "id": 289223,
      "login_status": {
        "created": "2022-09-21T20:10:00.123",
        "status": "Started Competition"
      },
      "name": "user_02",
      "team": "DA ATRAX ESPORTS"
    }
]

原使用代码 #

gacworksheet.columns = [
  { header: "User Name", key: "name", width: 10 },
  { header: "Team Name", key: "team", width: 10 },
  { header: "Character ID", key: "character_id", width: 10 },
  { header: "Login Time", key: "loginTime", width: 10 },
  { header: "Login Status", key: "loginStatus", width: 10 },
];

var json = JSON.parse(result);
var userData = json['data']; 
userData.forEach(user => {
  for (var i = 0; i < userData.length; i++) {
    var loginTime = userData[i].login_status.created;
    var loginStatus = userData[i].login_status.status;
  }
   gacworksheet.addRow(user);
});
let gacdata = gacworksheet.getSheetValues();
gacMData = gacdata.map(function(r){
  return [r[1],r[2],r[3],r[4],r[5]];
});
console.log(gacMData);

错误输出结果 #

[
    ['User Name', 'Team Name', 'Character ID', 'Login Time', 'Login Status'],
    ['user_01', 'DA ATRAX ESPORTS', '5785304882', undefined, undefined],
    ['user_02', 'DA ATRAX ESPORTS', '5670142641', undefined, undefined]
]

问题原因 #

1. 嵌套的for循环完全多余，且仅赋值变量未将值关联到当前遍历的user对象
2. Excel列的键是loginTime和loginStatus，但原始JSON中对应值在user.login_status.created和user.login_status.status，未完成字段映射

修正后的代码 #

gacworksheet.columns = [
  { header: "User Name", key: "name", width: 10 },
  { header: "Team Name", key: "team", width: 10 },
  { header: "Character ID", key: "character_id", width: 10 },
  { header: "Login Time", key: "loginTime", width: 10 },
  { header: "Login Status", key: "loginStatus", width: 10 },
];

var json = JSON.parse(result);
var userData = json['data']; 

userData.forEach(user => {
  // 将嵌套字段值映射到对应键，可选链避免字段缺失报错
  user.loginTime = user.login_status?.created;
  user.loginStatus = user.login_status?.status;
  // 添加处理后的用户数据到工作表
  gacworksheet.addRow(user);
});

let gacdata = gacworksheet.getSheetValues();
gacMData = gacdata.map(function(r){
  return [r[1],r[2],r[3],r[4],r[5]];
});
console.log(gacMData);

关键说明 #

• 直接在当前user对象上新增与Excel列键匹配的属性，完成嵌套值的映射
• 使用?.可选链操作符，防止部分用户数据缺失login_status字段时抛出错误
• 移除多余的嵌套循环，简化逻辑并提升效率

内容的提问来源于stack exchange，提问作者Arun Shrestha