UKReddy SharePoint Journey: How to: Use UDF in excel services for SharePoint 2010

User-defined functions (UDFs) are custom functions that extend the calculation and data-import capabilities of Excel. Developers create custom calculation packages to provide:

a. Functions that are not built into Excel.

b. Custom implementations to built-in functions.

Users who create workbooks can call UDFs from a cell through formulas—for example, "=MyUdf(A1*3.42)"—just like they call built-in functions.

Excel Services UDFs give you the ability to use formulas in cells to call custom functions written in managed code and deployed to Microsoft SharePoint Server 2010. You can create UDFs to:

a. Call custom mathematical functions.

b. Get data from custom data sources into worksheets.

c. Call Web services from the UDFs.

Now am going to create UDFs and calling them in excel sheet.

Taking simple example like inserting student's each subject marks and calculating Total, Percentage and Grade by using UDFs.

For this follow the steps,

1) Create one Document Library to maintain all excel files for excel service.

2) Open VS2012(or VS2010) and select new project--> select Class Library Template and specify name and location.

Note: UDFs wont work for .Net 4.0.

3) By default, Class1.cs is created we can change that name of the class file.

4) Now add "Microsoft.Office.Excel.Server.Udf.dll" reference to the project.

dll location is "C:\Program Files\Common Files\Microsoft Share\Webserver Extension\14\bin\ISAPI\"

5) Add name space to the class file.

Note: Class' access modifier should be public.

6) Specify [UdfClass] attribute before class declaration and [UdfMethod] before every method.

7) Build the project.

8) Now Go to Central Administration and click on Manage server applications which is under Application Management.

9) Click on Excel server application.

10) Click on "User Defined Function Assemblies".

11) Click on Add User-Defined Function Assembly.

12) Now specify the project dll's location.(if the project not within the SharePoint need to specify network filelocation).

Now enable assembly checking assembly enabled checkbox.

Note: Adding a trusted location for excel files in not required in SharePoint 2010 as by default all locations are trusted.

Till now i have created UDF methods which can be used in excel file.

Now am going to use those UDF methods in excel file.

13) Open new excel file and add the following text fields under Column 'A'.

Subject1

Subject2

Subject3

Subject4

Subject5

Subject6

Total

Percentage

Result/Grade

14) Now we need to define name for each input cell. so we need to define names for input cells.

15) Click on the cell which is adjacent to Subject1 under Column 'B' and click on Formulas in the ribbon and click on Define name.

16) Now specify name for the input value.

(or)

16a) Click on the cell and modify cell name by click on Name of the cell which is adjacent to fx and specify name and press enter key.

17) repeat 15 and 16 for all the subjects. or repeat 16a for all the subjects.

18) Now we need to calculate total marks click on adjacent to Total cell and go to fx specify formula as"=[UdfMethod Name](cell name of subject1,.,.,,,cellname of subject6)"

i.e =TotalMarks(B3,B4,B5,B6,B7,B8)

UdfMethod signature is "public int TotalMarks(int sub1,int sub2,int sub3,int sub4,int sub5,int sub6)"

19) Now we need to calculate Percentage based on total marks, for this click on the cell which is adjacent to percentage cell and go specify the formula in fx field as "=Percentage(cell name of total)"

i.e =Percentage(B10).

20) Now displaying result/grade of a student based on percentage,for this clcik on the cell which is adjacent to Grade and specify formula as "=[UdfMethod](cell name of percentage)"

i.e =FinalResult(B12).