Export List items to excel

In this blog we will see how to export a list in excel using server side object model.

First block we will retrieve all items from the list. This can be done in several ways.

• You can get all items without filtering any columns. This will consist of all hidden and default columns as well.
  
• You can get all items with removing hidden columns
  
• You can get specific set of items based on some filter
  

Add two references to your visual studio solution

System.Data.dll & Microsoft.SharePoint.dll

We will call getListData(siteUrl, listName), this method have two parameters:

siteurl : this is the site url from which you will fetch data listName : this is list name from which you will fetch data

private void getListData(string siteUrl, string listName)
{
    DataTable tableForExcel = null;

    SPSecurity.RunWithElevatedPrivileges(delegate ()
    {
        using (SPSite objectCurrentSite = new SPSite(SPContext.Current.Site.RootWeb.Url))
        {
            using (SPWeb objectCurrentWeb = objectCurrentSite.OpenWeb())
            {
                SPList objectList = objectCurrentWeb.Lists.TryGetList(listName);

                SPListItemCollection objectListItemCollection = null;

                if (objectList != null)
                    objectListItemCollection = objectList.GetItems();


                if (objectListItemCollection != null && objectListItemCollection.Count > 0)
                {
                    tableForExcel = new DataTable();

                    //Use only one approach of the below two as per your requirement

                    //#Approach1
                    //Here all fields will be included in excel including hidden fields

                    tableForExcel = objectListItemCollection.GetDataTable();

                    //#Approach2
                    //One approach where you can remove hidden fields from displaying in excel

                    foreach (SPField objectField in objectListItemCollection[0].Fields)
                    {
                        if (!objectField.Hidden)
                        {
                            tableForExcel.Columns.Add(objectField.Title);
                        }
                    }

                    foreach (SPListItem item in objectListItemCollection)
                    {
                        DataRow row = tableForExcel.NewRow();
                        int position = 0;

                        foreach (SPField objectField in item.Fields)
                        {
                            if (!objectField.Hidden)
                            {
                                row[position] = Convert.ToString(item[objectField.Title]);
                                position++;
                            }
                        }
                        tableForExcel.Rows.Add(row);
                    }

                    if (tableForExcel != null)
                        exportToExcel(tableForExcel,"Report");
                }
            }
        }
    });
}

We will call exportToExcel(tableForExcel, fileName), this method have two parameters:

tableForExcel : data table we have created with list data fileName : provide the name for excel which you want

This will give you a excel file direct download facility.

private static void exportToExcel(DataTable tableForExcel, string fileName)
{
    if (tableForExcel != null)
    {
        string attachment = "attachment; filename=" + fileName + ".xls";

        System.Web.HttpContext.Current.Response.ClearContent();

        System.Web.HttpContext.Current.Response.AddHeader("content-disposition", attachment);

        System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

        string tab = "";

        //Adding column header in excel 

        foreach (DataColumn dataColumn in tableForExcel.Columns)
        {
            System.Web.HttpContext.Current.Response.Write(tab + dataColumn.ColumnName);

            tab = "\t";
        }

        System.Web.HttpContext.Current.Response.Write("\n");

        int i;

        foreach (DataRow dr in tableForExcel.Rows)
        {
            tab = "";

            for (i = 0; i < tableForExcel.Columns.Count; i++)
            {
                System.Web.HttpContext.Current.Response.Write(tab + dr[i].ToString());

                tab = "\t";
            }

            System.Web.HttpContext.Current.Response.Write("\n");
        }

        System.Web.HttpContext.Current.Response.End();

        System.Web.HttpContext.Current.Response.Redirect(SPContext.Current.Web.Url.ToString());
    }
    else
    {
        //Your code here
    }
}