Cómo exportar a Excel el contenido de un objeto DataTable |
Por Enrique Martínez Montejo |
Última revisión: 04/09/2011 |
A menudo nos sentimos en la necesidad de transferir datos desde diferentes orígenes de datos diversos a un libro de trabajo de Excel, operación esta que se puede hacer de una manera directa si los datos están en una base de datos de Access o de Microsoft SQL Server, bien desde la propia interfaz de usuario de Microsoft Access o de Microsoft SQL Server Management Studio, o mediante programación utilizando el motor de datos Microsoft Jet (para versiones de Excel inferiores a la de 2007), o Microsoft ACE (para versiones de Excel 2007 o superior). Pero a veces sucede que, o no tenemos los datos en las bases de datos mencionadas, o hemos modificado los datos en nuestra aplicación antes de exportarlos al libro de trabajo de Excel. Es en estos últimos casos cuando hacemos uso de un objeto DataTable, para recuperar los datos desde la base de datos, efectuar las modificaciones oportunas y guardar los datos en Excel.
Transferir los datos de un objeto DataTable a Excel desde nuestra aplicación de Visual Basic .NET o de C# es fácil, y en Internet existen abundantes ejemplos que muestran cómo hacerlo, que si bien cumplen correctamente con la exportación de los datos, presentan un serio problema que surge cuando queremos hacer la exportación mediante la propia biblioteca de objetos de Microsoft Excel, y nos encontramos que tras finalizar la exportación de datos el proceso de Excel continúa estando en memoria: no se ha destruido el proceso, aún después de haber ejecutado el método Quit del objeto Excel.Application utilizado. Y esto se debe a los motivos que se explican en el siguiente artículo de la Base del Conocimiento de Microsoft:
Office application does not quit after automation from Visual Studio .NET client
Pienso que el artículo deja bien claro el motivo por el cual no finaliza el proceso de Excel, que consiste en que no se liberan todas las referencias al objeto RCW (Runtime Callable Wrapper) que se crea automáticamente cuando en tiempo de ejecución creamos una referencia a cualquier objeto del tipo COM existente en la biblioteca de Excel, ya sea del tipo Excel.Application, Excel.Workbook o simplemente Excel.Range.
Como se detalla en la sección Solución del artículo mencionado, nuestro código debe de cumplir las siguientes premisas para que el proceso se cierre correctamente:
Todo lo que no sea hacer esto hará que el proceso de Excel, o de cualquier otro proceso de Microsoft Office que se utilice, continúe estando en memoria, que en ocasiones puede que sea hasta que finalice nuestra aplicación, y en otras, hasta que se finalice el proceso explícitamente desde el Administrador de tareas de Windows, con lo que nos arriesgamos que tengamos varios procesos de Excel en memoria, tantos como veces hayamos hecho clic al botón que inicia la exportación de datos.
Otro problema que a mi entender muestran los ejemplos que he visto es que mientras recorren la colección de objetos DataRow existentes en el objeto DataTable, van asignando valores a la propiedad Value de un objeto Excel.Range previamente referenciado (una celda cualquiera de la hoja de cálculo), cuando en realidad lo que necesita la propiedad Value es una matriz o array bidimensional con los datos que se desean asignar a la celda, por tanto, hay que buscar un mecanismo para convertir un objeto DataTable en una matriz bidimensional de valores Object.
A continuación muestro un procedimiento genérico para transferir a una hoja de cálculo de Excel el contenido completo de un objeto DataTable utilizando la biblioteca de objetos de Microsoft Excel. Los únicos parámetros que hay que pasarle al método son los mínimos requeridos, como son el propio objeto DataTable, la ruta del archivo de Excel y el nombre de la hoja de cálculo donde se exportarán los datos.
Si ya tiene referenciada en su proyecto la versión de la biblioteca de objetos de Microsoft Excel que vaya a utilizar, inserte el siguiente contenido en algún módulo existente en su proyecto.
Imports
Microsoft.Office.Interop
''' <summary>
''' Exporta el contenido de
un objeto DataTable a una
''' hoja de cálculo de Microsoft Excel.
'''
</summary>
''' <author>Enrique Martínez Montejo - 2011</author>
'''
<param name="dt">Objeto DataTable.</param>
''' <param
name="fileName">Ruta completa del archivo de Excel.</param>
''' <param
name="sheetName">Nombre de la hoja de cálculo.</param>
'''
<remarks></remarks>
Public Sub ExportToExcel(ByVal
dt As DataTable, _
ByVal fileName
As String, _
ByVal sheetName
As String)
' Verificamos los
parámetros pasados
'
If ((dt Is
Nothing) OrElse _
(String.IsNullOrEmpty(fileName))
OrElse _
(String.IsNullOrEmpty(sheetName)))
Then _
Throw New ArgumentNullException()
Dim app As
Excel.Application = Nothing
Dim book As Excel.Workbook
= Nothing
Dim sheet As Excel.Worksheet =
Nothing
Try
'
Referenciamos la aplicación Excel.
'
app =
New Excel.Application()
'
Abrimos el libro de trabajo.
'
book = app.Workbooks.Open(fileName)
' Referenciamos la hoja de cálculo del libro.
'
sheet =
DirectCast(book.Sheets(sheetName), Excel.Worksheet)
With sheet
' Limpiamos el contenido de toda la hoja.
'
.Cells.Select()
.Cells.ClearContents()
' Seleccionamos la primera celda de la hoja.
'
.Range("A1").Select()
' Escribimos los nombres de las columnas en la primera
' celda de la primera fila de la hoja de cálculo
'
Dim fila As Integer = 1
Dim columna
As Integer = 1
For Each dc
As
DataColumn In dt.Columns
.Cells(fila, columna) = dc.ColumnName
columna += 1
Next
' Establecemos los atributos de la fuente para las
' celdas de la primera fila.
'
With .Range(.Cells(1, 1), .Cells(1, dt.Columns.Count)).Font
.Name = "Calibri"
.Bold = True
.Size = 12
End With
' Insertamos los datos en la hoja de cálculo, comenzando por la
' fila número 2, ya que la primera fila está ocupada
' por el nombre de las columnas.
'
fila
= 2
For Each row
As DataRow In
dt.Rows
' Primera columna
columna = 1
For Each dc
As
DataColumn In dt.Columns
.Cells(fila, columna) = row(dc.ColumnName)
' Siguiente columna
columna += 1
Next
' Siguiente fila
fila += 1
Next
' Autoajustamos el ancho de todas las columnas utilizadas.
'
.Columns().AutoFit()
End
With
Catch
ex As Exception
' Se ha producido una excepción;
' indicamos que el libro ha sido guardado.
'
If (book IsNot Nothing)
Then
book.Saved =
True
End If
' Devolvemos la excepción al procedimiento llamador
Throw
Finally
sheet = Nothing
If (book IsNot Nothing)
Then
' Si procede, guardamos el libro de trabajo.
If (Not
(book.Saved)) Then book.Save()
' Cerramos el libro de Excel.
book.Close()
End If
book = Nothing
If (app IsNot Nothing)
Then
' Si procede, cerramos Excel y disminuimos el recuento
' de referencias al objeto Excel.Application.
app.Quit()
While
(System.Runtime.InteropServices.Marshal.ReleaseComObject(app) > 0)
' Sin implementación.
End While
End If
app = Nothing
End Try
End Sub
Una vez que tenga referenciado en su código fuente un objeto DataTable, llamaría al procedimiento ExportToExcel de la siguiente manera:
Private Sub
Button1_Click(sender As
Object, e
As EventArgs)
Handles Button1.Click
'
Procesamos los mensajes que actualmente están
' en la
cola de mensajes.
'
Application.DoEvents()
'
Cambiamos el tipo de cursor
'
Dim tipoCursor
As Cursor =
Me.Cursor
Me.Cursor
= Cursors.WaitCursor
Try
' Referenciamos el objeto DataTable enlazado con
' el control DataGridView.
'
Dim dt As
DataTable = DirectCast(DataGridView1.DataSource,
DataTable)
' Exportamos los datos a la hoja de Excel de
un
' libro de Excel 2007 - 2010.
'
ExportToExcel(dt,
"C:\Mis documentos\Libro1.xlsx",
"Hoja1")
MessageBox.Show("Los datos han sido exportados
satisfactoriamente.")
Catch ex As
Exception
' Se ha producido un error.
'
MessageBox.Show(ex.Message)
Finally
' Restauramos el tipo de cursos existente
'
Me.Cursor = tipoCursor
End Try
End Sub
En el supuesto de que haya añadido un origen de datos a su proyecto utilizando el Asistente para la configuración de orígenes de datos de Visual Studio, referenciaría un objeto DataTable incluido en el objeto DataSet de la siguiente manera:
Dim dt As DataTable = NombreDataSet.NombreDataTable
Por ejemplo, para referenciar un objeto DataTable llamado Clientes existente en un objeto DataSet llamado PruebaDataSet, ejecutaría:
Dim dt As DataTable = PruebaDataSet.Clientes
Otros enlaces de interés:
Indice de la colección de ejemplos para trabajar con productos Microsoft Office
Copiar datos en Excel desde un objeto Recordset de ADO clásico
Enrique Martínez Montejo - 2011
NOTA: La información contenida en este artículo, así como el código fuente incluido en el mismo, se proporciona COMO ESTÁ, sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo explicado, recomendado o sugerido en el presente artículo.
NOTE: The information contained in this article and source code included therein, is provided AS IS without warranty of any kind, and confers no rights. You assume any risk to implement, use or run it explained, recommended or suggested in this article.