Duet Enterprise与Excel 2010

[原文发表地址] Duet Enterprise and Excel 2010

[原文发表时间] 2/3/2011 7:08 AM

下面是一个示例的分步说明。该示例基于集成在Excel2010内的Duet Enterprise列表。示例的目的是在Excel2010电子表格中显示Customer列表。

准备步骤

要在VSTO Excel应用程序中通过外部内容类型(External Content Type)使用外部列表(External List),你需要进行以下操作:

· 根据外部内容类型创建一个外部列表;

clip_image002

clip_image004

clip_image006

clip_image008

Excel VSTO 应用程序

1. 用Visual Studio 2010创建一个VSTO Workbook项目:

clip_image010

2. 在项目文件夹Helpers内创建一个名为SPHelper的辅助类:

clip_image012

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. 添加Client OM程序集:

– C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\

在项目内引用以下两个程序集:

o Microsoft.SharePoint.Client.dll

o Microsoft.SharePoint.Client.Runtime.dll

在Common下创建名为SPHelpers.cs的类:

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. 现在我们将编码实现用户界面,其中部分代码将调用Client OM类。

往项目内添加一个Ribbon

clip_image014

更改下面这些Ribbon的属性:

Label: Duet Enterprise

Name: tabDuet

往Ribbon上添加一个按钮,更改其属性如下:

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

注意:请确保Visual Studio以正确的用户身份运行。用户必须有读取Customers列表的权限。

点击Duet Enterprise Ribbon,然后点击 Customers按钮,你就会看到customers列表被加到Microsoft Excel中去了:

clip_image016

发表评论