Como Crear un Addin para Excel con C#
Como Crear un Addin para Excel con C# aparece primero en nuestro http://jonathanmelgoza.com/blog/como-crear-un-addin-para-excel-con-c/
Hola a todos! espero que se la estén pasando genial, yo por mi parte el día de hoy realizare un tutorial practico sobre como crear un addin para excel con c# y visual studio. Muchas veces nos vemos en la necesidad de desarrollar una herramienta personalizada que tiene mucho que ver con un software de office, Excel. En mi caso necesitaba crear una herramienta que conectara a bases de datos y me trajera la información que necesito para mis clientes, pero no solo es mostrar información y ya, necesitamos que la información este lo mas digerida posible y ademas es para reportes de perdidas y ganancias de la empresa asi que la necesidad es crear una herramienta que traiga información de bases de datos y crear reportes en excel con tablas dinámicas y gráficos dinámicos, es por esto que necesitamos que nuestra herramienta este empotrada en excel. ¿Aun no sabes como crear un addin para excel? Comencemos..
Lo primero que necesitamos es una edición de Visual Studio, yo tengo VS2010 pero cualquier versión te puede funcionar bien.
También necesitamos tener acceso a una base de datos ya sea local o remota, ya sea mysql o sqlserver.
Una vez teniendo esto vamos a ver como crear un proyecto con visual studio 2010 para crear un addin para excel.
Como crear un addin para excel
Primero vamos a echar un ojo a la tabla en sql server que he creado para explicar este ejemplo, se llama ventas y se encuentra en la base de datos Reportes, tiene algunos registro de ejemplo que tratarémos de obtener y mostrar en nuestro excel con un simple click,
Para comenzar vamos a necesitar utilizar algunos using en nuestro proyecto..
using System.Data.SqlClient; using Excel = Microsoft.Office.Interop.Excel; using data = System.Data; using Microsoft.Office.Interop.Excel; using System.Drawing;
Podrás realizar la conexión a tu base de datos sql server basta con crear tu cadena de conexión de la forma,
string CONNECTION_STRING = "Data Source=127.0.0.1;Initial Catalog=Reportes;Integrated Security=False;User ID=xxxxx;Password=xxxxx";
crear nuestro objeto de conexión,
cnnx = new SqlConnection(CONNECTION_STRING);
para obtener la información desde la base de datos sql server vamos a utilizar la siguiente función,
public data.DataTable getdata(string SQL) data.DataTable DATA = new data.DataTable(); SqlDataAdapter dat = new SqlDataAdapter(SQL, cnnx); dat.Fill(DATA); return DATA;
Ahora para trabajar con nuestro objeto excel vamos a crear un objeto conector así,
en nuestro código del botón obtener reporte vamos a crear un nuevo objeto application de excel, el cual nos permitirá trabajar con excel fácilmente, haremos visible este objeto y cambiamos el cursor al estilo wait para avisar al usuario que estamos trabajando..
app = new Excel.Application(); app.Visible = true; app.Cursor = Excel.XlMousePointer.xlWait;
agregamos un nuevo workbook,
app.Workbooks.Add(); app.DisplayAlerts = true;
ahora una nueva hoja o worksheet,
Excel.Worksheet wrksh = (Excel.Worksheet)app.Worksheets.Add();
deshabilitamos la actualizacion en pantalla asi,
app.Calculation = XlCalculation.xlCalculationManual; app.ScreenUpdating = false;
nombramos la nueva worksheet con el texto “resumen de ventas”,
wrksh.Name = "Resumen de Ventas";
cambiamos un poco el color de fondo y de texto de la fila de cabecera de nuestra información, consideremos la cantidad de columnas que traeremos de nuestra base de datos,
wrksh.get_Range("A1", "H1").Interior.Color = System.Drawing.ColorTranslator.ToOle((Color)cc.ConvertFromString("#5B9BD5")); wrksh.get_Range("A1", "H1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
hacemos estatica la primer fila y aplicamos filtros asi,
wrksh.Application.ActiveWindow.SplitRow = 1; wrksh.Application.ActiveWindow.FreezePanes = true; Excel.Range firstRow = (Excel.Range)wrksh.Rows[1]; firstRow.Activate(); firstRow.Select(); firstRow.AutoFilter(1,Type.Missing,Excel.XlAutoFilterOperator.xlAnd,Type.Missing,true);
mediante la funcion getdata que creamos anteriormente mandamos nuestra query a esta función que a su vez conecta y obtiene la información..
data.DataTable table = getdata(@"select * from Reportes.dbo.Ventas;");
primero escribimos los nombres de nuestras columnas..
int col = 1; foreach (data.DataColumn c in table.Columns) wrksh.Cells[1, col] = c.ColumnName; col++;
posteriormente creamos un array para guardar la información recibida de la base de datos que hasta ahora estaba almacenada en el objeto table, si es la columna 4 (indice 3) entonces le damos un formato de numero sino es un simple string, también encontraremos un código para darle cierto formato a las filas, una de color blanco otra de color azul claro..
int i = 0; string[,] data = new string[table.Rows.Count, table.Columns.Count]; foreach (data.DataRow row in table.Rows) int j = 0; foreach (data.DataColumn c in table.Columns) if (j == 3) try data[i, j] = Convert.ToDouble(row[c].ToString()).ToString("N02"); catch (Exception) else data[i, j] = row[c].ToString(); j++; // format row if (i % 2 == 0) wrksh.get_Range("A" + (i + 2), "H" + (i + 2)).Interior.Color = System.Drawing.ColorTranslator.ToOle((Color)cc.ConvertFromString("#DDEBF7")); i++;
Ahora pasamos este array data a nuestra worksheet de excel indicando en donde queremos que se muestren con la función Range,
wrksh.Range[wrksh.Cells[2, 1], wrksh.Cells[table.Rows.Count + 1, table.Columns.Count]].value = data;
aplicamos un truco para que se actualize el formato de las celdas numericas,
wrksh.Range[wrksh.Cells[2, 1], wrksh.Cells[table.Rows.Count + 1, table.Columns.Count]].value = wrksh.Range[wrksh.Cells[2, 1], wrksh.Cells[table.Rows.Count + 1, table.Columns.Count]].value;
y ajustamos el tamaño de las columnas
app.Columns.AutoFit(); app.Rows.AutoFit();
Por ultimo terminamos volviendo a activar la actualización en pantalla, regresando el estilo del cursor, eliminando las hojas de trabajo que no necesitamos y cerrando la conexión,
app.ScreenUpdating = true; app.Cursor = Excel.XlMousePointer.xlDefault; // remove default worksheet app.DisplayAlerts = false; for (int v = app.ActiveWorkbook.Worksheets.Count; v > 0; v--) // close the connection if (cnnx != null) cnnx.Dispose();
Por fin, hemos visto como obtener información desde sql server a excel con este addin.
Como crear una tabla dinamica
Para crear una tabla dinamica primero creamos ciertos objetos,
Range pivotData = null; Range pivotDestination = null; PivotTable pivotTable = null; PivotField formadepago_field = null; PivotField total_field = null;
luego establecemos de donde obtendremos la información con la que trabajara nuestra tabla dinámica, le decimos que trabajara de la hoja de trabajo con el nombre “Resumen de Ventas”, es decir, la misma en la que estamos trabajando,
Worksheet fromwrksh = app.ActiveWorkbook.Sheets["Resumen de Ventas"];
ahora seleccionamos el rango de celdas que componen nuestra informacion,
pivotData = fromwrksh.get_Range("A1", "H" + max);
y le decimos donde empezara nuestra tabla dinamica asi,
pivotDestination = wrksh.get_Range("C15", useDefault);
donde useDefault viene de,
static object useDefault = Type.Missing;
seleccionamos nuestra hoja de trabajo y comenzamos a crear nuestra tabla dinámica o pivot table asi,
fromwrksh.Select(); wrksh.PivotTableWizard( XlPivotTableSourceType.xlDatabase, pivotData, pivotDestination, "td1", true, true, true, true, useDefault, useDefault, false, false, XlOrder.xlDownThenOver, 0, useDefault, useDefault );
mediante la función pivottablewizard, donde pasamos algunos parámetros así como algunos objetos antes creados, para mas información de estos parámetros visita este enlace.
ahora obtenemos esta tabla que registramos en excel y la apuntamos a un objeto que podamos manipular,
pivotTable = (PivotTable)wrksh.PivotTables("td1");
creamos los campos en los que se basara nuestra tabla dinamica, en este caso seria forma de pago y costo del producto,
formadepago_field = ((PivotField)pivotTable.PivotFields("Forma_de_pago")); total_field = ((PivotField)pivotTable.PivotFields("Precio"));
damos un poco de formato a nuestra tabla dinamica..
pivotTable.Format(XlPivotFormatType.xlPTClassic); pivotTable.InGridDropZones = false; pivotTable.TableStyle2 = "PivotStyleMedium2";
establecemos que el campo forma de pago es una campo de fila
formadepago_field.Orientation = XlPivotFieldOrientation.xlRowField;
y que precio sera nuestro campo de datos, aprovechamos para darle un poco de formato a los números y para decirle que su función sera sumar todos los valores de precio agrupados por forma de pago,
total_field.Orientation = XlPivotFieldOrientation.xlDataField; total_field.Function = XlConsolidationFunction.xlSum; total_field.NumberFormat = "#,##0_);[Red](#,##0)";
al ejecutar tendremos el siguiente resultado..
Como crear un gráfico dinámico en excel con c#
Para crear un gráfico dinámico el proceso es mucho mas sencillo, simplemente tienes que indicar de donde obtendrás la información, indicar el tipo de gráfico y hacer uso de la función ChartWizard para personalizarlo, te dejo un ejemplo que toma los valores de la tabla dinámica generada en el paso anterior para dibujar un gráfica dinámica de pastel.
wrksh.Select(); int lRowCount = pivotTable.TableRange2.Rows.Count; Excel.Range oOTDPCSourceData = (Excel.Range)wrksh.get_Range("C15", "D" + lRowCount); Excel.Chart xlChart = (Excel.Chart)app.ActiveWorkbook.Charts.Add(useDefault, useDefault, useDefault, useDefault); xlChart.ChartType = Excel.XlChartType.xlPie; xlChart.ChartWizard(oOTDPCSourceData, Excel.XlChartType.xlPie, Type.Missing, Excel.XlRowCol.xlColumns, 1, 1, true, "Grafico", Type.Missing, Type.Missing, Type.Missing);
y el resultado al ejecutar es el siguiente,
Si te gusto este tutorial en el que te explico como crear un addin para excel utilizando visual studio, c# y sql server para mostrar información en excel desde la base de datos, generar tablas y gráficas dinámicas entonces te pido que compartas este enlace con cualquier persona que le pueda interesar o simplemente dame un +1 en Google o en tu red social favorita.
Descargar proyecto completo en .zip
