Extended Excel Services programming framework (translated from MSDN)

Abstract: The Excel Services Programming Framework 1.0 extends the Microsoft Office Excel 2007 and Excel Services functions to work with the following: MOSS list, Excel Service Query table, Business Reference reference, and SQL reply, and UDFS ).

Content:
Excel Services 1.0 function Overview
Create a user-defined Method Project
Sharepoint list
Write back SQL data source
Business Reference
Build and configure a UDF Library
Conclusion
Reference resources
Overview
The first version of Excel Service is used in MOSS. It provides a simple but powerful framework that can be customized to meet your business needs. However, because it is the first version, you may encounter some restrictions on the technology and features of version 1. In this article, we will discuss how you can overcome these limitations and expand the following functions built in the Excel Service solution:
Use of the Sharepoint list
Use of Excel Service to query and select tables
Create Business Reference
Development of user-defined methods compatible with Microsoft Office Excel 2007 client applications
In addition, this article also describes how to use SQL reply to further develop built-in functions.

System Requirements
To create and run the example code, you must install the following software on your computer:
Microsoft Visual maxcompute 2005
Excel 2007
Office SharePoint Server 2007
1. Create a user-defined Method Project
To start, you must create a project that contains UDFS code in Visual Studio.
1) Start Visual Studio 2005.
2) In the File menu, click New, and then click the project.
3) in the project type area-create a project dialog box, select a Windows project and use the Visual C # code.
4) in the template area, click the class library.
5) Rename the project and change it from ClassLibrary1 to XIUnlimitedUDFs.

Configure your project
Before writing code for a UDFS class library, you must modify your project configuration from the following aspects (these will be applied to any UDFs project you create ):
• Add a required reference for a UDFS Project
• Remove unnecessary code from the Project template
• Register a strong signature for the class library

Necessary references for adding a UDFS Project
When using the UDFS class library in Excel Service, Microsoft. Office. Excel. Server. Udf must be referenced to declare the class library attributes. Therefore, you must add a reference to this class and then perform the following steps.
You also need the following namespaces: Microsoft. Win32 and Syetem. Runtime. InteropServices.

Note: Before and After the steps, make sure that the class library is created on a computer with the Office SharePoint Server 2007 installed. You can find Microsoft. Office. Excel. Server. Udf. dll: \ Program Files \ Common Files \ Microsoft Shared \ Web server extensions \ 12 \ ISAPI in the following disk path.

Add a reference for the project
1. Click Add reference on the project menu.
Note: In solution view, you can right-click a reference to open the Add reference dialog box and select Add reference.
2. In the Add reference dialog box, on the. NET tab, select Excel Service UDF Ftamework.
3. Click OK.

Remove unnecessary code from the Project template
When you create a class library project, the Class1.cs file contains some code generated by default.
1) Rename the Class1.cs file to XIUnlimitedUDFs. cs.
2) In the XIUnlimitedUDFs class, use the following code to replace all the default generated code:
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 //
}
}
Note: The ClsId given in the previous sample code is just an example. If needed, you can create a GUID from the tool menu to generate a ClsId. The code lines ending with "// Client Compat" and "client compat. SECTION" are optional. If you want to run these codes in Excel 2007, they are required.

