In this article we will discuss How to export jQuery datatables to MS excel in asp.net MVC. The following example describes about datatables and exporting features. We can get the Tabletools flash swf file through cdn provides the ability to save pdf files. We will be using Population table.
Step 1: Create a table using the following script with data:
CREATE TABLE[dbo].[Population](
[PopulationId] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [nvarchar](150) NOT NULL,
[PhoneCode] [nvarchar](10) NULL,
[CountryCode] [nvarchar](50) NOT NULL,
[Population] [decimal](18, 0) NOT NULL,
[AREA] [nvarchar](50) NOT NULL,
[GDP] [nvarchar](50) NULL,
CONSTRAINT[PK_Population] PRIMARY KEY CLUSTERED
(
[PopulationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON[PRIMARY]
GO
SET IDENTITY_INSERT[chip].[Population] ON
INSERT [chip].[Population] ([PopulationId], [CountryName], [PhoneCode], [CountryCode],[Population], [AREA],[GDP]) VALUES (1, N'United States', N'1', N'US', CAST(310232863 AS Decimal(18, 0)), N'9,629,091', N'16.72 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (2, N'China', N'86', N'CN', CAST(1330044000 AS Decimal(18, 0)), N'9,596,960', N'9.33 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (3, N'Japan', N'81', N'JP', CAST(127288000 AS Decimal(18, 0)), N'377,835', N'5.007 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (4, N'Germany', N'49', N'DE', CAST(81802257 AS Decimal(18, 0)), N'357,021', N'3.593 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (5, N'Russia', N'7', N'RU', CAST(140702000 AS Decimal(18, 0)), N'17,100,000', N'2.113 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (6, N'France', N'33', N'FR', CAST(64768389 AS Decimal(18, 0)), N'547,030', N'2.739 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (7, N'Brazil', N'55', N'BR ', CAST(201103330 AS Decimal(18, 0)), N'8,511,965', N'2.19 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (8, N'Italy', N'39', N'IT', CAST(60340328 AS Decimal(18, 0)), N'301,230', N'2.068 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (9, N'United Kingdom', N'44', N'GB', CAST(62348447 AS Decimal(18, 0)), N'244,820', N'2.49 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (10, N'Canada', N'1', N'CA', CAST(33679000 AS Decimal(18, 0)), N'9,984,670', N'1.825 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (11, N'South Korea', N'82', N'KR', CAST(48422644 AS Decimal(18, 0)), N'98,480', N'1.198 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (12, N'Mexico', N'52', N'MX', CAST(112468855 AS Decimal(18, 0)), N'1,972,550', N'1.327 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (13, N'India', N'91', N'IN', CAST(1173108018 AS Decimal(18, 0)), N'3,287,590', N'1.67 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (14, N'Australia', N'61', N'AU', CAST(21515754 AS Decimal(18, 0)), N'7,686,850', N'1.488 Trillion')
INSERT [chip].[Population] ([PopulationId], [CountryName],[PhoneCode], [CountryCode], [Population],[AREA], [GDP]) VALUES (15, N'Spain', N'34', N'ES', CAST(46505963 AS Decimal(18, 0)), N'504,782', N'1.356 Trillion')
Step 2: Create an ado.net entity data model using table Population and generate entity for that.
Step 3: Right click on the "Controllers" folder and add " Population" controller. Copy and paste the following code. Please make sure to include "MVC_tutorials.Models" namespace.
using MVC_tutorials.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace MVC_tutorials.Controllers
{
public class DataTablesController : Controller
{
//
// GET: /DataTables/
models db = new models();
public ActionResult Index()
{
return View(db.Populations);
}
}
}
Step 4: Right click on the "Index" action method in the "datatablesController" and add "Index" view. Copy and paste the following code.
@model IEnumerable<MVC_tutorials.Models.Population>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>jQuery datatablesexport to MS excel</title>
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/tabletools/2.2.4/js/dataTables.tableTools.min.js"></script>
<link rel="stylesheet" type="text/css"
href="//cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<link rel="stylesheet" type="text/css"
href="//cdn.datatables.net/tabletools/2.2.4/css/dataTables.tableTools.css" />
<script type="text/javascript">
$(document).ready(function () {
var table = $('#datatable').dataTable();
var tableTools = new $.fn.dataTable.TableTools(table, {
'aButtons': [
{
'sExtends': 'xls',
'sButtonText': 'Save to Excel',
'sFileName': 'Data.xls'} ],
'sSwfPath': '//cdn.datatables.net/tabletools/2.2.4/swf/copy_csv_xls_pdf.swf'
});
$(tableTools.fnContainer()).insertBefore('#datatable_wrapper');
});
</script>
</head>
<body>
<div style="width: 800px; border: 1px solid black; padding: 3px">
<table id="datatable">
<thead>
<tr>
<th>CountryName
</th>
<th>PhoneCode
</th>
<th>CountryCode
</th>
<th>Population
</th>
</tr>
</thead>
<tfoot>
<tr>
<th>CountryName
</th>
<th>PhoneCode
</th>
<th>CountryCode
</th>
<th>Population
</th>
</tr>
</tfoot>
<tbody>
@foreach(var row in Model) {
<tr>
<td>@row.CountryName</td>
<td>@row.PhoneCode</td>
<td>@row.CountryCode</td>
<td>@row.Population1</td>
</tr>
}
</tbody>
</table>
</div>
</body>
</html>
Output: