Reportes en Excel con VB.NET Fecha: 26/May/04 (26/May/2004) |
A muchos desarrolladores se les a presentado la necesidad de crear reportes con ciertos estilos provenientes de herramientas de terceros, por lo regular los ejecutivos les gusta la forma en que se presentan los informes en formato EXCEL y muchas veces informes generados por sistemas de gestión, se nos solicita que sean exportados a EXCEL para ajustarlos a sus necesidades y gustos. Bueno con este articulo pretendo mostrarle una forma sencilla de leer una base de datos y generar un informe directamente en EXCEL aprovechando todas las funcionalidades de esta herramienta, se podría decir que mas del 50 porciento de las computadoras de oficina cuentan con alguna versión de Microsoft Office, así que estimo que le será de utilidad alguno de ustedes.
Iniciamos.
Creamos una nueva solución en Visual Studio .NET y agregamos una referencia a la librería de objeto de Excel que se encuentra en la paleta COM de la ventana de agregar referencias en Visual Studio .NET
Si la versión de Office con la que cuentan es 2000 la librería seria Microsoft Excel 9.0 Object Library, luego hacemos clic en Select y luego en OK y listo.
Creamos una forma que será nuestra plataforma de lanzamiento desde donde lanzaremos los ejemplos que crearemos nuestra forma ser vera así:
El primer botón lo utilizaremos para crear un pequeño ejemplo de cómo tratar un documento EXCEL como una base de datos y leer sus documentos con sentencias SQL, el segundo botón lo utilizaremos para crear nuestro listado en EXCEL, ahora le describo cual es el procedimiento.
1. Utilizaremos la base de datos Northwind que trae Microsoft Access como demo.
2. Leeremos las tablas Categories y Productos para crear un listado de productos por categorías.
3. Crearemos un grafico estadístico con todas las categorías de productos que hay en el listado.
El Código …
Llego la hora de la codificación, primero definiremos una variable tipo Excel.Application a nivel de formulario la cual hace referencia a EXCEL.
'' Creamos un objeto Excel
Dim m_Excel As Excel.Application
Agregamos el siguiente código en el evento “Closing” del formulario para cuando cerremos el formulario eliminar la instancia de Excel que este ejecutándose.
If Not m_Excel Is Nothing Then
m_Excel.Quit()
m_Excel = Nothing
End If
Hacemos doble clic en el segundo botón “Listado en Excel, luego definiremos una variable de tipo string para controlar la ruptura por nombre de categorías.
Dim CategoryName As String ' Variable para controlar la ruptura por nombre de categorías
Creamos 1 variable tipo Excel.WorkBook para crear el documento Excel y otra tipo Excel.WorkSheet para crear la hoja del documento.
'' Creamos un objeto WorkBook
Dim objLibroExcel As Excel.Workbook
'' Creamos un objeto WorkSheet
Dim objHojaExcel As Excel.Worksheet
Ahora crearemos una conexión a la base de datos northwind y leeremos los registros de las tablas Categories y Producta, crearemos un dataset y llevaremos los datos al DataSet.
Dim objSQLConnect As New System.Data.OleDb.OleDbConnection("Data Source=Northwind.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin")
Dim objSQLAdapter As New OleDb.OleDbDataAdapter("select CategoryName,ProductID,ProductName,UnitPrice from Products,Categories Where Products.CategoryID = Categories.CategoryID order by Categories.CategoryID", objSQLConnect)
Dim objDataSet As New DataSet("ExcelTest")
objSQLAdapter.Fill(objDataSet, "Categories")
Iniciamos una instancia de EXCEL y Hacemos visibles para ver como se va creando el reporte, podemos hacerlo visible al final si se desea.
'' Iniciamos una instancia a Excel
m_Excel = New Excel.Application
m_Excel.Visible = True
Es importante tener en mente que algunas datas, tales como Fechas y números, pueden ser desplegadas correctamente cuando se conoce la configuración regional de la computadora donde se trabaja. Para interpretar, convertir y presentar la data debemos considerar la variedad de lenguajes y versiones de Microsoft Office y Microsoft Windows que el usuario puede tener, por ejemplo cuando Excel recibe una llamada desde un programa externo, Excel utiliza el identificador Local (LCID) enviado por cliente que realizó la llamada para procesar los valores tales como “05/06/2005”or “29,250.00”, si su configuración regional no coincide con esta LCID se producirá un error de ejecución o la data no se desplegará correctamente.
Para evitar estos problemas crearemos una variable tipo “CultureInfo”para mantener la cultura con la que esta configurada la maquina y Crearemos una nueva cultura para trabajar de tipo Inglés-Estados Unidos (“es-US)”).
'' Creamos una variable para guardar la cultura actual
Dim OldCultureInfo As System.Globalization.CultureInfo = _ System.Threading.Thread.CurrentThread.CurrentCulture
'Crear una cultura standard (en-US) inglés estados unidos
System.Threading.Thread.CurrentThread.CurrentCulture = _
New System.Globalization.CultureInfo("en-US")
Ahora creamos un nuevo documento y seleccionamos la primera hoja del documento en la cual crearemos nuestro informe
'' Creamos una instancia del Workbooks de Excel
'' Creamos una instancia de la primera hoja de trabajo de Excel
objLibroExcel = m_Excel.Workbooks.Add()
objHojaExcel = objLibroExcel.Worksheets(1)
objHojaExcel.Visible = Excel.XlSheetVisibility.xlSheetVisible
'' Hacemos esta hoja la visible en pantalla
'' (como seleccionamos la primera esto no es necesario
'' si seleccionamos una diferente a la primera si lo
'' necesitaríamos, esto lo hacemos como forma de mostrar como
cambiar de entre hojas en un documento Excel).
objHojaExcel.Activate()
Para hacer referencia a cualquiera de las celdas de una hoja de Excel o a un rango de celdas de la hoja de Excel debemos hacerlo a través del Objeto Range entrelos parámetros que recibe este objeto debemos enviarle las coordenadas de la celda o rango de celda que deseamos acceder Ej.:
HojaExcel.Range(“A1:D1”) aquí hacemos referencia a las celdas desde la A1 hasta la D1 lo que quiere decir que incluye A1, B1, C1 y D1.
HojaExcel.Range(“A1”) aquí hacemos referencia a la celda A1.
HojaExcel.Range(“A1,A3,A5,A7”) aquí hacemos referencia a las celdas A1, A3, A5 y A7, esto seria igual que si presionáramos el control y hacemos clic en cada una de estas celdas.
Creamos el encabezado del reporte, la primera línea une las celdas y las convierte un en una sola, la segunda línea Asigna el nombre del encabezado, la tercera línea asigna negrita al titulo y la cuarta línea signa un Size a titulo de 15.
'' Crear el encabezado de nuestro informe
objHojaExcel.Range("A1:D1").Merge()
objHojaExcel.Range("A1:D1").Value = "MAÑON FOOT SHOP, S. A."
objHojaExcel.Range("A1:D1").Font.Bold = True
objHojaExcel.Range("A1:D1").Font.Size = 15
'' Crear el subencabezado de nuestro informe
objHojaExcel.Range("A2:D2").Merge()
objHojaExcel.Range("A2:D2").Value = "Ingredient List by Category"
objHojaExcel.Range("A2:D2").Font.Italic = True
objHojaExcel.Range("A2:D2").Font.Size = 13
Creamos los encabezados de las columnas de nuestro informe, este constará de 4 columnas,“Categoria”que desplegara el nombre de la categorías, “Código”que desplegara el código del producto, “Nombre”desplegara el nombre del producto y “Precio” para desplegar el precio del producto formateado según una mascarilla que especificaremos.
Dim objCelda As Excel.Range = objHojaExcel.Range("A3", Type.Missing)
objCelda.Value = "Categoría"
objCelda = objHojaExcel.Range("B3", Type.Missing)
objCelda.Value = "Código"
objCelda = objHojaExcel.Range("C3", Type.Missing)
objCelda.Value = "Nombre"
objCelda = objHojaExcel.Range("D3", Type.Missing)
objCelda.Value = "Precio RD$"
objCelda.EntireColumn.NumberFormat = "###,###,###.00"
Iniciamos las variables de control para desplegar los registros de nuestra tabla, la variable “i” la utilizaremos para controlar el número de la fila y la variable “j”la utilizaremos para controlar el rango que cubre el subtotal que será desde j+2 hasta i – 1.
Dim i As Integer = 5
Dim j As Integer = 5
CategoryName = ""
Recorremos los registros de la tabla y lo asignamos a su correspondiente celda en su respectiva fila en la hoja, por cada cambio de categorías imprimiremos un subtotal.
For Each objRow As DataRow In objDataSet.Tables(0).Rows
''' Si Ya se ha impreso una categorías y la próxima categorías
''' es diferente a la categorías a imprimir, imprimir los totales
If CategoryName.Length > 0 AndAlso CategoryName <> objRow.Item(0) Then
objHojaExcel.Cells(i, 1) = "Total Category " & CategoryName.Trim
objHojaExcel.Cells(i, 2) = "=count(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Cells(i, 3) = "Subtotal Precio:"
objHojaExcel.Cells(i, 4) = "=sum(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Range("A" & i.ToString & ":D" & i.ToString).Font.Bold = True
j = i
i += 2
End If
'' Asignar la categoría impresa
CategoryName = objRow.Item(0)
'' Asignar los valores de los registros a las celdas
objHojaExcel.Cells(i, "A") = objRow.Item(0) 'CategoryName'
objHojaExcel.Cells(i, "B") = objRow.Item(1) 'ProductID'
objHojaExcel.Cells(i, "C") = objRow.Item(2) 'ProductName'
objHojaExcel.Cells(i, "D") = objRow.Item(3) 'UnitPrice'
'' Avanzamos una fila
i += 1
Next
Luego de imprimir todos los registros imprimiremos el subtotal de la categorías final.
'* El Ultimo subtotal
objHojaExcel.Cells(i, 1) = "Total Category " & CategoryName.Trim
objHojaExcel.Cells(i, 2) = "=count(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Cells(i, 3) = "Subtotal Precio:"
objHojaExcel.Cells(i, 4) = "=sum(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Range("A" & i.ToString & ":D" & i.ToString).Font.Bold = True
Ahora seleccionado todo el informe y ajustaremos el ancho de las columnas al ancho de su contenido máximo, asignamos también el auto filtro para poder filtrar
'' Seleccionar todo el bloque desde A1 hasta D #de filas
Dim objRango As Excel.Range = objHojaExcel.Range(String.Format(strRangoTotal, intRowInicial, i - 1))
'' Seleccionamos todo el rango especificado
objRango.Select()
'' Ajustamos el ancho de las columnas al ancho máximo del
'' contenido de sus celdas
objRango.Columns.AutoFit()
'' Asignar filtro por columna
objRango.AutoFilter(1, , VisibleDropDown:=True)
'' Asignar un formato automático
objRango.AutoFormat(11, Alignment:=False)
Ahora crearemos el total general de los datos impresos, ejecutamos el método printpreview para presentarlo en vista previa
'' Crear un total general
objHojaExcel.Cells(i, “A”) = "Total "
objHojaExcel.Cells(i, “B”) = "=count(" & String.Format(strRangoTotal, intRowInicial, i - 1) & ")"
objHojaExcel.Cells(i, “C”) = "Total Precio:"
objHojaExcel.Cells(i, “D”) = "=sum(" & String.Format(strRangoTotal, intRowInicial, i - 1) & ")"
objLibroExcel.PrintPreview()
Hasta aquí podemos probar nuestro informe, al ejecutarlo y hacer clic en el botón “Listado en Excel”, se desplegara un listado como sigue:
Y este es nuestro código completo hasta el momento:
Dim CategoryName As String ' Variable para controlar la ruptura por nombre de categorías
'' Creamos un objeto WorkBook
Dim objLibroExcel As Excel.Workbook
'' Creamos un objeto WorkSheet
Dim objHojaExcel As Excel.Worksheet
'' Iniciamos una conexión a la base de datos NorthWind y seleccionamos
'' varios registros desde las tablas Products,Categories
Dim objSQLConnect As New System.Data.OleDb.OleDbConnection("Data Source=Northwind.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin")
Dim objSQLAdapter As New OleDb.OleDbDataAdapter("select CategoryName,ProductID,ProductName,UnitPrice from Products,Categories Where Products.CategoryID = Categories.CategoryID order by Categories.CategoryID", objSQLConnect)
Dim objDataSet As New DataSet("ExcelTest")
objSQLAdapter.Fill(objDataSet, "Categories")
'' Iniciamos una instancia a Excel
m_Excel = New Excel.Application
m_Excel.Visible = True
'' Creamos una variable para guardar la cultura actual
Dim OldCultureInfo As System.Globalization.CultureInfo = _
System.Threading.Thread.CurrentThread.CurrentCulture
'Crear una cultura standard (en-US) inglés estados unidos
System.Threading.Thread.CurrentThread.CurrentCulture = _
New System.Globalization.CultureInfo("en-US")
'' Creamos una instancia del Workbooks de Excel
'' Creamos una instancia de la primera hoja de trabajo de Excel
objLibroExcel = m_Excel.Workbooks.Add()
objHojaExcel = objLibroExcel.Worksheets(1)
objHojaExcel.Visible = Excel.XlSheetVisibility.xlSheetVisible
'' Hacemos esta hoja la visible en pantalla
'' (como seleccionamos la primera esto no es necesario
'' si seleccionamos una diferente a la primera si lo
'' necesitaríamos).
objHojaExcel.Activate()
'' Crear el encabezado de nuestro informe
objHojaExcel.Range("A1:D1").Merge()
objHojaExcel.Range("A1:D1").Value = "MAÑON FOOT SHOP, S. A."
objHojaExcel.Range("A1:D1").Font.Bold = True
objHojaExcel.Range("A1:D1").Font.Size = 15
'' Crear el subencabezado de nuestro informe
objHojaExcel.Range("A2:D2").Merge()
objHojaExcel.Range("A2:D2").Value = "Ingredient List by Category"
objHojaExcel.Range("A2:D2").Font.Italic = True
objHojaExcel.Range("A2:D2").Font.Size = 13
Dim objCelda As Excel.Range = objHojaExcel.Range("A3", Type.Missing)
objCelda.Value = "Categoría"
objCelda = objHojaExcel.Range("B3", Type.Missing)
objCelda.Value = "Código"
objCelda = objHojaExcel.Range("C3", Type.Missing)
objCelda.Value = "Nombre"
objCelda = objHojaExcel.Range("D3", Type.Missing)
objCelda.Value = "Precio RD$"
objCelda.EntireColumn.NumberFormat = "###,###,###.00"
Dim i As Integer = 5
Dim j As Integer = 5
CategoryName = ""
For Each objRow As DataRow In objDataSet.Tables(0).Rows
''' Si Ya se ha impreso una categorías y la próxima categorías
''' es diferente a la categorías a imprimir, imprimir los totales
If CategoryName.Length > 0 AndAlso CategoryName <> objRow.Item(0) Then
objHojaExcel.Cells(i, 1) = "Total Category " & CategoryName.Trim
objHojaExcel.Cells(i, 2) = "=count(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Cells(i, 3) = "Subtotal Precio:"
objHojaExcel.Cells(i, 4) = "=sum(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Range("A" & i.ToString & ":D" & i.ToString).Font.Bold = True
j = i
i += 2
End If
'' Asignar la categoría impresa
CategoryName = objRow.Item(0)
'' Asignar los valores de los registros a las celdas
objHojaExcel.Cells(i, "A") = objRow.Item(0) 'CategoryName'
objHojaExcel.Cells(i, "B") = objRow.Item(1) 'ProductID'
objHojaExcel.Cells(i, "C") = objRow.Item(2) 'ProductName'
objHojaExcel.Cells(i, "D") = objRow.Item(3) 'UnitPrice'
'' Avanzamos una fila
i += 1
Next
'* El Ultimo subtotal
objHojaExcel.Cells(i, "A") = "Total Category " & CategoryName.Trim
objHojaExcel.Cells(i, "B") = "=count(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Cells(i, "C") = "Subtotal Precio:"
objHojaExcel.Cells(i, "D") = "=sum(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Range("A" & i.ToString & ":D" & i.ToString).Font.Bold = True
'' Avanzamos una fila
i += 1
'' Seleccionar todo el bloque desde A1 hasta D #de filas
Dim objRango As Excel.Range = objHojaExcel.Range("A3:D" & (i - 1).ToString)
'' Seleccionamos todo el rango especificado
objRango.Select()
'' Ajustamos el ancho de las columnas al ancho máximo del
'' contenido de sus celdas
objRango.Columns.AutoFit()
'' Asignar filtro por columna
objRango.AutoFilter(1, , VisibleDropDown:=True)
'' Asignar un formato automático
objRango.AutoFormat(11, Alignment:=False)
'' Seleccionamos el total general del reporte y asignamos
'' font a negrita e itálica
objRango = objHojaExcel.Range("A" & i.ToString & ":D" & i.ToString)
objRango.Select()
objRango.Font.Bold = True
objRango.Font.Italic = True
'' Crear un total general
objHojaExcel.Cells(i, 1) = "Total "
objHojaExcel.Cells(i, 2) = "=count(A3:D" & (i - 1).ToString & ")"
objHojaExcel.Cells(i, 3) = "Total Precio:"
objHojaExcel.Cells(i, 4) = "=sum(A3:D" & (i - 1).ToString & ")"
objLibroExcel.PrintPreview()
Ahora crearemos un grafico estadístico en base a los resultados del informe que creamos, los valores de la “X” serán los totales y los valores de la “Y”seran los subtotales de las categorías.
Primero creamos un objetivo de tipo Excel.Chart, un objeto de tipo Excel.SeriesCollection que hace referencias a cada uno de los valores del grafico y 2 objetivos de tipos Excel.Axes que hace referencias a las columnas del grafico, el grafico será tipo Barra.
'' Crear un grafico estadístico
Dim objExcelChart As Excel.Chart
Dim xlsSeries As Excel.SeriesCollection
Dim xlsAxisCategory, xlsAxisValue As Excel.Axes
Primero instanciamos el objeto con un nuevo grafico
'' Agregamos un nuevo grafico
objExcelChart = objLibroExcel.Charts.Add
Luego creamos un rango con los subtotales del informe que serán los valores para nuestro grafico
'' Creamos un rango con los totales de cada categorías para crear nuestro gráfico
objRango = objHojaExcel.Range("=Sheet1!$D$17,Sheet1!$D$31,Sheet1!$D$46,Sheet1!$D$58,Sheet1!$D$67,Sheet1!$D$75,Sheet1!$D$82,Sheet1!$D$96")
Cada valor dentro de la sentencia Range representan la celda que contiene el valor para cada barra del grafico, cada valor separado por coma representa una barra para nuestro grafico
Ej.:
Sheet1!$D$17 = hace referencia a la celda 17 de la columna “D” de la hoja 1 (Sheet1).
Sheet1!$D$31 = hace referencia a la celda 31 de la columna “D” de la hoja 1 y así sucesivamente.
Asignamos el tipo de grafico que queremos crear, en este caso tipo columna
'' Asignamos el tipo de grafico
objExcelChart.ChartType = Excel.XlChartType.xlColumnClustered
Asignamos el rango que creamos como fuente de datos
'' Asignamos el total
objExcelChart.SetSourceData(objRango)
Seleccionamos las columnas del grafico, especificamos que se desplegue los valores de las columnas al top de cada columna, asignamos el titulo de cada subtotal a su correspondiente columna del grafico
'' Seleccionamos los diferentes elementos del grafico
xlsSeries = objExcelChart.SeriesCollection
'' Desplegar los valores de cada columna al top de cada columna
objExcelChart.ApplyDataLabels(_
Excel.XlDataLabelsType.xlDataLabelsShowValue)
'' Asignamos el nombre de cada serie
xlsSeries.Item(1).Name = objHojaExcel.Range("A17").Value
xlsSeries.Item(2).Name = objHojaExcel.Range("A31").Value
xlsSeries.Item(3).Name = objHojaExcel.Range("A46").Value
xlsSeries.Item(4).Name = objHojaExcel.Range("A58").Value
xlsSeries.Item(5).Name = objHojaExcel.Range("A67").Value
xlsSeries.Item(6).Name = objHojaExcel.Range("A75").Value
xlsSeries.Item(7).Name = objHojaExcel.Range("A82").Value
xlsSeries.Item(8).Name = objHojaExcel.Range("A96").Value
Seleccionamos las Axis del grafico y asignamos un titulo para las axis por categorías y otro para las axis por valor.
xlsAxisCategory = objExcelChart.Axes(, Excel.XlAxisGroup.xlPrimary)
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Totales por categoría"
xlsAxisValue = objExcelChart.Axes(, Excel.XlAxisGroup.xlPrimary)
xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "Rango de Precios"
xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Orientation = Excel.XlOrientation.xlVertical
xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMajorGridlines = True
Ahora movemos la sentencia que ejecuta el método PrintPreview al final del código luego de generar el grafico
objLibroExcel.PrintPreview()
Listo, nuestro código completo luce de la siguiente forma (este código también incluye la parte que describimos anterior sobre generar el informe):
Try
Dim CategoryName As String ' Variable para controlar la ruptura por nombre de categorías
'' Creamos un objeto WorkBook
Dim objLibroExcel As Excel.Workbook
'' Creamos un objeto WorkSheet
Dim objHojaExcel As Excel.Worksheet
'' Iniciamos una conexión a la base de datos NorthWind y seleccionamos
'' varios registros desde las tablas Products,Categories
Dim objSQLConnect As New System.Data.OleDb.OleDbConnection("Data Source=Northwind.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin")
Dim objSQLAdapter As New OleDb.OleDbDataAdapter("select CategoryName,ProductID,ProductName,UnitPrice from Products,Categories Where Products.CategoryID = Categories.CategoryID order by Categories.CategoryID", objSQLConnect)
Dim objDataSet As New DataSet("ExcelTest")
objSQLAdapter.Fill(objDataSet, "Categories")
'' Iniciamos una instancia a Excel
m_Excel = New Excel.Application
m_Excel.Visible = True
'' Creamos una variable para guardar la cultura actual
Dim OldCultureInfo As System.Globalization.CultureInfo = _
System.Threading.Thread.CurrentThread.CurrentCulture
'Crear una cultura standard (en-US) inglés estados unidos
System.Threading.Thread.CurrentThread.CurrentCulture = _
New System.Globalization.CultureInfo("en-US")
'' Creamos una instancia del Workbooks de Excel
'' Creamos una instancia de la primera hoja de trabajo de Excel
objLibroExcel = m_Excel.Workbooks.Add()
objHojaExcel = objLibroExcel.Worksheets(1)
objHojaExcel.Visible = Excel.XlSheetVisibility.xlSheetVisible
'' Hacemos esta hoja la visible en pantalla
'' (como seleccionamos la primera esto no es necesario
'' si seleccionamos una diferente a la primera si lo
'' necesitaríamos).
objHojaExcel.Activate()
'' Crear el encabezado de nuestro informe
objHojaExcel.Range("A1:D1").Merge()
objHojaExcel.Range("A1:D1").Value = "MAÑON FOOT SHOP, S. A."
objHojaExcel.Range("A1:D1").Font.Bold = True
objHojaExcel.Range("A1:D1").Font.Size = 15
'' Crear el subencabezado de nuestro informe
objHojaExcel.Range("A2:D2").Merge()
objHojaExcel.Range("A2:D2").Value = "Ingredient List by Category"
objHojaExcel.Range("A2:D2").Font.Italic = True
objHojaExcel.Range("A2:D2").Font.Size = 13
Dim objCelda As Excel.Range = objHojaExcel.Range("A3", Type.Missing)
objCelda.Value = "Categoría"
objCelda = objHojaExcel.Range("B3", Type.Missing)
objCelda.Value = "Código"
objCelda = objHojaExcel.Range("C3", Type.Missing)
objCelda.Value = "Nombre"
objCelda = objHojaExcel.Range("D3", Type.Missing)
objCelda.Value = "Precio RD$"
objCelda.EntireColumn.NumberFormat = "###,###,###.00"
Dim i As Integer = 5
Dim j As Integer = 5
CategoryName = ""
For Each objRow As DataRow In objDataSet.Tables(0).Rows
''' Si Ya se ha impreso una categorías y la próxima categorías
''' es diferente a la categorías a imprimir, imprimir los totales
If CategoryName.Length > 0 AndAlso CategoryName <> objRow.Item(0) Then
objHojaExcel.Cells(i, 1) = "Total Category " & CategoryName.Trim
objHojaExcel.Cells(i, 2) = "=count(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Cells(i, 3) = "Subtotal Precio:"
objHojaExcel.Cells(i, 4) = "=sum(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Range("A" & i.ToString & ":D" & i.ToString).Font.Bold = True
j = i
i += 2
End If
'' Asignar la categoría impresa
CategoryName = objRow.Item(0)
'' Asignar los valores de los registros a las celdas
objHojaExcel.Cells(i, "A") = objRow.Item(0) 'CategoryName'
objHojaExcel.Cells(i, "B") = objRow.Item(1) 'ProductID'
objHojaExcel.Cells(i, "C") = objRow.Item(2) 'ProductName'
objHojaExcel.Cells(i, "D") = objRow.Item(3) 'UnitPrice'
'' Avanzamos una fila
i += 1
Next
'* El Ultimo subtotal
objHojaExcel.Cells(i, "A") = "Total Category " & CategoryName.Trim
objHojaExcel.Cells(i, "B") = "=count(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Cells(i, "C") = "Subtotal Precio:"
objHojaExcel.Cells(i, "D") = "=sum(D" & (j + 2).ToString & ":D" & (i - 1).ToString & ")"
objHojaExcel.Range("A" & i.ToString & ":D" & i.ToString).Font.Bold = True
'' Avanzamos una fila
i += 1
'' Seleccionar todo el bloque desde A1 hasta D #de filas
Dim objRango As Excel.Range = objHojaExcel.Range("A3:D" & (i - 1).ToString)
'' Seleccionamos todo el rango especificado
objRango.Select()
'' Ajustamos el ancho de las columnas al ancho máximo del
'' contenido de sus celdas
objRango.Columns.AutoFit()
'' Asignar filtro por columna
objRango.AutoFilter(1, , VisibleDropDown:=True)
'' Asignar un formato automático
objRango.AutoFormat(11, Alignment:=False)
'' Seleccionamos el total general del reporte y asignamos
'' font a negrita e itálica
objRango = objHojaExcel.Range("A" & i.ToString & ":D" & i.ToString)
objRango.Select()
objRango.Font.Bold = True
objRango.Font.Italic = True
'' Crear un total general
objHojaExcel.Cells(i, 1) = "Total "
objHojaExcel.Cells(i, 2) = "=count(A3:D" & (i - 1).ToString & ")"
objHojaExcel.Cells(i, 3) = "Total Precio:"
objHojaExcel.Cells(i, 4) = "=sum(A3:D" & (i - 1).ToString & ")"
'' Crear un grafico estadístico
Dim objExcelChart As Excel.Chart
Dim xlsSeries As Excel.SeriesCollection
Dim xlsAxisCategory, xlsAxisValue As Excel.Axes
'' Agregamos un nuevo grafico
objExcelChart = objLibroExcel.Charts.Add
'' Creamos un rango con los totales de cada categorías para crear nuestro gráfico
objRango = objHojaExcel.Range("=Sheet1!$D$17,Sheet1!$D$31,Sheet1!$D$46,Sheet1!$D$58,Sheet1!$D$67,Sheet1!$D$75,Sheet1!$D$82,Sheet1!$D$96")
With objExcelChart
'' Asignamos el tipo de grafico
.ChartType = Excel.XlChartType.xlColumnClustered
'' Asignamos el total
.SetSourceData(objRango)
'' Seleccionamos los diferentes elementos del grafico
xlsSeries = .SeriesCollection
'' Desplegar los valores de cada columna al top de cada columna
.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue)
'' Asignamos el nombre de cada serie
xlsSeries.Item(1).Name = objHojaExcel.Range("A17").Value
xlsSeries.Item(2).Name = objHojaExcel.Range("A31").Value
xlsSeries.Item(3).Name = objHojaExcel.Range("A46").Value
xlsSeries.Item(4).Name = objHojaExcel.Range("A58").Value
xlsSeries.Item(5).Name = objHojaExcel.Range("A67").Value
xlsSeries.Item(6).Name = objHojaExcel.Range("A75").Value
xlsSeries.Item(7).Name = objHojaExcel.Range("A82").Value
xlsSeries.Item(8).Name = objHojaExcel.Range("A96").Value
xlsAxisCategory = .Axes(, Excel.XlAxisGroup.xlPrimary)
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).HasTitle = True
xlsAxisCategory.Item(Excel.XlAxisType.xlCategory).AxisTitle.Characters.Text = "Totales por categoría"
xlsAxisValue = .Axes(, Excel.XlAxisGroup.xlPrimary)
xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasTitle = True
xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Characters.Text = "Rango de Precios"
xlsAxisValue.Item(Excel.XlAxisType.xlValue).AxisTitle.Orientation = Excel.XlOrientation.xlVertical
xlsAxisValue.Item(Excel.XlAxisType.xlValue).HasMajorGridlines = True
End With
System.Threading.Thread.CurrentThread.CurrentCulture = OldCultureInfo
objLibroExcel.PrintPreview()
objHojaExcel = Nothing
objLibroExcel = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Ahora podemos ejecutar nuestro programa y obtendremos el siguiente resultado:
Vamos a crear un segundo ejemplo de cómo leer un archivo Excel como base de datos, mediante OLEDB podemos tratar los archivos Excel como si fueran base datos igualando el archivo como una base de datos, las hojas (Sheets) como tablas y las columnas como campos y las filas como registros Ej.
Archivo XLS = Base de datos
Sheets = Tablas
Column = Columnas (para nombrar las columnas toma la primera línea del archivo)
Cell = valor de una colunma en un registro
Row = Registro
En nuestro ejemplo vamos a crear una forma que nos pedirá el nombre y la localización del archivo Excel que queremos leer, luego haremos clic en un botón y se desplegara el contenido del archivo en un grid y agregaremos otro botón para abrir este mismo archivo en EXCEL.
Iniciando, nuestra forma se vera de la siguiente forma:
El botón “Leer Archivo”leera el archivo Excel y desplegara su contenido en el grid y el botón “Abrir Excel”abrira una instancia de Excel con archivo especificado.
código …
Primero definiremos una variable tipo EXCEL.Application a nivel del formulario
Dim m_Excel As Excel.Application
Iniciaremos codificando el botón “Leer Archivo”, hacemos clic sobre el botón
Definiremos 3 variables, la primera de tipo Dataset para almacenar la información del archivo Excel, la segunda de tipo OleDbDataAdapter para extraer la información del archivo Excel y pasarlo al dataset y la 3ra. De tipo OleDbConnection para crear la conexión entre el archivo Excel y la aplicación.
Dim objDataSet As System.Data.DataSet
Dim objDataAdapter As System.Data.OleDb.OleDbDataAdapter
Dim objOleConnection As System.Data.OleDb.OleDbConnection
Ahora configuraremos la conexión al archivo, los parámetros necesarios en la cadena de conexión son los siguientes
Provider = “Microsoft.Jet.OLEDB.4.0”
DataSource = <ruta y nombre del archivo>
Extended Properties = Nombre de la aplicación y la versión Ej. “Extended Properties=Excel 8.0”
'Creamos un objeto OLEDBConnection con el nombre del archivo seleccionado
' como Data Source
objOleConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & TextBox1.Text.Trim & "; Extended Properties=Excel 8.0;")
Nuestra cadena de conexión en el parámetro Data Source pasaremos el archivo especificado en el textbox.
Ahora configuraremos el DataAdapter, una vez creada la conexión al archivo Excel las hojas del archivo pueden ser tratados como tablas y aplicar sentencias SQL sobre estas hojas Ej.:
Si queremos seleccionar los registros que están en la hoja 1 utilizamos
SELECT * FROM [SHEET1$]
Si queremos seleccionar columnas especificas de una hoja utilizamos
SELECT * FROM [SHEET$1A1:D17]
Entonces, configuramos nuestro DataAdapter:
'* Si queremos seleccionar toda la hoja solo especificamos [Sheet1$]
objDataAdapter = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$] ", objOleConnection)
Ahora instanciamos el Dataset y lo llenamos
objDataSet = New System.Data.DataSet
objDataAdapter.Fill(objDataSet)
objOleConnection.Close()
Asignaremos el nombre de la tabla al titulo del grid, el dataset como datasource del grid y el nombre de la tabla creada en el dataset como datamember y listo.
DataGrid1.CaptionText = objDataSet.Tables(0).TableName
DataGrid1.DataSource = objDataSet
DataGrid1.DataMember = objDataSet.Tables(0).TableName
Ahora nuestra codificación completa se vera así:
Try
If TextBox1.Text.Length = 0 Then
Exit Sub
End If
Dim objDataSet As System.Data.DataSet
Dim objDataAdapter As System.Data.OleDb.OleDbDataAdapter
Dim objOleConnection As System.Data.OleDb.OleDbConnection
'Creamos un objeto OLEDBConnection con el nombre del archivo seleccionado
' como Data Source
objOleConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & TextBox1.Text.Trim & "; Extended Properties=Excel 8.0;")
'Seleccionamos los datos que están en la hoja 1 (Sheet1)
'Cada columna se representara como una columna y cada row
'como un row
'* Si queremos seleccionar un rango solo especificamos [Sheet1$An:Bm]
'* objDataAdapter = New System.Data.OleDb.OleDbDataAdapter( _
'* "select * from [Sheet1$A6:E29]", objOleConnection)
'* Si queremos seleccionar toda la hoja solo especificamos [Sheet1$]
objDataAdapter = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$] ", objOleConnection)
'where [Sheet1$A4] = 'Aruba'
objDataSet = New System.Data.DataSet
objDataAdapter.Fill(objDataSet)
objOleConnection.Close()
DataGrid1.CaptionText = objDataSet.Tables(0).TableName
DataGrid1.DataSource = objDataSet
DataGrid1.DataMember = objDataSet.Tables(0).TableName
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Ahora codificaremos el botón “Abrir EXCEL”, este botón es bastante sencillo, solo debemos instanciar la variable tipo EXCEL.Application que definimos a nivel de formulario y ejecutar el método Open de la propiedad Workbooks de la variable.
m_Excel = New Excel.Application
m_Excel.Workbooks.Open(TextBox1.Text)
m_Excel.Visible = True
Agregamos el siguiente código en el evento “Closing” del formulario para cuando cerremos el formulario eliminar la instancia de Excel que este ejecutándose.
If Not m_Excel Is Nothing Then
m_Excel.Quit()
m_Excel = Nothing
End If
Ya podemos ejecutamos nuestra aplicación y podemos probarla el resultado será parecido al siguiente:
Aquí abrí un archivo llamado Casa.XLS ubicado en mi My Documents folder hago clic en el botón Leer Archivo y se desplega lo siguiente:
Luego hago clic en el botón “Abrir EXCEL”y el resultado es este:
Como se puede notar el programa tomo la primera línea de la hoja Excel como títulos de las columnas esto lo hace automático el Provider.
Hasta aquí este articulo, espero que les sirva. Quiero agradecer a los lectores de mis artículos anteriores por escribirme y cualquiera que desee escribirme ya sea para fines de alguna pregunta o aclaración siéntance en la libertad de hacerlo estoy a sus servicios.
Gracias mil ...
Fichero con el código de ejemplo, (ELMoreno_ExcelReports.zip - 446 KB)