Exporta Automaticamente DataTable a Microsoft Excel
Hoy quiero mostrarles un ejemplo que me ha servido durante mucho tiempo y aún lo sigo utilizando.
Haremos un ejemplo para exportar un DataTable completo a Excel sin importar la cantidad de columnas o filas que este tenga.
Puede que existan muchas formas de realizar esta tarea, pero encuentro realmente útil este método dado que sirve para cualquier versión de excel. Por cuestiones de compatilidad con las nuevas versiones de Excel solo manejaremos 256 columnas.
Lo primero que necesitamos es agregar una referencia a Microsoft.Office.Interop.Excel y lo hacemos de la siguiente forma:
Importar referencia a Excel
Importar referencia Excel
Luego necesitamos un formulario, un botón, un dataGridView y una barra de progreso. Lo podemos disponer como se ve en la imagen:
Formulario aplicación para exportar dataset a excel
Ahora quiero mostrarles todo el código, realmente es sencillo lo más importante es que se adapta a cualquier tabla. Lo van a poder reutilizar sin problemas.
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Public Class Form1
Private ds As New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'//////////////////////////////////////////////////
'// Creamos la conexión y llenamos el DataGridView
'//////////////////////////////////////////////////
Dim cnn As New SqlConnection("Data Source=localhost\sqlexpress;Initial Catalog=Northwind; Integrated Security=True")
Dim da As New SqlDataAdapter("Select * from customers", cnn)
da.Fill(ds)
DataGridView1.DataSource = ds.Tables(0)
End Sub
Private Sub btnExportar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportar.Click
'/////////////////////////////
'// Creamos el Objeto Excel
'/////////////////////////////
Dim m_Excel
Dim objLibroExcel
Dim objHojaExcel
m_Excel = CreateObject("Excel.Application")
objLibroExcel = m_Excel.Workbooks.Add()
objHojaExcel = objLibroExcel.Worksheets(1)
objHojaExcel.Name = "Customers"
objHojaExcel.Visible = Excel.XlSheetVisibility.xlSheetVisible
objHojaExcel.Activate()
'/////////////////////////////////////////////////////////
'// Definimos dos variables para controlar fila y columna
'/////////////////////////////////////////////////////////
Dim fila As Integer = 1
Dim columna As Integer = 1
'/////////////////////////////////////////////////
'// Armamos la linea con los títulos de columnas
'/////////////////////////////////////////////////
objHojaExcel.Range("A1").Select()
For Each dc In ds.Tables(0).Columns
objHojaExcel.Range(nombreColumna(columna) & 1).Value = dc.ColumnName
columna += 1
Next
fila += 1
'/////////////////////////////////////////////
'// Le damos formato a la fila de los títulos
'/////////////////////////////////////////////
Dim objRango As Excel.Range = objHojaExcel.Range("A1:" & nombreColumna(ds.Tables(0).Columns.Count) & "1")
objRango.Font.Bold = True
objRango.Cells.Interior.ColorIndex = 35
objRango.Cells.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone
objRango.Cells.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone
objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone
objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous
objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
'//////////////////////////////////////////
'// Cargamos todas las filas del datatable
'//////////////////////////////////////////
ProgressBar1.Maximum = ds.Tables(0).Rows.Count
columna = 1
ProgressBar1.Value = 0
For Each dr In ds.Tables(0).Rows
columna = 1
For Each dc In ds.Tables(0).Columns
objHojaExcel.Range(nombreColumna(columna) & fila).Value = dr(dc.ColumnName)
columna += 1
Next
fila += 1
ProgressBar1.Value += 1
Next
'//////////////////////////////////////
'// Ajustamos automaticamente el ancho
'// de todas las columnas utilizada
'//////////////////////////////////////
objRango = objHojaExcel.Range("A1:" & nombreColumna(ds.Tables(0).Columns.Count) & ds.Tables(0).Rows.Count.ToString)
objRango.Select()
objRango.Columns.AutoFit()
'/////////////////////////////////////
'// Le decimos a Excel que se muestre
'/////////////////////////////////////
MsgBox("Exportación a Excel completa", MsgBoxStyle.Information, ".:: solovb.net ::.")
m_Excel.Visible = True
End Sub
Public Function nombreColumna(ByVal numero As Integer) As String
Dim columna(256) As String
columna(1) = "A"
columna(2) = "B"
columna(3) = "C"
columna(4) = "D"
columna(5) = "E"
columna(6) = "F"
columna(7) = "G"
columna(8) = "H"
columna(9) = "I"
columna(10) = "J"
columna(11) = "K"
columna(12) = "L"
columna(13) = "M"
columna(14) = "N"
columna(15) = "O"
columna(16) = "P"
columna(17) = "Q"
columna(18) = "R"
columna(19) = "S"
columna(20) = "T"
columna(21) = "U"
columna(22) = "V"
columna(23) = "W"
columna(24) = "X"
columna(25) = "Y"
columna(26) = "Z"
columna(27) = "AA"
columna(28) = "AB"
columna(29) = "AC"
columna(30) = "AD"
columna(31) = "AE"
columna(32) = "AF"
columna(33) = "AG"
columna(34) = "AH"
columna(35) = "AI"
columna(36) = "AJ"
columna(37) = "AK"
columna(38) = "AL"
columna(39) = "AM"
columna(40) = "AN"
columna(41) = "AO"
columna(42) = "AP"
columna(43) = "AQ"
columna(44) = "AR"
columna(45) = "AS"
columna(46) = "AT"
columna(47) = "AU"
columna(48) = "AV"
columna(49) = "AW"
columna(50) = "AX"
columna(51) = "AY"
columna(52) = "AZ"
columna(53) = "BA"
columna(54) = "BB"
columna(55) = "BC"
columna(56) = "BD"
columna(57) = "BE"
columna(58) = "BF"
columna(59) = "BG"
columna(60) = "BH"
columna(61) = "BI"
columna(62) = "BJ"
columna(63) = "BK"
columna(64) = "BL"
columna(65) = "BM"
columna(66) = "BN"
columna(67) = "BO"
columna(68) = "BP"
columna(69) = "BQ"
columna(70) = "BR"
columna(71) = "BS"
columna(72) = "BT"
columna(73) = "BU"
columna(74) = "BV"
columna(75) = "BW"
columna(76) = "BX"
columna(77) = "BY"
columna(78) = "BZ"
columna(79) = "CA"
columna(80) = "CB"
columna(81) = "CC"
columna(82) = "CD"
columna(83) = "CE"
columna(84) = "CF"
columna(85) = "CG"
columna(86) = "CH"
columna(87) = "CI"
columna(88) = "CJ"
columna(89) = "CK"
columna(90) = "CL"
columna(91) = "CM"
columna(92) = "CN"
columna(93) = "CO"
columna(94) = "CP"
columna(95) = "CQ"
columna(96) = "CR"
columna(97) = "CS"
columna(98) = "CT"
columna(99) = "CU"
columna(100) = "CV"
columna(101) = "CW"
columna(102) = "CX"
columna(103) = "CY"
columna(104) = "CZ"
columna(105) = "DA"
columna(106) = "DB"
columna(107) = "DC"
columna(108) = "DD"
columna(109) = "DE"
columna(110) = "DF"
columna(111) = "DG"
columna(112) = "DH"
columna(113) = "DI"
columna(114) = "DJ"
columna(115) = "DK"
columna(116) = "DL"
columna(117) = "DM"
columna(118) = "DN"
columna(119) = "DO"
columna(120) = "DP"
columna(121) = "DQ"
columna(122) = "DR"
columna(123) = "DS"
columna(124) = "DT"
columna(125) = "DU"
columna(126) = "DV"
columna(127) = "DW"
columna(128) = "DX"
columna(129) = "DY"
columna(130) = "DZ"
columna(131) = "EA"
columna(132) = "EB"
columna(133) = "EC"
columna(134) = "ED"
columna(135) = "EE"
columna(136) = "EF"
columna(137) = "EG"
columna(138) = "EH"
columna(139) = "EI"
columna(140) = "EJ"
columna(141) = "EK"
columna(142) = "EL"
columna(143) = "EM"
columna(144) = "EN"
columna(145) = "EO"
columna(146) = "EP"
columna(147) = "EQ"
columna(148) = "ER"
columna(149) = "ES"
columna(150) = "ET"
columna(151) = "EU"
columna(152) = "EV"
columna(153) = "EW"
columna(154) = "EX"
columna(155) = "EY"
columna(156) = "EZ"
columna(157) = "FA"
columna(158) = "FB"
columna(159) = "FC"
columna(160) = "FD"
columna(161) = "FE"
columna(162) = "FF"
columna(163) = "FG"
columna(164) = "FH"
columna(165) = "FI"
columna(166) = "FJ"
columna(167) = "FK"
columna(168) = "FL"
columna(169) = "FM"
columna(170) = "FN"
columna(171) = "FO"
columna(172) = "FP"
columna(173) = "FQ"
columna(174) = "FR"
columna(175) = "FS"
columna(176) = "FT"
columna(177) = "FU"
columna(178) = "FV"
columna(179) = "FW"
columna(180) = "FX"
columna(181) = "FY"
columna(182) = "FZ"
columna(183) = "GA"
columna(184) = "GB"
columna(185) = "GC"
columna(186) = "GD"
columna(187) = "GE"
columna(188) = "GF"
columna(189) = "GG"
columna(190) = "GH"
columna(191) = "GI"
columna(192) = "GJ"
columna(193) = "GK"
columna(194) = "GL"
columna(195) = "GM"
columna(196) = "GN"
columna(197) = "GO"
columna(198) = "GP"
columna(199) = "GQ"
columna(200) = "GR"
columna(201) = "GS"
columna(202) = "GT"
columna(203) = "GU"
columna(204) = "GV"
columna(205) = "GW"
columna(206) = "GX"
columna(207) = "GY"
columna(208) = "GZ"
columna(209) = "HA"
columna(210) = "HB"
columna(211) = "HC"
columna(212) = "HD"
columna(213) = "HE"
columna(214) = "HF"
columna(215) = "HG"
columna(216) = "HH"
columna(217) = "HI"
columna(218) = "HJ"
columna(219) = "HK"
columna(220) = "HL"
columna(221) = "HM"
columna(222) = "HN"
columna(223) = "HO"
columna(224) = "HP"
columna(225) = "HQ"
columna(226) = "HR"
columna(227) = "HS"
columna(228) = "HT"
columna(229) = "HU"
columna(230) = "HV"
columna(231) = "HW"
columna(232) = "HX"
columna(233) = "HY"
columna(234) = "HZ"
columna(235) = "IA"
columna(236) = "IB"
columna(237) = "IC"
columna(238) = "ID"
columna(239) = "IE"
columna(240) = "IF"
columna(241) = "IG"
columna(242) = "IH"
columna(243) = "II"
columna(244) = "IJ"
columna(245) = "IK"
columna(246) = "IL"
columna(247) = "IM"
columna(248) = "IN"
columna(249) = "IO"
columna(250) = "IP"
columna(251) = "IQ"
columna(252) = "IR"
columna(253) = "IS"
columna(254) = "IT"
columna(255) = "IU"
columna(256) = "IV"
Return columna(numero)
End Function
End Class
Este método me ha servido mucho y espero que a ustedes también… por favor dejen sus comentarios.
27 Responses to “Exporta Automaticamente DataTable a Microsoft Excel”
Comentar
Información Bitacoras.com…
Valora en Bitacoras.com: Hola: Hoy quiero mostrarles un ejemplo que me ha servido durante mucho tiempo y aún lo sigo utilizando. Haremos un ejemplo para exportar un DataTable completo a Excel sin importar la cantidad de columnas o filas que este teng…..
Buenas…
Excelente post!! Me ayudó full Muchas Gracias, pero tengo una gran duda.. Como esa conexion esta con sql.. Como hago para cambiarle el nombre a las columnas!? Porque me trae el nombre de los campos de sql..!!
Ojalá y me hayan entendido..
Gracias!!!
me lo voy a guardar…
en algun momento sera util…
¿Es válido para Excel 2007? Yo he tenido problemas con una exportación que desarrollamos, que sí es válida para versiones anteriores, pero no para 2007. Gracias
Tremendo codigo fuente. me sirvio de mucho. era lo q necesitaba desde hace tiempo.
Aunque tambien necesito la importacion de ecxel a un data table. aunque ambien podria ser a un datagrid y luego actualizando el dataadapter y aceptando los cambios en datatable se estaria guardando en la bd… espero q me puedan ayudar o poder encontrarlo.. gracias
Hola Muchas gracias!! esta excelente!!
Pero me pregunto donde se especifica que datagrid usara EN el caso que esten varios Datagrid?
Por la tabla que estes llamando…
select * from (Nombre de la Tabla)
Si quieres especificar que campos quieres
select (campos, campos, campos) * from tabla
ejm
select carro,color,anio from vehiculos
Tengo un problema con este ejemplo me marca 2 errores en las variables dc y dr, que puedo hacer para solucionarlo??
Hola, prueba esto:
For Each dr as datarow In ds.Tables(0).Rows y For Each dc as datacolumn In ds.Tables(0).Columns
espero que con esto se solucione. Saludos.
hola.
esta chevere, pero la parte de los nombre de columna no es muy funcional.
voy a subir a mi blog un codigo mucho mas pequeño y sin limitaciones de tamaño
jaysson.blogspot.com
listo ya subi el articulo
http://jaysson.blogspot.com/2010/06/columnas-es-excel.html
Buen artículo, pero yo tengo un problema, en mi ordenador con el excel 2007 funciona sin problemas, pero con ordenadores con excel 2003 o anteriores no funciona. Alguien sabe si es problema de las librerias que se importan, dado que das a entender que funciona para cualquier excel, pero yo me he encontrado con este problema.
A ver si alguien me puede resolver la duda.
Gracias.
Buenas…
Excelente post!! Me ayudó full Muchas Gracias, pero tengo una gran duda.. Como esa conexion esta con sql.. Como hago para cambiarle el nombre a las columnas!? Porque me trae el nombre de los campos de sql..!!
Ojalá y me hayan entendido..
Gracias!!!
Public Sub ExportarDatosExcel(ByVal DataGridView1 As DataGridView, ByVal titulo As String)
Dim m_Excel As New Excel.Application
m_Excel.Cursor = Excel.XlMousePointer.xlWait
m_Excel.Visible = True
Dim objLibroExcel As Excel.Workbook = m_Excel.Workbooks.Add
Dim objHojaExcel As Excel.Worksheet = objLibroExcel.Worksheets(1)
With objHojaExcel
.Visible = Excel.XlSheetVisibility.xlSheetVisible
.Activate()
‘Encabezado
.Range(“A1:L1″).Merge()
.Range(“A1:L1″).Value = “INSTITUTO ARGENCAF”
.Range(“A1:L1″).Font.Bold = True
.Range(“A1:L1″).Font.Size = 15
‘Copete
.Range(“A2:L2″).Merge()
.Range(“A2:L2″).Value = titulo
.Range(“A2:L2″).Font.Bold = True
.Range(“A2:L2″).Font.Size = 12
Const primeraLetra As Char = “A”
Const primerNumero As Short = 3
Dim Letra As Char, UltimaLetra As Char
Dim Numero As Integer, UltimoNumero As Integer
Dim cod_letra As Byte = Asc(primeraLetra) – 1
Dim sepDec As String = Application.CurrentCulture.NumberFormat.NumberDecimalSeparator
Dim sepMil As String = Application.CurrentCulture.NumberFormat.NumberGroupSeparator
‘Establecer formatos de las columnas de la hija de cálculo
Dim strColumna As String = “”
Dim LetraIzq As String = “”
Dim cod_LetraIzq As Byte = Asc(primeraLetra) – 1
Letra = primeraLetra
Numero = primerNumero
Dim objCelda As Excel.Range
For Each c As DataGridViewColumn In DataGridView1.Columns
If c.Visible Then
If Letra = “Z” Then
Letra = primeraLetra
cod_letra = Asc(primeraLetra)
cod_LetraIzq += 1
LetraIzq = Chr(cod_LetraIzq)
Else
cod_letra += 1
Letra = Chr(cod_letra)
End If
strColumna = LetraIzq + Letra + Numero.ToString
objCelda = .Range(strColumna, Type.Missing)
objCelda.Value = c.HeaderText
objCelda.EntireColumn.Font.Size = 8
‘objCelda.EntireColumn.NumberFormat = c.DefaultCellStyle.Format
If c.ValueType Is GetType(Decimal) OrElse c.ValueType Is GetType(Double) Then
objCelda.EntireColumn.NumberFormat = “#” + sepMil + “0″ + sepDec + “00″
End If
End If
Next
Dim objRangoEncab As Excel.Range = .Range(primeraLetra + Numero.ToString, LetraIzq + Letra + Numero.ToString)
objRangoEncab.BorderAround(1, Excel.XlBorderWeight.xlMedium)
UltimaLetra = Letra
Dim UltimaLetraIzq As String = LetraIzq
‘CARGA DE DATOS
Dim i As Integer = Numero + 1
For Each reg As DataGridViewRow In DataGridView1.Rows
LetraIzq = “”
cod_LetraIzq = Asc(primeraLetra) – 1
Letra = primeraLetra
cod_letra = Asc(primeraLetra) – 1
For Each c As DataGridViewColumn In DataGridView1.Columns
If c.Visible Then
If Letra = “Z” Then
Letra = primeraLetra
cod_letra = Asc(primeraLetra)
cod_LetraIzq += 1
LetraIzq = Chr(cod_LetraIzq)
Else
cod_letra += 1
Letra = Chr(cod_letra)
End If
strColumna = LetraIzq + Letra
‘ acá debería realizarse la carga
.Cells(i, strColumna) = IIf(IsDBNull(reg.ToString), “”, reg.Cells(c.Index).Value)
‘.Cells(i, strColumna) = IIf(IsDBNull(reg.(c.DataPropertyName)), c.DefaultCellStyle.NullValue, reg(c.DataPropertyName))
‘.Range(strColumna + i, strColumna + i).In()
End If
Next
Dim objRangoReg As Excel.Range = .Range(primeraLetra + i.ToString, strColumna + i.ToString)
objRangoReg.Rows.BorderAround()
objRangoReg.Select()
i += 1
Next
UltimoNumero = i
‘Dibujar las líneas de las columnas
LetraIzq = “”
cod_LetraIzq = Asc(“A”)
cod_letra = Asc(primeraLetra)
Letra = primeraLetra
For Each c As DataGridViewColumn In DataGridView1.Columns
If c.Visible Then
objCelda = .Range(LetraIzq + Letra + primerNumero.ToString, LetraIzq + Letra + (UltimoNumero – 1).ToString)
objCelda.BorderAround()
If Letra = “Z” Then
Letra = primeraLetra
cod_letra = Asc(primeraLetra)
LetraIzq = Chr(cod_LetraIzq)
cod_LetraIzq += 1
Else
cod_letra += 1
Letra = Chr(cod_letra)
End If
End If
Next
‘Dibujar el border exterior grueso
Dim objRango As Excel.Range = .Range(primeraLetra + primerNumero.ToString, UltimaLetraIzq + UltimaLetra + (UltimoNumero – 1).ToString)
objRango.Select()
objRango.Columns.AutoFit()
objRango.Columns.BorderAround(1, Excel.XlBorderWeight.xlMedium)
End With
m_Excel.Cursor = Excel.XlMousePointer.xlDefault
End Sub
Private Sub ToolStripButton2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
ExportarDatosExcel(DataGridView1, “expor”)
End Sub
EXCELENTE !!!!…. gracias hermano, ya llevaba 3 dias con un modulo de import/export Excel y nada daba resultado….pero con tu solucion vuelvo a ver las estrellas … saludos desde arequipa!!!:..
De nada. Me alegra que te sirvió el ejemplo.
Este código me ha ayudado mucho, por eso colaboro con mi granito de arena:
He optimizado la función “nombreColumna” para que llegue ahsta la columna ZZ (702 cols.)
Acá les dejo el código:
Public Function nombreColumna(ByVal numero As Integer) As String
Dim columna(703) As String
columna(1) = “A”
columna(2) = “B”
columna(3) = “C”
columna(4) = “D”
columna(5) = “E”
columna(6) = “F”
columna(7) = “G”
columna(8) = “H”
columna(9) = “I”
columna(10) = “J”
columna(11) = “K”
columna(12) = “L”
columna(13) = “M”
columna(14) = “N”
columna(15) = “O”
columna(16) = “P”
columna(17) = “Q”
columna(18) = “R”
columna(19) = “S”
columna(20) = “T”
columna(21) = “U”
columna(22) = “V”
columna(23) = “W”
columna(24) = “X”
columna(25) = “Y”
columna(26) = “Z”
Dim i = 27
For Ltr1 = 1 To 26
For Ltr2 = 1 To 26
columna(i) = columna(Ltr1) & columna(Ltr2)
i += 1
Next
Next
Return columna(numero)
End Function
TECKNOCK, muchas gracias por colaborar!, pero hay que tener en cuenta que si el libro que creamos es compatible con excel 97-2003 el máximo de columnas permitidas es de 256 (hasta IV). Solo como comentario para que lo tengan en cuenta.
Eliseo
Cuando ejecuto la aplicacion local funcioana correctamente, pero cuando lo subo en un sitio web no genera el excel pero tampoco sale error
mira yo estoy haciend una conecion a my sql
no se como hare lo estube haciendo pero aja
necesito ayuda trate de hacerlo todas las formas que estan aca y me da error todo
Mano, eres lo maximo funca perfecto.
[...] [...]
muy bueno, felicidades, son de los mejores post que he leido y consultado
Gracias!!! Fue de mucha ayuda
Oye el codigo no me funciona me marca un erro en da.fill (ds)
el error me marca que no se encontro sqlException
me puedes ayudar