Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / C# 4.0

MVC Grid to Excel file download

Rate me:
Please Sign up or sign in to vote.
4.43/5 (15 votes)
8 Feb 2012CPOL2 min read 141.6K   8K   42   14
Convert the results of a ASP.NET MVC grid to a downloadable Excel file

Introduction

This article is a "how to" on converting a tabulated or grid representation of data from an ASP.NET MVC web application to a downloadable Excel data file.



Image 1

Image 2


Image 3

Background

I was recently building an ASP.NET MVC 3 based reporting application. One of the features was to provide a downloadable Excel file for the report that was being rendered as a grid/table. Rather than recreate an entire module to accomplish this, I wanted to find a way to reuse the data being used to render the view, with the least amount of code possible.

Using the code

I have tried to encapsulate much of the implementation in a custom ActionResult class (DownloadFileActionResult), so you can just pass data in the form of a GridView object and expect a downloadable Excel file with tabulated data.

You can download the complete code from the below link.

As stated above most of the implementation is contained in the DownloadFileActionResult class.

Firstly the DownloadFileActionResult inherits from the abstract ActionResult class, the constructor accepts two parameters the databound gridview object and the name of the downloadable Excel file.

The ExecuteResult is overridden with the custom implementation that creates a response, then uses a HtmlTextWriter and a StringWriter to pipe the rendering of a gridview to a string. This string is then streamed back to the response to be delivered as an Excel file. Its that simple

C#
public class DownloadFileActionResult : ActionResult
    {

        public GridView ExcelGridView { get; set; }
        public string fileName { get; set; }


        public DownloadFileActionResult(GridView gv, string pFileName)
        {
            ExcelGridView = gv;
            fileName = pFileName;
        }


        public override void ExecuteResult(ControllerContext context)
        {

           //Create a response stream to create and write the Excel file
            HttpContext curContext = HttpContext.Current;
            curContext.Response.Clear();
            curContext.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
            curContext.Response.Charset = "";
            curContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            curContext.Response.ContentType = "application/vnd.ms-excel";

            //Convert the rendering of the gridview to a string representation 
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            ExcelGridView.RenderControl(htw);
            
            //Open a memory stream that you can use to write back to the response
            byte[] byteArray = Encoding.ASCII.GetBytes(sw.ToString());
            MemoryStream s = new MemoryStream(byteArray);
            StreamReader sr = new StreamReader(s, Encoding.ASCII);

            //Write the stream back to the response
            curContext.Response.Write(sr.ReadToEnd());
            curContext.Response.End();

        }

    } 

Within your controller's Action result where you bind the view with model data you will need to create a gridview, bind the model data to it and store it in a session variable.

C#
public ActionResult Index()
       {
           ViewBag.Message = "Welcome to ASP.NET MVC!";
           CarModels cm = new CarModels();
           List<Cars> model = cm.getAllCars();

           GridView gv = new GridView();
           gv.DataSource = model;
           gv.DataBind();
           Session["Cars"] = gv;

           return View(model);
       }

In the same controller we need to create a new ActionResult called Download to handle the file download request.

C#
public ActionResult Download()
        {
            if (Session["Cars"] != null)
            {
                return new DownloadFileActionResult((GridView)Session["Cars"], "Cars.xls");
            }
            else 
            {
               //Some kind of a result that will indicate that the view has 
               //not been created yet. I would use a Javascript message to do so. 
            }
        }


In the view you place the following download link

C#
@Html.ActionLink("Download File", "Download")

Points of Interest

I hope this article goes to show how you can override the default ActionResult class with a custom implementation to generate a custom response.

I am somehow not too happy about storing the results of the grid in a Session variable and will endeavor to come up with a for a better solution.

Limitation

As this is html being rendered as an Excel sheet you will not be able to make use of most of the Excel functionality. In my case, the client wanted was a downloadable version of the data so it was fine. But in case you want to create a true Excel file you could look at using XSLT to convert your html output to an Excel file.

Reference

http://www.codeshelve.com/code/details/54/export-to-excel-in-asp-net-mvc

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Infosolvex Solutions Inc
Australia Australia
Ritesh is an IT consultant with over ten years of experience in the IT industry varying from consultation, architecture, design, development to technical management. He has a strong background in solutions and applications architecture with a focus on Microsoft’s .Net platform. His area of expertise spans design and implementation of client/server, database and web-based systems. He has worked with C#, ASP.NET 1.1 and 2.0, ADO.NET, Web Services and SQL technology on several enterprise class projects.




Freedom is not worth having if it does not include the freedom to make mistakes.
Mahatma Gandhi

Comments and Discussions

 
QuestionGridView issues Pin
Member 1210021420-May-19 5:56
Member 1210021420-May-19 5:56 
QuestionDo you have a better way to do it without storing in session variables? Pin
phatlee25-May-18 7:19
phatlee25-May-18 7:19 
Questionhow to export filtered grid Pin
Member 133125107-Aug-17 2:34
Member 133125107-Aug-17 2:34 
Hello I'm trying to export filtered grid
but when I do it it exports the entire data and not the filtered one. what I'm I doing wrong.

This my code

controller code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using GridMvcExample.Models;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel.Composition.Primitives;

