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

[ammu001]

Response.Conten tType 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 xlWorkbookNorma l format and also have to delete that extra blank space in the excel sheet. Please help out if somebody knows.



Dim dt As DataTable = GetDataTableFro mDatabase()
GridView2.DataS ource = dt
GridView2.DataB ind()
Response.Clear( )
Response.Buffer = True
Response.AddHea der("content-disposition", "attachment;fil ename=OpenRequi sitions.xls")
Response.Conten tType = "applicatio n/vnd.ms-excel"
Response.Charse t = ""
Me.EnableViewSt ate = False
GridView1.Visib le = False ' making other controls on the page invisible
btnDBtoExcel.Vi sible = False
btnExcelLoad.Vi sible = False
Dim oStringWriter As New System.IO.Strin gWriter()
Dim oHtmlTextWriter As New System.Web.UI.H tmlTextWriter(o StringWriter)

Try

GridView2.Rende rControl(oHtmlT extWriter)
Response.Write( oStringWriter.T oString())
Response.End()

Catch ex As Exception

End Try


When I try the reverse operation , since the format is XlHtml and not xlWorkbookNorma l, 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.Applicati on()
Dim sheets As Excel.Sheets
Dim sheetName As String
'strFileName contains the full path of the Excel file
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0;" + "Data Source=" + strFileName + ";" + "Extended Properties=Exce l 8.0;"

Try

Dim theWorkbook As Excel.Workbook
theWorkbook = excelobj.Workbo oks.Open(strFil eName, 0, True, 5, "", "", True, Excel.XlPlatfor m.xlWindows, "\t", False, False, 0, True)
sheets = theWorkbook.Wor ksheets '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 OleDbDataAdapte r("SELECT * from [" + sheetName + "$]", strConn)
Dim myDataSet As New DataSet()
Dim iRowsAffected As Integer = myCommand.Fill( myDataSet, "ExcelInfo" )
GridView1.DataS ource = myDataSet.Table s("ExcelInfo"). DefaultView
GridView1.DataB ind()
GridView2.Visib le = False
btnExcelLoad.Vi sible = False
btnDBtoExcel.Vi sible = False
FileUpload1.Vis ible = False

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