使用JavaScript将JSON导出为使用UTF-8（例如希腊语）编码的CSV或Excel [英] Export JSON to CSV or Excel with UTF-8 (e.g. Greek) encoding using JavaScript

查看：266 发布时间：2016/11/19 14:36:21 javascript csv character-encoding export-to-excel export-to-csv

本文介绍了使用JavaScript将JSON导出为使用UTF-8（例如希腊语）编码的CSV或Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我尝试将 JSON 对象导出并下载到 CSV 文件，我对希腊字符有问题。我的代码工作;它不是完美的，但它的工作。

问题是希腊字符看起来像垃圾。

这是我现有的代码：

  function downloadJsonToCsv（jsonObject） {
 var array = typeof jsonObject！=object？ JSON.parse（jsonObject）：jsonObject; 
 
 if（array == null）{
 return; //在jsonObject上找不到数据
} 
 
 var str =; 
 
 for（var i = 0; i  var line =; 
 
 for（array [i]中的var index）{
 line + = array [i] [index] +;; //设置分隔符
} 
 
 //这里是一个用双引号括起来的值的例子
 // for（var index in array [i]）{
 // line + =''+ array [i] [index] +'，'; 
 //} 
 
 line.slice（0，line.Length-1）; 
 
 str + = line +\r\\\
; 
} 
 
 window.open（data：text / csv; charset = utf-8，+ encodeURI（str））; 
}

我有两个问题。

如何将这个包含正确的希腊字符的 CSV 文件导出？

格式 p> 导出到CSV

导出到包含非ASCII字符的CSV需要使用Byte Order Mark aka BOM。在您的代码更改中

var str =;

到：

var str =\\\;

您需要一个现代版本的Excel才能识别BOM。如本文 StackOverflow文章中所述，Excel 2003和更早版本将不会正确地使用BOM。我只能访问在Windows上的Excel 2003，所以我现在不能测试，但它有相当详细的文档。

很遗憾，Excel 2011的Macintosh不是一个现代Excel在这个意义上。很高兴，Google表格做的正确。

直接导出到Excel

以下是 jsFiddle 实施的代码。它会生成 SpreadsheetXml 文档。这个方法的好处是你可以得到非常棘手的...添加公式，做更多的事情特定于Excel。

  //测试脚本从JavaScript生成文件，例如
 // MS Excel将荣誉非ASCII字符。 
 
 testJson = [
 {
name：TonyPeña，
city：New York，
country 美国，
birthdate：1978-03-15，
amount：42 
 
}，
 {
 name：ZαλώνηςThessaloniki，
city：Athens，
country：Greece，
birthdate：1987-11-23 $ bamount：42 
} 
]; 
 
 //简单类型映射;日期可以硬
 //，我宁愿简单地使用`datevalue` 
 // ...你甚至可以在这里添加公式。 
 testTypes = {
name：String，
city：String，
country：String，
birthdate ：String，
amount：Number
}; 
 
 emitXmlHeader = function（）{
 var headerRow ='< ss：Row> \\\
'; 
 for（var colName in testTypes）{
 headerRow + ='< ss：Cell> \\\
'; 
 headerRow + ='< ss：Data ss：Type =String>'; 
 headerRow + = colName +'< / ss：Data> \\\
'; 
 headerRow + ='< / ss：Cell> \\\
'; 
} 
 headerRow + ='< / ss：Row> \\\
'; 
 return'<？xml version =1.0？> \\\
'+ 
'< ss：Workbook xmlns：ss =urn：schemas-microsoft-com：office：spreadsheet > \\\
'+ 
'< ss：Worksheet ss：Name =Sheet1> \\\
'+ 
'< ss：Table> \\\
\\\
' headerRow; 
}; 
 
 emitXmlFooter = function（）{
 return'\\\
< / ss：Table> \\\
'+ 
'< / ss：Worksheet> \\\
' + 
'< / ss：Workbook> \\\
'; 
}; 
 
 jsonToSsXml = function（jsonObject）{
 var row; 
 var col 
 var xml; 
 var data = typeof jsonObject！=object
？ JSON.parse（jsonObject）
：jsonObject; 
 
 xml = emitXmlHeader（）; 
 
 for（row = 0; row< data.length; row ++）{
 xml + ='< ss：Row> \\\
'; 
 
 for（col in data [row]）{
 xml + ='< ss：Cell> \\\
'; 
 xml + ='< ss：Data ss：Type ='+ testTypes [col] +'>'; 
 xml + = data [row] [col] +'< / ss：Data> \\\
'; 
 xml + ='< / ss：Cell> \\\
'; 
} 
 
 xml + ='< / ss：Row> \\\
'; 
} 
 
 xml + = emitXmlFooter（）; 
 return xml; 
}; 
 
 console.log（jsonToSsXml（testJson））;

