Excel Addin : SharePoint List data using Web Service

In this post I will tell you how a SharePoint List can be accessed through the web service. In this example I have a list name “LearningList”, which is accessed through the web service. I have accessed the list, shown the data from the list on to the excel sheet and updated the data using the same web service. To have the source code to the example Click here.

CODE SNIPPET

•  Create a new Custom List and name it “LearningList”.
•  Create the fields as shown in the image

• Create a new Visual Studio 2010 project, select the Office 2010 template and in it select the Excel 2010.
•  Now add the WPF User Control name it “BookUserControl.xaml”,  add a class name it “Books.cs” and add a User Control name it “HostingControl.cs”.
• After adding all the above file the code structure looks something like : 

•  Now double click the BookUserControl.xaml and add the 5 labels, 3 textbox, 2 buttons and 1 listbox, in the way as shown in picture below.

• While adding you will notice that the XAML code is being written, which defines the property of the controls added.
• Double click the Buttons to add the event handlers for each of them in the code behind (this will also update the XAML code).
•  Now add the service reference to the code. Right click the project and select the “Add Service Reference”.
•   A new pop window will open, click “Advanced àAdd Web Reference à type the url of the list service. Example: http://<server name>/_vti_bin/Lists.asmx
•  Click the go button, which will show all the available methods with the service,  name the Service reference (SPListWebService) and click on the Add Reference button to add the reference to the project.

• Now double click the Books.cs and add the following code.

    class Books

    {

        public string bookTitle { get; set; }

        public string bookAuthor { get; set; }

        public string bookPrice { get; set; }

        public string bookStockCount { get; set; }

    }

•  Open the BookUserControl.xaml.cs by double clicking it and add the following code:

