Javascript JSON to Excel file download

Question

I have Json data and i need convert json data to Excel file using javascript,

Reference URL : http://jsfiddle.net/hybrid13i/JXrwM/

i am using this code:

function JSONToTSVConvertor(JSONData, ReportTitle, ShowLabel, myTemplateName){

    //If JSONData is not an object then JSON.parse will parse the JSON string in an Object
    var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
    var TSV = '';    
    //Set Report title in first row or line
    //TSV += ReportTitle + '\r\n\n';
    //This condition will generate the Label/Header
    if (ShowLabel) {
        var row = "";
        //This loop will extract the label from 1st index of on array
        for (var index in arrData[0]) {
            //Now convert each value to string and tab-seprated
            row += index + '    ';
        }
        row = row.slice(0, -1);
        //append Label row with line break
        TSV += row + '\r\n';
    }

    //1st loop is to extract each row
    for (var i = 0; i < arrData.length; i++) {
        var row = "";
        //2nd loop will extract each column and convert it in string tab-seprated
        for (var index in arrData[i]) {
            row += '"' + arrData[i][index] + '" ';
        }
        row.slice(0, row.length - 1);
        //add a line break after each row
        TSV += row + '\r\n';
    }

    if (TSV == '') {        
        alert("Invalid data");
        return;
    }   
    var blob = new Blob([TSV], {type: "data:text/tsv;charset=utf-8"});
    //Generate a file name

    var fileName = myTemplateName;
    //this will remove the blank-spaces from the title and replace it with an underscore
    fileName += ReportTitle.replace(/ /g,"_"); 
    saveAs(blob, ""+fileName+".tsv");
}

this sample code work to csv and tsv format. and i need to Excel format i don't think any idea please help me. pls suggest some example code. Thanks...

Answer 1

I have used the following code Javascript JSON to Excel or CSV file download

change file extension only (reports.xlsx or reports.CSV)

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.1/xlsx.full.min.js"></script> 
<script>
    function ExportData()
    {
            filename='reports.xlsx';
       data=[{Market: "IN", New Arrivals: "6", Upcoming Appointments: "2", Pending - 1st Attempt: "4"},
            {Market: "KS/MO", New Arrivals: "4", Upcoming Appointments: "4", Pending - 1st Attempt: "2"},
            {Market: "KS/MO", New Arrivals: "4", Upcoming Appointments: "4", Pending - 1st Attempt: "2"},
            {Market: "KS/MO", New Arrivals: "4", Upcoming Appointments: "4", Pending - 1st Attempt: "2"}]
        var ws = XLSX.utils.json_to_sheet(data);
        var wb = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(wb, ws, "People");
        XLSX.writeFile(wb,filename);
     }
</script>

Answer 2

CSV, as said, is excel file itself. But, in many locales, csv generated by the script above is opened incorrectly, where excel puts everything into 1 cell. Small modification of original script helps: just replace header with "sep=,".

var CSV = 'sep=,' + '\r\n\n';

Working example with change here: https://jsfiddle.net/1ecj1rtz/.

Spent some time figuring this out, and therefore answering old thread to help others save some time.

Answer 3

I've created a class to export json data to excel file. I'll be happy if some productive edit is made in my code.

Just add the class in your JS library and call:

var myTestXML = new myExcelXML(myJsonArray);
myTestXML.downLoad();

My myExcelXML Class:

