VSTO, la evolución del VBA

Los que estamos habituados a la programación en .NET  y en C# nos resulta algo frustrante e incómodo enfrentarnos al Visual Basic for Applications (VBA) de Excel, entorno heredado del  Visual Basic 6.0.

Esto nos produce suspiros tales como “¿Y yo programaba en esta […]?”, “¿Ande narices está el Reflection?” o “Y pensar que antes se programaba sin linq”…

 Las “Visual Studio Tools for Office”  posibilitan crear documentos Office  automatizados y plug-ins usando el entorno Visual Studio 2010 junto a la potencia del Framework.

En este enlace http://msdn.microsoft.com/es-es/office/hh133430 hay videos y manuales donde podéis ampliar un poco más.

Ejemplo de VSTO en Excel

Vamos a ver un ejemplo de cómo procesar datos usando VSTO 2010.

Procesaremos unas tablas de productos y ventas , y usando una tabla de rangos de descuentos, completaremos la información en una nueva tabla.

Tenemos una hoja Excel llamada “datos.xslx” con la siguiente información.

Para localizar la información, podemos apuntar directamente a la celda (en B4 empiezan los productos) o podemos definir un “named range” o nombre de rango.

Para ello, seleccionamos la zona de los productos y le asignamos el nombre “Productos”.

Repetiremos esta operación con “Descuentos”, “Ventas” y “Procesados”.

Si quisiéramos trabajar sin nombres de rangos, en el código de ejemplo más abajo comentaré la forma de hacerlo.

Guardamos el libro.

Ahora abriremos con el Visual Studio 2010 un nuevo proyecto.

Aprovechamos para mirar las opciones que tenemos disponibles.

Un complemento de Office permite ubicar código en el Ribbon o en los eventos que podamos capturar de la aplicación principal.

También podemos crear plantillas, libros de Excel y Documentos de Word.

En nuestro caso, creamos un libro de Excel a partir de un documento existente y cargamos la Excel que hemos rellenado.

Cuando termine de cargar,  en el explorador de soluciones, abrimos la "hoja1.cs" y añadiremos un botón, le cambiaremos el nombre y haremos doble click en él para abrir el código.

Aparte del evento Button1_Click, tenemos a nuestra disposición otros eventos como Initialize, OnShutDown, etc.. que nos podrían servir para otros casos.

Para el caso que nos interesa, que es procesar las listas, empezamos a escribir código.

1º, crearemos las clases donde alojaremos las listas de los datos.

public class Producto

{

    public int id;

    public string nombre;

    public double precio;

}

public class Descuento

{

    public int producto;

    public int? desde;

    public int? hasta;

    public double descuento;

}

public class Venta

{

    public int pedido;

    public int producto;

    public double cantidad;

}

A continuación, las 3 funciones que usaremos para convertir los rangos en listas tipadas

private List<Producto> CargaListaProductos()

{

    List<Producto> lista = new List<Producto>();

    Excel.Range celda;

    Producto dato;

    celda = this.Range["Productos"].Cells[1,1]; 

    // También se podría haber llamado a la celda

    // celda = this.Range["K4"];

    do

    {

        dato = new Producto();

        dato.id = (int)celda.Value;

        dato.nombre = celda.Offset[0, 1].Value;

        dato.precio = celda.Offset[0, 2].Value;

        lista.Add(dato);

        celda = celda.Offset[1, 0];

    } while(celda.Value != null);  // Ejecutaremos hasta encontrar la primera línea vacía

    return lista;

}

private List<Descuento> CargaListaDescuentos()

{

   

    List<Descuento> lista = new List<Descuento>();

    Excel.Range celda;

    Descuento dato;

    celda = this.Range["Descuentos"].Cells[1,1];

    do

    {

        dato = new Descuento();

        dato.producto = (int)celda.Value;

        // Aquí usamos un operador ternario: Si tiene valor, mete su valor; si no, mete el valor mínimo

        dato.desde = ( celda.Offset[0, 1].Value ?? double.MinValue );

       

        // Aquí usamos otro operador ternario: Si no tiene valor , mete su valor máximo; si no, mete su valor

        dato.hasta = (celda.Offset[0, 2].Value == null ? double.MaxValue : celda.Offset[0, 2].Value);

       

        dato.descuento = celda.Offset[0, 3].Value;

        lista.Add(dato);

        celda = celda.Offset[1, 0];

    } while(celda.Value != null); 

    return lista;

}

private List<Venta> CargaListaVentas()

{

    List<Venta> lista = new List<Venta>();

    Excel.Range celda;

    Venta dato;

    celda = this.Range["Ventas"].Cells[1,1];

    do

    {

        dato = new Venta();

        dato.pedido = (int)celda.Value;

        dato.producto = (int)celda.Offset[0, 1].Value;

        dato.cantidad = celda.Offset[0, 2].Value;

        lista.Add(dato);

        celda = celda.Offset[1, 0];

    } while(celda.Value != null); 

    return lista;

}

Por último, haremos la función principal que borra las respuestas, carga las listas, hace un join de las listas y, para cada resultado, busca en descuentos y escribe en las celdas correspondientes.

private void button1_Click(object sender, EventArgs e)

{

    // Borramos el rango donde pondremos los resultados

    //Ojo con esto! si en vez de Clear usamos Delete por confusión,

    //      borramos el range "Procesadas", y cuando lo volvamos a usar dará error

    this.Range["Procesadas"].Clear(); 

    // Cargaremos las 3 listas en listas de objetos tipados

    List<Producto> listaProductos = CargaListaProductos();

    List<Descuento> listaDescuentos= CargaListaDescuentos();

    List<Venta> listaVentas = CargaListaVentas();

   

    // Unimos mediante linq (sintaxis de consulta) las tablas de ventas y productos

    var procesado = from v in listaVentas

                    join p in listaProductos

                    on v.producto equals p.id

                    select new

                    {

                        pedido = v.pedido,

                        productoId = v.producto,

                        producto = v.producto + "-" + p.nombre,

                        cantidad = v.cantidad,

                        precio = p.precio

                    };

   

    // Nos situamos en el rango "Procesadas" y escribimos cada resultado

    Excel.Range cell;

    cell = this.Range["Procesadas"].Cells[1,1];

    foreach(var p in procesado)

    {

        double descuento = 0;

        cell.Value = p.pedido; //Pedido

        cell.Offset[0, 1].Value = p.producto; //Producto

        cell.Offset[0, 2].Value = p.cantidad; //Cantidad

        cell.Offset[0, 3].Value = p.precio; //Precio

        //Localizamos dentro de la lista de descuentos si hay
        //algún descuento mediante Linq(sintaxis de método)

        var calculoDescuento = listaDescuentos.Where(x => x.producto == p.productoId &&

                        p.cantidad >= x.desde &&

                        p.cantidad <= x.hasta).FirstOrDefault();

       

        if (calculoDescuento!=null)

            descuento=calculoDescuento.descuento;

        cell.Offset[0, 4].Value = descuento; //Dto

        cell.Offset[0, 5].Value = p.cantidad*p.precio*(1-descuento); //Importe

        cell = cell.Offset[1, 0];

    }

}

El resultado sería la siguiente lista.

Si quisieramos aplicar filtros, acceder a información mediante Web services, wcf,  etc… se podría hacer sin ningún problema.

Bueno, ¿Y dónde tengo la Excel con el botoncito montado? Pues en el directorio del proyecto/bin/debug

Os dejo la Excel con los datos y el proyecto en Visual Studio 2010 aquí

Happy coding!!!