Export 2D javascript array to Excel sheet

Question

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:

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?
The file name is some cryptic code. How can I set the file name?
The downloaded file has a double .xls ending (.xls.xls). How can get a single .xls ending?
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/

Ente Fetz · Accepted Answer · 2017-08-10 14:26:44Z

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();
}

Bas · Accepted Answer · 2017-08-11 13:31:22Z

0

The separator Excel expects for csv depends on your system locale setting for list separator. You can hint Excel which separator to use for your csv file by adding "sep=," as the first line. In your case use could use: var CsvString = '"sep=,"\r\n';

edited Aug 11, 2017 at 13:31

answered Aug 10, 2017 at 11:15

Bas

11 bronze badge

2 Comments

Ente Fetz Over a year ago

Thanks for the suggestion, but did that work for you in the fiddle ? When I try it, the first row of the sheet is replaced by sep=,

Bas Over a year ago

My bad, misplaced the double quote. Now fixed.

Collectives™ on Stack Overflow

Export 2D javascript array to Excel sheet

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related