Skip to content

Exportar de un DataTable a Excel

June 27, 2012

Aquí va una función muy útil para poder exportar datos de un DataTable a .xls (Microsoft Excel).
A tener en cuenta que tenemos que añadir las Librerías Microsoft.Office.Interop.Excel.dll y Microsoft.Vbe.Interop.dll como Referencia

Imports System.IO
Imports Microsoft.Office.Interop.Excel
 
Private Sub ExportToExcel(ByVal objDT As DataTable)
Dim Excel As Object = CreateObject("Excel.Application")
Dim strFilename As String
Dim intCol, intRow As Integer
Dim strPath as String="c:\"
If Excel Is Nothing Then
MsgBox("It appears that Excel is not installed on this machine. 
This operation requires MS Excel to be installed on this machine.", MsgBoxStyle.
Critical)
Return
End If
Try
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

.cells(1, 1).value = "Heading" 'Heading of the excel file
.cells(1, 1).EntireRow.Font.Bold = True
Dim intI As Integer = 1
For intCol = 0 To objDT.Columns.Count - 1
.cells(2, intI).value = objDT.Columns(intCol).ColumnName
.cells(2, intI).EntireRow.Font.Bold = True
intI += 1
Next
intI = 3
Dim intK As Integer = 1
For intCol = 0 To objDT.Columns.Count - 1
intI = 3
For intRow = 0 To objDT.Rows.Count - 1
.Cells(intI, intK).Value = objDT.Rows(intRow).ItemArray(intCol)
intI += 1
Next
intK += 1
Next
If Mid$(strPath, strPath.Length, 1) <> "\" Then
strPath = strPath & "\"
End If
strFilename = strPath & "Excel.xls"
.ActiveCell.Worksheet.SaveAs(strFilename)
End With
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
Excel = Nothing
MsgBox("Data's are exported to Excel Succesfully in '" & strFilename & 
"'", MsgBoxStyle.Information)
Catch ex As Exception
MsgBox(ex.Message)
End Try
' The excel is created and opened for insert value. We most close this 
excel using this system
Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName
("EXCEL")
For Each i As Process In pro
i.Kill()
Next
End Sub


En el Evento de un botón, se puede crear y obtener los datos deseados, que pueden venir directamente de una consulta SQL, o bien desde un DataGridView, etc; y con solo pasar el DataTable como parámetro, será suficiente.

Después de la llamada a la función, podemos abrir el fichero acabado de crear, así:
Process.Start(“ruta”)

Si observamos un error que nos informa que el tipo DataTable es ambiguo porqué puede tratarse de un objeto EXCEL o bien de un objeto SYSTEM.DATA, se puede solucionar eliminando el import que hace referencia al Excel Object:

Imports Microsoft.Office.Interop.Excel

From → Datos

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: