How to store Javascript array elements in excel file while converting JSON object to excel file in node js?

Question

I've a usecase where I need to export user data into excel file. User model(mongo db model) consists a field of type array. While exporting, every field is stored into excel file except, that array field data. How to store that array data into excel file as comma separated values?

Here's my code:

var XLSX = require('xlsx');

exportCustomerDataToExcel: async function(customerId){
    let err,customerData;

    [err, customerData]=await to(Customer.find({"_id":customerId},'name email phone unit block communityAccountNo status').lean().exec());

    //*name email phone unit block communityAccountNo status* are fields of customer & unit is an array

    if(err) {TE(err, true)};

    if(!customerData || customerData.length==0) return false;

    if(customerData){
        customerData.map(function(elem) {
            if (elem._id) delete elem._id

        });
        var newWB=XLSX.utils.book_new();
        var newWS=XLSX.utils.json_to_sheet(customerData);
        XLSX.utils.book_append_sheet(newWB, newWS,"Customers");
        [err,excelWB]=await to(this.writeFileQ(newWB, "./public/exports/"+customerId+".xlsx"));
        if(err) TE(err, true);
        return ({file:customerId+".xlsx"});
    }
},

writeFileQ: function(workbook, filename) {
    return new Promise((resolve, reject) => {
        XLSX.writeFileAsync(filename, workbook, (error, result) => {
          (error)? reject(error) : resolve(result);
        });
    });
}

Sample code is:

{
    "name" : "Some Name",
    "email" : "SomeName@gmail.com",
    "phone" : "94XXXXXXXX",
    "communityAccountNo" : "ABCD1234",
    "status" : true,
    "block" : "1",
    "unit" : [ 
        "9-2", 
        "9-3"
    ]
}

Here is the excel I got:

I want to store the unit elements as, 9-2,9-3 under the unit tab. Is it possible to do so?

Answer 1

You should reformat the array in a string before creating the excel file to make it works the way you want. You can achieve this by using the Array join method, and use , as parameter. Something like this:

customerData.unit = customerData.unit.join(',');

Or even customerData.unit = customerData.unit.join(); since , is the default parameter.

And then create your excel sheet.