Data Export (DataGrid --> Excel) 1

[JGresko]

Last month a visitor to this forum posted this code. I've tried it out and found Excel opens, but the worksheet is empty. I've never interfaced with Excel from ASP.NET before. Is anyone familiar enough with what this snippet is doing to see what the problem is.

Thanks in advance,
Judy

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
   Response.ContentType = "application/vnd.ms-excel"
   Response.Charset = ""
   Me.EnableViewState = False
   Dim tw As New System.IO.StringWriter()
   Dim hw As New System.Web.UI.HtmlTextWriter(tw)
   DataGrid1.RenderControl(hw)
   Response.Write(tw.ToString())
   Response.End()
End Sub

 

[jfrost10]

Do you mean the worksheet is empty-no data, or worksheet is empty-can't see it?

We had a problem where for some reason the newly created worksheet would be hidden, and we had to manually un-hide it from within XHell.

Jack

 

[JGresko]

Empty, no data

It has the name of the ASPX page as the worksheet name (ie on the tab), but there is no data (and the gridlines are turned off).

Could it be reading the DataGrid before it's bound to it's content or is it a problem with the code that passes it?

 

[JGresko]

Empty, no data

It has the name of the ASPX page as the worksheet name (ie on the tab), but there is no data (and the gridlines are turned off).

Could it be reading the DataGrid before it's bound to it's content or is it a problem with the code that passes it?

 

[checkai]

I am having some issues with DataGrid as well. This export code seems to work great up to a point. I'm not sure if that point is records (don't think so) or size? I have a max on my datagrid to only return 10000 records. When I have anywhere from 5+ columns It will sometimes prompt me to save, and other times hang and the page says it's done without prompting me to Open/Save.

Any clues as to when I should enable/disable my Export Option...(i.e. when col * row > 60000)...

Any help would be great!!

DLC

 

[crazyboybert]

Hmm looks to me like your stream writer doesn't get the html for the datagrid? I'll be honest i'm not really familiar with the writer methods above but have used the same approach(changing the content type and writing a datagrid to the response stream) to create excel spreadsheets with no problem...

I wondered wether you meant to have

response.Write([red]hw[/red].ToString()) ??

just a thought

Rob

 

[checkai]

Well, the thing is...most of time it works great! It doesn't when ther is a Lot of data. didn't know if these writer's have a limit to them?

DLC

 

[checkai]

Is there a way for this exported .xls file to open in a new window when the export button is pressed?

DLC

 

[checkai]

I am now getting this error. I believe it is because my headers are sortable.

Control 'dgBU__ctl1__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Control 'dgBU__ctl1__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.

Source Error:


Line 143: Dim tw As New System.IO.StringWriter()
Line 144: Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Line 145: dgBU.RenderControl(hw)
Line 146: Response.Write(tw.ToString())
Line 147: Response.End()

any ideas?

 

[checkai]

Any Ideas regarding exporting with AllowSorting set to true?

DLC

 

[checkai]

ANything? mine bonks when sorting is on...

 

[snoopdougie]

Any sortable, pageable etc settings result in errors doing this export, but it works for a blank grid. Therefore, there are 2 possible solns -

1. disable the sorting etc in code prior to exporting

2. Declare a new grid in your code
dim newgrid as new datagrid
newgrid.datasource = oldgrid.datasource
newgrid.databind()

newgrid has no formatting. If you want to format it, use newgrid.itemstyle.backcolor = blue etc.

 

[newmem]

Here's a very good artcile which explains the work-around to overcome the errors caused by the paging,sorting.

http://www.c-sharpcorner.com/Code/2003/Sept/ExportASPNetDataGridToExcel.asp

 

[adamroof]

Old post, but new info...
Response.End() seems to be key. Also, set allow sorting to false to prevent errors. The change does not need to be set back.

    Sub BtnExport_Click(sender As Object, e As EventArgs)
        dgChange.AllowSorting=False
        BindDG("UploadID", "ASC")

        Dim strFileName As String = "TSMiResearch.xls"

        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=""" & strFileName & """")
        Response.ContentEncoding = System.Text.Encoding.UTF7
        Response.Charset = ""
        EnableViewState = False
        Dim oStringWriter As New System.IO.StringWriter
        Dim oHTMLTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)

        dgChange.RenderControl(oHTMLTextWriter)
        Response.Write(oStringWriter.ToString())
        Response.End()

    End Sub