将2D JavaScript数组导出到Excel工作表 [英] Export 2D javascript array to Excel sheet

问题描述

我知道，这里有关于此主题的数百个问题，但是经过一天的搜索，我仍然找不到令人满意的答案:

我有一个2D javascript数组，我想将其下载为Excel工作表.

这里是我到目前为止获得的代码的小提琴:

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

下载正常，但是有一些问题，经过数天的尝试，我无法解决:

1. 所有项目最终都在Excel工作表的第一列中，因为Excel将解释，"，将元素分隔为数据的一部分.如何以Excel理解的方式分离元素?
2. 文件名是一些密码.如何设置文件名?
3. 下载的文件具有两个结尾的.xls(.xls.xls).如何获得单个.xls结尾?
4. 每次文件损坏或不安全时，Excel都会告诉我.如何防止这种情况?

对这些问题的任何帮助将不胜感激.

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

更新

我只是偶然发现1. 2.和4.可以通过用csv替换vnd.ms-excel来解决.

该文件将不再是.xls，但是Excel可以打开csv而不出现问题，并且行为与预期的一样.

剩下的唯一问题就是文件名！

更新2

最后，经过2个工作日的搜索和尝试，我找到了解决方案，我想在这里与大家分享，以帮助遇到相同问题的任何人:

仅包含一个不可见的<a>元素，该元素使用其download="somedata.csv"属性将文件定义为有用的名称.

这是我的最后一个功能齐全的小提琴:

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

解决方案

最后，经过2个工作日的搜索和尝试，我找到了要在此处分享的解决方案，以帮助遇到相同问题的任何人:

仅包含一个不可见元素，该元素使用其download ="somedata.csv"属性为文件提供有用的名称:

这是我的最后一个功能齐全的小提琴:

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

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/

解决方案

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

这篇关于将2D JavaScript数组导出到Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！