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