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.

1. You can get all items without filtering any columns. This will consist of all hidden and default columns as well.

2. You can get all items with removing hidden columns

3. 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:

1. siteurl : this is the site url from which you will fetch

2. datalistName : 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:

1. tableForExcel: data table we have created with list data

2. 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
    }
}