WebCast ExcelServices Formulas Personalizadas – 24 Moss – MOSSCA

El jueves 23 de Octubre estuvimos con mi amigo y colega Juan Andrés Valenzuela dictando la segunda parte del WebCast sobre Excel Services en Sharepoint. En esta oportunidad estuvimos profundizando algunos puntos que en el Webcast pasado estuvimos viendo.

En esta oportunidad mostramos como podemos hacer formulas personalizadas para Excel Services utilizando código C# y como en las mismas además podemos consumir recursos externos, en este ocasión estuvimos conectándonos a una base de datos para extraer información y mostrarla en un Excel publicado en Excel Service.

En este ejemplo, utilizamos Visual Studio 2008 para desarrollar las mismas, pero vale la pena destacar que podemos usar perfectamente Visual Studio 2005, ya que lo único que necesitamos hacer es un Ensamblado, proyecto de biblioteca de clases, y referenciar un ensamblado en particular que dispone Sharepoint para realizar las formulas, que se llama “Microsoft.Office.Excel.Server.Udf” y se encuentra en la siguiente ruta “drive:Program FilesCommon FilesMicrosoft SharedWeb server extensions12ISAPI”, donde instalamos Sharepoint. Vale la pena destacar que acá debemos usar Microsoft Office Sharepoint Server y no Windows Sharepoint Services.

Entonces, vamos a comenzar, lo primero que vamos hacer es abrir el Visual Studio y crear un nuevo proyecto de biblioteca de clases como les comente. Una vez el proyecto esta generado, lo próximo que haremos es configurar el mismo, es decir, configurar el espacio de nombre, en mi caso lo llame “Siderys.Blog.ExcelServices.Functions” y el nombre del ensamblado que queremos que se genere al compilar nuestro proyecto, en mi caso lo llame “Siderys.Blog.ExcelServices.Functions.CustomFunctions”. También vamos a firmar nuestro ensamblado con un nombre fuerte (Strong Name) para que podamos instalar el mismo en nuestra Cache Glogal de Ensamblados (GAC), en la imagen 1 podemos ver la opción de la propiedades del proyecto que nos permite realizar dicha configuración.

[Imagen 1]

Una vez nuestro proyecto está configurado, lo próximo que vamos a realizar es codificar nuestra formulas. Pare realizar esta tarea debemos colocar una clase o renombrar la clase por defecto que nos pone Visual Studio, en mi caso la clase se llama “CustomFunctions”. Una vez la clase está colocada, lo próximo que vamos hacer es marcar la misma con un atributo de clase llamado “UdfClass”, lo cual marca a la clase como un clase que implementa formulas personalizadas. Después debemos ingresar los métodos que queremos exponer como formulas personalizadas, tal cual lo hacemos en los Servicios Web, cada método que queremos que se exponga como una formula deberemos marcarlo con el atributo “UdfMethod”.

En la sección 1 veremos el código completo para dos formulas que se conectan a una base de datos SQL Server 2005, extraen información y devuelven un resultado. Estos métodos son nuestras formulas en el Excel cuando querramos invocarlos.

[Sección 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);           

        }

    }

}
Lo próximo que vamos a realizar es compilar nuestro ensamblado y colocarlo en el Cache Global de Ensamblados (GAC), para que quede disponible en el servidor. Para instalar el esamblado podemos hacerlo desde la herramienta de configuración de Microsoft.Net o corremos el comando “Assembly” en nuestra venta de ejecución. En nuestro caso vamos a correr el comando “Assembly” y como vemos en la imagen 2 se nos abre la ventana donde estan instalados tosos los ensamblados en nuestro equipo.
[Imagen 2]

Lo que hacemos ahroa, es arrastramos nuestra ensamblado hasta la ventana recien abierta y lo soltamos. Una vez realizada esta operación buscamos nuestro ensamblado para ver la información del nombre fuerte que se le coloco, puesto que para referirnos a el desde Excel Services, vamos a necesitar conocer su nombre fuerte (Strong Name), en la imagen 3 podemos la ventana de propiedades de nuestro ensamblado.
[Imagen 3]

