Skip to content Skip to sidebar Skip to footer

Javascript Json To Excel File Download

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

Solution 1:

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.

Solution 2:

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() {
    letWorkbook, 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">';
    constWorkbookEnd = '</ss:Workbook>';
    let fs, SheetName = 'SHEET 1',
        styleID = 1, columnWidth = 80,
        fileName = "Employee_List", uri, link;

    classmyExcelXML {
        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() {
            constWorksheet = 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);
        }

        getfileName() {
            return fileName;
        }

        setfileName(n) {
            fileName = n;
        }

        getSheetName() {
            returnSheetName;
        }

        setSheetName(n) {
            SheetName = n;
        }

        getstyleID() {
            return styleID;
        }

        setstyleID(n) {
            styleID = n;
        }
    }

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

        returnStyles;
    }

    const myXMLWorkSheet = function(name, o) {
        constTable = myXMLTable(o);
        letWorksheetStart = '<ss:Worksheet ss:Name="' + name + '">';
        constWorksheetEnd = '</ss:Worksheet>';

        returnWorksheetStart + Table + WorksheetEnd;
    }

    const myXMLTable = function(o) {
        letTableStart = '<ss:Table>';
        constTableEnd = '</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;
        }

        returnTableStart + TableEnd;
    }

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


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

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

        returnHeadStart + HeadEnd;
    }

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

        returnRowStart + RowEnd;
    }

    const myXMLCell = function(n) {
        letCellStart = '<ss:Cell>';
        constCellEnd = '</ss:Cell>';

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

        returnCellStart + CellEnd;
    }

    const myXMLData = function(d) {
        letDataStart = '<ss:Data ss:Type="String">';
        constDataEnd = '</ss:Data>';

        returnDataStart + 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) {
                returnflatten(obj1);
            } else {
                return obj1;
            }
        } else {
            return obj1;
        }
    }

    return myExcelXML;
})();

Solution 3:

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

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

<scripttype="text/javascript"src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.13.1/xlsx.full.min.js"></script><script>functionExportData()
    {
            filename='reports.xlsx';
       data=[{Market: "IN", NewArrivals: "6", UpcomingAppointments: "2", Pending - 1st Attempt: "4"},
            {Market: "KS/MO", NewArrivals: "4", UpcomingAppointments: "4", Pending - 1st Attempt: "2"},
            {Market: "KS/MO", NewArrivals: "4", UpcomingAppointments: "4", Pending - 1st Attempt: "2"},
            {Market: "KS/MO", NewArrivals: "4", UpcomingAppointments: "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>

Solution 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'])

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

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

Post a Comment for "Javascript Json To Excel File Download"