How to export table data to Microsoft excel in asp.net mvc application ~ MiTechDev.com

Tuesday, 4 October 2016

How to export table data to Microsoft excel in asp.net mvc application

21:35:00 Posted by raviteja swayampu , 2 comments
Hi every one welcome again to our Microsoft Technology Developments programming tutorials.In previous tutorial we learned about How to read the Microsoft Excel data file using ExcelDataReader library and How to display that data in asp.net mvc web application.read here
How to Read and Display Excel file in asp.net MVC application
Now in this tutorial we gonna implement How to export that Database(SQL) table data to Excel file using asp.net mvc web application.
for this we need to import some namespaces into our application..
using System.Web.UI.WebControls;
using System.IO;
using System.Web.UI;

Steps to implement Exporting Data

1.Create a table in Local Sql express database.
2.Right click on App_Start folder in solution --> Add --> New Item --> Select Sql Database under Data section.
3.Double click on Database which you added it will be opened in Server explorer --> right click on Tables --> Add New Table.
4.I created table and inserted some data into table using below server code.
CREATE TABLE [dbo].[Exports] (
    [UserID]   INT            IDENTITY (1, 1) NOT NULL,
    [UserName] NVARCHAR (MAX) NULL,
    [Contact]  INT            NOT NULL,
    [Address]  NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.Exports] PRIMARY KEY CLUSTERED ([UserID] ASC)
);

---Insert data into table

SET IDENTITY_INSERT [dbo].[Exports] ON
INSERT INTO [dbo].[Exports] ([UserID], [UserName], [Contact], [Address]) VALUES (1, N'Raviteja', 908988377, N'Hyderabad')
INSERT INTO [dbo].[Exports] ([UserID], [UserName], [Contact], [Address]) VALUES (2, N'MarkTwain', 93384387, N'Chennai')
INSERT INTO [dbo].[Exports] ([UserID], [UserName], [Contact], [Address]) VALUES (3, N'Thomas', 98374374, N'Kochin')
INSERT INTO [dbo].[Exports] ([UserID], [UserName], [Contact], [Address]) VALUES (4, N'Rajkumar', 90988743, N'Canada')
INSERT INTO [dbo].[Exports] ([UserID], [UserName], [Contact], [Address]) VALUES (5, N'Gopal', 89788777, N'Kolkata')
SET IDENTITY_INSERT [dbo].[Exports] OFF

Add ADO.NET Entity DataModel

1.It creates all Models classes,Context classes and Connection string of application.
2.Right click on Models folder--> Add --> New Item -->Select ADO.NET Entity Data Model(Under Data) --> name it -->Add --> select Add from Database (In Entity Data Model wizard) --> Next
3.Select Database --> give name for web.config.
4.Choose your Database objects(tables) and click finish.

Create Controller to display View

1.Right click on Controllers folder --> Add --> controller --> Name it and add.
2.Right click on Index Action method -->  add view --> name it --> Add.
3.Replace view code with below code.
@{
    ViewBag.Title = "www.mitechdev.com";
}

<h3>Exporting Database table data to Excel</h3>
<p class="text-info">Click on below button to export table to Microsoft Word</p>
@Html.ActionLink("ExportToExcel", "ExportToExcel");
4.When we click on this action link it calls a action method "ExportToExcel".This method gets all the records in table and returns to Excel file using GridView class.
5.See all Controller code here.
using System.Linq;
using System.Web.Mvc;
using ExportDataToExcel.Models;
using System.Web.UI.WebControls;
using System.IO;
using System.Web.UI;

namespace ExportDataToExcel.Controllers
{
    public class ExportController : Controller
    {
        // GET: Export
        public ActionResult Index()
        {
            return View();
        }
        public ActionResult ExportToExcel()
        {
            using (DbEntities db=new DbEntities())
            {
                //get data from db
                var data = db.Exports.ToList();
                GridView grid = new GridView();
                //assign data to gridview
                grid.DataSource = data;
                //bind data
                grid.DataBind();
                Response.ClearContent();
                Response.Buffer = true;
                //Adding name to excel file
                Response.AddHeader("content-disposition", "attachment;filename=sampleExcel.xls");
                //specify content type of file
                //Here i specified "ms-excel" format
                //you can also specify it "ms-word" to get word document
                Response.ContentType = "application/ms-excel";
                Response.Charset = "";
                using (StringWriter sw=new StringWriter())
                {
                    using (HtmlTextWriter htwriter=new HtmlTextWriter(sw))
                    {
                        grid.RenderControl(htwriter);
                        Response.Output.Write(sw.ToString());
                        Response.Flush();
                        Response.Close();
                    }
                }
            }
                return RedirectToAction("Index");
        }
    }
}



6.Now run the application and see the output in browser window.
7.After clicking on link "ExportToExcel" we will get this.


8.Download the source code for this application from here(source vs15).This code is for reference only.Try to implement this application on your own.

Conclusion

I hope this tutorial is understandable and useful for every reader.Please comment if you suggest any modification to improve the quality of article.If you are not subscribed to this blog subscribe.follow on Social networks for daily awesome updates[Facebook,twitter,google plus].

2 comments:

  1. Alternatively, you can use my free C#/VB.Net library, and export your data to a *real* Excel .xlsx file, with one line of code.
    http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

    ReplyDelete
  2. Hi Ravi,

    Thanks for the article. Somehow it is not working for me. It tells me that "The file you are trying to open, excelSample.xls, is in a different format than specified by the file extension".

    Do you know what I need to do to make it work?

    Thanks

    ReplyDelete