let myExcelXML = (function() {
    let Workbook, WorkbookStart = '<?xml version="1.0"?><ss:Workbook  xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">';
    const WorkbookEnd = '</ss:Workbook>';
    let fs, SheetName = 'SHEET 1',
        styleID = 1, columnWidth = 80,
        fileName = "Employee_List", uri, link;

    class myExcelXML {
        constructor(o) {
            let respArray = JSON.parse(o);
            let finalDataArray = [];

            for (let i = 0; i < respArray.length; i++) {
                finalDataArray.push(flatten(respArray[i]));
            }

            let s = JSON.stringify(finalDataArray);
            fs = s.replace(/&/gi, '&amp;');
        }

        downLoad() {
            const Worksheet = myXMLWorkSheet(SheetName, fs);

            WorkbookStart += myXMLStyles(styleID);

            Workbook = WorkbookStart + Worksheet + WorkbookEnd;

            uri = 'data:text/xls;charset=utf-8,' + encodeURIComponent(Workbook);
            link = document.createElement("a");
            link.href = uri;
            link.style = "visibility:hidden";
            link.download = fileName + ".xls";

            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }

        get fileName() {
            return fileName;
        }

        set fileName(n) {
            fileName = n;
        }

        get SheetName() {
            return SheetName;
        }

        set SheetName(n) {
            SheetName = n;
        }

        get styleID() {
            return styleID;
        }

        set styleID(n) {
            styleID = n;
        }
    }

    const myXMLStyles = function(id) {
        let Styles = '<ss:Styles><ss:Style ss:ID="' + id + '"><ss:Font ss:Bold="1"/></ss:Style></ss:Styles>';

        return Styles;
    }

    const myXMLWorkSheet = function(name, o) {
        const Table = myXMLTable(o);
        let WorksheetStart = '<ss:Worksheet ss:Name="' + name + '">';
        const WorksheetEnd = '</ss:Worksheet>';

        return WorksheetStart + Table + WorksheetEnd;
    }

    const myXMLTable = function(o) {
        let TableStart = '<ss:Table>';
        const TableEnd = '</ss:Table>';

        const tableData = JSON.parse(o);

        if (tableData.length > 0) {
            const columnHeader = Object.keys(tableData[0]);
            let rowData;
            for (let i = 0; i < columnHeader.length; i++) {
                TableStart += myXMLColumn(columnWidth);

            }
            for (let j = 0; j < tableData.length; j++) {
                rowData += myXMLRow(tableData[j], columnHeader);
            }
            TableStart += myXMLHead(1, columnHeader);
            TableStart += rowData;
        }

        return TableStart + TableEnd;
    }

    const myXMLColumn = function(w) {
        return '<ss:Column ss:AutoFitWidth="0" ss:Width="' + w + '"/>';
    }


    const myXMLHead = function(id, h) {
        let HeadStart = '<ss:Row ss:StyleID="' + id + '">';
        const HeadEnd = '</ss:Row>';

        for (let i = 0; i < h.length; i++) {
            const Cell = myXMLCell(h[i].toUpperCase());
            HeadStart += Cell;
        }

        return HeadStart + HeadEnd;
    }

    const myXMLRow = function(r, h) {
        let RowStart = '<ss:Row>';
        const RowEnd = '</ss:Row>';
        for (let i = 0; i < h.length; i++) {
            const Cell = myXMLCell(r[h[i]]);
            RowStart += Cell;
        }

        return RowStart + RowEnd;
    }

    const myXMLCell = function(n) {
        let CellStart = '<ss:Cell>';
        const CellEnd = '</ss:Cell>';

        const Data = myXMLData(n);
        CellStart += Data;

        return CellStart + CellEnd;
    }

    const myXMLData = function(d) {
        let DataStart = '<ss:Data ss:Type="String">';
        const DataEnd = '</ss:Data>';

        return DataStart + d + DataEnd;
    }

    const flatten = function(obj) {
        var obj1 = JSON.parse(JSON.stringify(obj));
        const obj2 = JSON.parse(JSON.stringify(obj));
        if (typeof obj === 'object') {
            for (var k1 in obj2) {
                if (obj2.hasOwnProperty(k1)) {
                    if (typeof obj2[k1] === 'object' && obj2[k1] !== null) {
                        delete obj1[k1]
                        for (var k2 in obj2[k1]) {
                            if (obj2[k1].hasOwnProperty(k2)) {
                                obj1[k1 + '-' + k2] = obj2[k1][k2];
                            }
                        }
                    }
                }
            }
            var hasObject = false;
            for (var key in obj1) {
                if (obj1.hasOwnProperty(key)) {
                    if (typeof obj1[key] === 'object' && obj1[key] !== null) {
                        hasObject = true;
                    }
                }
            }
            if (hasObject) {
                return flatten(obj1);
            } else {
                return obj1;
            }
        } else {
            return obj1;
        }
    }

    return myExcelXML;
})();

