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

Retrieve items from list using JSOM

This blog demonstrates how to connect to a list and retrieve specific items based on filter/query using JSOM.

<script>
    var ListItem;

    function getTheList(employee) {

        clientContext = new SP.ClientContext.get_current();

        //get web object from current context
        var web = clientContext.get_web();

        //Provide list name and fetch the list
        var myList = web.get_lists().getByTitle('LIST_NAME');

        //write a query to fetch records based on filter
        var query = new SP.CamlQuery();
        query.set_viewXml('<View><Query><Where><Eq><FieldRef Name="EmployeeName"/>
            <Value Type="Text">' + employee + '</Value></Eq></Where></Query></View>');      

        //fetch list items based on query formed in above statement
        ListItem = myList.getItems(query); 

        //Load the object
        clientContext.load(ListItem,'Include(EmployeeName)'); 

        //Final step to execute the query to get details for loaded objects
        clientContext.executeQueryAsync(Function.createDelegate(this, this.mySuccessFunction),
             Function.createDelegate(this, this.myFailFunction));

        return false;
    }

    //This method will be called on success of executeQueryAsync
    function mySuccessFunction(sender, args) 
    {    
        var listItemEnumerator = ListItem.getEnumerator();
        while (listItemEnumerator.moveNext())
        {
              var item = listItemEnumerator.get_current();
                  
              var employee = item.get_item('EmployeeName');
              //Your code goes here..
        }
    }

    //This method will be called on failure or on any exception thrown in executeQueryAsync
    function myFailFunction(sender, args) 
    { 
        alert(args.get_message() + "\n" + args.get_stackTrace()); 
    }

</script>

Retrieve custom fields only from list using CSOM

This blog demonstrates how to retrieve/print only custom fields (user created) from a list using CSOM.

Add two references to your visual studio solution
Microsoft.SharePoint.Client.dll &
Microsoft.SharePoint.Client.Runtime.dll

string siteURL = "http://server/sites/site";
ClientContext context = new ClientContext(siteURL);
Web oWebSite = context.Web;

context.Load(oWebSite);
context.ExecuteQuery();

//Get the list by title
List employeeList = oWebSite.Lists.GetByTitle("Employee");
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "";
ListItemCollection listItems = employeeList.GetItems(camlQuery);

context.Load(employeeList);
context.Load(listItems);
context.Load(employeeList.Fields);
context.ExecuteQuery();

foreach(Field field in employeeList.Fields)
{
    if(!field.FromBaseType)
         Console.WriteLine("{0} - {1} - {2}",field.Title, field.InternalName, field.Hidden);
}

Delete specific file from attachment of a list item programatically

Consider you have a listitem with multiple files added as attachments. Now you need to delete a specific file from those attachments programmatically,
follow below steps:

      public static void DeleteAttachment(int itemId, string fileName)  
      {   
          using (SPSite site = new SPSite("http://server/sites/sitename"))  
          {  
              using (SPWeb web = site.OpenWeb())  
              {  
                  SPListItem listItem = web.Lists.TryGetList("Employees").GetItemById(itemId);  
 
                  List fileNames = new List();  
                  
                  if(listItem.Attachments.Count > 0){
                      //Store all file names from attachment collection
                      foreach (string fileName in listItem.Attachments){  
                           fileNames.Add(fileName);  
                      }  
                  }

                  if(fileNames.Count > 0 && fileNames.Contains(filename)){  
                      listItem.Attachments.Delete(fileName);                        
                  }  
                   
                  listItem.update();
              }   
          }                     
      } 

Now make a call to the method

      int itemID = 10;
      string filename = "address.docx";
      
      DeleteAttachment(itemId, fileName);