Export TO Excel

Now is time to export to excel from generic list of any entity.

Start with two entities: 1) employee, 2) project

We need to export project and employee list as a excel file using same code.
Lest start with code.
First we need to create entities.

public class Employee
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Code { get; set; }
        public double Salary { get; set; }
    }

public class Project
    {
        public int ID { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime StatDate { get; set; }
        public DateTime EndDate { get; set; }
        public double Budget { get; set; }
 }

Now need to create some data access layer for create list of entities.

 public class DataAccess
    {
        public List[Employee] GetEmployees()
        {
            var lstemp = new List[Employee]();
            for (int i = 1; i <= 10; i++)
            {
                lstemp.Add(new Employee
                {
                    ID = i,
                    FirstName = "FirstName",
                    LastName = "LastName",
                    Code = "ECode" + i.ToString(),
                    Salary = 150.6 * i
                });
            }
            return lstemp;
        }
        public List[Project] GetProjects()
        {
            var lstprj = new List[Project]();
            for (int i = 1; i <= 10; i++)
            {
                lstprj.Add(new Project
                {
                    ID = i,
                    Budget = 124.5 * i,
                    Code = "PCODE" + i.ToString(),
                    Description = "Project Description",
                    EndDate = DateTime.Now.AddMonths(1),
                    StatDate = DateTime.Now,
                    Name = "Project"
                });
            }
            return lstprj;
        }
    }

Now last one, need to create generic class for export list to excel

public static class Export[T] where T : class
    {
        public static void ExcelExport(string name, List[T] objlist)
        {

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.
            AddHeader("content-disposition", 
            string.Format("attachment; filename={0}", name + ".xls"));
            HttpContext.Current.Response
            .ContentType = "application/ms-excel";

            using (var sw = new StringWriter())
            {
                using (var htw = new HtmlTextWriter(sw))
                {
                    //  Create a form to contain the List
                    var table = new Table();
                    table.Style.Add(HtmlTextWriterStyle.BorderStyle, 
                                    "solid 1px #000");
                    var row = new TableRow();
                    var firstOrDefault = objlist.FirstOrDefault();
                    if (firstOrDefault != null)
                        foreach (var hcell in firstOrDefault.GetType()
                           .GetProperties().Select(proinfo => new TableHeaderCell
                                                        { Text = proinfo.Name }))
                        {
                            hcell.Style.Add(HtmlTextWriterStyle
                            .BackgroundColor, "#d0d0d0");
                            hcell.Style.Add(HtmlTextWriterStyle
                            .BorderColor, "#000000");
                            row.Cells.Add(hcell);
                        }
                    table.Rows.Add(row);

                    //  add each of the data item to the table
                    foreach (var emp in objlist)
                    {
                        var row1 = new TableRow();
                        foreach (var cell in emp.GetType()
                       .GetProperties().Select(prop => new TableCell 
                        { Text = prop.GetValue(emp, null).ToString() }))
                        {
                            cell.Style.Add(HtmlTextWriterStyle
                            .BackgroundColor, "#FFF");
                            cell.Style.Add(HtmlTextWriterStyle
                           .BorderColor, "#000000");
                            row1.Cells.Add(cell);
                        }
                        table.Rows.Add(row1);

                    }

                    //  render the table into the htmlwriter
                    table.RenderControl(htw);

                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }
    }

Ok, we have done. Now just need to use above export class for export list using following code.

            var objDBContext = new DataAccess();
            grdproject.DataSource = objDBContext.GetProjects();
            grdproject.DataBind();
            grdemployee.DataSource = objDBContext.GetEmployees();
            grdemployee.DataBind();
       
            Export[Project].ExcelExport("MyProjects", new DataAccess().GetProjects());
            Export[Employee].ExcelExport("CompanyEmployee", new DataAccess().GetEmployees());

That's all I have done! Thank you for your attention. For more details you can comments on post or can contact me directly by my contact info. I hope you like my blog and find it usefully for you.

That's ALL ! I hope it will help you!!!