ritacse
8/12/2018 - 5:35 AM

Export to Excel

//call from js
$('#btnExcelExport').click(function () {
    tableToExcel('TableID', reportTitle);
    // if there are multiple table then push all the table in a div & push the div in a table. then export from that table. example:
    
    // html code: <table id="Export-body" style="display:none" ></table>
    //js code:
      //var ExportBody = $('#report-body').html();
       //$('#Export-body').append(ExportBody);
});

//Must write in HTML Header section
 <script type="text/javascript">
         var tableToExcel = (function () {
             var uri = 'data:application/vnd.ms-excel;base64,'
               , template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>'
               , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
               , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
             return function (table, rptName) {
                 if (!table.nodeType) table = document.getElementById(table)
                 var ctx = { worksheet: rptName || 'Worksheet', table: table.innerHTML }


                 var file = document.createElement('a');
                 file.href = uri + base64(format(template, ctx));
                 file.download = rptName + '.xls';

                 document.body.appendChild(file);
                 file.click();
                 document.body.removeChild(file);
             }
         })()
    </script>
var vData = [{ "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 }]

$(document).ready(function () {
    $('#btnExport').click(function () {
        var data = vData;
        if (data == '')
            return;

        JSONToCSVConvertor(data, "Employee Report", true);
    });
});




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 = '';
    //Set Report title in first row or line

    CSV += ReportTitle + '\r\n\n';  ///--- Not required now

    //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 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.length; i++) {
        var row = "";

        //2nd loop will extract each column and convert it in string comma-seprated
        for (var index in arrData[i]) {
            row += '"' + arrData[i][index] + '",';
        }

        row.slice(0, row.length - 1);

        //add a line break after each row
        CSV += row + '\r\n';
    }

    if (CSV == '') {
        alert("Invalid data");
        return;
    }

    //Generate a file name
    var fileName = "Transsion_";
    //this will remove the blank-spaces from the title and replace it with an underscore
    fileName += ReportTitle.replace(/ /g, "_");

    //Initialize file format you want csv or xls
    var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);

    // Now the little tricky part.
    // you can use either>> window.open(uri);
    // but this will not work in some browsers
    // or you will not get the correct file extension    

    //this trick will generate a temp <a /> tag
    var link = document.createElement("a");
    link.href = uri;

    //set the visibility hidden so it will not effect on your web-layout
    link.style = "visibility:hidden";
    link.download = fileName + ".csv";

    //this part will append the anchor tag and remove it after automatic click
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
}
/// This works only for small array
 /// Code in HTML
 <button title="Export to Excel" onclick="excelReport(event);" id="btnExport" class="icomoon-icon-file-excel">Export</button></div>

//in Js file: Export table Data To excel
function excelReport() {
    var a = document.createElement('a');
    var data_type = 'data:application/vnd.ms-excel';
    var table_div = document.getElementById('contentHtml');
    var table_html = table_div.innerHTML;

    var agent = window.navigator.userAgent;
    var MSIE = agent.indexOf("MSIE");

    if (MSIE > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) {
        txtArea1.document.open("txt/html", "replace");
        txtArea1.document.write(tab_text);
        txtArea1.document.close();
        txtArea1.focus();
        return txtArea1.document.execCommand("SaveAs", true, "Daily_OverTime_Report_On " + Year + ' - ' + Month + ".xlsx");
    } 

    a.href = data_type + ', ' + encodeURIComponent(table_html);
    a.download = 'Daily_Overtime_Report_On_' + Month + ' - ' + Year + '.xlsx';
    a.click();
    e.preventDefault();
}