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:
Post your comments / questions
Recent Article
- ValueError:All arrays must be of the same length - Python
- Check hostname requires server hostname - SOLVED
- How to restrict access to the page Access only for logged user in Django
- Migration admin.0001_initial is applied before its dependency admin.0001_initial on database default
- Add or change a related_name argument to the definition for 'auth.User.groups' or 'DriverUser.groups'. -Django ERROR
- Addition of two numbers in django python
- The request was aborted: Could not create SSL/TLS secure channel -Error in Asp.net
- FieldError: Cannot resolve keyword 'id' into field in Django project
Related Article