0

I modified this code to convert JSON to .xls format. The code actually works, but while opening the file in MS Excel 2013, it throws a warning that the file format and extension do not match.

This is what I have so far:

var json3 = { "d": "[{\"Id\":1,\"UserName\":\"Sam Smith\"},{\"Id\":2,\"UserName\":\"Fred Frankly\"},{\"Id\":3,\"UserName\":\"Zachary Zupers\"}]" }

DownloadJSON2CSV(json3.d);

function DownloadJSON2CSV(objArray)
{
    var array = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;

    var str = '';

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

        for (var index in array[i]) {
            line += array[i][index] + '\t';
        }

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

        str += line + '\r\n';
    }
    window.open( "data:application/vnd.ms-excel;charset=utf-8," + escape(str));
}

What am I missing?

jsFiddle

Community
  • 1
  • 1
Rahul Desai
  • 15,242
  • 19
  • 83
  • 138
  • Looks like a CSV rather than XLS – Gio Jun 16 '14 at 10:30
  • @Gio How do I get it to .xls? – Rahul Desai Jun 16 '14 at 10:36
  • Not an easy feat - there's a PDF with all the details about the xls format here (bear in mind that Microsoft may have patents on some of this stuff): http://download.microsoft.com/download/2/4/8/24862317-78F0-4C4B-B355-C7B2C1D997DB/%5BMS-XLS%5D.pdf – Gio Jun 16 '14 at 10:46
  • Bottom line - I'd keep it as CSV (save with .csv extension) unless you have a very good reason for using XLS – Gio Jun 16 '14 at 10:47
  • XLS as legacy proprietary undocumented binary format was obsoleted by [XLSX](http://en.wikipedia.org/wiki/XLSX) open format. So give your users file format with wide and open support. BTW your code is not producing XLS neither XLSX it produces just tab-separated data file, usually called *.tab or *.txt – xmojmr Jun 17 '14 at 04:41

1 Answers1

0

The .xls format is a far more complex (and for that matter, proprietary) file format - the modification you made only modifies the mime type, not the actual content of the file. In other words, the file is still a CSV file inside, but you just tricked your browser into thinking it's an XLS file.

More info on mime types: http://en.wikipedia.org/wiki/Internet_media_type

For a solution to your problem, if you really, REALLY need XLS, the best idea is to find an online service that offers an API which converts CSV to XLS (googling "CSV to XLS online" might help).

fstanis
  • 5,234
  • 1
  • 23
  • 42