We’ve managed to achieve some pretty impressive results by using the tools that are available out of the box with Excel 2010 and Excel Services. However, one of the areas from our demonstration scenario that we haven’t properly addressed is the requirement to dynamically convert the sales data to a range of currencies. We’ve designed our workbook to allow for such a calculation; we just haven’t done the actual calculation part yet, and there’s a good reason for that: Excel doesn’t have a function for automatically retrieving exchange rates for a stated currency. In this section, we’ll create a UDF that accepts a currency code as an input parameter. The function will then connect to a web service to retrieve a current exchange rate for the selected currency and will pass the resulting value back to Excel for use by the calculations within our workbook.
Attributes Used when Creating UDFs
UDFs are simply managed code assemblies that have been marked up using specific attributes that denote methods and classes that are callable from Excel Services. The attributes in question can be found in the Microsoft.Office.Excel.Server.Udf namespace, which is defined in the Microsoft.Office.Excel.Server.Udf.dll assembly. The following attributes are available.
This attribute is applied to a public class and is used to denote that the class may contain UDFs that can be used by Excel Services. UdfMethodAttribute This attribute can be applied to public methods and is used to indicate that the method is a UDF and can be used by Excel Services. The UdfMethodAttribute accepts a couple of properties:IsVolatile This Boolean value specifies whether or not the UDF should be considered volatile. When referring to Excel functions, specifying that a function is volatile indicates that the function should be reevaluated whenever any value in the workbook changes. The default behavior is non-volatile, which means that the function is reevaluated only when any of the parameters that are passed into it change. Generally, the default behavior is appropriate unless the UDF is reading other related data behind the scenes that may have changed as a result of activities elsewhere in the workbook.
ReturnsPersonalInformation This Boolean value determines whether the identity of the calling user can be accessed via the executing thread’s current principal. This means that where this value is set to true, details of the calling user can be retrieved as follows:
Usable Data Types Within UDFs
The .NET Framework contains many different data types, and any of these types can be used as a parameter or return value for a method. However, as you may imagine, Excel doesn’t necessarily know how to process each and every data type and can therefore use only a small subset of the types available. General speaking, only types defined in the System namespace are supported, such as String, Boolean, DateTime, and Object. Exceptions to this rule are Int64 and UInt64, which are not supported. As well as passing simple values, contiguous ranges can also be passed into a UDF as one- or two-dimensional object arrays. For example, the following two functions accept a single row and a range of cells, respectively:
Creating a UDF Using Visual Studio 2010
For most of our code samples throughout this book, we’ve made use of the SharePoint 2010 templates that are available in Visual Studio. Since a UDF is simply a managed assembly, we can create it using a basic class library project. We can then configure SharePoint to pick up the assembly from the file system whenever it is required by a workbook.
Notice a few things about this short code sample. First, the CurrencyConvertor class is marked as public and has the Udf Class attribute attached. Second, the Get Exchange Rate method is also marked as public and has the Udf Method attribute attached.Within the Ge tExchange Rate method, we create an instance of the Xurrency Soap webservice proxy class, and then we use the proxy to call the getValue method. Since getValue actually performs a currency conversion rather than simply providing the exchange rate,we’ve specified via our parameters that the method should return the value of $1 whenconverted into whatever currency code is passed into the function.
Configuring UDFs for Development
We’ve created a simple UDF using Visual Studio. We can now build the project and then move on to make the necessary configuration changes within SharePoint. As discussed earlier, configuration of Excel Services is done via the Central Administration tool.
Let’s look at how we’d configure an assembly to be picked up from the file system and how we can debug UDFs.
Using UDFs Within Excel
We can now make use of our custom function in our Excel workbook.
Using the REST API to Test a UDF
As it stands, we don’t have a user interface to switch between currency codes, but we can check that our UDF is working properly by using the REST API. In a web browser this URL is using the REST API to return the contents of the Exchange Rate named range when the CurrencyCode named range contains the value AUD. In other words, we’re using< the REST API to display the exchange rate between USD and AUD. The resulting output should be a number similar to 1.15, depending on the actual exchange rate at the time you call the API. If the number 1 is returned, this indicates that you’ve either set the Currency Code to USD (or left it blank) or an error has occurred. The value of 1 is returned because wewrapped our UDF call in an IFERROR function within Excel.
Debugging UDFs is not quite as simple as debugging other SharePoint artifacts. The UDF is called by the Excel Services service application, which runs as a separate process. Bearing this in mind, we have two options: we can either attach our debugger to all instances of the w3wp.exe process, or we can find out which process is being used to run service applications on our server.
Discovering the ProcessID Used to Run a UDF
Even though most of us will take the lazy option of attaching to all worker processes, here’s how we can find out the correct Process ID. As mentioned, Excel Services runs under a separate process. All service applications running on a server are published under the SharePoint Web Services application in Internet Information Server 7.0 (IIS7) and run within a specific application pool. Knowing the name of this application pool is important when we’re working with UDFs since we may need to recycle it to free up locked assemblies.
The returned list will be formatted as follows:
The first column, WP, identifies the type of record—in our case worker process. The second column contains the process ID, and the last column contains the application pool that is using the process. By finding the process that corresponds to our SharePoint Web Services application pool, we can determine the process ID. Note that the process ID may change between debugging sessions, but the application pool name will remain constant unless SharePoint is reinstalled.
Manually Attaching the Visual Studio 2010 Debugger
Now let’s manually attach the Visual Studio Debugger and recycle the SharePoint Web Services application pool.
You’ve seen how to debug UDFs using Visual Studio; now let’s step into our UDF code. What happens if we need to make changes to our code and recompile our assembly? You’d think it would simply be a case of clicking the Build button, but unfortunately it’s not quite that straightforward if we’ve configured SharePoint to pick up the UDF from our bin folder. The Excel Services service application will maintain a lock on the file while it’s in use, making it impossible for Visual Studio to overwrite when compiling a new version. Thankfully, this is an easy problem to fix. All we need to do is recycle the SharePoint Web Services application pool and the lock will be released. This can be done using IIS or using the command line by entering the following: appcmd recycle apppool /apppool.name:<the name of the app pool>
Configuring UDFs for Production
You’ve already learned the two ways for SharePoint to reference UDF assemblies: either directly via file path or indirectly via the Global Assembly Cache. I covered the steps required to reference an assembly via a file path in a development environment and the steps required to debug UDF assemblies. Now that development of our UDF is complete, let’s take a look at the steps required to reference our assembly via the GAC:
The resulting output will be the strong name for our assembly:
Sample Functions, Version=220.127.116.11, Culture = neutral, Public Key Token = your- token - here
We can now test using the REST API URL that we created earlier to confirm that our assembly is being correctly picked up from the GAC.
Share Point 2010 Related Interview Questions
|Web Services Interview Questions||XML Interview Questions|
|Share Point 2010 Interview Questions||ASP.NET Interview Questions|
|Share Point Administration Interview Questions||BizTalk Admin Interview Questions|
|Microsoft Office SharePoint Server (MOSS) Interview Questions||Biztalk Server Interview Questions|
|Asp Dot Net Mvc 4 Interview Questions||Biztalk Esb Toolkit Interview Questions|
|InfoPath Interview Questions|
Share Point 2010 Tutorial
The Microsoft Sharepoint 2010 Platform
Developing With Sharepoint 2010
Presentation Layer Overview
Client Object Model
Infopath Forms Services
Enterprise Content Management
User Interface Customization
Application Services Overview
Service Application Framework
Word Automation Services
Data Access Overview
Linq To Sharepoint And Spmetal
Business Connectivity Services
User Profiles And Social Data
Packaging And Deployment Model
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.