Reportes en Excel con VB.NET

Fecha: 26/May/04 (26/May/2004)
Autor: Enriquillo Mañón Ramírez (enriquillo_manon@hotmail.com)


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 ...


ir al índice

Fichero con el código de ejemplo, (ELMoreno_ExcelReports.zip - 446 KB)