这将生成下面的XML文档。如果此XML保存在名为test.xls的文件中，则Excel应该识别此XML并使用正确的编码打开它。

 < ;？xml version =1.0？> 
< ss：Workbook xmlns：ss =urn：schemas-microsoft-com：office：spreadsheet> 
< ss：Worksheet ss：Name =Sheet1> 
< ss：Table> 
 
< ss：Row> 
< ss：Cell> 
< ss：Data ss：Type =String> name< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =String> city< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =String> country< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =String> birthdate< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =String> amount< / ss：Data> 
< / ss：Cell> 
< / ss：Row> 
 
< ss：Row> 
< ss：Cell> 
< ss：Data ss：Type =String> TonyPeña< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =String>纽约< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =String>美国< / ss：Data> 
< / ss：单元格> 
< ss：Cell> 
< ss：Data ss：Type =String> 1978-03-15< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =Number> 42< / ss：Data> 
< / ss：Cell> 
< / ss：Row> 
< ss：Row> 
< ss：Cell> 
< ss：Data ss：Type =String>ZαλώνηςThessaloniki< / ss：Data> 
< / ss：Cell> 
 < ss：Data ss：Type =String>雅典< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =String>希腊< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =String> 1987-11-23< / ss：Data> 
< / ss：Cell> 
< ss：Cell> 
< ss：Data ss：Type =Number> 42< / ss：Data> 
< / ss：Cell> 
< / ss：Row> 
 
< / ss：Table> 
< / ss：Worksheet> 
< / ss：Workbook>

然而，我必须承认，如果可能的话，我强烈的倾向是做这个服务器端。我使用Python库 openpyxl 在过去这样做，它是相当简单。大多数服务器端语言都有一个生成Excel文件的库，它们应该提供比字符串连接更好的结构。

无论如何，参见 MSDN博客了解基本信息。和这个 StackOverflow文章的各种其他选项的优点/缺点。

I am trying to export and download a JSON object to CSV file and I have problem with Greek characters. My code works; it is not perfect, but it works.

The problem is that Greek characters looks like junk.

Here is my existing code:

function downloadJsonToCsv(jsonObject) {
    var array = typeof jsonObject != "object" ? JSON.parse(jsonObject) : jsonObject;

    if (array == null) {
        return; // No data found on the jsonObject
    }

    var str = "";

    for (var i = 0; i < array.length; i++) {
        var line = "";

        for (var index in array[i]) {
            line += array[i][index] + ";"; // Set delimiter
        }

        // Here is an example where you would wrap the values in double quotes
        // for (var index in array[i]) {
        //    line += '"' + array[i][index] + '",';
        // }

        line.slice(0,line.Length-1); 

        str += line + "\r\n";
    }

    window.open("data:text/csv;charset=utf-8," + encodeURI(str));
}

I have two questions.

How can export this CSV file with correct Greek chars?
How can I export this data in Excel format and not in CSV format?

解决方案

Export to CSV

Exporting to CSV with non-ASCII characters requires prepending the file with the Byte Order Mark aka BOM. In your code change

var str = "";

to:

var str = "\uFEFF";

You need a modern version of Excel to recognize the BOM. As mentioned in this helpful StackOverflow article, Excel 2003 and earlier will not honor the BOM correctly. I only have access to Excel 2003 on Windows, so I cannot test this at the moment, but it's fairly well documented.

Sadly, Excel 2011 for the Macintosh is NOT a "modern Excel" in this sense. Happily, Google Sheets do the right thing.

Export directly to Excel

Here's a jsFiddle implementation of the code below. It generates a SpreadsheetXml document. The upside to this method is you could get VERY tricky ... adding in formulas and doing a lot more things specific to Excel.