Bien ahora que tenemos el nombre fuerte (Strong Name) de nuestro ensamblado estamos en condiciones de poder instalarlo en Excel Services para su utilización, en nuestro caso el nombre fuerte del ensamblado es “Siderys.Blog.ExcelServices.Functions.CustomFunctions, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=711eed342842acee”
Con el ensamblado colocado en la GAC, lo que vamos hacer ahora es configurar Excel Services para que se puedan utilizar nuestras formulas, abrimos la administración central y vamos a nuestro servicio de proveedor compartido. Como podemos tener más de uno, tendremos que realizar estos pasos para todos los servicios compartidos que queramos que estén disponibles nuestras formulas. 
En la imagen 4 podemos ver la sección dedica a Excel Services en nuestro servicio compartido y en la cual debemos presionar el link “funciones definidas por el usuario”.
[Imagen 4]

Una vez en la esta sección, presionamos el link “Agregar función definida” que se encuentra en la parte superior de la pantalla y en la nueva ventana cargamos todos los datos necesarios para nuestro ensamblado, como podemos ver en la imagen 5. En la sección 5 debemos cargar el nombre fuerte de nuestro ensamblado si el mismo está instalado en la GAC, si no la ruta y nombre completa al mismo, si la opción seleccionada es que se cargue desde una ubicación de red o del disco duro del servidor.
[Imagen 5]

Una vez configurado el mismo la pantalla nos debería quedar como se ve en la imagen 6 y lo próximo que debemos realizar es un IISRESET para que se cargue el nuevo ensamblado colocado en la GAC.
[Imagen 6]

Ahora lo que debemos hacer es abrir un libro de Excel y usar nuestras formulas. Vale la pena destacar que como las mismas solo serán ejecutadas en el servidor, en el cliente, no funcionaran, para que estén disponibles en el cliente, debemos instalarlas en cada máquina que se vayan a utilizar. Es por eso, que cuando estemos trabajando con el Excel vamos a encontrar que para nuestra formula, Excel nos coloca #NAME?, esto es porque no encuentra la formula localmente. Para nuestro caso, no es un problema, ya que una vez que terminemos de cargar la formula en el Excel, procederemos a publicarla en el servidor de Excel Services. En la imagen 7 podemos ver las formulas cargadas en el Excel.
[Imagen 7]

Ya que nuestras formulas reciben parámetros de entrada, una la fecha y la otra el nombre de un producto, lo que debemos hacer es definir los mismos, para la publicación en Excel Services, así que seleccionamos la celda que estamos utilizando en el Excel para pasarle valores a las formulas en nuestro caso “D2” y “D3” y vamos a la sección formulas y luego presionamos “Definir Nombre”, a lo cual se nos abrirá un cuadro de dialogo donde vamos a definir el nombre de los dos parámetros, en nuestro caso, llamamos “FechaTC” a la celda utilizada para pasarle una fecha a la fórmula del tipo de cambio y “NombreProducto” al la celda utilizada para pasar el nombre del producto.
Por último debemos publicar el Excel dentro de Excel Services de nuestro servidor Sharepoint, para lo cual vamos al menú principal y seleccionamos “Publicar”. Se nos abrirá un cuadro de dialogo como el de “Salvar como” y debemos configurar los parámetros para que sean tenidos en cuenta por Excel Services, en la imagen 8 vemos como podemos acceder a las opciones de publicación y en la imagen 9 como establecemos los parámetros para la publicación, simplemente agregando los mismos, presionando el botón “Add”. 
[Imagen 8]

[Imagen 9]

Una vez configurados los parámetros, publicamos el Excel, para lo cual navegamos hasta la biblioteca de documentos donde queremos publicar el mismo (Recuerden que dicha biblioteca deberá estar configurada como un sitio de confianza dentro de Excel Services) y presionamos el botón “Guardar”. En la imagen 10 podemos ver la biblioteca de documentos de nuestros Sharepoint donde vamos a guardar el Excel. 
[Imagen 10]

Una vez que la publicación finalice se nos abrirá el libro dentro de Excel Services y podremos ver como nuestra formulas se ejecutaron correctamente. En la imagen 11 vemos el libro Excel cargado y las formulas ejecutándose. 
[Imagen 11]

Si quieren ver la grabación del WebCast dictado sobre Excel Services, podrá hacerlo presionando aquí
Fabián Imaz