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

The command '.' is unable to be executed as an internal or external command, executable program, or batch file when using npm start -- -e=stag -c=it

Completes a form on a separate website which then populates information onto a different website

unable to update the table due to issues with the knockout observableArray

There seems to be an issue with the hidden field value not being properly set within the

Storing user input in a MongoDB database using Angular and Node.js

Tips for customizing the border of an outlined TextField in MUI

Leverage the power of Pandas to easily parse through complex nested JSON data with either the

Updating JSON data post XMLHttpRequest call

Achieving synchronous function execution with a single click in React

Struggle with Transmitting Information in Ionic 2

Encountering a critical issue with Angular 12: FATAL ERROR - The mark-compacts are not working effectively near the heap limit, leading to an allocation failure due

obtain a Javascript variable within a Jade template by using inline code

JavaScript: Use onclick events to change the class of a div on multiple divs

Display a dropdown menu when clicking on a close button in a single element using Vanilla JavaScript

A method for reversing specific characters within lengthy strings

Having trouble extracting the date modified from a JSON file

The API response indicating success is simply denoted as "Success: True", without any accompanying data

What is the best way to restrict the input options for a String field within a TextField component in Material-UI?

Vue.js - computed property not rendering in repeated list

Creating Javascript objects using provided JSON data