ASP.net Gridview Export to Excel, it works but bombs with large number of lines

There is a limit of 65K lines in any Excel spreadsheet. You'll need to be aware of this if 50K isn't the upper bound of your record count.

Personally, I use Oledb to export data from my dataset to the Excel spreadsheet. Then I just serve up the Excel spreadsheet by redirecting to the relevant filename.

Here's a sample:

      Public Shared Function FillWorksheet(ByVal conn As OleDbConnection, ByVal lineId As Integer) As Integer
            Dim dt As DataTable = GetWorkTickets(lineId)

            For Each dr As DataRow In dt.Rows
                  Using cmd As New OleDbCommand(GetInsertCommand(dr), conn)
                        cmd.ExecuteNonQuery()
                  End Using
            Next

            Return dt.Rows.Count
      End Function

      Private Shared Function GetInsertCommand(ByVal dr As DataRow) As String
            Dim sb As New System.Text.StringBuilder(512)
            With sb
                  .Append("INSERT INTO [Sheet1$] ")
                  .Append("([Equip No],      [Work Order No], [Problem],      [Component], [Task],      [Prob Comments], [Originator], ")
                  .Append("[SafetyNote], [Access], [Underbuild], [County], [Repair/Replacement Information], ")
                  .Append("[Closed Date], [Twr Length]) ")
                  .Append("VALUES (")
                  .Append(String.Format("'{0}', ", dr("EquipNo").ToString))
                  .Append(String.Format("'{0}', ", dr("WorkTicketNumber").ToString))
                  .Append(String.Format("'{0}', ", dr("Problem").ToString))
                  .Append(String.Format("'{0}', ", dr("Component").ToString))
                  .Append(String.Format("'{0}', ", dr("Task").ToString))
                  Dim comment As String = dr("Comments").ToString.Replace("'", "''")
                  .Append(String.Format("'{0}', ", comment.Substring(0, Math.Min(comment.Length, 255))))
                  .Append(String.Format("'{0}', ", dr("Originator").ToString))
                  .Append(String.Format("'{0}', ", dr("SafetyNote").ToString))
                  .Append(String.Format("'{0}', ", dr("Access").ToString))
                  .Append(String.Format("'{0}', ", dr("Underbuild").ToString))
                  .Append(String.Format("'{0}', ", dr("County").ToString))
                  .Append(String.Format("'{0}', ", String.Empty))      ' repair/replace info
                  .Append(String.Format("'{0}', ", String.Empty))      ' closed date
                  .Append(String.Format("'{0}')", String.Empty)) ' twr length
            End With

            Return sb.ToString
      End Function