Excel Service User Defined Function in SharePoint Server 2010

Tweet

In this post, we will see how to create an Excel Service User Defined Function in SharePoint Server 2010. We will also see how to deploy and enable the UDF using Central Administration Tool. We will also test the function using Microsoft Excel 2010.

To start with, let’s create a Class Library Project using Visual Studio 2010 with the name ‘SalaryCalciUDF’–

Now let’s add a reference to the DLL file – ‘Excel Services Application UDF Framework’ as shown below –



Now rename ‘Class1’ with ‘SalaryCalculator’ and add a function called ‘CalculateGrossSalary’. Also import a namespace ‘using Microsoft.Office.Excel.Server.Udf;’. Now your class should look similar to the following –

If you check the above code, we have applied ‘[UdfClass]’ attribute to our class and ‘[UDFMethod]’ attribute to our method. If we do not apply the attribute ‘[UdfClass]’, Excel calculation service will ignore this class. The same applies to method attribute.

Now compile the assembly and you will get a ‘SalaryCalciUDF.dll’ file in the debug folder of your project. Let’s copy this DLL and paste it in a folder ‘C:\ExcelUDFs’ folder. If the folder is not created, create the

folder.

Now let’s deploy the created UDF in our shared location of Excel Service. To deploy this UDF, click on Central Administration tool and click on ‘Application Management’ link from the left navigation pane. Now from the ‘Service Applications’ group on the right hand side, click on ‘Manage Service Applications’ as shown below –

This will show you all the available services. Now click on ‘Manage Excel Services Application’ link. This will show you different options to manage the Excel Services application. I assume that you have already created a SharePoint Site. If you have not created the SharePoint site, please refer to my article ‘SharePoint Dashboard with Common Filters using PerformancePoint Services 2010’ on http://www.dotnetcurry.com/.

Now let’s add a ‘Shared Document’ library path into the trusted file location as shown below–

Click on ‘Trusted File Locations’ shown above and click on a link ‘Add trusted file location’. In the address, paste the path of ‘Shared Document library as shown below –

‘http://localhost:21068/Shared%20Documents/’

Please note that you will have put the ‘Shared Document’ library path on your machine.

Then in ‘Location Type’ choose ‘Microsoft SharePoint Foundation’. Under ‘Trust Children’ check the checkbox ‘Children Trusted’ and at the end of this page, check the check box ‘User Defined Functions allowed’ from the ‘Allow User defined Functions’ section and click  the ‘OK’ button.

The next step is to enable the UDFs. To enable the UDFs, go back to the ‘Manage Excel Services Application’ and click on ‘User Defined Function Assemblies’ link. Now click on ‘Add User Defined Function Assembly’ link.

Add the path ‘C:\ExcelUDFs\SalaryCalciUDF.dll’ file path and choose ‘File Path’ from ‘Assembly Location’. Check the checkbox ‘Assembly Enabled’ from the section ‘Enable Assembly’ and click ‘OK’ button.

Now let’s test our UDF. Let’s create an Excel Workbook and add all the data as shown below –

Now let’s add a parameter. Select the cell next to the ‘BasicSalary’. Go to ‘Formulas’ ribbon and click on ‘Name Manager’ as shown below –



Now click on ‘New’ button and in the name textbox, type ‘BS’ as shown below –



Now repeat the above steps to add name parameters till PF. Now choose a cell which is after ‘GrossSalary’ and write the following formula –

=CalculateGrossSalary(BS,HRA,TA,DA,PF)

You will see ‘#NAME?’. This function will be evaluated only when the Excel workbook is displayed in Excel Services. So let’s publish the workbook into our ‘Shared Document’ library. Before that, save the workbook on local drive. To publish the excel workbook, go to ‘File’ menu of Excel workbook and click on ‘Save and Send’ menu. It will show you ‘Save to SharePoint’ option and Publish Option button as shown below –



Now click on the button ‘Publish Options’. Click on ‘Parameters’ tab. Click on ‘Add’ button and choose all the parameters and click ‘OK’ button. Now click on ‘Save As’ button and paste the path of ‘Shared Document’ library and hit Enter. Now save your workbook with the name ‘TestUDF’ and click on ‘Save’ button.

As soon as you save the workbook in SharePoint Library ‘Shared Document’, it will open the workbook in a browser. In the parameters task pane, add the values for the parameters and click on ‘Apply’ button. You will see your gross salary like below –




Tweet