Reportes en Excel con VB.NET

Fecha: 26/May/04 (26/May/2004)
Autor: Enriquillo Ma��n Ram�rez ([email protected])


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)