First Snap Shot: Hi everyone. This is my first article on MVC. In this small article we can learn how to download the Excel format file in MVC. I have used the Razor Engine in this article. We know that MVC has no server control, so how can we do this?

Note: In the MVC Razor engine we use HTML 5. The Razor engine has no server controls like a Button, Radio Button, Grid View and so on. If you want more details about HTML5 then try this link.
Road Map:

  • Design
  • SQL Data
  • jQuery snippet code
  • jQuery complete code
  • Model
  • Controller
  • Details of Controller
  • Business Logic Layer
  • Data Access layer
  • Create Stored Procedure
  • Get data using Stored Procedure
Design
  1. <input type="text" id="EmployeeName" title="Employee Name" />
  2. <br />
  3. <input type="text" id="Age" title="AGE" />
  4. <br />
  5. <input type="text" id="Address" title="Address" />
  6. <br />
  8. <a id="btnDownloadFile" href="#">Down Load File </a>
  9. <br/>
You can check the preceding code. This is a HTML control, it has no runat="server" and also has no server-side click. This HTML control works with the lovely and smart jQuery. This is a link type control and I have it set in the HREF # because I don't want it to redirect to another page.

Note: Suppose I want a file downloaded based on some condition. I know you might be confused, don't worry, I will explain more of what I want.
I have one Employees data as in the following.

SQL Data
  1. SrNo. Employee Age City
  2. 1 Jogi 23 Noida
  3. 2 Vimal 40 Delhi
  4. 3 Naveen 24 Gurgoan
I want only the data with the Employee Name "Jogi" or the age is greater then 23. So I need the three TextBoxes for Employee Name, Age and City. I pass some condition to get the data based on a condition.
jQuery snippet code here

