Duet Enterprise et Excel 2010

Corps :
Auteur : Joyanta Sen (Microsoft France)
 
Voici une description étape par étape d’un exemple basé sur l’intégration de listes Duet Enterprise dans Excel 2010. L’objectif consiste à afficher la liste Customer dans une feuille de calcul Excel 2010.

Étapes préliminaires

Pour utiliser une liste externe à partir d’un type de contenu externe dans une application Excel VSTO, vous devez effectuer les opérations suivantes :
  • Créer une liste externe à partir d’un type de contenu externe









 

Application Excel VSTO


1. Créez un projet Classeur VSTO dans Visual Studio 2010 :

 

2. Créez une classe d’assistance appelée SPHelper dans un dossier Projet appelé Helpers.


 


3. Ajoutez dans votre projet un type correspondant à la structure Customer dans une classe appelée CommonTypes.cd, dans un répertoire Common :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace DuetExcelWorkbook.Common
{
    public struct CustomerType
    {
        public string FirstLineName;
        public string CountryCode;
        public string AddressregionCode;
        public string AddresscityName;
 
    }
}

 

 

4. Ajoutez l’assembly Client OM :

-      C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\
-          Ajoutez  les deux références d’assembly suivantes dans votre projet :
o   Microsoft.SharePoint.Client.dll
o   Microsoft.SharePoint.Client.Runtime.dll

 -          Créez une classe appelée SPHelpers.cs dans Common :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Client;
using System.Linq.Expressions;
using System.Xml;
 
namespace DuetExcelWorkbook.Helpers
{
    public static class SPHelper
    {
        public static List<CustomerType> GetCustomerList(string TargetSiteUrl, string TargetListName)
        {
            List<CustomerType> CustomerList = new List<CustomerType>();
 
            try
            {
                ClientContext clientContext = new ClientContext(TargetSiteUrl);
 
                List externalList = clientContext.Web.Lists.GetByTitle(
                    TargetListName);
 
                // To properly construct the CamlQuery and
                // ClientContext.LoadQuery,
                // we need some View data of the Virtual List.
                // In particular, the View will give us the CamlQuery
                // Method and Fields.
                clientContext.Load(
                    externalList.Views,
                    viewCollection => viewCollection.Include(
                        view => view.ViewFields,
                        view => view.HtmlSchemaXml));
 
                // This tells us how many list items we can retrieve.
                clientContext.Load(clientContext.Site,
                    s => s.MaxItemsPerThrottledOperation);
 
                clientContext.ExecuteQuery();
 
                // Let's just pick the first View.
                View targetView = externalList.Views[0];
                string method = ReadMethodFromViewXml(
                    targetView.HtmlSchemaXml);
                ViewFieldCollection viewFields = targetView.ViewFields;
 
                CamlQuery vlQuery = CreateCamlQuery(
                    clientContext.Site.MaxItemsPerThrottledOperation,
                    method,
                    viewFields);
 
                Expression<Func<ListItem, object>>[] listItemExpressions =
                    CreateListItemLoadExpressions(viewFields);
 
                ListItemCollection listItemCollection =
                    externalList.GetItems(vlQuery);
 
                // Note: Due to limitation, you currently cannot use
                // ClientContext.Load.
                //       (you'll get InvalidQueryExpressionException)
                IEnumerable<ListItem> resultData = clientContext.LoadQuery(
                    listItemCollection.Include(listItemExpressions));
 
                clientContext.ExecuteQuery();
 
                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();
 
                    CustomerList.Add(customer);
                    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
                    // not yet retrieved.               
                }
            }
            catch (Exception ex)
            {
 
                throw ex;
            }
 
            return CustomerList;
        }
 
