Duet Enterprise y Excel 2010

Cuerpo:
Autor: Joyanta Sen (Microsoft Francia)
 
A continuación se proporciona una descripción paso a paso de un ejemplo basado en la integración de listas de Duet Enterprise dentro de Excel 2010. El objetivo es mostrar la lista de clientes en una hoja de cálculo de Excel 2010.

Pasos preliminares

Para consumir una lista externa de un tipo de contenido externo de una aplicación de Excel con VSTO, debe realizar las siguientes operaciones:
  • Crear una lista externa de un tipo de contenido externo









 

Aplicación de Excel con VSTO


1. Cree un proyecto de libro de VSTO mediante Visual Studio 2010:

 

2. Cree una clase auxiliar denominada SPHelper en una carpeta de proyecto denominada Helpers


 


3. Agregue un tipo al proyecto correspondiente a la estructura de cliente en una clase denominada CommonTypes.cd en un directorio 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. Agregue el ensamblado del modelo de objetos cliente:

-      C:\Archivos de programa\Archivos comunes\Microsoft Shared\Web Server Extensions\14\ISAPI\
-          Agregue  al proyecto las dos referencias de ensamblado siguientes:
o   Microsoft.SharePoint.Client.dll
o   Microsoft.SharePoint.Client.Runtime.dll

 -          Cree una clase denominada SPHelpers.cs en 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. Ahora codificaremos la interfaz de usuario, la parte del código que llamará a la clase del modelo de objetos cliente.
Agregue una cinta de opciones al proyecto:

 

 
Modifique las siguientes propiedades de la cinta de opciones:

Label: Duet Enterprise
Name: tabDuet

Agregue un botón a la cinta de opciones y modifique las siguientes propiedades: 

ControlSize: RibbonControlSizeLarge
Label: Customers
OfficeImageId: SlideMasterChartPlacehoderInsert


 6. Agregue el siguiente código a la función 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. Ejecute Visual Studio

Nota: asegúrese de ejecutar Visual Studio mediante la identidad de usuario correcta. El usuario debe ser capaz de tener el permiso de lectura adecuado en la lista de clientes.

Haga clic en la cinta de opciones de Duet Enterprise y haga clic en el botón Clientes (Customers). Verá la lista de clientes que se agregó a Microsoft Excel:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Publicado: 03/02/2011 9:00 a.m.

Esta entrada de blog es una traducción. Puede consultar el artículo original en Duet Enterprise and Excel 2010