Save javascript data to Excel

Question

I have a web application that needs to be able to export some javascript data (arrays, mostly) to Microsoft Excel. I've been told the best way to do that is to save my data as a CSV file. I know there are a lot of threads that deal with similar issues, but so far I have not found exactly what I am looking for... I would like to get around calling a web service and do this through the client side.

Following some suggestions I tried writing my arrays to disk using data URIs. Here is the code that I have in javascript:

function exportToCsv() {

        var data = [JSONdata.mass, JSONdata.waterMass, JSONdata.bedTemp, JSONdata.evapRate];
        var keys = ["Mass(kg)", "H2O Mass in Bed(kg)", "Bed Temperature(kg)", "Evaporation Rate"];

        var str;
        var orderedData = [];
        for (var i = 0, iLen = data.length; i < iLen; i++) {
            temp = data[i];
            for (var j = 0, jLen = temp.length; j < jLen; j++) {

                if (!orderedData[j]) {
                    orderedData.push([temp[j]]);
                } else {
                    orderedData[j].push(temp[j]);
                }
            }
        }


        str = keys.join(',') + '\r\n' + orderedData.join('\r\n');      



        var uri = 'data:application/csv;charset=UTF-8,' + str;
        window.open(uri);       

}

The string str looks like its in CSV format, but when I save my file, it turns up empty. I also would very much like to save the contents of str to a CSV file from a pop-up prompt.

I looked into Downloadify and FileSaver.js, but had trouble finding good documentation. This seems like it should be a fairly easy thing to do... Please lend a hand!

Thanks in advance!

EDIT/UPDATE

I ended up getting my solution to work using FileSaver.js. My data was a header followed by four large double arrays, so CVS was a good enough format for me. Here is the code I used to get my CSV files to save properly:

function exportExcel()
    {
        var data = [JSONdata.mass, JSONdata.waterMass, JSONdata.bedTemp, JSONdata.evapRate];
        //var data = [[1,2,3,4,5],[11,22,33,44,55],[111,222,333,444,555],[1111,2222,3333,4444,5555]];
        var keys = ['"Mass(kg)"', '"H2O Mass in Bed(kg)"', '"Bed Temperature(kg)"', '"Evaporation Rate"'];

        var convertToCSV = function(data, keys) {
            var orderedData = [];
            for (var i = 0, iLen = data.length; i < iLen; i++) {
                temp = data[i];
                for (var j = 0, jLen = temp.length; j < jLen; j++) {

                    quotes = ['"'+temp[j]+'"'];
                    if (!orderedData[j]) {
                        orderedData.push([quotes]);
                    } else {
                        orderedData[j].push(quotes);
                    }
                }
            }
            return keys.join(',') + '\r\n' + orderedData.join('\r\n');
        }


        var str = convertToCSV(data, keys);

        var blob = new Blob([str], {type: "text/plain;charset=utf-8"});
        var filename = prompt("Please enter the filename");
        if(filename!=null && filename!="")
            saveAs(blob, [filename+'.csv']);
        else
            alert("please enter a filename!");        

    }

If anyone else comes across a similar issue, this solution worked pretty well for me and allowed me to skip going back to the server side. Thanks for the help everyone.

Have you noticed that `orderedData` is turning up empty after the `for` loop? Appart from that the file I get after the download seems fine (headers only). Here's a [fiddle](http://jsfiddle.net/4mZ39/). — juan.facorro, Jul 25 '13 at 21:07
For me the file has just the headers, and all the data goes into one Excel cell instead of being distributed across the table properly... — tbogatchev, Jul 26 '13 at 13:08

score 2 · Accepted Answer · answered Jul 26 '13 at 15:24

2

I was able to understand and reproduce the behavior you are describing. The problem with the code that generates the URI is that it's not encoding the contents of str.

Here's the line that I changed, notice the wrapping of the str variable with the encodeURI() built-in function:

var uri = 'data:application/csv;charset=UTF-8,' + encodeURI(str);

Here's a fiddle that generates the CSV file with the new lines included: DEMO. I simplified the for loop since orderedData turned out empty after its execution.

Note that Excel generated "CSV" files, don't actually use commas but semicolons. So if you want to use the file in Excel, replace , for ;. Here's an Excel compatible file generation: DEMO.

answered Jul 26 '13 at 15:24

juan.facorro

9,791
2
33
41

1

Thank you for your patience. I am still getting my results in only one cell of the table (2 cells in the Excel version). Is there a way to get all the data properly distributed through the table cells? – tbogatchev Jul 26 '13 at 16:56
What does the content of the file you download look like? – juan.facorro Jul 26 '13 at 20:18
It is just a header and four arrays of doubles. I ended up getting my solution to work using FileSaver.js. I'll post what I did in the edit. – tbogatchev Jul 29 '13 at 16:54

score -1 · Answer 2 · edited May 23 '17 at 11:52

-1

Easy way to turn Javascript array into comma-separated list?

seems to be a good thread on this, and a relatively straightforward implementation of the functionality you want.

Is there some reason this did not work for you?

EDIT:

It seems like you are interested in the HTML5 FileSaver, though I do not know how widely this is supported, it would seem to fulfill your functionality.

this: http://updates.html5rocks.com/2011/08/Saving-generated-files-on-the-client-side

and this: How can i generate a file and offer it for download in plain javascript?

edited May 23 '17 at 11:52

Community

1
1

answered Jul 25 '13 at 21:04

krishnakid

108
5

1

I have a list, that isn't the problem. I would like to download the list as a CSV or Excel file with a download prompt. – tbogatchev Jul 26 '13 at 13:09

Save javascript data to Excel

2 Answers2