Add a strong name for your Assembly
1. In solution view, right-click the XIUnlimitedUDFs project and select Properties.
2. In the tag option, select the tag Assembly box.
In the select a strong name Key file list, select new.
1. Enter keypair as the file name, and then clear the Key file protection option with password.
2. Save your project.
SharePoint list
Now that you have completed the configuration of your project, you can write code to implement UDF, which can be called by the Excel 2007 business manual based on Excel Service. In this way, you can extend the functions of Excel Services, such as using the SharePoint list, executing SQL write back, and adding external business references.
When you write a UDF of Excel 2007 business manual based on Excel Services, a common requirement is that it must simulate the current user of the business manual that contains UDF calls. An example of simulated login is if the code you write needs to read the SharePoint list. In this case, you must use a user with the permission to access the data to log on. The following section describes how to simulate an Excel Services user by writing code.
Simulate an ExcelServices user
You must take two simple steps to simulate the current Excel Services user, as described below:
To simulate an Excel Services user,
1. Mark the following metadata in the UDF Method
C #
[UdfMethod (ReturnsPersonalInformation = true)]
Note: For security reasons, if the UDF method does not contain the metadata of the login user identity object, Excel Services does not allow.
2. Add the following reference at the top of the XIUnlimitedUDFs. cs file:
Using System. Security. Principal;
3. Implement a helper method to execute Simulation
/// <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 shocould not be impersonated.
If (System. Diagnostics. Process. GetCurrentProcess ()
. ProcessName = "EXCEL ")
{
// COM case (client-only), shocould 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 ();
}
4. Call this method in a Using lock to ensure correct disposal of the WindowsImpersonationContext object.
[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.
}
Write a UDF to access the SharePoint list
Excel Services generally does not support SharePoint connections. However, it can still obtain data from the SharePoint list through UDF and assign it to the Excel 2007 business manual. UDF calls the Windows SharePoint Services API to obtain list data, and return them to the Business Manual as an array of objects (add UDF calls as an array to Excel 2007 by using CTRL + SHIFT + ENTER ).
To retrieve data from the SharePoint list through udf to the Excel2007 business manual, you must first Add a reference to the Windows SharePoint Services API, as described below:
Add a reference to the project
1. Click Add reference on the project menu.
Note: You can also right-click the reference in solution view to open the Add reference dialog box and select Add reference.
2. In the Add reference dialog box, on the. NET tab, select Windows SharePoint Services.
3. Click OK.
Note: The above steps assume that your class library is created on a computer with Office SharePoint Server 2007 installed. You can find Microsoft. SharePoint. dll in the disk path \ Program Files \ Common Files \ Microsoft Shared \ Web server extensions \ 12 \ ISAPI.
4. Add the following reference at the top of the XIUnlimitedUDFs. cs File
Using Microsoft. SharePoint;
To write a UDF to access the SharePoint list
• Add the following method to XIUnlimitedUDFs. cs:
/// <Summary>
/// Using SharePoint API to read data from
/// 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-dimenstmobject array of list values. </returns>
[UdfMethod (IsVolatile = true, ReturnsPersonalInformation = true)]
Public object [,] getmediapointview (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-dimen1_arrays with only
// One-dimencategory 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
// Relying on the common language runtime.
If (site! = Null)
Site. Dispose ();
}
} // End impersonation.
Return toExcel;
}
View data in an Excel Services Query table
When you use the SharePoint list data connection to perform a query, the query table is very useful for displaying the returned relational data. In this version, Excel Services does not support table query, but you can use a workspace to perform most of the functions supported by Table query. In the previous example (and the example business manual included in the sample code downloaded together with this Article), when you insert data into Excel 2007 in the form of arrays, an array of data similar to the query table is returned in addition to the sorting and filtering functions. It is very easy to convert the array format into a false query table (view the downloaded sample business manual ). The following steps demonstrate how to do this.

