vb.net y excel

[pirula]

Yo lo hago asi y me funciona, sobre todo si quieres colocar las celdas de una determinada manera.

Otra forma es darle formato excel a la salida, mucho más rápido, si lo quieres ver pidemelo.


Protected Sub Export2Excel(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)


Dim dtSRlist As System.Data.DataTable

'dimensiones de objetos excel
Dim oExcel As New Excel.Application
Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
Dim oCells As Excel.Range
Dim sFile As String, sTemplate As String

'creamos el datatable si no lo tenemos

dtSRlist = GridView.DataSource

'indicamos la ruta del archivo Excel
sFile = "C:\hoja.xls"

'El nombre de la plantilla
sTemplate = "C:\template.xls"

'no queremos que se vea el Excel trabajando
oExcel.Visible = False : oExcel.DisplayAlerts = False
'abrimos la plantilla:
oBooks = oExcel.Workbooks
oBooks.Open(sTemplate)

'seleccionamos la hoja donde queremos exportar
oBook = oBooks.Item(1)
oSheets = oBook.Worksheets
oSheet = CType(oSheets.Item(1), Excel.Worksheet)

'damos un nombre a la hoja
oSheet.Name = "Hoja1"
oCells = oSheet.Cells

'llamamos a DumpData que vuelca los datos en el libro
DumpData(dtSRlist, oCells)
oSheet.SaveAs(sFile)
oBook.Close()

'Salir de Excel y liberar objetos y cerramos la conexión
oExcel.Quit()

Marshal.ReleaseComObject(oCells)
Marshal.ReleaseComObject(oSheet)
Marshal.ReleaseComObject(oSheets)
Marshal.ReleaseComObject(oBook)
Marshal.ReleaseComObject(oBooks)
Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
oBooks = Nothing
oBook = Nothing
oSheets = Nothing
oSheet = Nothing
oCells = Nothing
System.GC.Collect()


End Sub



'Esta función contiene la lógica que exporta un DataTable a una hoja Excel:
Private Function DumpData(ByVal dt As DataTable, ByVal oCells As Excel.Range)
Dim dr As DataRow, ary() As Object
Dim iRow As Integer, iCol As Integer

'Output Column Headers
For iCol = 0 To dt.Columns.Count - 1
oCells(2, iCol + 1) = dt.Columns(iCol).ToString
Next

'Output Data
For iRow = 0 To dt.Rows.Count - 1
dr = dt.Rows.Item(iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
oCells(iRow + 3, iCol + 1) = ary(iCol).ToString
Response.Write(ary(iCol).ToString & vbTab)
Next
Next
End Function