Export 2D javascript array to Excel sheet

Ente Fetz picture Ente Fetz · Aug 10, 2017 · Viewed 13.8k times · Source

I know, there are hundreds of questions on this topic on here, but I still could not find satisfactory answers after a day of searching:

I have a 2D javascript array, which I want to download as an Excel sheet.

Here is a fiddle with the code I got so far:

https://jsfiddle.net/3an24jmw/7/

The download works, but there are several issues, which I could not solve after days of trying:

  1. All items end up in the first column of the Excel sheet, because Excel interprets the "," separating the elements as part of the data. How can I separate the elements in a way Excel understands?
  2. The file name is some cryptic code. How can I set the file name?
  3. The downloaded file has a double .xls ending (.xls.xls). How can get a single .xls ending?
  4. Excel tells me every time the file could be corrupted or unsafe. How do I prevent this?

Any help for any of these questions would be appreciated.

exportToCsv = function() {
    var CsvString = "";
    Results.forEach(function(RowItem, RowIndex) {
        RowItem.forEach(function(ColItem, ColIndex) {
            CsvString += ColItem + ',';
        });

        CsvString += "\r\n";
    });

    window.open('data:application/vnd.ms-excel,' + encodeURIComponent(CsvString));
}

UPDATE

I just found out by chance, that 1. 2. and 4. can be solved by replacing vnd.ms-excel with csv.

The file will not be .xls anymore, but the csv can be opened by Excel without problems and behaves like intended.

Only problem remaining is the file name!

UPDATE 2

Finally after 2 full workdays of searching and trying, I found the solution, which I would like to share here, to help anybody with the same problem:

Simply include an invisible <a> element, which defines the file an useful name using its download="somedata.csv" attribute.

Here is my final and fully functional fiddle:

https://jsfiddle.net/3an24jmw/25/

Answer

Ente Fetz picture Ente Fetz · Aug 10, 2017

Finaly after 2 full workdays of searching and trying, I found the solution, which I would like to share here, to help anybody with the same problem:

Simply include an invisible element, which gives the file an usefull name using its download="somedata.csv" attribute:

Here is my final and fully functional fiddle:

https://jsfiddle.net/3an24jmw/25/

var Results = [
["Col1", "Col2", "Col3", "Col4"],
["Data", 50, 100, 500],
["Data", -100, 20, 100],
];

exportToCsv = function() {
  var CsvString = "";
  Results.forEach(function(RowItem, RowIndex) {
    RowItem.forEach(function(ColItem, ColIndex) {
      CsvString += ColItem + ',';
    });
    CsvString += "\r\n";
  });
  CsvString = "data:application/csv," + encodeURIComponent(CsvString);
  var x = document.createElement("A");
  x.setAttribute("href", CsvString );
  x.setAttribute("download","somedata.csv");
  document.body.appendChild(x);
  x.click();
}