how to download json data in excel (.xlsx) format without using express.

[ajay jaiswal]

I am firing query to database and getting in json format .

I want to send it to client (browser) using express js.

but i am not getting the data in browser.

 app.post("/downloadImportedTable", function (req, res) {

       var connection = new sql.Connection(dbConfig, function (err) {

        

               var dbObject = req.body.tableName;

        if (err) {

            res.status(500).send(String(err));

        } else {

            // Query 

            var sqlrequest = new sql.Request(connection);

            sqlrequest.query('select * from ' + dbObject, function (err, recordset) {

                

                if (err) {

                    res.status(500).send(String(err));

                    connection.close();

                } else {

                    //console.log(recordset);-----> it successfully fetch the data 

                    res.set({ 'Content - Type': 'application/ms-excel; charset = UTF - 8' });

                      res.status(200).send(recordset);

                  

                    connection.close();

                     

                         

                };

            });

        }

    });

    };

The recored set contains millions of record so if i convert it using npm json2xls it makes busy the browser and server to converting json into  xlsx .so this approach i don't want to use.

[Ryan Schmidt]

So what approach do you want to use, then?

Your code above appears to get an array of data from the database and send that array directly to the browser, without any conversion. You're at minimum going to have to convert the array to some kind of text. For example, you could convert it to JSON. (Possibly node does so for you automatically; I'm not sure.) But JSON of course is not Excel-format. If you want to send in a format Excel can open, I don't see an alternative to the solution you don't want, which is to have the server convert the data to Excel format using some module.

[A]

I save it as csv which opens nicely in excel

I use this example

http://jsfiddle.net/sturtevant/vunf9/

[Ajay Kumar Jaiswal]

Even i convert it using "\t" and "\n". it blocks the other process . how to resolve it. i am dealing with large data set. 

[Zlatko]

On Thursday, March 3, 2016 at 7:11:14 AM UTC+1, Ajay Kumar Jaiswal wrote:

The recored set contains millions of record so if i convert it using npm json2xls it makes busy the browser and server to converting json into  xlsx .so this approach i don't want to use.

Even i convert it using "\t" and "\n". it blocks the other process . how to resolve it. i am dealing with large data set. 

You could stream your response to the client. It will involve several stages in your stream pipeline.

Client opens the request, so you then:

- get the connection

- get the result stream, preferably something in object mode that gives you row by row

- pipe it to a stream that will convert result set row into a CSV table row (or excel, not sure if you can make that work as a stream though).

- pipe that stream to response.

Then your stream is nice and non-blocking. It's still CPU hungry and lasts long, but it doesn't block the server completely, so you can serve multiple clients in the same time.

Alternatively, spawn another process that will run your conversion (like json2xls). So your server takes a request, and if there is available capacity, spawns a process to handle this conversion, returning you a tmp file location or something of the sort. Then your server will still work for the other, regular requests because this processing is essentially off your main process.

Or do something like a two-step process - take the request and put it on your job queue - then tell the client "ok, I'll inform you when it's ready". Then this job queue is on a separate node or similar process, and it informs the client when the xls is ready ;)