namespace GridMvcExample.Controllers
{
public class HomeController : Controller
{
private object db;

public ActionResult Index()
{
return View();
}


public ActionResult About()
{
ViewBag.Message = "Your application description page.";

return View();
}

public ActionResult Contact()
{
ViewBag.Message = "Your contact page.";

return View();
}
public ActionResult TransEnquiry()
{
EzeeApiEntities1 db = new EzeeApiEntities1();

var result = (from c in db.Vw_Transactions_Detail
select new TransactionDetailsModel
{

TransactionId = c.TransactionId,
InvoiceNo = c.InvoiceNo,
TransactionDate = c.TransactionDate,
TransactionTime = c.TransactionTime,
Category = c.Category,
ChannelUsed = c.ChannelUsed,
TransactionType = c.TransactionType,
Amount = c.Amount,
Customer = c.Customer,
TransactionStatus = c.TransactionStatus,
Source = c.Source
}
).ToList();

return View(result);


}

public void ExportToCSV()
{

StringWriter sw = new StringWriter();

sw.WriteLine("\"Transaction Id \",\"Invoice No\",\"Transaction Date\",\"Transaction Time\",\"Category\",\"Channel Used\",\"Transaction Type\",\"Amount\",\"Customer\",\"TransactionStatus\",\"Source\"");



Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=Exported_Users.csv");
Response.ContentType = "text/csv";

EzeeApiEntities1 db = new EzeeApiEntities1();

var result = (from c in db.Vw_Transactions_Detail
select new TransactionDetailsModel
{

TransactionId = c.TransactionId,
InvoiceNo = c.InvoiceNo,
TransactionDate = c.TransactionDate,
TransactionTime = c.TransactionTime,
Category = c.Category,
ChannelUsed = c.ChannelUsed,
TransactionType = c.TransactionType,
Amount = c.Amount,
Customer = c.Customer,
TransactionStatus = c.TransactionStatus,
Source = c.Source
}
).ToList();

foreach (var line in result)
{
sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\",\"{9}\",\"{10}\"",
line.TransactionId,
line.InvoiceNo,
line.TransactionDate,
line.TransactionTime,
line.Category,
line.ChannelUsed,
line.TransactionType,
line.Amount,
line.Customer,
line.TransactionStatus,
line.Source
));
}

Response.Write(sw.ToString());

Response.End();

}


public void ExportToExcel()
{
var grid = new System.Web.UI.WebControls.GridView();

EzeeApiEntities1 db = new EzeeApiEntities1();

var result = (from c in db.Vw_Transactions_Detail
select new TransactionDetailsModel
{

TransactionId = c.TransactionId,
InvoiceNo = c.InvoiceNo,
TransactionDate = c.TransactionDate,
TransactionTime = c.TransactionTime,
Category = c.Category,
ChannelUsed = c.ChannelUsed,
TransactionType = c.TransactionType,
Amount = c.Amount,
Customer = c.Customer,
TransactionStatus = c.TransactionStatus,
Source = c.Source
}
).ToList();


grid.DataSource = result; /*from d in dbContext.diners
where d.user_diners.All(m => m.user_id == userID) && d.active == true
from d in ClientsList
select new
{
FirstName = d.FirstName,
LastName = d.LastName,
DOB = d.Dob,
Email = d.Email

};*/

grid.DataBind();

Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=Exported_Diners.xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

grid.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();


}



}
}



cshtml code
@model List<gridmvcexample.models.transactiondetailsmodel>

@using GridMvc.Html




@{
ViewBag.Title = "Transactions Enquiry";
}

Transactions Enquiry










@Html.ActionLink("Download Excel File ", "ExportToExcel") @Html.ActionLink("Download CSV File", "ExportToCSV")



@Html.Grid(Model).Columns(Columns =>
{
Columns.Add(c => c.TransactionId).Titled("Wallet Transaction Id").Filterable(true);
Columns.Add(c => c.InvoiceNo).Titled("Invoice No").Filterable(true);
Columns.Add(c => c.TransactionDate).Format("{0:dd/MMM/yyyy}").Titled("Transaction Date").Filterable(true);
Columns.Add(c => c.TransactionTime).Titled("Transaction Time").Filterable(false);
Columns.Add(c => c.Category).Titled("Category").Filterable(true);
Columns.Add(c => c.ChannelUsed).Titled("Channel Used").Filterable(true);
Columns.Add(c => c.TransactionType).Titled("Transaction Type").Filterable(true);
Columns.Add(c => c.Amount).Titled("Amount").Filterable(true);
Columns.Add(c => c.Customer).Titled("Customer").Filterable(true);
Columns.Add(c => c.TransactionStatus).Titled("Transaction Status").Filterable(true);
Columns.Add(c => c.Source).Titled("Source").Filterable(false);
}).WithMultipleFilters().WithPaging(100).Sortable(true)




QuestionExport Filtered Data Pin
Member 1212068916-Nov-15 10:58
Member 1212068916-Nov-15 10:58 
GeneralThanks! Pin
steven717-Nov-14 23:41
steven717-Nov-14 23:41 
QuestionHow do I generate multiple Sheets Pin
sumitp1715-May-14 20:40
sumitp1715-May-14 20:40 
QuestionIt solved Pin
Nanda Rengasamy24-Sep-13 2:25
Nanda Rengasamy24-Sep-13 2:25 
GeneralMy vote of 1 Pin
Jaydeep Jadav8-Mar-13 22:38
Jaydeep Jadav8-Mar-13 22:38 
Questioncode is working in google chrome browser but not in ie Pin
magy11-Feb-13 19:43
magy11-Feb-13 19:43 
QuestionLog Kuchh bhi dalate hai.. Pin
Member 862979031-Oct-12 6:10
Member 862979031-Oct-12 6:10 
GeneralMy vote of 4 Pin
andy(-RKO)1-May-12 4:13
andy(-RKO)1-May-12 4:13 
GeneralMy vote of 3 Pin
xExTxCx7-Feb-12 7:37
xExTxCx7-Feb-12 7:37 
QuestionNot real XLS file Pin
Robert Hutch5-Feb-12 22:03
Robert Hutch5-Feb-12 22:03 
AnswerRe: Not real XLS file Pin
Ritesh Ramesh8-Feb-12 7:35
Ritesh Ramesh8-Feb-12 7:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.