By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,749 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,749 IT Pros & Developers. It's quick & easy.

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

P: 3
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
Oct 13 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.