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.