public partial class BooksUserControl : UserControl

    {

        //Collection makes it easier to query and bind data to controls

        List<Books> myBooks = new List<Books>();

        public string ListName = "LearningList";

        public BooksUserControl()

        {

            InitializeComponent();

        }

        private void btnLoad_Click(object sender, RoutedEventArgs e)

        {

            lstBxBooksTitle.Items.Refresh();

            //Accessing the List Web Service : Provide Credentials and URL of the

list Web Service

            SPListsWebService.Lists myListProxy = new SPListsWebService.Lists();

            myListProxy.Credentials =

     System.Net.CredentialCache.DefaultCredentials;

            myListProxy.Url = "http://<server name>/_vti_bin/lists.asmx";

            //Get the list items by making call to the GetListItem() method

            try

            {

                XmlNode xmlListItems = myListProxy.GetListItems(ListName, null,

null, null, null, null, null);

                //XElement newRoot =  new XElement("

                foreach (XmlNode outerNode in xmlListItems.ChildNodes)

                {

                    if (outerNode.NodeType.Equals(System.Xml.XmlNodeType.Element))

                    {

                        foreach (XmlNode innerNode in outerNode.ChildNodes)

                        {

                            if (innerNode.NodeType.Equals(System.Xml.XmlNodeType.Element))

                            {

                                XmlNode listFieldTitle = innerNode.Attributes.GetNamedItem("ows_Title");

                                XmlNode listFieldAuthor = innerNode.Attributes.GetNamedItem("ows_Author0");

                                XmlNode listFieldPrice = innerNode.Attributes.GetNamedItem("ows_Price");

                                XmlNode listFieldInStock = innerNode.Attributes.GetNamedItem("ows_InStock");

                                Books objBooks = new Books();

                                objBooks.bookTitle = listFieldTitle.InnerText;

                                objBooks.bookAuthor = listFieldAuthor.InnerText;

                                objBooks.bookPrice = listFieldPrice.InnerText;

                                objBooks.bookStockCount = listFieldInStock.InnerText;

                                myBooks.Add(objBooks);

                                lstBxBooksTitle.Items.Add(objBooks.bookTitle);

                            }

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error Occured: " + ex.Message);

            }

        }

        private void btnUpdate_Click(object sender, RoutedEventArgs e)

        {

            Excel.Worksheet myWorksheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;

            int index = 0, temp = 0;

            string bookTitleUpdate = Convert.ToString(lstBxBooksTitle.SelectedItem);

            string bookAuthorUpdate = txtAuthor.Text;

            string bookPriceUpdate = txtPrice.Text;

            string bookInStockUpdate = txtStockCount.Text;

            foreach (Books objBooks in myBooks)

            {

                temp++;

                if (objBooks.bookTitle.Equals(bookTitleUpdate))

                {

                    index = temp;

                }

            }

            //Accessing the List Web Service : Provide Credentials and URL of the list Web Service

            SPListsWebService.Lists myListProxy = new SPListsWebService.Lists();

            myListProxy.Credentials = System.Net.CredentialCache.DefaultCredentials;

            myListProxy.Url = "http://br-pc-350:2789/_vti_bin/lists.asmx";

            //get the list id and the View id

            XmlNode xmlListView = myListProxy.GetListAndView(ListName, "");

            string ListId = xmlListView.ChildNodes[0].Attributes["Name"].Value.ToString();

            string ViewId = xmlListView.ChildNodes[1].Attributes["Name"].Value.ToString();

            //Create a XML for updating the list

            XmlDocument xmlDoc = new XmlDocument();

            XmlElement xmlBatch = xmlDoc.CreateElement("Batch");

            xmlBatch.SetAttribute("OnError", "Continue");

            xmlBatch.SetAttribute("ListVersion", "1");

            xmlBatch.SetAttribute("ViewName", ViewId);

            xmlBatch.InnerXml = "<Method ID='1' Cmd='Update'>" + "<Field Name='ID'>" + index + "</Field>" + "<Field Name='Title'>" + bookTitleUpdate + "</Field><Field Name='Author0'>" + bookAuthorUpdate +

                "</Field><Field Name='Price'>" + bookPriceUpdate + "</Field><Field Name='InStock'>" + bookInStockUpdate + "</Field></Method>";

            //Update the List using the Proxy

            try

            {

                XmlNode xmlReturn = myListProxy.UpdateListItems(ListName, xmlBatch);

                MessageBox.Show("List Item Updated Succesfully..!!");

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error Occured " + ex.Message);

            }

        }

        private void lstBxBooksTitle_SelectionChanged(object sender, SelectionChangedEventArgs e)

        {

            string bookAuthor = null, bookPrice = null, bookStockCount = null;

            string selectedBook = Convert.ToString(lstBxBooksTitle.SelectedItem);

            //LinQ Query

            var bookData = from p in myBooks.Where(p => p.bookTitle == selectedBook)

                           select new { p.bookAuthor, p.bookPrice, p.bookStockCount };

            foreach (var tmp in bookData)

            {

                bookAuthor = tmp.bookAuthor;

                bookPrice = tmp.bookPrice;

                bookStockCount = tmp.bookStockCount;

            }

            Excel.Worksheet myWorkSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;

            myWorkSheet.Cells[1, 1] = "Title";

            myWorkSheet.Cells[1, 2] = "Author";

            myWorkSheet.Cells[1, 3] = "Price";

            myWorkSheet.Cells[1, 4] = "Stock Count";

            //Displaying the Data on to the Excel Sheet

            myWorkSheet.Cells[2, 1] = selectedBook;

            myWorkSheet.Cells[2, 2] = bookAuthor;

            myWorkSheet.Cells[2, 3] = bookPrice;

            myWorkSheet.Cells[2, 4] = bookStockCount;

            //Displaying the data in the text Box

            txtAuthor.Text = bookAuthor;

            txtPrice.Text = bookPrice;

            txtStockCount.Text = bookStockCount;

        }

    }

• Now compile the code by pressing the F6.
•  Now your WPF control will be available in the toolbox, drag and drop it on to the HostingControl user control. (as shown in in the picture below)

•  Now open the This.Addin.cs and add the following code :

public partial class ThisAddIn

    {

        HostingControl objHostingControl = new HostingControl();

        string hostingControlTitle = "Books Data";

        Office.CustomTaskPane ctp;

        private void ThisAddIn_Startup(object sender, System.EventArgs e)

        {

            ctp = this.CustomTaskPanes.Add(objHostingControl, hostingControlTitle);

            ctp.Visible = true;

            ctp.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight;

        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)

        {

        }

        #region VSTO generated code

        /// <summary>

        /// Required method for Designer support - do not modify

        /// the contents of this method with the code editor.

        /// </summary>

        private void InternalStartup()

        {

            this.Startup += new System.EventHandler(ThisAddIn_Startup);

            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);

        }

       

        #endregion

    }

•   Now you can run your code which will appear somewhat like this:

·