How to convert excel file to JSON with nested array in nodejs-CodePudding

I am trying to convert my excel file to JSON. Basically, I am able to do it but I did not get my expected result. I am trying to add a nested array to my JSON file.

My basic excel sheet.

Here I have tried to convert it into JSON files using convert-excel-to-json npm, and I did it successfully. But I want to add another field whose name is images. On the images field, there will be id and src. The images filed will show in an array of objects.

app.post("/upload-excel", async (req, res) => {
            const file = req.files.file;
            const filename = file.name;
            const worksheetsArray = xlsx.parse(filename); // parses a file

            const excelData = excelToJson({
                sourceFile: filename,
                sheets: [{
                    // Excel Sheet Name
                    name: worksheetsArray[0].name,

                    // Header Row -> be skipped and will not be present at our result object.
                    header: {
                        rows: 1
                    },

                    // Mapping columns to keys
                    columnToKey: {
                        A: '_id',
                        B: 'name',
                        C: 'address',
                        D: 'age'
                    }
                }]
            });
             const withImages = excelData.Customers.map((customer) => {
                return {
                    images: [
                        { id: customer.image_id, src: customer.image_src }
                    ],
                    ...customer,
                };
            });
            console.log(withImages);
        });

The above code output is:

{
  Customers: [
    { _id: 1, name: 'Jack Smith', address: 'Massachusetts', age: 23 },
    { _id: 2, name: 'Adam Johnson', address: 'New York', age: 27 },
    {
      _id: 3,
      name: 'Katherin Carter',
      address: 'Washington DC',
      age: 26
    },
    { _id: 4, name: 'Jack London', address: 'Nevada', age: 33 },
    { _id: 5, name: 'Jason Bourne', address: 'California', age: 36 }
  ]
}

Below JSON is My expected output. I think it's not possible by the npm which I am using. if you could suggest to me how can achieve my expected result I will be very helpful.

{
  Customers: [
    { _id: 1, name: 'Jack Smith',images:[{id:1, src:"sadasd.com"}], address: 'Massachusetts', age: 23 },
    { _id: 2, name: 'Adam Johnson', address: 'New York',images:[{id:1, src:"sadasd.com"}] age: 27 },
    {
      _id: 3,
      name: 'Katherin Carter',
      address: 'Washington DC',
      age: 26,
images:[{id:1, src:"sadasd.com"}, {id:1, src:"sadasd.com"}],
 
    { _id: 4, name: 'Jack London', address: 'Nevada',images:[{id:1, src:"sadasd.com"}], {id:1, src:"sadasd.com"}], age: 33 },
    { _id: 5, name: 'Jason Bourne',images:[{id:1, src:"sadasd.com"}, address: 'California', age: 36 }
  ]
}

CodePudding user response：

You can use map to change the items in the array and add more information and then use spread to add the existing fields as well.

const withImages = excelData.Customers.map((customer) => {
  const imgIds = customer.image_id.split(",");
  const imgSrcs = customer.image_src.split(",");
  return {
    images: imgIds.map((id, i) => {
      return { id, src: imgSrcs[i] };
    }),
    ...customer,
  };
});