        /// <summary>
        /// Parses the viewXml and returns the Method value.
        /// </summary>       
        private static string ReadMethodFromViewXml(string viewXml)
        {
            XmlReaderSettings readerSettings = new XmlReaderSettings();
            readerSettings.ConformanceLevel = ConformanceLevel.Fragment;
 
            XmlReader xmlReader = XmlReader.Create(
                new StringReader(viewXml), readerSettings);
            while (xmlReader.Read())
            {
                switch (xmlReader.NodeType)
                {
                    case XmlNodeType.Element:
                        if (xmlReader.Name == "Method")
                        {
                            while (xmlReader.MoveToNextAttribute())
                            {
                                if (xmlReader.Name == "Name")
                                {
                                    return xmlReader.Value;
                                }
                            }
                        }
                        break;
                }
            }
 
            throw new Exception("Unable to find Method in View XML");
        }
 
        /// <summary>
        /// Creates a CamlQuery based on the inputs.
        /// </summary>       
        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(
                stringBuilder, xmlSettings);
 
            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
 
            if (viewFields.Count > 0)
            {
                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
            }
 
            writer.WriteElementString(
                "RowLimit", rowLimit.ToString(CultureInfo.InvariantCulture));
 
            writer.WriteEndElement();  // View
 
            writer.Close();
 
            query.ViewXml = stringBuilder.ToString();
 
            return query;
        }
 
        /// <summary>
        /// Returns an array of Expression used in
        /// ClientContext.LoadQuery to retrieve
        /// the specified field data from a ListItem.       
        /// </summary>       
        private static Expression<Func<ListItem, object>>[]
            CreateListItemLoadExpressions(
            ViewFieldCollection viewFields)
        {
            List<Expression<Func<ListItem, object>>> expressions =
                new List<Expression<Func<ListItem, object>>>();
 
            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<Func<ListItem, object>>
                    retrieveFieldDataExpression =
                    listItem => listItem[fieldInternalName];
 
                expressions.Add(retrieveFieldDataExpression);
            }
 
            return expressions.ToArray();
        }
    }

5. Nous allons à présent coder l’interface utilisateur, la partie du code qui appellera la classe Client OM.
Ajoutez un Ruban dans votre projet :

 

 
Modifiez les propriétés suivantes du Ruban :

Label: Duet Enterprise
Name: tabDuet

 Ajoutez un bouton dans le Ruban et modifiez les propriétés suivantes : 

ControlSize: RibbonControlSizeLarge
Label: Customers
OfficeImageId: SlideMasterChartPlacehoderInsert


 6. Ajoutez le code suivant dans la fonction buttonCustomers_Click() :

private void buttonCustomers_Click(object sender, RibbonControlEventArgs e)
        {
            List<CustomerType> result = new List<CustomerType>();
 
            try
            {
                result = SPHelper.GetCustomerList("http://litware", "SAPCustomers");
 
                //string[] Names = new string[];
                //string[] Countries;
                List<string> Names = new List<string>();
                List<string> Countries = new List<string>();
                int counter = 0;
 
                // Fill the collections
 
                foreach (var item in result)
                {
                    // Add the customers in the ListView
 
                    Names.Add(item.FirstLineName);
                    Countries.Add(item.CountryCode);
                         
                    counter++;
 
                }
 
                // 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));
                table.Columns.Add(column1);
                table.Columns.Add(column2);
 
                // Add the four rows of data to the table.
                DataRow row;
                for (int i = 0; i < counter; i++)
                {
                    row = table.NewRow();
                    row["Name"] = Names[i];
                    row["Country"] = Countries[i];
                    table.Rows.Add(row);
                }
 
                Microsoft.Office.Tools.Excel.ListObject list1 =
                    Globals.Sheet1.Controls.AddListObject(Globals.Sheet1.Range["A1", "B4"], "list1");
 
                // Bind the list object to the table.
                list1.SetDataBinding(table);
 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw;
            }
 
        }
 

7. Exécutez Visual Studio.

Remarque : veillez à exécuter Visual Studio à l’aide de l’identité utilisateur correcte. L’utilisateur doit détenir une autorisation de lecture sur la liste Customers.

Cliquez sur le Ruban de Duet Enterprise, puis sur le bouton Customers. La liste des clients est ajoutée à Microsoft Excel :

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Catégorie : Duet; Excel Services
Date de publication : 03/02/2011 9:00

Ce billet de blog a été traduit de l’anglais. L’article d’origine est disponible à la page Duet Enterprise and Excel 2010