EXPORT TO XLS, CSV and EXCEL USING MVC 5

EXPORT TO XLS, CSV and EXCEL USING MVC 5

In this article I will show you how to export student detail from MVC 5 application using Entity-framework. Let start with creating simple MVC 5 project.

1. Open Visual Studio 2015 => New Project => Select ASP.Net web Application => Name it Export => Click ok


2. Select MVC click OK.


3. Now add 2 class files name it as ExportDB and StudentDetail respectively. And add below mentioned code to StudentDetail class.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public class Export_XlsCsvExcel: DbContext
{
public DbSet<StudentDetail> Studentrecord{ get; set; }
}
public class StudentDetail
{
[Key]
public int id { get; set; }
[Required]
public String Name { get; set; }
public String Address { get; set; }
public String Marks { get; set; }
}

4. Now add right click on controller => Add => Controller.
    Select MVC 5 Controller with views, using entity Framework.

5. Put model class as "StudentDetail" and data context class as “Export_XlsCsvExcel” then click on Add button. It will create one controller file and 5 cshtml files in views for index/add/edit/delete.


6. Run application by F5 and in your URL type studentdetails if cause error then type studentdetails/create otherwise click on create new. Add data from here.


7. After adding records from application. Add the following code in studentdetailscontroller

public ActionResult ExportData()
        {
            string val = Request["Export"].ToString();
            List<StudentDetail> lst = db.Studentrecord.ToList();

            if (val.ToLower() == "xls")
            {
                GridView gv = new GridView();
                gv.DataSource = db.Studentrecord.ToList();
                gv.DataBind();
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment; filename=Marklist.xls");
                Response.ContentType = "application/ms-excel";
                Response.Charset = "";
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
                gv.RenderControl(htw);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
            else if (val.ToLower() == "csv")
            {
                StringBuilder sb = new StringBuilder();
                string[] columns = new string[4] { "StudentId", "Name", "Address", "Marks" };
                for (int k = 0; k < columns.Length; k++)
                {
                    //add separator
                    sb.Append(columns[k].ToString() + ',');
                }

                sb.Append("\r\n");
                foreach (StudentDetail item in lst)
                {
                    sb.Append(item.id + ",");
                    sb.Append(item.Name + ",");
                    sb.Append(item.Address + ",");
                    sb.Append(item.Marks);
                    //append new line
                    sb.Append("\r\n");
                }
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=Marklist.csv");
                Response.Charset = "";
                Response.ContentType = "application/text";
                Response.Output.Write(sb.ToString());
                Response.Flush();
                Response.End();
            }
            else if (val.ToLower() == "xlsx")
            {
                var data = from stuDetails in lst
                           select new
                           {
                               StudentId = stuDetails.id,
                               Name = stuDetails.Name,
                               Address = stuDetails.Address,
                               Marks = stuDetails.Marks
                           };
                ExcelPackage excel = new ExcelPackage();
                var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
                workSheet.Cells[1, 1].LoadFromCollection(data, true);
                using (var memoryStream = new MemoryStream())
                {
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    Response.AddHeader("content-disposition", "attachment; filename=Marklist.xslx");
                    excel.SaveAs(memoryStream);
                    memoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }

            return RedirectToAction("StudentDetails");
        }

NOTE: For the ExcelPackage() have to add EPPlus.dll to the references. Download EPPlus.dll from HERE. Add the downloaded dll to solution bin folder (C:\Export\Export\bin). In the solution click on add reference browse it from bin folder and add it.

8. Add following code in index.cshtml
@using (Html.BeginForm("ExportData", "StudentDetails", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
        <table><tbody>
        <tr>             <td></td>             <td>
        <input id="Export" name="Export" type="submit" value="XLS" />
        </td><td></td>             <td>
        <input id="Export" name="Export" type="submit" value="CSV" />
        </td><td></td>             <td>
        <input id="Export" name="Export" type="submit" value="XLSX" />
        </td><td></td>         </tr>
        </tbody></table>
}

9. Now run application and append studentdetails on URL and click on export button.


This will export you data into Xls, CSV and Excel.

Admin

Admin

Powered by Blogger.