How to Export SharePoint List Data to Excel Sheet programmatically

·         If you have data stored in a list in SharePoint Server you can easily export it to Microsoft Office Excel

·         By exporting your SharePoint list to Excel, you can take advantage of the rich set of list features and formatting options in Excel

·         After exporting the list to Excel, you can choose whether to keep it linked to the SharePoint list

Solution:

·         I have created a custom web part that will take list name and column names separated by comma through web part custom properties rather than hard coding the list name and column names in the code

·         Basically, I would like the user to click on export to excel button and have the SharePoint list exported to Excel from within the code, and not the Export to Excel button on the ribbon

·         To add list name through custom  properties in the web part, please follow the below code:

public partial class ExportSPListToExcel : WebPart

    {

[WebBrowsable(true),

 WebDisplayName("List Name"),

 WebDescription("This accepts List name"),

 Personalizable(PersonalizationScope.Shared),

 Category("Custom Properties")]

 public string ListName { get; set; }

}

·         To add column names through custom  properties in the web part, please follow the below code:

[WebBrowsable(true),

 WebDisplayName("Column Names"),

 WebDescription("This accepts Comma seperated Column names"),

 Personalizable(PersonalizationScope.Shared),

 Category("Custom Properties")]

  public string ColumnNames { get; set; }

·         The above code will allow you to give the list name and columns names separated by comma in the web part custom properties as shown in the below screenshot:

                                      

·         I have used the below code to export the SP List data to Excel:

public void ProcessRequest(HttpContext context)

        {

            

            string filename = ListName;

                context.Response.Clear();

                context.Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls");

                context.Response.ContentType = "application/ms-excel";

                context.Response.Charset = "utf-8";

               

                StringBuilder exceltable = new StringBuilder();

                exceltable.Append("<table border=\"1\" ><tr style=\"text-align:left\">");

                String[] columns = ColumnNames.Split(',');

                foreach (string column in columns)

                {

                    exceltable.Append("<th>" + column.ToString() + "</th>");

                }

                exceltable.Append("<th>Created Date</th><th>Modified Date</th></tr>");

                SPSecurity.RunWithElevatedPrivileges(delegate()

                {

                                                      

                            SPList lst = SPContext.Current.Web.Lists[ListName.ToString()];

                            SPListItemCollection itmColl = lst.Items;

                            foreach (SPListItem itm in itmColl)

                            {

                                exceltable.Append("<tr>");

                                /*get column names and iterate throuh each */

                                foreach (string column in columns)

                                {

                                    String value = string.Empty;

                                    String val = Convert.ToString(itm[column]);

                                    if (String.IsNullOrEmpty(val)) { }

                                    else

                                    {

                                        String newVal = String.Empty;

                                        value = val.ToLower().Contains(";#") ? String.Join(newVal, val.Replace('#', ' ').Split(';').Where(((s, i) => i % 2 == 1))) : val;

                                    }

                                    exceltable.Append("<td>" + value + "</td>");

                                }

                                /*get column names and iterate throuh each */

                                exceltable.Append("<td style=\"text-align:center\">" + Convert.ToDateTime(itm["Created"]).ToString() + "</td>");

                                exceltable.Append("<td style=\"text-align:center\">" + Convert.ToDateTime(itm["Modified"]).ToString() + "</td>");

                                exceltable.Append("</tr>");

                            }

                            exceltable.Append("</table>");

                        }

                    }

                 

                });

                context.Response.Write(exceltable);

                context.Response.End();

               

                }

·         I have added a Button in .ascx and on click of that Button, called the above function:

protected  void Button1_Click(object sender, EventArgs e)

        {

           

            ProcessRequest(HttpContext.Current);

        }

·         The below screen shot shows the end result where the user can click on export to excel button in the page and the SP list name and column names given in the web part custom properties will be downloaded in excel format:

·         The below screen shot shows the end result where the user successfully downloads the SP List to excel sheet