Export GridView to Excel cause System.OutOfMemoryException - ASP.NET VB

Recently I was working on a report where I need to display data in Excel sheet instead of PDF etc. So I designed the query which was returning more than 80,000 records for a given date criteria.
I used the technique of exporting data from gridview to Excel. Since the process is slow but most effcient where you are not required to create worksheet object. Things are managed by ASP.NET because Gridview has inbuilt functionality of exporting data to Excel.
Note: I was having MS office 2003 installed on my PC. there Excel 2003
I used the below code

Public Sub ExportDataToExcel()
' A function that bind data to GridView
bindDataToGridViewControl()
Response.Clear()
Response.Buffer = True
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Response.Write("")
Response.Write(vbCr & vbLf)
Response.Write("")
Dim tw As New StringWriter
Dim hw As New HtmlTextWriter(tw)
' Gridview that have data
GridView1.RenderControl(hw)
Response.AppendHeader("content-disposition","attachment;filename=Excel.xls")
Response.Write(tw.ToString())
Response.End()

End Sub


Issues:I started getting Exception "System.OutOfMemoryException"
After doing lot is searching on Internet, I came to know that Excel 2003's sheet has a limitation where a sheet can stored maximum of 65,536 records in one sheet.

If client has office 2010 installed or later version, one single sheet can contain more than 10,00,0000 records.

So the solution would be better to breakdown your DataTable records into multiple sets, and bind each set of records with each sheet. For that you need to created Worksheet object and each and ever column will be defnied by you.

Tips on Exception Handling

1. Never do a 'catch exception and do nothing'. If you hide an exception, you will never know if the exception happened or not. Lot of developers uses this handy method to ignore non-significant errors. You should always try to avoid exceptions by checking all the error conditions programmatically. In any case, catching an exception and doing nothing is not allowed. In the worst case, you should log the exception and proceed.

2. In case of exceptions, give a friendly message to the user, but log the actual error with all possible details about the error, including the time it occurred, method and class name etc.

3. Always catch only the specific exception, not generic exception.
Good:
Private Sub ReadFromFile (fileName As String)
Try
// read from file.
catch (FileIOException ex)
// log error.
// re-throw exception depending on your case.
Throw
End Try
End Sub

Not Good:
Private Function ReadFromFile (fileName As String) As String
Try
// read from file.
Catch (Exception ex)
// Catching general exception is bad... we will never know whether
// it was a file error or some other error.
// Here you are hiding an exception.
// In this case no one will ever know that an exception happened.
Return String.Empty
End Try
End Function

4. No need to catch the general exception in all your methods. Leave it open and let the application crash. This will help you find most of the errors during development cycle. You can have an application level (thread level) error handler where you can handle all general exceptions. In case of an 'unexpected general error', this error handler should catch the exception and should log the error in addition to giving a friendly message to the user before closing the application, or allowing the user to 'ignore and proceed'.

5. When you re throw an exception, use the throw statement without specifying the original exception. This way, the original call stack is preserved.
Good:
Catch
// do whatever you want to handle the exception
throw
Not Good:
catch (Exception ex)
// do whatever you want to handle the exception
throw ex

6. Do not write try-catch in all your methods. Use it only if there is a possibility that a specific exception may occur and it cannot be prevented by any other means. For example, if you want to insert a record if it does not already exists in database, you should try to select record using the key. Some developers try to insert a record without checking if it already exists. If an exception occurs, they will assume that the record already exists. This is strictly not allowed. You should always explicitly check for errors rather than waiting for exceptions to occur. On the other hand, you should always use exception handlers while you communicate with external systems like network, hardware devices etc. Such systems are subject to failure anytime and error checking is not usually reliable. In those cases, you should use exception handlers and try to recover from error.

7.Do not write very large try-catch blocks. If required, write separate try-catch for each task you perform and enclose only the specific piece of code inside the try-catch. This will help you find which piece of code generated the exception and you can give specific error message to the user.

8. Write your own custom exception classes if required in your application. Do not derive your custom exceptions from the ApplicationException class. Instead, inherit from base class System.Exception.

Good bye Exceptional Handling

Some Quick help

1) Make a field readonly:
txtName.Attributes.Add("readonly", "readonly")

2) Keyboard enter button is pressed in the textbox and fire an event:
This is done to ensure that when the keyboard enter button is pressed in the textbox we fire the "btnButton" click event
txtName.Attributes.Add("onkeypress", "return WebForm_FireDefaultButton(event, '" + btnButton.ClientID + "')")

3) To set the focus on a control like Textbox when the page loads.
ScriptManager.RegisterStartupScript(Page, Page.GetType(), "focus", "setTimeout(""try{ document.getElementById('" & txtName.ClientID & "').focus(); } catch(err){}"", 1200);", True)

4) To directly access the key and value of the resource file.
lblName.Text = GetGlobalResourceObject("ABC", "Name") ////Where Main refers for a file “ABC.resx”


5) To disable controls of a form/page

// Below is the method definition
Public Sub DisableControl(ByVal ctrl As Control)
For Each subctrl As Control In ctrl.Controls
DisableControl(subctrl)
Next

If TypeOf ctrl Is TextBox Then
CType(ctrl, TextBox).Enabled = True
CType(ctrl, TextBox).ReadOnly = True
ElseIf TypeOf ctrl Is RadioButtonList Then
CType(ctrl, RadioButtonList).Enabled = False
ElseIf TypeOf ctrl Is CheckBox Then
CType(ctrl, CheckBox).Enabled = False
ElseIf TypeOf ctrl Is RadioButton Then
CType(ctrl, RadioButton).Enabled = False
End If
End Sub