摘要：Excel Services编程框架1.0扩展了Microsoft Office Excel 2007和Excel Services功能，使之与下列协同工作：ＭＯＳＳ列表，Excel Service查询表，业务参考引用，ＳＱＬ回复，以及用户定义方法（ＵＤＦＳ）。
内容：
Ｅｘｃｅｌ Ｓｅｒｖｉｃｅｓ １．０ 功能概述
创建一个用户定义方法项目
Ｓｈａｒｅｐｏｉｎｔ列表
写回ＳＱＬ数据源
业务参考饮用
构建和配置一个ＵＤＦ库
结论
引用资源
概述
Excel Service的第一个版本，在ＭＯＳＳ中被使用到，提供了一个简单但却强大的框架，这个框架可以定制来满足你的业务需求。可是，因为它是第一个版本，你可能会遇到一些关于版本１的技术和特征的限制。这篇文章讨论的时你怎样能克服这些限制，以及怎样扩展以下构建在Excel Service解决方案中的功能：
对Ｓｈａｒｅｐｏｉｎｔ 列表的使用
Ｅｘｃｅｌ Service查选表的使用
创建业务参考引用
与Microsoft Office Excel 2007客户端应用程序兼容的用户定义方法开发
另外，本文还介绍了怎样用SQL 回复的方式来进行更进一步的内置功能开发。
系统要求
创建和运行事例代码，你必须在你的电脑中安装以下软件：
Microsoft Visual Studio 2005
Excel 2007
Office SharePoint Server 2007
１．创建一个用户定义方法项目
为了开始，你必须在Visual Studio中创建一个包含UDFS代码的项目。
1）启动Visual Studio 2005。
2）在File菜单，点击New，然后点击项目。
3）在项目类型区域—新建项目对话框，选择Windows项目，适用Visual C#代码。
4）在木板区，点击类库。
5）重命名项目，从ClassLibrary1 修改为XIUnlimitedUDFs。
配置你的项目
在开始为一个ＵＤＦＳ类库写代码前，你必须从以下几个方面修改你的项目配置（这些将应用于任何你创建的ＵＤＦｓ项目）：
·添加一个ＵＤＦＳ项目的必须引用
·从项目模板中移除不必要的代码
·为类库注册一个强签名
添加一个ＵＤＦＳ项目的必要引用
使用在Ｅｘｃｅｌ Ｓｅｒｖｉｃｅ中的ＵＤＦＳ类库必须引用Microsoft.Office.Excel.Server.Udf来声明类库属性。因此，你必须添加这个类的引用，然后再进行如下步骤。
你还需要以下两个命名空间：Microsoft.Win32以及Syetem.Runtime.InteropServices.
注意：之前的和后面的步骤，前提为类库创建在一个安装了Office SharePoint Server 2007的电脑上。你可以在以下磁盘路径找到Microsoft.Office.Excel.Server.Udf.dll:"Program Files"Common Files"Microsoft Shared"Web server extensions"12"ISAPI.
为项目添加一个引用
１．在项目菜单上，点击添加引用
注意：你可以在解决方案视图通过右键点击引用来打开添加引用对话框，然后选择添加引用。
２．在添加引用对话框中，在.NET选项卡，选择Excel Service UDF Ftamework.
３．点击确定。
从项目模板中移除不必要的代码
当你创建类库项目时，Class1.cs文件包含着默认被生成的一些代码。
１）将Class1.cs文件重命名为XIUnlimitedUDFs.cs.
２）在XIUnlimitedUDFs类中，用以下代码取代默认生成的所有代码：
C#
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices; // Client Compat.
using Microsoft.Win32; // Client Compat.
using Microsoft.Office.Excel.Server.Udf;
namespace XlUnlimitedUDFs
{
[UdfClass]
[Guid(XlUnlimitedUDFs.ClsId)] // Client Compat.
[ProgId(XlUnlimitedUDFs.ProgId)] // Client Compat.
[ClassInterface(ClassInterfaceType.AutoDual)] // Client Compat.
[ComVisible(true)] // Client Compat.
public class XlUnlimitedUDFs
{
// BEGIN CLIENT COMPAT. SECTION //
const string ClsId = "FE6C8D4D-3600-499a-9FA5-F8E252328CDD";
const string ProgId = "XlUnlimitedUDFs.XlUnlimitedUDFs";
[ComRegisterFunction]
public static void RegisterFunction(Type type)
{
if (typeof(XlUnlimitedUDFs) != type)
{
return;
}
RegistryKey key = Registry.ClassesRoot.CreateSubKey(
@"CLSID"{" +
ClsId +
@"}"Programmable");
key.Close();
}
[ComUnregisterFunction]
public static void UnregisterFunction(Type type)
{
if (typeof(XlUnlimitedUDFs) != type)
{
return;
}
Registry.ClassesRoot.DeleteSubKey(@"CLSID"{" +
ClsId +
@"}"Programmable");
}
// END CLIENT COMPAT. SECTION //
// BEGIN UDF SECTION //
// END UDF SECTION//
}
}
注意：在之前的示例代码中给出的ClsId仅仅是一个例子。如果需要，你可以通过选择从工具菜单中创建ＧＵＩＤ来生成一个ClsId.以”//Client Compat”结尾的代码行和”CLIENT COMPAT.SECTION”是可选得；如果你想在Excel 2007中运行这些代码，它们才是必须的。
为你的程序集添加一个强命名
１． 在解决方案视图中，右键点击XIUnlimitedUDFs项目，然后选择属性。
２． 在标记选项中，选择标记程序集框。
在选择一个强命名Ｋｅｙ文件列表中，选择新建。
１． 输入keypair作为文件名，然后清除使用密码保护我的Ｋｅｙ文件选项。
２． 保存你的项目。
SharePoint 列表
现在你已经完成了配置你的项目，你可以写实现ＵＤＦ的代码，这些代码能被基于Excel Service的Excel 2007业务手册所调用。通过这种方式，你可以扩展Excel Services的功能，例如使用SharePoint列表，执行ＳＱＬ写回，以及添加外部业务引用。
当你写一个被基于Excel Services的Excel 2007业务手册的UDF时，一个通常的要求是，它必须模拟包含UDF调用的业务手册的当前用户。一个需要模拟登陆的例子就是如果你写的代码需要读取SharePoint列表。在这种情况下，你必须使用有权限访问这些数据的用户来登陆。以下部分描述了怎样通过写代码来模拟Excel Services用户。
模拟ExcelServices 用户
你必须执行两个简单的步骤来模拟当前Excel Services的用户，如下所述：
为了模拟Excel Services 用户，
１． 向UDF方法中标记以下元数据
C#
[UdfMethod(ReturnsPersonalInformation = true)]
注意：处于安全的原因，如果UDF方法不包含这个登陆用户身份对象的元数据，Excel Services不会允许。
２． 在XIUnlimitedUDFs.cs文件的顶部，添加如下引用：
C#
using System.Security.Principal;
３． 实现一个helper方法来执行模拟
C#
/// <summary>
/// Returns the WindowsImpersonationContext of the current user.
///</summary>
private WindowsImpersonationContext impersonateUser()
{
WindowsIdentity wi = null;
try
{
// BEGIN CLIENT COMPAT. SECTION
// Check if code is not running on the server.
// If true, then it is running through a COM interface
// on the client and should not be impersonated.
if (System.Diagnostics.Process.GetCurrentProcess()
.ProcessName == "EXCEL")
{
// COM case (client-only), should not impersonate.
return null;
}
// END CLIENT COMPAT. SECTION
// Get identity of user who loaded the workbook.
wi = (WindowsIdentity)
System.Threading.Thread.CurrentPrincipal.Identity;
}
catch (System.Exception ex)
{
throw new InvalidOperationException(
"An error has occurred.");
}
// Impersonate user and return the context.
return wi.Impersonate();
}
４． 在一个Using锁中调用这个方法来确保WindowsImpersonationContext对象的正确处置。
C#
[UdfMethod(ReturnsPersonalInformation = true)]
public void sampleUDF()
{
// Begin user impersonation through helper function.
using (WindowsImpersonationContext wiContext =
impersonateUser())
{
// Any code in this block impersonates the user.
}
// No longer impersonating the user.
}
写一个UDF来访问SharePoint列表
Excel Services通常不支持对SharePoint的连接。尽管如此，它仍然可以通过UDF从SharePoint 列表中取得数据赋入Excel 2007业务手册中。UDF调用Windows SharePoint Services API来取得列表数据，并将它们以对象数组的形式返回给业务手册（通过使用CTRL+SHIFT+ENTER来将作为数组形式的UDF调用加入到Excel 2007中）。
为了能通过ＵＤＦ从SharePoint列表中取得数据到Ｅｘｃｅｌ２００７业务手册中，你必须首先添加对Windows SharePoint Services API的引用，如下所述：
添加一个引用到项目中
１． 在项目菜单上，点击添加引用
注意：你也可以通过在解决方案视图中右键点击引用来打开添加引用对话窗，然后选择添加引用。
２． 在添加引用对话框中，在.NET选项卡上，选择Windows SharePoint Services.
３． 点击确定
注意：以上的步骤是假定你的类库创建在一台安装了Office SharePoint Server 2007的电脑上。你可以在磁盘路径"Program Files"Common Files"Microsoft Shared"Web server extensions"12"ISAPI找到Microsoft.SharePoint.dll.
４． 在XIUnlimitedUDFs.cs文件的顶部，添加如下引用
C#
using Microsoft.SharePoint;
为了写一个ＵＤＦ来访问SharePoint列表
· 向XIUnlimitedUDFs.cs中添加如下方法
C#
/// <summary>
/// Using SharePoint API to read data from a
/// SharePoint list and return it as an object array.
/// </summary>
/// <param name="serverName">Server name.</param>
/// <param name="siteName">SharePoint site name.</param>
/// <param name="listName">SharePoint list name.</param>
/// <param name="viewName">View name of the SharePoint list.</param>
/// <param name="getTitles">Include column titles.</param>
/// <returns>Two-dimensional object array of list values.</returns>
[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
public object[,] getSharepointView(string serverName,
string siteName,
string listName,
string viewName,
bool getTitles)
{
object[,] toExcel = null;
// Impersonate current user viewing Excel Web Access.
using (WindowsImpersonationContext wiContext = impersonateUser())
{
SPSite site = null;
try
{
// Get Sharepoint objects.
site = new SPSite(serverName);
SPList list = site.AllWebs[siteName].Lists[listName];
SPView view = list.Views[viewName];
// Grab collection of Sharepoint list values.
SPListItemCollection values =
list.GetItems(new SPQuery(view));
// Add a row for the titles if requested.
int titleRow = getTitles? 1 : 0;
int totalRows = values.Count + titleRow;
// Avoid two-dimensional arrays with only
// one-dimensional of data
// by adding an empty row if needed.
if (totalRows == 1)
totalRows++;
// Create an object array to return to Excel.
toExcel = new object[totalRows, view.ViewFields.Count];
// Add column titles to array if needed.
if (getTitles)
{
for (int i = 0; i < view.ViewFields.Count; i++)
{
toExcel[0, i] = list.Fields.
GetFieldByInternalName(view.ViewFields[i]).Title;
}
}
int j = titleRow;
// Iterate through SPListItems in the view.
foreach (SPListItem currVal in values)
{
// Add every field value of current item to array.
for (int i = 0; i < view.ViewFields.Count; i++)
{
// Get current field.
SPField field = currVal.Fields.
GetFieldByInternalName(view.ViewFields[i]);
// Get field value as a text string.
toExcel[j, i] = field.GetFieldValueAsText(
currVal[field.Id]);
}
j++;
}
}
catch(System.Exception ex)
{
object[,] error = new object[1, 1];
error[0,0] = "An error has occurred.";
toExcel = error;
}
finally
{
// Dispose of SharePoint objects instead of
// relying on the common language runtime.
if (site != null)
site.Dispose();
}
} // End impersonation.
return toExcel;
}
在一个Excel Services的查询表中查看数据
当你用SharePoint列表数据连接来执行一次查询的时候，查询表对展示你返回的关系数据非常有用。在这个版本中，Excel Services不支持查询表，但是你可以通过一个工作区来实现查询表的支持的大部分功能。在之前的示例中（以及在与本文一起下载的示例代码中包含的示例业务手册），当你用数组的形式将数据插入到Excel 2007中，一个结构上与查询表类似的数据数组就被返回，除了排序及筛选功能。将这种数组格式转换为一个假的查询表是非常简单的（查看下载的示例业务手册）。以下步骤演示了怎样做到这些。
在Excel Services中仿效一个查询表
１．有数组格式的ＵＤＦ数据，例如上面的SharePoint 列表UDF示例。
２．通过以下步骤在同一个工作表中创建一个表。
１．选择一个和数组格式行数相同列数多一的区域
２．按CTRL+T.
３．标记正确的列索引
４．以恰当的顺序，将数组格式占据的行填充到正确的列中，也就是说，从最小行到最大行。
注意：因为在业务手册中数组格式通过B7:D12排列定位，所以用以下数值:7,8,9,10,11,12来填充Index列。（你可能想跳过７，因为这行是你可能不想在表中包含列标题。）
５．在顶端左侧的表单元中输入以下公式，然后将它粘贴到其它单元中。
=IFERROR(INDIRECT("R" & Table1[[#This Row],[INDEX]] & "C" & COLUMN(<TopLeftMostCellofArrayFormula>), FALSE), "")
注意：在上面的格式中，用你的数组格式的顶端左侧单元来取代<TopLeftMostCellofArrayFormula>,在示例业务手册中，它是B7.( 你可能想使用B8替换，因为在数组格式的顶端左侧单元中是你可能不想在表中包含列标题。)
在第五步中输入的公式执行了以下的功能：
· 为数组格式的每一个单元格创建了一个R1C1文本引用。
· 通过伤上面创建的文本引用间接的访问那些单元格中的数据。
· 通过一个IFERROR方法来包装数据，将在空的数组格式单元格中看到的#N/A转化为一个空的字符串。
写回SQL 数据库
通过与在Excel 2007的客户端ＶＢ代码托管的一个通常要求是能写回到SQl 数据库中，也能从之都取数据。这个功能在服务器端更加有用。例如，你可能会想在特定情况下不同的Excel Services sessions能共享和操作同样的数据。因为每一个session都有它自己的业务手册版本，所以不能简单的通过在Excel Services中共享业务手册并编辑一个单元再映射到其它sessions来实现。要实现这个情况，你可以在SQL 数据库中保存数据，然后通过UDFS读／写它。这样就能让你在不同的sesions之间共享数据。
创建一个ＵＤＦ来写入SQL 数据库
利用内置在.Net Framework的ＳＱＬ处理能力，创建一个写入SQL 数据库的ＵＤＦ并不复杂。以下的步骤演示了怎样创建这个UDF.
１．在XIUnilimitedUDFs.cs文件的顶部，添加如下引用：
C#
using System.Data.SqlClient;
２．向你的UDF类中添加以下方法
警告：我们提供的以下代码仅仅作为一个示例。由于有可能会产生插入约束，所以一定要注意代码在何时访问数据库。如果可以，限制以下的代码为仅接受在解决方案中需要的列表数据，不要使用限制逗号的字符串。查看更多信息，请参阅SQL约束。尽管以下的代码有这些实用建议，在大多数情况下它仍然比要求的更具有可扩展性。你可以通过移除一些可扩展性来使这些代码更加健壮安全。
C#
/// <summary>
/// Write data to a SQL database.
/// </summary>
/// <param name="serverName">SQL Server name.</param>
/// <param name="databaseName">Database name.</param>
/// <param name="tableName">Table name.</param>
/// <param name="columnNames">Column names (comma separated).</param>
/// <param name="values">Values (comma separated).</param>
/// <returns>Status string.</returns>
[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
public string writeToSql(string serverName,
string databaseName,
string tableName,
string columnNames, // Comma delimited.
string values) // Comma delimited.
{
String[] restricted = { ";", "--", "/*", "*/", "xp_" };
String[] checkRestriction = { tableName, columnNames, values };
string returnVal;
// Be restrictive about accepted inputs.
foreach (String currCheck in checkRestriction)
{
foreach (String currRestricted in restricted)
{
if (currCheck.Contains(currRestricted))
{
return "An error has occurred";
}
}
}
// Escape quotes.
tableName = tableName.Replace(""'", ""'"'");
columnNames = columnNames.Replace(""'", ""'"'");
columnNames = columnNames.Replace("""", """""");
values = values.Replace(""'", ""'"'");
// Impersonate current user.
// (see earlier section on user impersonation)
using (WindowsImpersonationContext wiContext = impersonateUser())
{
SqlConnection connection = null;
SqlCommand command = null;
try
{
// Build connection string.
string connectionString =
"Integrated Security=SSPI;" +
"Persist Security Info=True;Initial Catalog=" +
databaseName +
";Data Source=" +
serverName +
";";
// Connect to SQL database.
connection = new SqlConnection(connectionString);
connection.Open();
// Use sp_executesql to avoid SQL injection attacks.
command = new SqlCommand("sp_executesql", connection);
command.CommandType =
System.Data.CommandType.StoredProcedure;
// Comma-separated string of values from parameter.
string rowValues = ""'" +
values.Replace(",", ""',"'") +
""'";
// Comma-separated string of values from parameter.
string columnValues = """" +
columnNames.Replace(",", """,""") +
"""";
// Prepare statement to insert row in SQL database.
string parameter = "insert into " +
tableName +
" (" +
columnValues +
")" +
" values (" +
rowValues +
")";
// Execute command.
command.Parameters.AddWithValue("@statement", parameter);
command.ExecuteNonQuery();
// Close connection.
connection.Close();
returnValue = "Success!"
}
catch (System.Exception ex)
{
returnValue = "An error has occurred.";
}
finally
{
// Dispose of SQL objects instead of
// relying on the common language runtime.
if (connection != null)
connection.Dispose();
if (command != null)
command.Dispose();
}
} // End impersonation.
return returnValue;
}
外部业务引用
Excel Services不支持加载具有外部引用的业务手册。你可以通过使用Excel Web Services API取得外部业务手册中的特定单元里的数据而不是直接引用外部业务手册，来克服这个限制。
为了实现这个功能，你必须引用Excel Web Services API,写一个UDF来调用业务手册，并从它的单元里取得数据。
注意：获得更多的关于Excel Web Services API的信息，请参阅Excel Web Services.
引用Excel Web Services API
要向Excel Web Services添加一个引用，你必须安装在你的服务器上安装Office SharePoint Server 2007。然后，从以下位置添加一个对Excel Web Services API的引用：http://server/_vti_bin/excelservice.asmx?WSDL，其中server是你服务器的名字。
向Excel Web Services添加一个Web引用。
１．在项目菜单中，点击添加Web引用。
注意：你可以通过在解决方案视图中右肩点击引用选择添加Web引用来打开对话框。
２．在添加外部引用对话框中，在URL区域，输入http://server/_vti_bin/excelservice.asmx?WSDL.
注意：用你服务器的名字来取代server.
３．点击下一步。
４．将Web引用名字改为ExcelServices.
５．点击添加引用。
写一个引用外部业务手册的ＵＤＦ
以下代码示范了你怎样通过使用ＵＤＦ调用Excel Web Services API来打开一个保存 在可信位置的外部业务手册，并返回引用的数据。
C#
/// <summary>
/// Using the Excel Services user-defined functions API
/// to read data from an external workbook
/// and return it as an object.
/// </summary>
/// <param name="workbookPath">Path to the workbook.</param>
/// <param name="sheetName">Sheet name.</param>
/// <param name="range">Sheet range.</param>
/// <returns>Object containing cell contents.</returns>
[UdfMethod(IsVolatile = true, ReturnsPersonalInformation = true)]
public object externalRef(string workbookPath,
string sheetName,
string range)
{
ExcelServices.Status[] status = null;
string sessionId = null;
object cellValue = null;
// Initialize Excel Web Services.
ExcelServices.ExcelService es = new
ExcelServices.ExcelService();
es.UseDefaultCredentials = true;
// Impersonate the current user viewing Excel Web Access.
using (WindowsImpersonationContext wiContext =
impersonateUser())
{
// Open the workbook – this loads the workbook
// that was saved to the SharePoint document library
// and returns a sessionId to use in API calls.
try
{
sessionId = es.OpenWorkbook(workbookPath,
"en-US",
"en-US",
out status);
// Retrieve the data from the referenced cell.
cellValue = es.GetCellA1(sessionId,
sheetName,
range,
true,
out status);
}
catch (System.Exception ex)
{
return "An error has occurred.";
}
}
return cellValue;
}
运行并发布你的ＵＤＦ库
你可以在下载的示例中找到先前创建的调用了ＵＤＦｓ的示例业务手册，以及完整的XIUnlimitedUDFs Visual Studio项目文件。获得更多关于怎样运行并发布ＵＤＦ程序集到Excel Services，参阅Excel Services User-Defined Functions.
注意：如果你使用XIUnlimitedUDFs Visual Studio项目，你必须用你的服务器替代所有的http://<server>引用。这个项目也包含了一个Web引用，你必须使用对你的服务器的正确引用来替代它。
在Excel 客户端发布UDF程序集
在Excel 2007客户端使用你的UDFs而在服务器运行Excel Services是非常有用的。不仅在客户端你能得到UDFs暴露出来的功能，而且可以创建一个使用UDFs的业务手册，通常当调用未知格式时候我们会看到# NAME?错误，而在使用UDFs的业务手册中我们能看到真正的数据。
在本文中使用的代码有类似//Client Compat和CLIENT COMPAT.SECTION的注释。这些注释指出了要让你的UDF代码工作在客户端上而要求的代码行。如果你是逐字拷贝了这些代码，或者使用XIUnlimitedUDFs示例Visual Studio项目，在将UDFs使用在客户端你还必须执行一些剩余的步骤。
注意：获得更多关于在Ｅｘｃｅｌ客户端使用Excel Services ＵＤＦｓ的信息，参阅Making Excel Services UDFs work on Excel 2007 - Part 1 和Making Excel Services
UDFs work on Excel 2007 - Part 2.
让ＵＤＦ代码运行在Ｅｘｃｅｌ客户端
１．使用本文中的代码，并确认包含了标记了CLIENT COMPAT的所有部分。
２．在解决方案视图中，右键点击XIUnlimitedUDFs项目，然后选择属性。
３．在生成选项卡中，选择为COM interop生成注册。
注意：另外一个选择是，如果你要发布的电脑与你生成的电脑不同，你可以转移生成的XIUnlimitedUDfs.dll文件，并使用包含.Net Framework的ＲｅｇＡｓｍ工具来注册它。
４．保存你的项目，然后生成它。
５．打开Ｅｘｃｅｌ ２００７，从Office按钮上，选择Excel .
６．在Add-Ins选项卡上，点击下一步，然后点击自动。
７．找到并选择XIUnlimitedUDFs.XIUnlimitedUDFs作为自动的服务器，然后点击确定。
８．如果你收到关于mscoree.dll的警告，选择不，然后再Add-Ins对话框中点击确定。
注意：使用一个具有控制代码的附加功能块是被推荐的。关于更多信息，参阅
Isolating Microsoft Office Extensions with the COM Shim
９．打开一个包含UDF调用的业务手册，或者新建一个。
结论
通过这篇文章，你学到了怎样在Excel Services中通过使用编程框架来克服版本１中的一些限制，并扩展了Excel Services默认实现的功能。你也可以使用同样的框架类库创建满足你特定业务需求的解决方案。强大的Excel Services框架类库和ＡＰＩ使你能够方便的将Excel Services扩展成一个业务手册发布中心及称为一个解决方案平台。
关于作者
Luis F.Bitencourt-Emilio是Excel Services测试组里的设计工程师。他的博客是LuisBE on Services。
引用资源
获得更多信息，请参阅以下资源：
Data-Type Handling with Excel Services User-Defined Functions
● Developing User-Defined Functions for Excel 2007 and Excel Services
● Creating Custom Solutions with Excel Service
● Excel Services User-Defined Functions
● Excel Services Technical Overview
● Data-Type Handling with Excel Services User-Defined Functions
● Using Excel Web Services in a SharePoint Web Part
● Deploying and Optimizing a SharePoint Web Part That Calls Excel Web Services.
● Microsoft Office Developer Center: Excel Developer Portal
● Microsoft Office Developer Center: SharePoint Server 2007 Developer Portal
● Blog: LuisBE on Services
● Blog: Microsoft Excel 2007 and Excel Services
● Blog: Cum Grano Salis
