Been stuck on this issue for a while now finally narrowed it down to the fact that AllowSorting is True. When I try to run the excel export With sorting, excel opens up into a blank document without any gridlines. Turned it off and the data appears as expected. I thought that if i turned off the sorting in the excel export button click event, then turn it back on afterwards, this would fix the issue, however this has not seemed to be the case.
I have also tried shifting where I turn off page sorting just to make sure I didn't place it in the wrong spot but still does not seem to change the result of the blank page.
Below is the coding I am using. I did read some talk about using a BindingSource but that also did not seem to work for me.
Am I missing a step or doing something wrong?
Dim tw As New StringWriter()
Dim hw As New HtmlTextWriter(tw)
Dim frm As HtmlForm = New HtmlForm()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition",
"attachment;filename=" & "Facility Detail Report" & ".xls")
Response.Charset = ""
EnableViewState = False
dgCustomers.AllowPaging = False
dgCustomers.AllowSorting = False
'Dim BindingSource As New BindingSource
'BindingSource.DataSource = dgCustomers.DataSource()
'dgCustomers.DataSource = BindingSource.DataSource
dgCustomers.DataBind()
Controls.Add(frm)
frm.Controls.Add(dgCustomers)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
dgCustomers.AllowPaging = True
dgCustomers.AllowSorting = True
dgCustomers.DataBind()
Well what I ended up doing was putting the DataSet from the search into a session variable. Then used that into a dummy data grid used to send out to excel. That way the website will still sort by column if they need to and the same data is still transferred to Excel. I feel like this is a "hack" in a way but no matter what I tried, I couldn't get the real datagrid to work with turning on and off the AllowSorting.
Maybe this will help someone else who is having the same issues. I spent wayyy too many hours looking around to solve what should have been really simple. I found several good solutions but they did not seem to apply for my situation. Hopefully this helps someone in similar shoes spend less time trying to get it to work.
Dim DummydgCustomers As New WebControls.DataGrid
DummydgCustomers.DataSource = Session("dsCustomers").tables(0)
DummydgCustomers.DataBind()
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" & "Facility Detail Report" & ".xls")
HttpContext.Current.Response.Charset = ""
EnableViewState = False
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
DummydgCustomers.RenderControl(hw)
HttpContext.Current.Response.Write(sw.ToString())
HttpContext.Current.Response.End()