编码的CSV或Excel

编码的CSV或Excel

关注
发信
关注(28)粉丝(399)

使用JavaScript将JSON导出为使用UTF-8（例如希腊语）编码的CSV或Excel

本文介绍了使用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））; 
}

我有两个问题。


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

  2. 格式 p> 导出到CSV



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



    var str =;



    到：



    var str =\\\;



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



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



    直接导出到Excel



    以下是 实施的代码。它会生成文档。这个方法的好处是你可以得到非常棘手的...添加公式，做更多的事情特定于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文件的库，它们应该提供比字符串连接更好的结构。



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


    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.

    1. How can export this CSV file with correct Greek chars?
    2. 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

08-01 05:01
Powered by LMLPHP ©2024 编码的CSV或Excel 0.037706