Generic List export to excel

public void ExportToExcel(List<T> myList)
{
  string fileName = "MyFilename.xls";
  
  DataGrid dg = new DataGrid();
  dg.AllowPaging = false;
  dg.DataSource = myList;
  dg.DataBind();
  
  System.Web.HttpContext.Current.Response.Clear();
  System.Web.HttpContext.Current.Response.Buffer = true;
  System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
  System.Web.HttpContext.Current.Response.Charset = "";
  System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition",
    "attachment; filename=" + fileName);
  
  System.Web.HttpContext.Current.Response.ContentType = 
    "application/vnd.ms-excel";
  System.IO.StringWriter stringWriter = new System.IO.StringWriter();
  System.Web.UI.HtmlTextWriter htmlTextWriter = 
    new System.Web.UI.HtmlTextWriter(stringWriter);
  dg.RenderControl(htmlTextWriter);
  System.Web.HttpContext.Current.Response.Write(stringWriter.ToString());
  System.Web.HttpContext.Current.Response.End();
}

Regards,

zxj

Reply

atulthummar

Participant

1499 Points

501 Posts

Re: Generic List export to excel

Jul 05, 2017 05:56 AM|atulthummar|LINK

Hello,

Here is the sample generic code for export

 var result = SampleClass.List(authorizationToken, urlParameters);

                if (result.TotalCount > 0)
                {
                    var entityLst = result.List;

                    StringWriter sw = new StringWriter();
                    sw.WriteLine("\"Column1\",\"Column2\",\"Column3\",\"Column4\"");
                    Response.ClearContent();
                    string fileName = "Sample_" + DateTime.UtcNow.ToString("dd-MM-yyyy");
                    Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".csv");
                    Response.ContentType = "text/csv";
                    if (entityLst.Count > 0)
                    {
                        foreach (var line in entityLst)
                        {
                            sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\"",
                                                       line.Column1,
                                                       line.Column2,
                                                       line.Column3,
                                                       line.Column4
                                                       ));
                        }
                    }
                    Response.Write(sw.ToString());
                    Response.End();
                }

Thanks & Best Regards,
Atul T.

Please click "mark as answer" if this post helped you.

Reply

Zhi Lv - MSF...

All-Star

45479 Points

7008 Posts

Microsoft

Re: Generic List export to excel

Jul 06, 2017 10:02 AM|Zhi Lv - MSFT|LINK

Hi Ravindranath,

You could refer to the following methods to export data to excel.

1. As zxj said, using the following code:

        public void ExportToExcel<T>(List<T> myList)
        {
            string fileName = "MyFilename.xlsx";

            DataGrid dg = new DataGrid();
            dg.AllowPaging = false;
            dg.DataSource = myList;
            dg.DataBind();

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.AddHeader("Content-Disposition",
              "attachment; filename=" + fileName);

            HttpContext.Current.Response.ContentType =
              "application/vnd.ms-excel";
            System.IO.StringWriter stringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlTextWriter =
              new System.Web.UI.HtmlTextWriter(stringWriter);
            dg.RenderControl(htmlTextWriter);
            System.Web.HttpContext.Current.Response.Write(stringWriter.ToString());
            System.Web.HttpContext.Current.Response.End();
        }

2. using ClosedXML, code as below:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI.WebControls;
using ClosedXML.Excel;
using System.Reflection;

        protected void Button1_Click(object sender, EventArgs e)
        {
            List<TestEntity> itemlist1 = new List<TestEntity>()
            {
                new TestEntity() { ID=1001, Name="AAA" },
                new TestEntity() { ID=1002, Name="BBB" },
                new TestEntity() { ID=1003, Name="CCC" }
            };
            //
            //ExportToExcel<TestEntity>(itemlist1);

            //openxml method.
            //CreateExcelFile.CreateExcelDocument<TestEntity>(itemlist1, "TestEntity.xlsx", Response);

            //ClosedXML method.
            //1. convert the list to datatable 
            DataTable dt = ToDataTable<TestEntity>(itemlist1);
            //2. using the following code to export datatable to excel.
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt);

                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=TestEntity.xlsx");
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }
        }

        //convert generic list to datatable.
        public DataTable ToDataTable<T>(List<T> items)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);
            //Get all the properties by using reflection   
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                //Setting column names as Property names  
                dataTable.Columns.Add(prop.Name);
            }
            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {

                    values[i] = Props[i].GetValue(item, null);
                }
                dataTable.Rows.Add(values);
            }

            return dataTable;
        }

More details about using OpenXML and ClosedXML, see:

https://www.codeproject.com/Articles/692121/Csharp-Export-data-to-Excel-using-OpenXML-librarie

https://www.aspsnippets.com/Articles/Export-data-from-SQL-Server-to-Excel-in-ASPNet-using-C-and-VBNet.aspx

Best regards,
Dillion

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Generic List export to excelRSS

3 replies

Shortcuts

Related Links