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. This may be useful. Good Luck. — Nayana_Das
– Nayana_Das, Commented Nov 24, 2014 at 7: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
– Renuka Thakur, Commented Nov 24, 2014 at 7:55
Hope this module helps you please check out icg-json-to-xlsx module, from here, u will get the git repository link, where u can download that module and work out. — Nayana_Das
– Nayana_Das, Commented Nov 24, 2014 at 8: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
– Praneeth, Commented Jul 6, 2015 at 19:59

Sujit Kumar Singh · Accepted Answer · 2017-12-27 11:33:22Z

2

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).

edited Dec 27, 2017 at 11:33

answered Jun 22, 2017 at 4:33

Sujit Kumar Singh

1,08810 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JSON to excel file in javascript

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related