1

I have a dynamic JSON data fro which I am creating a Excel file using jsno2xls library nut here the resulting excel file header are like Year,DOB,Name and Age and I want excel data in this format Name,Age,DOB and Year.Is there any way I can perform this action and get data in desired format.

Below is what I am doing to create Excel sheet.

const parsedData = [{},....,{}];
const xls = json2xls(parsedData);
fs.writeFileSync('datasheet.xlsx', xls, 'binary');

Here in above code parsedData is coming from database which can have any number of fields in JSON object.

But in resulting Excel file I always want Name and Age column at first.

Digvijay
  • 2,887
  • 3
  • 36
  • 86

1 Answers1

1

Try providing options object, where you set fields property to an array of fields you want to export, which will also export them in that order:

const xls = json2xls(parsedData, {
    fields: ['Name', 'Age', 'DOB', 'Year']
});

If keys don't correspond (not case sensitive), then try transforming the data, where you'll create a new object whose keys correspond to those you want in the xlsx file:

const parsedData = [{
    dob: "dob",
    name: 'bar',
    age: 'moo',
    year: new Date()
}];    

const transformedJson = parsedData.map(obj => {

    const newObject = {};

    newObject['D O B'] = obj['dob'];
    newObject['Name'] = obj['name'];
    newObject['Age'] = obj['age'];
    newObject['Year'] = obj['year'];

    return newObject;

});   
 
var xls = json2xls(transformedJson, {
    fields: ['Name', 'Age', 'D O B', 'Year']
});

fs.writeFileSync('data.xlsx', xls, 'binary');
traynor
  • 5,490
  • 3
  • 13
  • 23
  • I have white spaces in `D O B` like this when I am writing this inside `fields` array as per you solution with white spaces then this `D O B` column is coming empty in excel file. – Digvijay Mar 04 '23 at 20:37
  • is the order of columns respected? if so, then that's another question. can you include an example of json, it's hard to say.. – traynor Mar 04 '23 at 21:45
  • how does the `D O B` look like in JSON? try `fields: {'D O B':'string'}` – traynor Mar 04 '23 at 22:12
  • No columns order is respected and when I put 'D O B' its showing this particular column empty.Its because json field was 'dob' but in excel sheet I want it to be 'D O B'. – Digvijay Mar 05 '23 at 04:13
  • alright, so your initial question about the order is answered. About the column names, try transforming the data then by creating new objects and setting the keys exactly as you want them in the file, try the updated code – traynor Mar 05 '23 at 07:23