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();
}