Storing an extra blank space (which is for hidden viewstate )also stored into excel

[ammu001]

Response.ContentType Excel

I am trying to export a datagrid into an excel sheet.
What it now does is the entire page is saved as an excel sheet in XlHtml file format. I want it in xlWorkbookNormal format and also have to delete that extra blank space in the excel sheet. Please help out if somebody knows.



Dim dt As DataTable = GetDataTableFromDatabase()
GridView2.DataSource = dt
GridView2.DataBind()
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=OpenRequisitions.xls")
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
GridView1.Visible = False ' making other controls on the page invisible
btnDBtoExcel.Visible = False
btnExcelLoad.Visible = False
Dim oStringWriter As New System.IO.StringWriter()
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

Try

GridView2.RenderControl(oHtmlTextWriter)
Response.Write(oStringWriter.ToString())
Response.End()

Catch ex As Exception

End Try


When I try the reverse operation , since the format is XlHtml and not xlWorkbookNormal, it is not filling into dataset . I dont get a solution.
Pls Help Out. Here is the code for getting it.

Dim strConn As String
Dim excelobj As New Excel.Application()
Dim sheets As Excel.Sheets
Dim sheetName As String
'strFileName contains the full path of the Excel file
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=Excel 8.0;"

Try

Dim theWorkbook As Excel.Workbook
theWorkbook = excelobj.Workbooks.Open(strFileName, 0, True, 5, "", "", True, Excel.XlPlatform.xlWindows, "\t", False, False, 0, True)
sheets = theWorkbook.Worksheets 'get the sheets
sheetName = CType(sheets(1), Excel.Worksheet).Name.Trim() 'get the sheet you want

Catch ex As Exception

End Try
Try
'You must use the $ after the object you reference in the spreadsheet
Dim myCommand As New OleDbDataAdapter("SELECT * from [" + sheetName + "$]", strConn)
Dim myDataSet As New DataSet()
Dim iRowsAffected As Integer = myCommand.Fill(myDataSet, "ExcelInfo")
GridView1.DataSource = myDataSet.Tables("ExcelInfo").DefaultView
GridView1.DataBind()
GridView2.Visible = False
btnExcelLoad.Visible = False
btnDBtoExcel.Visible = False
FileUpload1.Visible = False

Catch ex As Exception
Response.Write("File not in Normal workbook format")
End Try