Duet Enterprise と Excel 2010



Duet Enterprise と Excel 2010

Body:
執筆者: Joyanta Sen (Microsoft France)
 
ここでは、Excel 2010 内での Duet Enterprise のリスト統合に基づいた例を手順を追って説明します。目標は、Excel 2010 スプレッドシートに顧客リストを表示することです。

準備作業

VSTO Excel アプリケーション内の外部コンテンツ タイプから外部リストを利用するには、次の作業を実行する必要があります。
  • 外部コンテンツ タイプから外部リストを作成する


Excel VSTO アプリケーション

1. Visual Studio 2010 を使用して VSTO ブック プロジェクトを作成します。

 

2. Helpers というプロジェクト フォルダーに SPHelper というヘルパー クラスを作成します。

 

3. Common ディレクトリにある CommonTypes.cd というクラス内の Customer 構造体に対応するタイプをプロジェクトに追加します。

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. クライアント OM アセンブリを追加します。

-      C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\
-            次の 2 つのアセンブリ参照をプロジェクトに追加します。
o   Microsoft.SharePoint.Client.dll
o   Microsoft.SharePoint.Client.Runtime.dll

 -          SPHelpers.cs というクラスを 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. 今度は、ユーザー インターフェイスをコーディングします。このコードの一部が、クライアント OM クラスを呼び出します。
リボンをプロジェクトに追加します。

 

 
リボンの次のプロパティを変更します。

Label: Duet Enterprise
Name: tabDuet

ボタンをリボンに追加し、次のプロパティを変更します。 

ControlSize: RibbonControlSizeLarge
Label: Customers
OfficeImageId: SlideMasterChartPlacehoderInsert

 6. 次のコードを 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. Visual Studio を実行します。

注意: 正しいユーザー ID を使用して Visual Studio を実行してください。ユーザーは顧客リストに対して適切な読み取り権限を持っている必要があります。

Duet Enterprise リボンをクリックし、[Customers] ボタンをクリックします。顧客リストが Microsoft Excel に追加されていることを確認できます。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

カテゴリ: DuetExcel Services
投稿日: 2011 年 2 月 3 日 (木曜日) 午前 9:00

これはローカライズされたブログ投稿です。原文の記事は、「Duet Enterprise and Excel 2010」をご覧ください。



Skip to main content