Answer 4

I know its a little late to answer but I have found an nice angular library that does all the hard work it self.

GITHUB: ngJsonExportExcel

Library Direct Download : Download

Filesaver JS : Download

How to use?

1. Include the module in you app

var myapp = angular.module('myapp', ['ngJsonExportExcel'])

2. Add a export button and use the ng-json-export-excel directive and pass data into the directive

ng-json-export-excel : it is the directive name

data : it is the data that will be exported (JSON)

report-fields :

pass the column name and the keys that are present in your JSON e.g. customer_name": "Customer Name"

HTML

<button ng-json-export-excel data="dataList" report-fields="{'uesr.username': 'Heder 1', keyjson2: 'Header 2', keyjson3: 'Head 3'}" filename =" 'export-excel' " separator="," class="css-class"></button>

Answer 5

Excel is a very complex format with many versions. If you really need to do this I would investigate some of the JavaScript libraries that others have written. Do a Google search for "javascript excel writer" to see some examples.

Answer 6

This code snippet is using node.js with the excel4node and express modules in order to convert JSON data to an Excel file and send it to the client, using Javascript.

const xl = require('excel4node');
const express = require('express');
const app = express();

var json = [{"Vehicle":"BMW","Date":"30, Jul 2013 09:24 AM","Location":"Hauz Khas, Enclave, New Delhi, Delhi, India","Speed":42},{"Vehicle":"Honda CBR","Date":"30, Jul 2013 12:00 AM","Location":"Military Road,  West Bengal 734013,  India","Speed":0},{"Vehicle":"Supra","Date":"30, Jul 2013 07:53 AM","Location":"Sec-45, St. Angel's School, Gurgaon, Haryana, India","Speed":58},{"Vehicle":"Land Cruiser","Date":"30, Jul 2013 09:35 AM","Location":"DLF Phase I, Marble Market, Gurgaon, Haryana, India","Speed":83},{"Vehicle":"Suzuki Swift","Date":"30, Jul 2013 12:02 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Civic","Date":"30, Jul 2013 12:00 AM","Location":"Behind Central Bank RO, Ram Krishna Rd by-lane, Siliguri, West Bengal, India","Speed":0},{"Vehicle":"Honda Accord","Date":"30, Jul 2013 11:05 AM","Location":"DLF Phase IV, Super Mart 1, Gurgaon, Haryana, India","Speed":71}]

const createSheet = () => {

  return new Promise(resolve => {

// setup workbook and sheet
var wb = new xl.Workbook();

var ws = wb.addWorksheet('Sheet');

// Add a title row

ws.cell(1, 1)
  .string('Vehicle')

ws.cell(1, 2)
  .string('Date')

ws.cell(1, 3)
  .string('Location')

ws.cell(1, 4)
  .string('Speed')

// add data from json

for (let i = 0; i < json.length; i++) {

  let row = i + 2

  ws.cell(row, 1)
    .string(json[i].Vehicle)

  ws.cell(row, 2)
    .date(json[i].Date)

  ws.cell(row, 3)
    .string(json[i].Location)

  ws.cell(row, 4)
    .number(json[i].Speed)
}

resolve( wb )

  })
}

app.get('/excel', function (req, res) {

  createSheet().then( file => {
file.write('ExcelFile.xlsx', res);
  })

});

app.listen(3040, function () {
  console.log('Excel app listening on port 3040');
});