Export Razor View to Excel in MVC

#1. ASP.NET MVC Tutorial - Advanced Developer Guide - C# 
How to Export Razor View to Excel file (Without using Third-Party Library) in ASP.NET MVC (C#)?                                      

Video Tutorials:  Click here 
Source Code:  Click to download [24.3 MB]

1) First Create a Model View Controller Project.

Step 1: Create a new empty MVC project In Visual Studio & solution name 
            "RazorViewToExcel".

Step 2: Adding Entity framework version 6.1.3 from Manage NuGet Packages.

Step 3: After add reference of Entity Framework, it looks like that.

Step 4: Here I am using entity framework code first approach for retrieve data from the database.

Step 5: 
Create Model - First, we need to add a tblSalesman.cs class file into the Model folder. After that Copy & Paste this below code into this tblSalesman.cs file.
          
          using System;
          using System.ComponentModel.DataAnnotations;
          using System.ComponentModel.DataAnnotations.Schema;

          namespace ExportDatabaseToExcel.Models
          {
              [Table("tblSalesman")]
              public class tblSalesman
               {
                  [Key]
                  public int id { get; set; }
                  public string Name { get; set; }
                  public decimal SaleAmount { get; set; }
                  public string Country { get; set; }
                  public DateTime Date { get; set; }
               }
         }

Step 6: 
Create Database Context - Add a SalesmanDBContext.cs class file into the model folder.
            After that Copy & Paste this code into this SalesmanDBContext.cs file.

           using System.Data.Entity;
            namespace ExportDatabaseToExcel.Models
            {
              public class SalesmanDBContext : DbContext
               {
                 public DbSet<tblSalesman> tblSalesman { get; set; }
               }
           }

Step 7: 
Create Repository - After that add a SalesmanRepository.cs class file into the model folder. After that Copy & Paste this code within this SalesmanRepository.cs file.

         using System.Collections.Generic;
         using System.Linq;

         namespace ExportDatabaseToExcel.Models
         {
          public class SalesmanRepository
           {
              SalesmanDBContext salmanDBContext = new SalesmanDBContext();
              public List<tblSalesman> GetSalesmans()
              {
                return salmanDBContext.tblSalesman.ToList();
              }
          }
       }

Step 8: 
Configure Web.config : We need to attach a connection string into the Web.config file & name of the connection is same as  SalesmanContext class file.
           
    <connectionStrings>
       <add name="SalesmanDBContext"
         connectionString="server=localhost; database=dbSample; user id=sa; password=your_password;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

Step 9: Jquery Script File : We need to attach a jquery library file into the scripts folder in solution (Download the jquery library from https://jquery.com/download ).

Step 10: Controller:  Add HomeController.cs file into the Controllers folder in solution.

After that Copy & Paste this below code within this HomeController.cs file.

using RazorViewToExcel.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Mvc;

namespace RazorViewToExcel.Controllers
{
   Action Method 1:       
   SalesmanRepository SalRepository = new SalesmanRepository();

     public ActionResult Index()
        {
            return View(SalRepository.GetSalesmans());
        }

  Non Action Method 2:       
     public void ExportToExcel()
        {
            string Filename = "ExcelFrom" + DateTime.Now.ToString("mm_dd_yyy_hh_ss_tt") + ".xls";
            string FolderPath = HttpContext.Server.MapPath("/ExcelFiles/");
            string FilePath = System.IO.Path.Combine(FolderPath, Filename);

            //Step-1: Checking: If file name exists in server then remove from server.
            if (System.IO.File.Exists(FilePath)) 
            {
                System.IO.File.Delete(FilePath);
            }

            //Step-2: Get Html Data & Converted to String
            string HtmlResult = RenderRazorViewToString("~/Views/Home/GenerateExcel.cshtml", SalRepository.GetSalesmans());

            //Step-4: Html Result store in Byte[] array
            byte[] ExcelBytes = Encoding.ASCII.GetBytes(HtmlResult);

            //Step-5: byte[] array converted to file Stream and save in Server
            using (Stream file = System.IO.File.OpenWrite(FilePath))
            {
                file.Write(ExcelBytes, 0, ExcelBytes.Length);
            }

            //Step-6: Download Excel file 
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(Filename));
            Response.WriteFile(FilePath);
            Response.End();
            Response.Flush();
        }

 Non Action Method 3:    
 protected string RenderRazorViewToString(string viewName, object model)
        {
            if (model != null)
            {
                ViewData.Model = model;
            }
            using (StringWriter sw = new StringWriter())
            {
ViewEngineResult viewResult = ViewEngines.Engines.FindPartialView(ControllerContext, viewName);
                ViewContext viewContext = new ViewContext(ControllerContext, viewResult.View, ViewData, TempData, sw);
                viewResult.View.Render(viewContext, sw);
                viewResult.ViewEngine.ReleaseView(ControllerContext, viewResult.View);

                return sw.GetStringBuilder().ToString();
            }
        }
}

