Monday, October 23, 2017

Export JQGrid Data to CSV - JavaScript


//Create Grid Table
$("#JQGridName").jqGrid({
    datatype: "local",
    colNames: ['colname'], // Pass Col Name
    colModel: [{ name: 'colval'}], // Pass Col Values
    search: true,
    pager: '#JQGridPager', // Pass Grid Pager Name
    data: ExhbReportArrayData, // Pass Value for Grid
    rowNum: 20,
    rowList: [10, 20],
    viewrecords: true,
    shrinkToFit: false,
    height: 'auto',
    fixed: false,
    multiselect: true,
    forceFit: true
});

//Add Button to Grid Pager
$("#JQGridName").jqGrid('navGrid', '#JQGridPager', { view: true, del: false, add: false, edit: false, excel: true })
.navButtonAdd('#JQGridPager', {
    caption: "Export to Excel",
    buttonicon: "ui-icon-save",
    onClickButton: function (e) {
        exportData(e, '#JQGridName');
    },
    position: "last"
});

// Export Function - exportData

function exportData(e, id) {
    var gridid = jQuery(id).getDataIDs(); // Get all the ids in array
    var label = jQuery(id).getRowData(gridid[0]); // Get First row to get the labels
    var mydata = $(id).jqGrid('getGridParam', 'data');
    var obj = new Object();
    obj.count = mydata.length;
    obj.items = new Array();
    for (var i = 0; i < mydata.length; i++) {
        obj.items.push(mydata[i]);
    }
    var json = JSON.stringify(obj);
    JSONToCSVConvertor(json, "csv", 1);
}

// JSON to CSV Converter Function

function JSONToCSVConvertor(JSONData, ReportTitle, ShowLabel) {
    //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 CSV = '';
    //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.items[0]) {
            //Now convert each value to string and comma-seprated
            row += index + ',';
        }
        row = row.slice(0, -1);
        //append Label row with line break
        CSV += row + '\r\n';
    }
    //1st loop is to extract each row
    for (var i = 0; i < arrData.items.length; i++) {
        var row = "";
        //2nd loop will extract each column and convert it in string comma-seprated
        for (var index in arrData.items[i]) {
            row += '"' + arrData.items[i][index].replace(/(<([^>]+)>)/ig, '') + '",';
        }
        row.slice(0, row.length - 1);
        //add a line break after each row
        CSV += row + '\r\n';
    }
    if (CSV == '') {
        alert("Invalid data");
        return;
    }
    //this trick will generate a temp "a" tag
    var link = document.createElement("a");
    link.id = "lnkDwnldLnk";
    //this part will append the anchor tag and remove it after automatic click
    document.body.appendChild(link);
    var csv = CSV;
    blob = new Blob([csv], { type: 'text/csv' });
    var isIE = false || !!document.documentMode;
    var isEdge = !isIE && !!window.StyleMedia;
    if (isIE) {
        window.navigator.msSaveOrOpenBlob(blob, 'DataName.csv'); // Downloaded File Name Value
    }
    else {
        var myURL = window.URL || window.webkitURL;
        var csvUrl = myURL.createObjectURL(blob);
        var filename = 'DataName.csv'; // Downloaded File Name Value
        jQuery("#lnkDwnldLnk")
                        .attr({
                            'download': filename,
                            'href': csvUrl
                        });
        jQuery('#lnkDwnldLnk')[0].click();
        document.body.removeChild(link);
    }
}

1 comment:

  1. I stumbled upon your blog and wanted to say how much I appreciate the content you're sharing here. Your writing style is engaging, and I find your posts insightful and enjoyable to read.

    erp software companies in middle east

    ReplyDelete

How to Clear Cache in Canvas PowerApps while working on Offline mode?

  Introduction In this blog, we’ll look at how to clear cache in Canvas Apps when using the Power Apps mobile application, especially when t...