javascript - 如何使用json_to_sheet提取xlxs中的json数据

Question

我正在尝试使用下面的 JSON 填充 Excel 文件。我从 HTTP 响应获取 JSON 数据，我想用它来下载 Excel 文件。

   {  
       "dynaModel":[  
          {  
             "map":{  
                "UNIT/SUBUNIT":"sdasd",
                "SUBUNIT/ISU/GEO":"sasd",
                "REVENUEINR-RS":"₹87,sdd",
                "COSTINR-RS":"₹47,33",
                "GMINR-RSUSD-$":46,
                "REVENUEINR-RS":"₹87,64,",
                "COSTINR-RS":"₹47,33,",
                "GMINR-RSUSD-$":46
             }
          },
          {  
             "map":{  
                "UNIT/SUBUNIT":"fghf",
                "SUBUNIT/ISU/GEO":"CMghhfI",
                "REVENUEINR-RS":"₹59,06",


          "COSTINR-RS":"₹30,43",
            "GMINR-RSUSD-$":48.47,
            "REVENUEINR-RS":"₹59",
            "COSTINR-RS":"₹30,43",
            "GMINR-RSUSD-$":48.47
         }
      },
      {  
         "map":{  
            "UNIT/SUBUNIT":"hfgh",
            "SUBUNIT/ISU/GEO":"fghh",
            "'APR-16'_REVENUEINR-RS":"₹29,72",
            "'APR-16'_COSTINR-RS":"₹11,43",
            "'APR-16'_GMINR-RSUSD-$":61.53,
            "'Total'_REVENUEINR-RS":"₹29,72",
            "'Total'_COSTINR-RS":"₹11,43",
            "'Total'_GMINR-RSUSD-$":61.53
         }
      }
   ]
}

我的组件代码如下所示:

excelDownload(){
   this._isuGeoSubunitReportService.excelDownload(this.isugeosubunitTO)
    .subscribe(data =>this.responseStatus = data,
        err => console.log(err),
       () => console.log('Request Completed222')
    );



const ws_name = 'SomeSheet';  
const wb: WorkBook = { SheetNames: [], Sheets: {} };
const ws: any = utils.json_to_sheet(this.responseStatus.dynamoModel);
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
const wbout = write(wb, { bookType: 'xlsx', bookSST: true, type: 'binary' });

function s2ab(s) {
  const buf = new ArrayBuffer(s.length);
  const view = new Uint8Array(buf);
  for (let i = 0; i !== s.length; ++i) {
    view[i] = s.charCodeAt(i) & 0xFF;
  };
  return buf;
}

saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), 'exported.xlsx');
}

我正在尝试下载 xlxs 中的结果，我正在使用 json 的 xlxs 模块。它对于简单的 json 工作正常，但我的 json 数据不同。

   const ws: any = utils.json_to_sheet(this.responseStatus);

如果我只是将 this.responseStatus 放入 xlxs 中，它只会返回一个 map 值。

Answer 1

您的代码中存在输入错误。您正在使用:this.responseStatus.dynamoModel 而不是 this.responseStatus.dynaModel。

此外，根据 documentation在 xlsx 模块中，在使用 utils.json_to_sheet 之前，要解析的数组预计具有如下语法:

[
  {S:1,h:2,e:3,e_1:4,t:5,J:6,S_1:7},
  {S:2,h:3,e:4,e_1:5,t:6,J:7,S_1:8}
]

因此您可以使用函数utils.aoa_to_sheet代替utils.json_to_sheet

另一种选择是，您可以创建自己的函数来解析数据，以便获得类似于 utils.json_to_sheet 文档中给出的形式。这就是我在您的组件中转换代码的方法。

excelDownload() {
    this.appService.excelDownload()
      .subscribe(data => {
          this.responseStatus = data;
          this.generateExcelFile(data);
        },
        err => console.log(err),
        () => console.log('Request Completed222')
      );
  }
  generateExcelFile(data: any) {
    this.responseStatus = data;
    const ws_name = 'SomeSheet';
    const wb: WorkBook = { SheetNames: [], Sheets: {} };
      const ws: any = utils.json_to_sheet(parseArray(this.responseStatus.dynaModel));
    wb.SheetNames.push(ws_name);
    wb.Sheets[ws_name] = ws;
    const wbout = write(wb, { bookType: 'xlsx', bookSST: true, type: 'binary' });

    function s2ab(s) {
      const buf = new ArrayBuffer(s.length);
      const view = new Uint8Array(buf);
      for (let i = 0; i !== s.length; ++i) {
        view[i] = s.charCodeAt(i) & 0xFF;
      }
      return buf;
    }
    // function to parse your array coming from the backend 
    function parseArray(dataToParse: any) {
      const newArray = [];
      dataToParse.forEach(item => {
        Object.keys(item).forEach(key => {
          newArray.push(item[key]);
        });
      });
      console.log('newArray:' + JSON.stringify(newArray));
      return newArray;
    }

    FileSaver.saveAs(new Blob([s2ab(wbout)],
      { type: 'application/octet-stream'}),
      'exported.xlsx');
  }