Step 11:  After that add two views under the home folder into view folder solution.
                a) First View Name: Index.cshtml
                b) Second View Name: GenerateExcel.cshtml

We need to add a Jquery library CDN link into the Index View or download the jquery library.

Download Link:  https://code.jquery.com/jquery-3.2.1.min.js
CDN Link: <script src="https://code.jquery.com/jquery-3.2.1.min.js"></script> 

a) First Copy & Paste this below code within this Index.cshtml file.
b) After that Copy & Paste this same code (excluding scripts section) into the 
   GenerateExcel.cshtml file.

 @model IEnumerable<RazorViewToExcel.Models.tblSalesman>
 @{
       ViewBag.Title = "Everyday Be Coding"; ;
    }

 @*<script src="~/Scripts/jquery-3.2.1.js"></script>*@
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script> 

 <style type="text/css">
     table {
         border-collapse: collapse;
     }

     table, th, td {
         border: 1px solid black;
     }
 </style>
 <br>
 <br>
 <center>
 <h2>Export Razor View To Excel File</h2>
     <p>
         <input type="button" class="excel" value="Export Razor View to Excel File"  style="align-content:center" />
     </p>
 </center>
      <table class="table" border="1" align="center">
         <tr>
             <th>
                 @Html.DisplayNameFor(model => model.Name)
             </th>
             <th>
                 @Html.DisplayNameFor(model => model.SaleAmount)
             </th>
             <th>
                 @Html.DisplayNameFor(model => model.Country)
             </th>
             <th>
                 @Html.DisplayNameFor(model => model.Date)
             </th>
         </tr>

     @foreach (var item in Model) {
     <tr>
             <td>
                 @Html.DisplayFor(modelItem => item.Name)
             </td>
             <td>
                 @Html.DisplayFor(modelItem => item.SaleAmount)
             </td>
             <td>
                 @Html.DisplayFor(modelItem => item.Country)
             </td>
             <td>

                 @string.Format("{0: MM/dd/yyyy}", Convert.ToDateTime(Html.DisplayFor(modelItem => item.Date).ToString()))
            </td>
        </tr>
}
    </table>

     <script>
        $(document).on('click', '.excel', function () {
            window.location.href = '/Home/ExportToExcel/';
        });
    </script>

In this view, I have attached a html button for export excel file. A jquey functionis handle by this button. This jquery code communicates the specific server side method ExportToExcel() into the home controller. 

In this ExportToExcel() method : 

a) First, render the razor view HTML code to string object by using RenderRazorViewToString(string viewName, object model) method.

b) After that this string HTML data converted to byte[] array by using  Encoding.ASCII.GetBytes() method.

c) This byte[] array converted to file Stream and store in server file path by using write method of Stream class.

c) Stream class object "file" read this byte[] array and save into the server location.

d) After that download this excel file from server location by using response property. 

