JSON to excel file in javascript

Question

I am using the following code to create excel file data from JSON object and then download it on the click of a button.

getExcelFile: function() {
            testJson = validation_data;
            testTypes = {
                "name": "String",
                "city": "String",
                "country": "String",
                "birthdate": "String",
                "amount": "Number"
            };

            emitXmlHeader = function() {
                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';
            };

            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;
            };
            download = function(content, filename, contentType) {
                if (!contentType)
                    contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
                var a = document.getElementById('test');
                var blob = new Blob([content], {
                    'type': contentType
                });
                a.href = window.URL.createObjectURL(blob);
                a.download = filename;
            };

            download(jsonToSsXml(testJson), 'validation_data.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        }

But the file created doesn't open in Microsoft Office 2007 and gives the error 'File may be corrupt'. Please help.

Please refer this previous stack overflow [Question](http://stackoverflow.com/questions/4130849/convert-json-format-to-csv-format-for-ms-excel). This may be useful. Good Luck. — Nayana_Das, Nov 24 '14 at 07:20
Thank you @Nayana_Das for your help. But the question you mentioned has the code which converts JSON into CSV format. I want my JSON object to be converted to XLSX format. — Renuka Thakur, Nov 24 '14 at 07:55
Hope this module helps you please check out [icg-json-to-xlsx module](https://www.npmjs.org/package/icg-json-to-xlsx), from here, u will get the git repository link, where u can download that module and work out. — Nayana_Das, Nov 24 '14 at 08:41
@Nayana_Das: does it help to parse the json data from an ipaddress for instance: 127.0.0.1:8000/courses/?format=json and generate a xlsx sheet as output — Praneeth, Jul 06 '15 at 19:59

Sujit Kumar Singh · Answer 1 · 2017-12-27T11:33:22.913

I recently got a solution for this question using AlaSQL.

Their working example.

var sheet_1_data = [{Col_One:1, Col_Two:11}, {Col_One:2, Col_Two:22}];
var sheet_2_data = [{Col_One:10, Col_Two:110}, {Col_One:20, Col_Two:220}];
var opts = [{sheetid:'Sheet One',header:true},{sheetid:'Sheet Two',header:false}];
var res = alasql('SELECT * INTO XLSX("sample_file.xlsx",?) FROM ?', [opts,[sheet_1_data ,sheet_2_data]]);

Libraries required:

<script src="http://alasql.org/console/alasql.min.js"></script> 
<script src="http://alasql.org/console/xlsx.core.min.js"></script>

NOTE: Don't pass undefined values to the function. Generated file will produce warning messages if you try to open them in this case.

Other options were able to convert JSON to CSV (not XLSX).

JSON to excel file in javascript

1 Answers1

Linked