// Test script to generate a file from JavaScript such
// that MS Excel will honor non-ASCII characters.

testJson = [
    {
        "name": "Tony Peña",
        "city": "New York",
        "country": "United States",
        "birthdate": "1978-03-15",
        "amount": 42

    },
    {
        "name": "Ζαλώνης Thessaloniki",
        "city": "Athens",
        "country": "Greece",
        "birthdate": "1987-11-23",
        "amount": 42
    }
];

// Simple type mapping; dates can be hard
// and I would prefer to simply use `datevalue`
// ... you could even add the formula in here.
testTypes = {
    "name": "String",
    "city": "String",
    "country": "String",
    "birthdate": "String",
    "amount": "Number"
};

emitXmlHeader = function () {
    var headerRow =  '<ss:Row>\n';
    for (var colName in testTypes) {
        headerRow += '  <ss:Cell>\n';
        headerRow += '    <ss:Data ss:Type="String">';
        headerRow += colName + '</ss:Data>\n';
        headerRow += '  </ss:Cell>\n';        
    }
    headerRow += '</ss:Row>\n';    
    return '<?xml version="1.0"?>\n' +
           '<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">\n' +
           '<ss:Worksheet ss:Name="Sheet1">\n' +
           '<ss:Table>\n\n' + headerRow;
};

emitXmlFooter = function() {
    return '\n</ss:Table>\n' +
           '</ss:Worksheet>\n' +
           '</ss:Workbook>\n';
};

jsonToSsXml = function (jsonObject) {
    var row;
    var col;
    var xml;
    var data = typeof jsonObject != "object" 
             ? JSON.parse(jsonObject) 
             : jsonObject;

    xml = emitXmlHeader();

    for (row = 0; row < data.length; row++) {
        xml += '<ss:Row>\n';

        for (col in data[row]) {
            xml += '  <ss:Cell>\n';
            xml += '    <ss:Data ss:Type="' + testTypes[col]  + '">';
            xml += data[row][col] + '</ss:Data>\n';
            xml += '  </ss:Cell>\n';
        }

        xml += '</ss:Row>\n';
    }

    xml += emitXmlFooter();
    return xml;  
};

console.log(jsonToSsXml(testJson));

This generates the XML document below. If this XML is saved in a file named test.xls, Excel should recognize this and open it with the proper encoding.

<?xml version="1.0"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Worksheet ss:Name="Sheet1">
<ss:Table>

<ss:Row>
  <ss:Cell>
    <ss:Data ss:Type="String">name</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">city</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">country</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">birthdate</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">amount</ss:Data>
  </ss:Cell>
</ss:Row>

<ss:Row>
  <ss:Cell>
    <ss:Data ss:Type="String">Tony Peña</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">New York</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">United States</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">1978-03-15</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="Number">42</ss:Data>
  </ss:Cell>
</ss:Row>
<ss:Row>
  <ss:Cell>
    <ss:Data ss:Type="String">Ζαλώνης Thessaloniki</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">Athens</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">Greece</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="String">1987-11-23</ss:Data>
  </ss:Cell>
  <ss:Cell>
    <ss:Data ss:Type="Number">42</ss:Data>
  </ss:Cell>
</ss:Row>

</ss:Table>
</ss:Worksheet>
</ss:Workbook>

I must admit, however, my strong inclination would be to do this server-side if possible. I've used the Python library openpyxl to do this in the past and it is fairly simple. Most server-side languages have a library that generates Excel files and they should provide much better constructs than string concatenation.

Anyway, see this MSDN blog for the basics. And this StackOverflow article for some pros/cons of various other options.

这篇关于使用JavaScript将JSON导出为使用UTF-8（例如希腊语）编码的CSV或Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

使用JavaScript将JSON导出为使用UTF-8（例如希腊语）编码的CSV或Excel [英] Export JSON to CSV or Excel with UTF-8 (e.g. Greek) encoding using JavaScript

问题描述

相关文章

前端开发最新文章

热门教程

热门工具

登录关闭

使用JavaScript将JSON导出为使用UTF-8（例如希腊语）编码的CSV或Excel [英] Export JSON to CSV or Excel with UTF-8 (e.g. Greek) encoding using JavaScript

问题描述

相关文章

前端开发最新文章

热门教程

热门工具

登录 关闭

登录关闭