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)
//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];
}
}
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!!!
1 comentario:
Interesante la información.
Publicar un comentario