Learn the process of retrieving JSON data in an Excel file using the json_to_sheet function

Question

Learn the process of retrieving JSON data in an Excel file using the json_to_sheet function

I am currently working on populating an excel file by utilizing the JSON data provided below. This JSON data is retrieved from an HTTP response and I intend to utilize it for downloading an excel file.

   {  
       "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
             }
          }
       ]
   }

The code snippet of my components can be seen below:

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

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');
}

I am attempting to download the result in xlsx format using the xlsx module of json. While it works well for simple JSON data, I am encountering challenges with my complex JSON data.

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

If I directly use this.responseStatus, only one map value is returned in the excel sheet.

javascript json angular typescript

Answer 1

Answer №1

There seems to be a typo in your code where you are using: this.responseStatus.dynamoModel instead of this.responseStatus.dynaModel.

Additionally, as per the documentation for the module xlsx, the array you are trying to parse should have a structure similar to this before utilizing 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}
]

You can use the function utils.aoa_to_sheet instead of utils.json_to_sheet.

Alternatively, you have the option to create a custom function that will restructure your data to match the format specified in the documentation for utils.json_to_sheet. Here's how I modified the code in your component:

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');
  }

Answer 2

There seems to be a typo in your code where you are using: this.responseStatus.dynamoModel instead of this.responseStatus.dynaModel.

Additionally, as per the documentation for the module xlsx, the array you are trying to parse should have a structure similar to this before utilizing 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}
]

You can use the function utils.aoa_to_sheet instead of utils.json_to_sheet.

Alternatively, you have the option to create a custom function that will restructure your data to match the format specified in the documentation for utils.json_to_sheet. Here's how I modified the code in your component:

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');
  }

Learn the process of retrieving JSON data in an Excel file using the json_to_sheet function

Answer №1

Similar questions

Place content following a three.js display created within a <div id="mainWindow">

A TypeScript class transferring data to a different class

Show images exclusively on screens with a smaller resolution

Adding a total property at the row level in JavaScript

Implementing jQuery to easily upload and display images

Experiencing erratic outcomes with window.innerWidth in Mobile Safari

A guide on iterating through an array to extract the initial character from every string

Transferring the Header component from standard React to Gatsby: Eliminating Duplicates

JSONPath - select only the fields with names containing "@" symbol

The geometry map in THREE.js fails to render

How does jQuery create a hover effect that changes the background color of a link and keeps it when the mouse hovers over a

Dealing with Angular routes in Express: Solving the issue of "res.sendFile is not a function"

Tips for troubleshooting a Typescript application built with Angular 2

prolonging inner interface created by supabase

Using the getAttribute method in Edge with JavaScript

Expanding Submenu Width

Unable to fetch all data from the Json file

Parsing an XML array using XSLT and implementing a universal template for nested elements to convert from XML to JSON

Steps for establishing a connection to a MongoDB database on Heroku using mongoose

Having trouble with Next-Auth's signIn with Credentials feature in NextJS?