Code : 
public void ExportToExcel()
        {
            string Filename = "ExcelFrom" + DateTime.Now.ToString("mm_dd_yyy_hh_ss_tt") + ".xls";
            string FolderPath = HttpContext.Server.MapPath("/ExcelFiles/");
            string FilePath = System.IO.Path.Combine(FolderPath, Filename);

            //Step-1: Checking: If file name exists in server then remove from server.
            if (System.IO.File.Exists(FilePath)) 
            {
                System.IO.File.Delete(FilePath);
            }

            //Step-2: Get Html Data & Converted to String
            string HtmlResult = RenderRazorViewToString("~/Views/Home/GenerateExcel.cshtml", SalRepository.GetSalesmans());

            //Step-4: Html Result store in Byte[] array
            byte[] ExcelBytes = Encoding.ASCII.GetBytes(HtmlResult);

            //Step-5: byte[] array converted to file Stream and save in Server
            using (Stream file = System.IO.File.OpenWrite(FilePath))
            {
                file.Write(ExcelBytes, 0, ExcelBytes.Length);
            }

            //Step-6: Download Excel file 
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(Filename));
            Response.WriteFile(FilePath);
            Response.End();
            Response.Flush();
        }

RenderRazorViewToString(string viewName, object model) method : 

a) First check this model parameter is null or not. if it is not null then assign to  ViewData.Model property.

b) Create a StringWriter class object on using block.

c) ViewEngines class is represent a collection of view engines. This class property Engines and its has FindPartialView() method get the view engines result & store into the ViewEngineResult class object.

d) After that ViewContext class constructor encapsulate this ViewEngineResult object & render this view context value by Render() method & put into the StringWriter object.

e) StringWriter object bind this render data using GetStringBuilder() method & after that converted to string.

Code:
protected string RenderRazorViewToString(string viewName, object model)
        {
            if (model != null)
            {
                ViewData.Model = model;
            }
            using (StringWriter sw = new StringWriter())
            {
ViewEngineResult viewResult = ViewEngines.Engines.FindPartialView(ControllerContext, viewName);
                ViewContext viewContext = new ViewContext(ControllerContext, viewResult.View, ViewData, TempData, sw);
                viewResult.View.Render(viewContext, sw);
                viewResult.ViewEngine.ReleaseView(ControllerContext, viewResult.View);

                return sw.GetStringBuilder().ToString();
            }
        }

Step 12:  After adding all files with code into the solution. the solution looks like that.

Step 13:  Now execute this project. After execute database is created by the entity framework code first approach. But right now no data in the database. So we need to Insert some sample data to the tblSalesman from the Back end. Please execute this below script.

use dbSample
Go

Insert into tblSalesman values ('Brown', 600, 'UK', '6/1/2017')
Insert into tblSalesMan values ('Smaith', 450, 'USA', '4/15/2017')
Insert into tblSalesMan values ('Sunil', 700, 'IND', '5/28/2017')
Insert into tblSalesMan values ('Mark', 450, 'USA', '7/26/2017')
Insert into tblSalesMan values ('Matt', 300, 'USA', '8/15/2017')
Insert into tblSalesMan values ('Williamson', 350, 'USA', '3/10/2017')
Insert into tblSalesMan values ('John', 800, 'USA', '2/11/2017')
Insert into tblSalesMan values ('Jack', 350, 'UK', '3/10/2017')
Insert into tblSalesMan values ('Tom', 800, 'USA', '2/11/2017')

After executing this script. Now go to the Visual Studio and execute this project.

The output of Browser:

Step 14:  Click on this "Export Razor View To Excel File" button & download the excel file. 

The output of downloaded Excel File:

Thank you for reading this blogs. 
Please subscribe my YouTube Channel & don't forget to like and share. 

YouTube :	https://goo.gl/rt4tHH
Facebook :	https://goo.gl/hgpQsh
Twitter :	https://goo.gl/nUwGnf