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).

21) Now publish this file to SharePoint's document library i.e Excel Service Doc.

Click on Office symbol which on top right corner , publish-->Excel services.

22) Open SharePoint's Document library Excel Service Doc .

23) Click on Excel Service Options,Select parameters tab and click on Add button

24) Now select All checkboxes. and click on "OK".

25) It will automatically open excel file in browser. First look of the file is as

26) Now specify all subject marks and click on "Apply".

27) Total,Percentage and Grade is calculated in UDF dll and display results in cells.

Download complete source code from here.

And also get excel file from here.

Feel free to drop me a mail if you don't get it working.

Error Type: Exception has been thrown by the target of an invocation.

I have created one chart web part and configured with excel service and successfully displayed data on webpart.

After sometime i reopened my SharePoint site and then suddenly am getting the following error.

 Exception has been thrown by the target of an invocation.

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.Office.Excel.Server.WebServices.ExcelServerApiException: An error has occurred. at Microsoft.Office.Excel.Server.WebServices.ApiShared.ExecuteServerSessionMethod(Boolean hasSessionId, String sessionId, CoreServerSessionMethod coreWebMethod, String name) at Microsoft.Office.Excel.Server.WebServices.ExcelService.GetRangeA1(String sessionId, String sheetName, String rangeName, Boolean formatted, Status[]& status) --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.Office.Server.Internal.Charting.Data.ExcelWebService.GetRangeA1(String sessionId, String sheetName, String rangeName, Boolean formatted) at Microsoft.Office.Server.Internal.Charting.Data.DataSourceExcelService.get_Data() at Microsoft.Office.Server.WebControls.ChartDataBinding.GetEnumerableData(Chart designtimeChart, Int32 index) at Microsoft.Office.Server.WebControls.ChartDataBinding.Bind(Chart designtimeChart, Chart renderChart, Int32 index) at Microsoft.Office.Server.WebControls.ChartWebPart.BindData(Chart designtimeChart, Chart renderChart) at Microsoft.Office.Server.WebControls.ChartWebPart.PrepareRenderChart(Chart designtimeChart, Chart renderChart, Boolean initialize)

And also am unable to reconfigure through Data & Appearance....

Go into the server running the excel services.

and reset IIS.

Then my problem got resolved. But actual reason for this is do not know. if anyone knows let me know about it pls.

Excel Services Rest API in SharePoint 2010 example

1. Create sample excel file for sales report.

Get my excel sheet from here.

2. Now upload this file to document library here am uploading to SalesReports document library.

3. Now you can use REST API to get data and charts from excel file.
The following steps shows how to write URLs to get excel file data and charts using REST api and after that how to display excel file Charts in SharePoint. 

Basic URL looks like this:

http://<servername>:<portnumber>/_vti_bin/ExcelRest.aspx/<documentlibrary where excel file is available>/<Name of the excel file>/model

Here my URL

http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/

and will get output like 

4. Now i want to know what are the tables available in my excel sheet and you can know  table names(see image). 

URL for that one is:
http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Tables

5. Now i want to display table.

URL for that one is:
http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Tables('SalesTable')

NOTE:
aslo we can change tables URLs output format to atom but not for charts
http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Tables('SalesTable')?$format=atom

but default format for tables is html

http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Tables('SalesTable')?$format=html

and

http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Tables('SalesTable')

both the URLs are same.
6. Now i wanna know what are the charts available in my excel file. for that URL is 

http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Charts

7. Now i want to get charts.
In the excel file  have two charts

I. for first chart, use the following URL
http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Charts('AllCitiesChart').

II. for second chart, user the following URL

http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Charts('PivotChart4SalesReport').

8. Using REST API we can get particular range of cell values,
now i wanna to get data from excel based on range of cell values

Cells range is B5 to E5

now the URL for getting the values is:

http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Ranges('Sheet1!B5|E5')

and the output is:

9. Now i wannt to get complete table data, for this URL is:

http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Ranges('Sheet1!B4|E9')

and OutPut is:

Till now we are used REST API for getting excel file data to show in browser.

Now i want to display charts in SharePoint.
9. Open your SharePoint site in browser. Add image viewer web part which is Available in "Media and Content" category.

Click on open the tool pane , it will open image viewer properties panel in that paste your REST API URL(which in Step 7.I).

And change Image viewer webpart Title and click on OK and save the page.

Note:
Like this you can add another chart in another Image view web part because default format for the chart is Image.

http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Charts('AllCitiesChart')
and 
http://demoshare:6969/_vti_bin/ExcelRest.aspx/SalesReports/SalesReport.xlsx/model/Charts('AllCitiesChart')?$format=image

both the URL are same.

Thats it..

pls let me know any queries or suggestions.

Will update more on excel service... very soon.

How to create custom timer job in SharePoint 2010

Timer jobs are executable tasks that run on one or more servers at a scheduled time.They can be configured to run exactly one time, or on a recurring schedule. They are similar to task scheduler in windows and  Microsoft SQL Server Agent jobs, which maintain a SQL Server installation by backing up databases, defragmenting database files, and updating database statistics. SharePoint uses timer jobs to maintain long-running workflows, to clean up old sites and logs, and to monitor the farm for problems. The Windows SharePoint Services Timer service runs the timer jobs in your farm. The service must be enabled and running on each server in your farm. The service enables the various SharePoint timer jobs to configure and maintain the servers in the farm. If you stop the Windows SharePoint Services Timer service on a server, you also stop all SharePoint timer jobs running on that server.

Here i want to create a sample timer job i.e like for every 5 minutes i wanna to add list item to list. Complete code available at the end of the post.

1. Create one sample list i.e ListTimerJob.

2. Now Open visual studio 2010 or 2012 RC, click on new project.

3. In new project window, select 2010 under SharePoint and select SharePoint 2010 project. Name the project as "SP_CustomTimerJob".

4. Specify SharePoint portal address for debugging location and select Deploy as a farm solution.

5. Right click on project and add class 'ListTimerJob.cs'.

6. now add Microsoft.SharePoint & Microsoft.SharePoint.Administrator namespace to 'ListTimerJob.cs'.

and inherit 'SPJobDefinition'.Implement 3 different types of constructors and override Excute method.

7. Now add feature to the project.

here u can change feature name and set scope of the feature.

8. Now right click on Feature and add Event Receiver.

9. Add Microsoft.SharePoint.Administrator namespace to the project.

Uncomment FeatureActivated and FeatureDeactivating methods in EventReceiver of the feature.

Now add your action code in Feature activated like specifying job definition and schedule time.

10. Now deploy the project.

11. Now you can open ListTimerJob list , for every five minutes one list item adding with system data time as Title.

we can see our newly added timer job definition in central administration.

a. open central administration , click on "monitoring".

b. Lick on Review job definition under TimerJobs.

c. Now you can see our ListTimerJob along with associate web application and schedule interval.

Finally list will automatically adds one new item for every 5 minutes. this is sample example like this we can write any sort of code which will automatically runs.

i will update new type of examples on timer job shortly.. Thank you..


Complete code available here