Copy a query table in Excel Services
1. UDF data in array format, for example, the UDF sample in the SharePoint list above.
2. Follow these steps to create a table in the same worksheet.
1. Select an area with the same number of rows as the array format and with more columns
2. Press CTRL + T.
3. Mark the correct column Index
4. In an appropriate order, fill the rows occupied by the array format in the correct column, that is, from the smallest row to the largest row.
Note: Because the array format in the business manual is arranged and located by B7: D12, the Index column is filled with the following values: 7, 8, 9, 10, 11, and 12. (You may want to skip 7 because you may not want to include the column title in the table .)
5. Enter the following formula in the form element on the left at the top and paste it into another unit.
= IFERROR (INDIRECT ("R" & Table1 [[# This Row], [INDEX] & "C" & COLUMN (<TopLeftMostCellofArrayFormula>), FALSE ),"")
Note: In the preceding format, replace <TopLeftMostCellofArrayFormula> with the top left cell of your array format. In the example business manual, it is B7. (You may want to replace it with B8, you may not want to include the column title in the table in the top left Unit of the array format .)
The formula entered in step 5 performs the following functions:
• Creates an R1C1 text reference for each cell in the array format.
• Access data in those cells indirectly by hurting the text reference created above.
• Wrap data using an IFERROR method, and convert # N/A displayed in an empty array format cell to an empty string.
Write back SQL database
A common requirement that is hosted by the client VB code in Excel 2007 is that data can be written back to the SQL database and retrieved from the database. This function is more useful on the server side. For example, you may want to share and operate the same data in different Excel Services sessions under specific circumstances. Because each session has its own business manual version, you cannot simply share the business manual in Excel Services, edit a unit, and map it to other sessions. To achieve this, you can save the data in the SQL database and then read/write it through UDFS. In this way, you can share data between different sesions.
Create a UDF to write data to the SQL database
Using the SQL processing capabilities built in the. Net Framework, it is not complicated to create a UDF that writes data to the SQL database. The following steps demonstrate how to create this UDF.
1. Add the following reference to the top of the XIUnilimitedUDFs. cs file:
Using System. Data. SqlClient;

2. Add the following methods to your UDF class
Warning the following code is just an example. As insertion constraints may occur, you must pay attention to the time when the Code accesses the database. If yes, restrict the following code to accept only list data required in the solution. Do not use strings that contain commas. For more information, see SQL constraints. Although the following code provides these practical recommendations, it is still more scalable than required in most cases. You can remove some scalability to make the code more robust and secure.
/// <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
// Relying on the common language runtime.
If (connection! = Null)
Connection. Dispose ();
If (command! = Null)
Command. Dispose ();
}
} // End impersonation.
Return returnValue;
}
External Business Reference
Excel Services does not support loading business manuals with external references. You can use the Excel Web Services API to obtain data from a specific unit in the external business manual rather than directly referencing the external business manual.
To implement this function, you must reference the Excel Web Services API, write a UDF to call the business manual, and obtain data from its unit.
Note: For more information about Excel Web Services APIs, see Excel Web Services.
Reference Excel Web Services API
To add a reference to Excel Web Services, you must install Office SharePoint Server 2007 on your Server. Then, add a reference to the Excel Web Services API from the following locations: http: // server/_ vti_bin/excelservice. asmx? WSDL, where server is the name of your server.
Add a Web reference to Excel Web Services.
1. Click Add Web reference in the project menu.
Note: you can click "Reference" on the right of the solution view and select "add Web reference" to open the dialog box.
2. In the Add external reference dialog box, in the URL area, enter http: // server/_ vti_bin/excelservice. asmx? WSDL.
Note: replace server with your server name.
3. click Next.
4. Change the Web reference name to ExcelServices.
5. Click Add reference.

Write a UDF that references the external business manual
The following code demonstrates how to use UDF to call the Excel Web Services API to open an external business manual stored in a trusted location and return the referenced data.
/// <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. usedefacrecredentials = 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 cils.
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;
}
Run and publish your UDF Library
You can find the previously created example business manual that calls UDFs and the complete XIUnlimitedUDFs Visual Studio project file in the downloaded example. For more information about how to run and publish a UDF assembly to Excel Services, see Excel Services User-Defined Functions.
Note: If you use the XIUnlimitedUDFs Visual Studio project, you must replace all http: // % 3 cserver/> references with your server. This project also contains a Web reference. You must use a correct reference to your server to replace it.

Publish a UDF assembly on an Excel Client
Using UDFs on the Excel 2007 client and running Excel Services on the server is very useful. Not only can you obtain UDFs exposed functions on the client, but you can also create a UDFs business manual. Generally, when calling an unknown format, we will see # NAME? Errors, but we can see real data in the UDFs business manual.
The code used in this article includes comments like // Client Compat and client compat. SECTION. These Annotations indicate the required code lines for your UDF code to work on the client. If you copy the code word by word or use the XIUnlimitedUDFs sample Visual Studio project, you must perform the remaining steps when using UDFs on the client.
Note: For more information about using Excel Services UDFs on the Excel client, see Making Excel Services UDFs work on Excel 2007-Part 1 and Making Excel Services
UDFs work on Excel 2007-Part 2.

Run UDF code on the Excel Client
1. Use the code in this article and confirm that it contains all parts marked with client compat.
2. In solution view, right-click the XIUnlimitedUDFs project and select Properties.
3. On the generate tab, select generate registration for COM interop.
Note: If the computer you want to publish is different from the one you have generated, you can transfer the generated XIUnlimitedUDfs. dll file, and use the include.. Net Framework.
4. Save your project and generate it.
5. Open Excel 2007, select Excel from the Office button.
6. On the Add-Ins tab, click next and then click auto.
7. Find and select XIUnlimitedUDFs. XIUnlimitedUDFs as the automatic server, and then click OK.
8. If you receive a warning about mscoree. dll, select "no" and click "OK" in the Add-Ins dialog box.
Note: It is recommended to use an additional function block with control code. For more information, see
Isolating Microsoft Office Extensions with the COM Shim
Wizard Version 2.0.
9. Open a business manual that contains UDF calls or create a new one.
Conclusion
Through this article, you learned how to use the programming framework in Excel Services to overcome some restrictions in version 1 and extend the default functions implemented by Excel Services. You can also use the same framework class library to create a solution that meets your specific business needs. The powerful Excel Services framework class library and APIs allow you to easily expand Excel Services into a business manual release center and a solution platform.
About the author
Luis F. Bitencourt-Emilio is a design engineer in the Excel Services test team. His blog is LuisBE on Services.

Reference resources
For more information, see the following resources:
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 CILS 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