Share this page

Sunday, November 2, 2008

Webcast ExcelServices Custom Functions – 24 Moss - MOSSCA

On Thursday 23 October we were with my friend and colleague Juan Andrés Valenzuela dictating the second part of Webcast on Excel Services in SharePoint.
In this Webcast we were showing as we can do custom functions for Excel Services using C# and we can consume external resources connecting to a database to extract information and use it in a Excel in Excel Service.
In this example we used Visual Studio 2008 to develop it, but we can use Visual Studio 2005 because we need to do an assembly and also we need referenced an assembly of SharePoint called “Microsoft.Office.Excel.Server.Udf" and is installed in the following path “drive:\Program Files\Microsoft Shared\Web Server \you 12 ISAPI.
We must use Microsoft Office SharePoint Server not Windows SharePoint Service to develop our custom functions.
Let's start, the first thing we do is to open the Visual Studio and create a new class library project with our name. Once the project is generated, we will go to configure it, created the base namespace, in my case it call “Siderys.Blog.ExcelServices.Functions” and the name of the Assembly that we want, in my case it call “Siderys.Blog.ExcelServices.Functions.CustomFunctions. We must sign our Assembly with a strong name so that we can install it in our Global Assembly Cache (GAC), in image 1 we can see the option of the project properties that allows us to do this.

[Image 1] 

Visual Studio Properties

Now we are going to develop our functions. We must place a class or rename the class created by Visual Studio, in my case the name is “customfunctions ”. Once the class is created, we must put an attribute of class named “udfclass”. This attribute stereotyped to the class as a class that implements custom functions.The second step is develop the methods that we want to expose as custom functions, each method that we want to use as a custom function we must put an attribute named “UDFMethod”.
In section 1 we will see the complete code for our two custom function that connect to SQL Server 2005 to extract information and return a result.

[Section 1]

using System;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;
namespace Siderys.Blog.ExcelServices.Functions
{
   [UdfClass]
    public class CustomFunctions
    {
       [UdfMethod()]
        public double GetTipoDeCambio(string pDia)
        {
            string lStrCommand = "SELECT ValorCompra, VarlorVenta FROM CotizacionMoneda WHERE (Fecha = '" + pDia + "')";
            SqlConnection lConnection = null;
            SqlCommand lCommand = null;
            SqlDataReader lDataReader = null;
            double lRetorno = 0;
            try
            {
                lConnection = new SqlConnection("Data Source=lanouse;Initial Catalog=WebCastExcelServices;Integrated Security=True");
                lCommand = new SqlCommand(lStrCommand, lConnection);
                lConnection.Open();
                lDataReader = lCommand.ExecuteReader();
                if (lDataReader.Read())
                {
                    double lCompra = Convert.ToDouble(lDataReader["ValorCompra"]);
                    double lVenta = Convert.ToDouble(lDataReader["VarlorVenta"]);
                    lRetorno = (lCompra + lVenta) / 2;
                }
            }
            catch (Exception ex)
            {
                WriteExceptionEventViwer(ex);
            }
            finally
            {
                lDataReader.Close();
                lConnection.Close();
            }
            return lRetorno; 
        }
        [UdfMethod()]
        public double GetPrecioProducto(string pNombreProducto)
        {
            //return 10;
            string lStrCommand = "SELECT * FROM Productos WHERE Nombre = '" + pNombreProducto + "'";
            SqlConnection lConnection = null;
            SqlCommand lCommand = null;
            SqlDataReader lDataReader = null;
            double lRetorno = 0;
            try
            {
                lConnection = new SqlConnection("Data Source=lanouse;Initial Catalog=WebCastExcelServices;Integrated Security=True");
                lCommand = new SqlCommand(lStrCommand, lConnection);
                lConnection.Open();
                lDataReader = lCommand.ExecuteReader();
                if (lDataReader.Read())
                {
                    lRetorno = Convert.ToDouble(lDataReader["Precio"]);
                }
            }
            catch (Exception ex)
            {
                WriteExceptionEventViwer(ex);
            }
            finally
            {
                lDataReader.Close();
                lConnection.Close();
            }
            return lRetorno;
        }
        private void WriteExceptionEventViwer(Exception ex)
        {            
            EventLog myLog = new EventLog();
            myLog.Source = "Application";
            myLog.WriteEntry(ex.Message, EventLogEntryType.Error,1);            
        }
    }
}


Once our functions were developed we are going to compile our Assembly and put it in the Global Assembly Cache (GAC). To install the Assembly we have 2 ways, the configuration tool of Microsoft.NET or we run the command “Assembly” in our command line. In our case we are going to run the command “Assembly” and as we see in the image 2 the GAC is opened and shows all the assemblies in our server.



[Image 2] 



2_Assembly_Sistema_Operativo



We need load our Assembly in the GAC then we will use drag and drop to put the assembly into the GAC. When our Assembly was installed we are going to search it and will open their properties. We will need to know the strong name, in image 3 we have the Properties window of our Assembly.



[Image 3] 



 3_Assembly_Strong_Name



Well we have the strong name of our Assembly, we are going to install it on Excel Services , in our case the strong name of the assembly is “Siderys.Blog.ExcelServices.Functions.CustomFunctions, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=711eed342842acee” .

We opened the central administration and our service provider. As we can have more than one, we will do these steps for all shared services provider that we want install our custom functions.


In the Image 4 we can see the section dedicated to Excel Services in our shared service provider in which we must press the link “User-defined functions assemblies”.



[Image 4] 



4_ExcelServices



Once in this section, we will select the link “Add User-Defined Function Assembly” on the top of the screen and the new window is loaded with all information that we need set for our Assembly, as we can see in the Image 5. In the section Assembly must load the strong name if the option is GAC (by default), if we select "file path" must set the path from a network location or hard disk.



[Image 5] 



5_Configuracion_ExcelServices



When finish to configured should see a pages as show in the image 6. We must do a IISRESET to load the new Assembly installed in the GAC.



[Image 6] 



6_Lista_ExcelServices



We will open an Excel workbook to use our formulas. Our custom function will be executed in the server, on the client not working because we must install them on each machine. It is not a problem because once you finish to load the custom function on the Excel, we will proceed to publish it on the Excel Services. In the Image 7 we can see the custom functions in our Excel.



[Image 7] 



7_formulasExcel



As our functions are using parameters, a date and the name of a product, we must define these for the publication in Excel Services. We will select the cell that we are using in the Excel to pass values to functions in our case are “D2" and "D3" and go to the section formulas, here select “define name” and a new window is opened where we will must set the name of the two parameters, “fechatc” to the cell used to pass a date and “ProductName” to the cell used to pass the name of the product.

Finally we must publish our Workbook in Excel services of SharePoint Server. We are going to the main menu and we select “Publish”. A new window is open as “Save As” and must set of the parameters created into of the Excel Services. In the image 8 we see as we can access to the options and in the image 9 as we set the parameters for the publication pressing the button “Add ”.



[Image 8] 



8_Publicar 






[Image 9] 



9_Publicar_Parametros



Once configured the parameters we will need publish the Excel. We are going to the document library where we want to publish (remember that library must be configured as a trusted site within Excel Services) and press the button “Save”. In the Image 10 we can see the document library in our SharePoint.



[Image 10] 



10_publicando_ExcelServices




When the publication finish our Excel will be open within Excel Services and we can see as our custom functions were executed. In the Image 11 we see the Excel workbook loaded and custom functions running.



[Image 11] 



11_Excel_ExcelServices




If you want to see the Webcast (in Spanish) please enter here









Fabián Imaz

No comments: