Skipping certain key-value pairs during the conversion from JSON to Excel Worksheet using the XLSX library in JavaScript

Question

Skipping certain key-value pairs during the conversion from JSON to Excel Worksheet using the XLSX library in JavaScript

I have a set of objects in JSON format within my JavaScript code and I am looking to transform this data into an Excel worksheet. Within the JSON structure, there are certain key-value pairs that I do not wish to include in the Excel output. For instance, I want to exclude any information related to the year as shown below (PLEASE EXECUTE HTML CODE TO VIEW DESIRED EXCEL OUTPUT):

JSON example:

[
  {
    car: "Range Rover",
    color: "silver",
    year: "2021"
  },
  
  {
    car: "Benz",
    color: "black",
    year: "2019"
  },
  
  {
    car: "Toyota",
    color: "silver",
    year: "2020"
  }
]

Desired Excel view (Execute snippet):

<html>
  <body>
    <table>
      <tr>
        <th>car</th>
        <th>color</th>
      </tr>

      <tr>
        <td>Range Rover</td>
        <td>silver</td>
      </tr>

      <tr>
        <td>Benz</td>
        <td>black</td>
      </tr>

      <tr>
        <td>Toyota</td>
        <td>silver</td>
      </tr>
    </table>
  </body>
</html>

Is it possible to accomplish this using XLSX library in JavaScript?

javascript json excel xlsx worksheet

Answer 1

Answer №1

There are a couple of options you have in this scenario.

To manipulate your JSON data, one approach is to save it into an xlsx worksheet, modify the worksheet's !ref property to define the range you want, and then include that sheet in the xlsx book.

let file = fs.readFileSync('test.json', 'utf-8');
let data = JSON.parse(file);

var wb = xlsx.utils.book_new();
var ws = xlsx.utils.json_to_sheet(data);

ws['!ref'] = 'A1:B4';
xlsx.utils.book_append_sheet(wb, ws, 'Sheet 1');
xlsx.writeFile(wb, 'test1.xlsx');

An alternate method involves filtering out specific properties from your JSON data into a separate array and then incorporating them into the file.

let file = fs.readFileSync('test.json', 'utf-8');
let data = JSON.parse(file);

var wb = xlsx.utils.book_new();

let dataArray = [];
data.forEach(d => {
  dataArray.push({"car": d.car, "color": d.color});
});

var ws = xlsx.utils.json_to_sheet(dataArray);
console.log(ws);
xlsx.utils.book_append_sheet(wb, ws, 'Sheet 1');
xlsx.writeFile(wb, 'test2.xlsx');

Referencing the utility functions documentation can also provide valuable insights.

https://www.npmjs.com/package/xlsx#utility-functions

Answer 2

There are a couple of options you have in this scenario.

To manipulate your JSON data, one approach is to save it into an xlsx worksheet, modify the worksheet's !ref property to define the range you want, and then include that sheet in the xlsx book.

let file = fs.readFileSync('test.json', 'utf-8');
let data = JSON.parse(file);

var wb = xlsx.utils.book_new();
var ws = xlsx.utils.json_to_sheet(data);

ws['!ref'] = 'A1:B4';
xlsx.utils.book_append_sheet(wb, ws, 'Sheet 1');
xlsx.writeFile(wb, 'test1.xlsx');

An alternate method involves filtering out specific properties from your JSON data into a separate array and then incorporating them into the file.

let file = fs.readFileSync('test.json', 'utf-8');
let data = JSON.parse(file);

var wb = xlsx.utils.book_new();

let dataArray = [];
data.forEach(d => {
  dataArray.push({"car": d.car, "color": d.color});
});

var ws = xlsx.utils.json_to_sheet(dataArray);
console.log(ws);
xlsx.utils.book_append_sheet(wb, ws, 'Sheet 1');
xlsx.writeFile(wb, 'test2.xlsx');

Referencing the utility functions documentation can also provide valuable insights.

https://www.npmjs.com/package/xlsx#utility-functions

Skipping certain key-value pairs during the conversion from JSON to Excel Worksheet using the XLSX library in JavaScript

Answer №1

Similar questions

Exploring test suite pathways while utilizing ArcGIS JSAPI as an alternative loader within the Intern framework

Utilizing DOMStringMap data to populate CSS URLs

"Encountering a challenge when trying to populate a partial view using AngularJs and MVC

javascript path as the first argument and callback as the last argument

Setting the Date in Angular.js: A Step-by-Step Guide

Enhance Your Website with Interactive Tooltips Using Twitter Bootstrap

Open the JSON file on Amazon Alexa

What's the best way to use JavaScript to obtain the width of a 'css-pixel' based on a media query?

Learning how to replace the alert function with Bootbox

Navigating intricate JSON information using AngularJS

Can someone guide me on how to display Jade content using JSON in Node.js?

Change button to an ajax spinner when it is clicked using jQuery

Struggling with loading.jsx file in next js version 13.4.5?

How can I verify the status of an occasional undefined JSON value?

How to Create a Custom Callback Function for jQuery's .html

The never-ending nightmare of acquiring a Facebook Graph token

Is there a way to dynamically load a file on scroll using JavaScript specifically on the element <ngx-monaco-diff-editor>?

How to make a straightforward task list using ExpressJS

Looking for the location of a matching brace in a dataset?

Attempting to send numerous identifiers in an API request