Duet Enterprise and Excel 2010 Sharepoint Asp.Net C#

Here is a step-by-step description of an example based on Duet Enterprise list integration inside Excel 2010. The goal is to display the Customer list in an Excel 2010 Spreadsheet.To consume External List from an External Content Type in a VSTO Excel application, you should perform the following operations:Create an external List from an External Content Type?

 
1. Create a VSTO workbook project using Visual Studio 2010:

 

2.  Create a helper class called SPHelper in a project folder called Helpers

 

3. Add a type in your project corresponding to the Customer structure in a class called CommonTypes.cd in a Common directory:

using System.Collections.Generic;namespace DuetExcelWorkbook.Common        public string AddressregionCode;        public string AddresscityName;

4.  Add the Client OM assembly:

-      C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\ -          Add  the two following assembly references to your project:

o   Microsoft.SharePoint.Client.dllo   Microsoft.SharePoint.Client.Runtime.dll

 -          Create a class called SPHelpers.cs in Common:

using System.Collections.Generic;using Microsoft.SharePoint.Client;using System.Linq.Expressions;namespace DuetExcelWorkbook.Helpers        public static List GetCustomerList(string TargetSiteUrl, string TargetListName)            List CustomerList = new List();                ClientContext clientContext = new ClientContext(TargetSiteUrl);                List externalList = clientContext.Web.Lists.GetByTitle(                // To properly construct the CamlQuery and                 // we need some View data of the Virtual List.                // In particular, the View will give us the CamlQuery                     viewCollection => viewCollection.Include(                // This tells us how many list items we can retrieve.                clientContext.Load(clientContext.Site,                    s => s.MaxItemsPerThrottledOperation);                // Let's just pick the first View.                View targetView = externalList.Views[0];                string method = ReadMethodFromViewXml(                ViewFieldCollection viewFields = targetView.ViewFields;                CamlQuery vlQuery = CreateCamlQuery(                    clientContext.Site.MaxItemsPerThrottledOperation,                Expression>[] listItemExpressions =                    CreateListItemLoadExpressions(viewFields);                ListItemCollection listItemCollection =                    externalList.GetItems(vlQuery);                // Note: Due to limitation, you currently cannot use                 //       (you'll get InvalidQueryExpressionException)                IEnumerable resultData = clientContext.LoadQuery(                    listItemCollection.Include(listItemExpressions));                foreach (ListItem li in resultData)                    // Now you can use the ListItem data!                    CustomerType customer = new CustomerType();                    customer.FirstLineName = li["FirstLineName"].ToString();                    //customer.AddresscityName = li["AddresscityName"].ToString();                    //customer.AddressregionCode = li["AddressregionCode"].ToString();                    customer.CountryCode = li["CountryCode"].ToString();                    Console.WriteLine("First Name: {0} Country : {1} \n", li["FirstLineName"].ToString(), li["CountryCode"].ToString());                    // Note: In the CamlQuery, we specified RowLimit of                     // MaxItemsPerThrottledOperation.                    // You may want to check whether there are other rows         /// Parses the viewXml and returns the Method value.        private static string ReadMethodFromViewXml(string viewXml)            XmlReaderSettings readerSettings = new XmlReaderSettings();            readerSettings.ConformanceLevel = ConformanceLevel.Fragment;            XmlReader xmlReader = XmlReader.Create(                new StringReader(viewXml), readerSettings);                        if (xmlReader.Name == "Method")                            while (xmlReader.MoveToNextAttribute())            throw new Exception("Unable to find Method in View XML");        /// Creates a CamlQuery based on the inputs.        private static CamlQuery CreateCamlQuery(            uint rowLimit, string method, ViewFieldCollection viewFields)            CamlQuery query = new CamlQuery();            XmlWriterSettings xmlSettings = new XmlWriterSettings();            xmlSettings.OmitXmlDeclaration = true;            StringBuilder stringBuilder = new StringBuilder();            XmlWriter writer = XmlWriter.Create(            writer.WriteStartElement("View");            // Specifies we want all items, regardless of folder level.            writer.WriteAttributeString("Scope", "RecursiveAll");            writer.WriteStartElement("Method");            writer.WriteAttributeString("Name", method);            writer.WriteEndElement();  // Method                writer.WriteStartElement("ViewFields");                foreach (string viewField in viewFields)                    if (!string.IsNullOrEmpty(viewField))                        writer.WriteStartElement("FieldRef");                        writer.WriteAttributeString("Name", viewField);                        writer.WriteEndElement();  // FieldRef                writer.WriteEndElement();  // ViewFields                "RowLimit", rowLimit.ToString(CultureInfo.InvariantCulture));            writer.WriteEndElement();  // View            query.ViewXml = stringBuilder.ToString();        /// Returns an array of Expression used in         /// ClientContext.LoadQuery to retrieve         /// the specified field data from a ListItem.                private static Expression>[]            CreateListItemLoadExpressions(            ViewFieldCollection viewFields)            List>> expressions =                new List>>();            foreach (string viewFieldEntry in viewFields)                // Note: While this may look unimportant,                 // and something we can skip, in actuality,                //       we need this step.  The expression should                 // be built with local variable.                                string fieldInternalName = viewFieldEntry;                Expression>                    listItem => listItem[fieldInternalName];                expressions.Add(retrieveFieldDataExpression);5. Now we will code the user interface, the part of the code that will call the Client OM class.

Modify the following properties for the Ribbon:

Label: Duet Enterprise
Name: tabDuet

 Add a button on the ribbon and modifies the following properties: 

ControlSize: RibbonControlSizeLarge
Label: Customers
OfficeImageId: SlideMasterChartPlacehoderInsert

 6.  Add the following code in the buttonCustomers_Click() function:

private void buttonCustomers_Click(object sender, RibbonControlEventArgs e)

            List result = new List();                result = SPHelper.GetCustomerList("http://litware", "SAPCustomers");                //string[] Names = new string[];                List Names = new List();                List Countries = new List();                    // Add the customers in the ListView                    Names.Add(item.FirstLineName);                    Countries.Add(item.CountryCode);                // Create a data table with two columns.                System.Data.DataTable table = new DataTable();                DataColumn column1 = new DataColumn("Name", typeof(string));                DataColumn column2 = new DataColumn("Country", typeof(string));                // Add the four rows of data to the table.                for (int i = 0; i < counter; i++)                    row["Country"] = Countries[i];                Microsoft.Office.Tools.Excel.ListObject list1 =                    Globals.Sheet1.Controls.AddListObject(Globals.Sheet1.Range["A1", "B4"], "list1");                // Bind the list object to the table.

 

Note: make sure you run Visual Studio using the correct user identity. The user must be able to have appropriate read permission on the Customers list.

Click on the Duet Enterprise Ribbon and click on the Customers button. You will see the customers list added to Microsoft Excel:

Hope this helps,happy coding