Video Tutorial:
Download Full Source Code:
Download here
Introduction:
Here I will explain How to export html
table into excel using JQuery . Here in this example we use jquery.table2excel.js
library to convert html into excel format.
Description:
In previous articles I will explain How
to bind Country, State, City Dropdown list in mvc using ajax,
How
to show database values using Dataset in table with Edit and delete operation
using Asp.net MVC, How to bind dropdown in mvc using ajax, How to login with Facebook in
asp.net mvc, How
to upload multiple image or files in asp.net mvc with Source Code. ,Retrieve
data from database in asp.net web api,How
to login with gmail in Asp.net MVC with relevant example
So now we
start.
Step1: Firstly we add Jquery library to access all
functionality
Step2: in this we add jquery.table2excel.js library for converting html table into excel
//table2excel.js
;(function ( $, window,
document, undefined ) {
var pluginName = "table2excel",
defaults = {
exclude: ".noExl",
name: "Table2Excel"
};
// The
actual plugin constructor
function Plugin ( element, options ) {
this.element = element;
//
jQuery has an extend method which merges the contents of two or
// more
objects, storing the result in the first object. The first object
// is
generally empty as we don't want to alter the default options for
//
future instances of the plugin
//
this.settings = $.extend( {}, defaults, options );
this._defaults = defaults;
this._name = pluginName;
this.init();
}
Plugin.prototype = {
init: function () {
var e = this;
var utf8Heading = "<meta
http-equiv=\"content-type\" content=\"application/vnd.ms-excel;
charset=UTF-8\">";
e.template = {
head: "<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\">" + utf8Heading + "<head><!--[if
gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets>",
sheet: {
head: "<x:ExcelWorksheet><x:Name>",
tail: "</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>"
},
mid: "</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>",
table: {
head: "<table>",
tail: "</table>"
},
foot: "</body></html>"
};
e.tableRows = [];
// get
contents of table except for exclude
$(e.element).each( function(i,o) {
var tempRows = "";
$(o).find("tr").not(e.settings.exclude).each(function (i,o) {
tempRows += "<tr>" +
$(o).html() + "</tr>";
});
e.tableRows.push(tempRows);
});
e.tableToExcel(e.tableRows,
e.settings.name, e.settings.sheetName);
},
tableToExcel: function (table, name,
sheetName) {
var e = this, fullTemplate="", i, link, a;
e.uri = "data:application/vnd.ms-excel;base64,";
e.base64 = function (s) {
return window.btoa(unescape(encodeURIComponent(s)));
};
e.format = function (s, c) {
return s.replace(/{(\w+)}/g, function (m, p) {
return c[p];
});
};
sheetName = typeof sheetName === "undefined" ? "Sheet" :
sheetName;
e.ctx = {
worksheet: name || "Worksheet",
table: table,
sheetName: sheetName,
};
fullTemplate= e.template.head;
if ( $.isArray(table) ) {
for (i in table) {
//fullTemplate += e.template.sheet.head +
"{worksheet" + i + "}" + e.template.sheet.tail;
fullTemplate +=
e.template.sheet.head + sheetName + i + e.template.sheet.tail;
}
}
fullTemplate += e.template.mid;
if ( $.isArray(table) ) {
for (i in table) {
fullTemplate +=
e.template.table.head + "{table" + i + "}" + e.template.table.tail;
}
}
fullTemplate += e.template.foot;
for (i in table) {
e.ctx["table" + i] =
table[i];
}
delete e.ctx.table;
if (typeof msie !== "undefined" && msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./)) // If
Internet Explorer
{
if (typeof Blob !== "undefined") {
//use blobs if we can
fullTemplate =
[fullTemplate];
//convert to array
var blob1 = new Blob(fullTemplate, { type: "text/html" });
window.navigator.msSaveBlob(blob1, getFileName(e.settings) );
} else {
//otherwise use the iframe and save
//requires a blank iframe on page called txtArea1
txtArea1.document.open("text/html", "replace");
txtArea1.document.write(e.format(fullTemplate, e.ctx));
txtArea1.document.close();
txtArea1.focus();
sa = txtArea1.document.execCommand("SaveAs", true, getFileName(e.settings) );
}
} else {
link = e.uri +
e.base64(e.format(fullTemplate, e.ctx));
a = document.createElement("a");
a.download = getFileName(e.settings);
a.href = link;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
}
return true;
}
};
function getFileName(settings) {
return ( settings.filename ? settings.filename : "demoexcel" ) +
( settings.fileext ? settings.fileext : ".xls" );
}
$.fn[ pluginName ] = function ( options ) {
var e = this;
e.each(function() {
if ( !$.data( e, "plugin_" + pluginName ) ) {
$.data( e, "plugin_" +
pluginName, new Plugin( this, options ) );
}
});
// chain
jQuery functions
return e;
};
})(
jQuery, window, document );
Step3: Now add html page and write fallowing code
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<script src="jquery.min.js"></script>
<script src="jquery.table2excel.js"></script>
</head>
<body>
<table id="tbldemo" style="width:100%">
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
</tr>
<tr>
<td>Jill</td>
<td>Smith</td>
<td>50</td>
</tr>
<tr>
<td>Eve</td>
<td>Jackson</td>
<td>94</td>
</tr>
<tr>
<td>John</td>
<td>Doe</td>
<td>80</td>
</tr>
</table>
<button id="btnexport" >Export</button>
<script>
$(function () {
$("#btnexport").click(function () {
$("#tbldemo").table2excel();
});
});
</script>
</body>
</html>
Step4: We have done all steps , Now it’s time to run
the application
hi
ReplyDeletei have encountered an error - Uncaught TypeError: $(...).table2excel is not a function. Have followed your way of putting the files and js. Please help.
ReplyDelete