If have no server control then you must use another resource so here I used jQuery. Without jQuery and Ajax I have no idea how to do this. I created a simple function to download the Excel format file.
I have created some code for downloading the Excel format file. How to working check out line by line.
  1. $('#btnExportToExcel').click(function () {
Make one function to download the Excel format file. $('#your button id').function like click, over, mouse out are many there check itself.
  1. var employeename = null;
  2. var age= null;
  3. var address=null;
You know I have downloaded the file based on some condition. So use the three parameters name, age and address.
  1. if($('#txtemployeename').val()!='')
  2. {
  3. employeename=$('#txtemployeename').val();
  5. }
Here we check whether the text box is empty. If the Text box is not empty then pass the value otherwise pass the null value. I checked every value one by one. I placed every code.
  1. $.ajax({
  3. type: "GET",
Note: Without Ajax we have no option available to get or post the data. Ajax is a true developer's friend because it solves many problems. Ajax is part of various things, if you want more about ajax then check on Google. Try this link for more information about ajax. Type: "Get" is an action of a page.
Any page has two states one is "GET" and the other is "Post". Here we used the GET type.
  1. url: '@Url.Action("Method Name", "Controller Name")',
URL stands for Uniform Resource Locator. An URL holds the address for where to get the data. You can check we have get the address of @url.action based on pass the parameters.
  1. async: false,
I think you are somewhat confused after seeing the preceding line. The line is nothing but it's is more often used for any type of query or function. I have used the async:false that means no async is used to download the file.
  1. data: {EmployeeName: employeename,Age:age,Address:address},
Data holds the data and is sent to the code behind. EmployeeName, Age and address are string a type parameter. If you don't use this type then you can try this.
  1. data: {
  2. EmployeeName: employeename,
  3. Age=age,
  4. Address:address
  5. }
It is a shortcut to bind the data.
  1. if (d.success) { // d.success is return type of Boolean. If d.success is true then download your file otherwise pop message "NO Records file"
  3. window.location = '/Project/DownloadExcelReport?fName=' + d.fileName;
  5. }
  6. else alert('No records found');
jQuery complete code here
  1. $('#btnExportToExcel').click(function () {
  3. var employeename = null;
  4. var age= null;
  5. var address=null;
  6. if($('#txtemployeename').val()!='')
  7. {
  8. employeename=txtemployeename;
  10. }
  11. if($('#txtage').val()!='')
  12. {
  13. age=txtage;
  15. }
  16. if($('#txtaddress').val()!='')
  17. {
  18. address=txtaddress;
  20. }
  21. $.ajax({
  23. type: "GET",
  25. url: '@Url.Action("DownLoadProjectProposal", "Project")',
  27. async: false,
  29. data: {EmployeeName: employeename,Age:age,Address:address},
  31. datatype: "JSONP",
  33. contentType: "application/json; charset=utf-8",
  35. success: function (d) {
  37. if (d.success) {
  39. window.location = '/Project/DownloadExcelReport?fName=' + d.fileName;
  41. }
  42. else alert('No records found');
  45. }
  47. });
  49. });
  51. });
Model

In the model we defined the properties in the Export class. In the class are a couple of properties, like EmployeeName, Age and Address.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  6. namespace Export
  7. {
  8. public class ExportTOExcel
  9. {
  10. public string EmployeeName { get; set; }
  11. public int Age { get; set; }
  12. public string Address { get; set; }
  13. }
  14. }
Controller
  1. public ActionResult ExportToExcel(string EmployeeName,int Age,string Address )
  2. {
  3. objExport ObjEx=new ObjExport();
  4. string fileName = string.Empty;
  5. bool success = false;

  7. try
  8. {
  9. if ((EmployeeName!='' || EmployeeName!=null) || Age>0 ||(Address !='' ||Address!=null)
  10. {
  11. StringBuilder str = new StringBuilder();
  12. var lstCompany = ObjEx.GetExportToExcel(EmployeeName,Age,Address);
  13. if (lstCompany.Count() > 0)
  14. {
  15. str.Append("<table border=`" + "1px" + "`b>");
  16. str.Append("<tr>");

  18. str.Append("<td><b><font face=Arial Narrow size=3>EmployeeName</font></b></td>");
  19. str.Append("<td><b><font face=Arial Narrow size=3>Age</font></b></td>");
  20. str.Append("<td><b><font face=Arial Narrow size=3>Address</font></b></td>");
  21. str.Append("</tr>");
  22. foreach (var val in lstCompany)
  23. {
  24. str.Append("<tr>");
  25. str.Append("<td><font face=Arial Narrow size=" + "14px" + ">" + val.EmloyeeName+ "</font></td>");
  26. str.Append("<td><font face=Arial Narrow size=" + "14px" + ">" + val.Age + "</font></td>");
  27. str.Append("<td><font face=Arial Narrow size=" + "14px" + ">" + val.Address+ "</font></td>");
  28. str.Append("</tr>");
  29. }
  30. str.Append("</table>");
  31. fileName = "Employee.xls";
  32. success = true;
  33. TempData["myExcel"] = str.ToString();
  34. TempData.Keep("myExcel");
  35. }
  36. }
  38. return Json(new { success = success, fileName }, JsonRequestBehavior.AllowGet);
  39. }
  40. catch
  41. {
  42. throw;
  43. }
Details of Controller
  1. objExport ObjEx=new ObjExport(); // crate reference for business logic function.
  1. if ((EmployeeName!='' || EmployeeName!=null) || Age>0 ||(Address !='' ||Address!=null) //condition
  2. // true when any parameters have value.
  1. var lstCompany = ObjEx.GetExportToExcel(EmployeeName,Age,Address);
  2. if (lstCompany.Count() > 0) //lcheck here stCompany have data or not. if lstCompany.count
  3. //is greater then means have data otherwise false this condition.
  1. //create a header for excel file.
  2. str.Append("<td><b><font face=Arial Narrow size=3>EmployeeName</font></b></td>");
  3. str.Append("<td><b><font face=Arial Narrow size=3>Age</font></b></td>");
  4. str.Append("<td><b><font face=Arial Narrow size=3>Address</font></b></td>");
Business Logic Layer
  1. Public List<ExportTOExcel > GetExportToExcel(string EmployeeName,int Age, string Address)
  2. {
  3. List<ExportTOExcel> LstExportFile=null;
  4. DLExport DL= new DLExport();
  5. LstExportFile=DL.GetExportToExcel(EmployeeName,Age,Address);
  6. return LstExportFile;

  8. }
Data Access Layer: In this demo file i used the EF(Entity Framework).
  1. Public List<ExportTOExcel > GetExportToExcel(string EmployeeName,int Age, string Address)
  2. {
  3. List<ExportTOExcel> LstExportFile=null;

  5. try
  6. {
  7. using (CGRS_OGEntities dbContext = new CGRS_OGEntities())
  8. {
  10. IEnumerable<ExportToExcel> IExportFile = (from T in dbContext.TableName
  11. where T.EmployeeName.Contains(EmployeeName) OR T.Age.contains(Age) or T.Address.contains(Address)
  12. select new ExportTOExcel
  13. {
  14. EmployeeName= T.EmployeeName,
  15. Age= T.Age,
  16. Address=T.Address

  18. LstExportFile= IExportFile.ToList<ExportTOExcel>();

  20. return LstExportFile;
  21. }
  22. finally
  23. { }
  24. }
You can do it using SQL Server. I made another option for getting the data from SQL Server. First, create the Stored Procedure to get the data. Create a simple Stored Procedure to get the data based on a condition.
Create Stored Procedure
  1. Create Store Procedure GetDataExport
  2. (
  3. @EmployeeName varchar(max),
  4. @Age int,
  5. @address varchar(max)
  7. )
  8. as
  9. begin
  11. SET NOCOUNT ON
  12. select Employee,Age,Address from TableName T --Note:-You can use the *(Star), but due to performance issue we avoid this.
  13. where T.Employee=@EmployeeName and T.Age=@Age and T.Address=@Address
  15. end
  1. SET NOCOUNT ON --Set Nocount On it's return null value. No overhead Sql count how many records effect.
  2. -- So try always if no count really need this.
Get data using Stored Procedure
  1. Public List<ExportTOExcel > GetExportToExcelUsingProcedure(string EmployeeName,int Age, string Address)
  2. {
  3. List<ExportTOExcel> LstExportFile=null;
  5. try
  6. {
  7. using (CGRS_OGEntities dbContext = new CGRS_OGEntities())
  8. {
  10. LstExportFile = dbContext.GetDataExport(EmployeeName,Age,Address).ToList<ExportTOExcel>();

  12. }
  13. return LstExportFile;
  14. }
  15. finally
  16. { }
  17. }
Final Words

I hope it's helpful for everyone to export data in Excel format. If you have an issue regarding this article or code please drop your comments in the comment box.