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" : "[email protected]",
    "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?

snaipeberry · Accepted Answer · 2019-12-27 14:19:57Z

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.

answered Dec 27, 2019 at 14:19

snaipeberry

1,0991 gold badge9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aravind Over a year ago

And do you know how to store them as array in database when I tried to update the excel file and import? For ex, I'd update the unit and upload, it would store as an array in database. Is it possible?

snaipeberry Over a year ago

It depends of the database, if you are using MongoDB, you won't have any problem pushing an array in a collection. If you are using MySQL, it's not that efficient. But you can use JSON SQL types to store arrays

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related