Dim style As String = "<style>.text{mso-number-format:\@;}</style>"
                Response.Clear()
                Response.Buffer = True
                Response.AddHeader("content-disposition", "attachment; filename=nomina.xls")
                Response.ContentType = "application/excel"
                Dim sw As StringWriter = New StringWriter()
                Dim ioSw As New System.IO.StringWriter()
                Dim htw As New System.Web.UI.HtmlTextWriter(ioSw)
                GridView1.RenderControl(htw)
                Response.Output.Write(style & ioSw.ToString().Replace("td", "td class='text'"))
                Response.Flush()
                Response.End()
 

' asi llamo la funcion que genera el excel
DataTableToExcel(CType(dt, DataTable))
 
'funcion que genera el excel
    Public Sub DataTableToExcel(ByVal pDataTable As DataTable)
        Dim vFileName As String = Path.GetTempFileName()
 
        FileOpen(1, vFileName, OpenMode.Output)
        Dim sb As String
        Dim dc As DataColumn
        For Each dc In pDataTable.Columns
            sb = dc.Caption & Microsoft.VisualBasic.ControlChars.Tab
        Next
        PrintLine(1, sb)
        Dim i As Integer = 0
        Dim dr As DataRow
        For Each dr In pDataTable.Rows
            i = 0
            sb = ""
            For Each dc In pDataTable.Columns
                If Not IsDBNull(dr(i)) Then
                    sb &= CStr(dr(i)) & Microsoft.VisualBasic.ControlChars.Tab
                Else
                    sb &= Microsoft.VisualBasic.ControlChars.Tab
                End If
                i += 1
            Next
            PrintLine(1, sb)
        Next
        FileClose(1)
        TextToExcel(vFileName)
    End Sub
 
 
'funcion que guarda el archivo
    Public Sub TextToExcel(ByVal pFileName As String)
        Dim vFormato As Excel.XlRangeAutoFormat
        Dim vCultura As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
 
        Dim Exc As Excel.Application = New Excel.Application
        Exc.Workbooks.OpenText(pFileName, , , , Excel.XlTextQualifier.xlTextQualifierNone, , True)
 
        Dim Wb As Excel.Workbook = Exc.ActiveWorkbook
        Dim Ws As Excel.Worksheet = Wb.ActiveSheet
 
        vFormato = Excel.XlApplicationInternational.xlGeneralFormatName
        Ws.Range(Ws.Cells(1, 1), Ws.Cells(Ws.UsedRange.Rows.Count, Ws.UsedRange.Columns.Count)).AutoFormat(vFormato)
        pFileName = Path.GetTempFileName.Replace("tmp", "xls")
        File.Delete(pFileName)
        Dim Nombre As String = InputBox("Ingrese el nombre del archivo:", "Guardar Archivo", "C:\")
        If Nombre <> "" Then
            Exc.ActiveWorkbook.SaveAs(Nombre, Excel.XlTextQualifier.xlTextQualifierNone - 1)
        End If
        Exc.ActiveWorkbook.SaveAs(pFileName, _
          Excel.XlTextQualifier.xlTextQualifierNone - 1)
        Exc.Quit()
        Ws = Nothing
        Wb = Nothing
        Exc = Nothing
        GC.Collect()
        If valor > -1 Then
            Dim p As System.Diagnostics.Process = New System.Diagnostics.Process
            p.EnableRaisingEvents = False
            'p.Start("Excel.exe", pFileName)
        End If
        System.Threading.Thread.CurrentThread.CurrentCulture = vCultura
        limpia()
    End Sub
 

Imports System.IO
Imports Microsoft.Office.Interop