I have an array of objects (JSON) in JS and want to convert it to an Excel worksheet. In the JSON data, there are some key-value pairs I do not want convert to Excel, for example Excel should not contain year information like below (PLEASE RUN HTML CODE TO SEE EXCEL OUTPUT REQUIRED):
JSON data:
[
{
car: "Range Rover",
color: "silver",
year: "2021"
},
{
car: "Benz",
color: "black",
year: "2019"
},
{
car: "Toyota",
color: "silver",
year: "2020"
}
]Excel view (Run the 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>How can I achieve this using XLSX in JS?
There are few things you can do here.
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');
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');
Checking the utility functions documentation might be helpful too.