Export datagrids to Excel

[Guest]

I have a web page written in asp.net that has multiple datagrids on it that
would need to be exported to Excel. Each of the datagrids would be a subset
of what the datagrid above it was.

Thus far, I've had no luck in finding anything to work reliably. Some of
the files I've saved (using a radio button click event), will open fine in
Excel, others give me an "Unable to open file" message.

Any help appreciated.

Thanks,

SC

 

[Cowboy \(Gregory A. Beamer\)]

For simple pages, you can export the page as Excel as tables will render.
For more complex Excel, you will either have to program against the Excel
objects (set up Office components on the web server), which is awfully
expensive, perf wise, or get a third party component. My suggestion would be
to find a third party component on a site like componentsource.com.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************

 

[Guest]

I found the following online article and sample to be useful. Check it out

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

Suresh

----- (e-mail address removed) wrote: ----

I have a web page written in asp.net that has multiple datagrids on it tha
would need to be exported to Excel. Each of the datagrids would be a subse
of what the datagrid above it was

Thus far, I've had no luck in finding anything to work reliably. Some o
the files I've saved (using a radio button click event), will open fine i
Excel, others give me an "Unable to open file" message

Any help appreciated

Thanks

S

 

[Guest]

I've tried a VB version of this and just end up with a blank page with
nothing in it.


SC

 

[Guest]

It's working for me in VB.NET as well
Can you post your code? Maybe some C# wasn't coverted properly

Suresh

FYI: (in VB.NET from the code that worked for me
Private Sub btnConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConvert.Clic
Response.Clear(
Response.Buffer = Tru
Response.ContentType = "application/vnd.ms-excel
Response.Charset = "
Me.EnableViewState = Fals

Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter(
Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter

Me.ClearControls(DataGrid1
DataGrid1.RenderControl(oHtmlTextWriter

Response.Write(oStringWriter.ToString()

Response.End(
End Su

Private Sub ClearControls(ByVal ctrl As Control
Dim i As Int3

For i = ctrl.Controls.Count - 1 To 0 Step -
ClearControls(ctrl.Controls(i)
Nex

If ctrl.GetType().ToString() <> "TableCell" The
If Not ctrl.GetType().GetProperty("SelectedItem") Is Nothing The
Dim literal As LiteralControl = New LiteralControl(
ctrl.Parent.Controls.Add(literal
Tr
literal.Text = CType(ctrl.Controls.GetType().GetProperty("SelectedItem").GetValue(ctrl, Nothing), System.String
Catc
End Tr
ctrl.Parent.Controls.Remove(ctrl
End I
Els
Dim literal As LiteralControl = New LiteralControl(
ctrl.Parent.Controls.Add(literal
literal.Text = CType(ctrl.Controls.GetType().GetProperty("Text").GetValue(ctrl, Nothing), System.String
ctrl.Parent.Controls.Remove(ctrl
End I

Retur
End Su

----- (e-mail address removed) wrote: ----

I've tried a VB version of this and just end up with a blank page wit
nothing in it


S

 

[Guest]

When I put in your code, exactly as you have it below, I change only the
Private Sub btnconvert_click line, to reflect the button I am using. It's a
radio button.

The code I have executing follows:

Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChanged

Response.Clear()

Response.Buffer = True

Response.ContentType = "application/vnd.ms-excel"

Response.Charset = ""

Me.EnableViewState = False

Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter()

Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New
System.Web.UI.HtmlTextWriter(oStringWriter)

Me.ClearControls(DataGrid1)

DataGrid1.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString())

Response.End()

End Sub

Private Sub ClearControls(ByVal ctrl As Control)

Dim i As Int32

For i = ctrl.Controls.Count - 1 To 0 Step -1

ClearControls(ctrl.Controls(i))

Next

If ctrl.GetType().ToString() <> "TableCell" Then

If Not ctrl.GetType().GetProperty("SelectedItem") Is Nothing Then

Dim literal As LiteralControl = New LiteralControl()

ctrl.Parent.Controls.Add(literal)

Try

literal.Text =
CType(ctrl.Controls.GetType().GetProperty("SelectedItem").GetValue(ctrl,
Nothing), System.String)

Catch

End Try

ctrl.Parent.Controls.Remove(ctrl)

End If

Else

Dim literal As LiteralControl = New LiteralControl()

ctrl.Parent.Controls.Add(literal)

literal.Text =
CType(ctrl.Controls.GetType().GetProperty("Text").GetValue(ctrl, Nothing),
System.String)

ctrl.Parent.Controls.Remove(ctrl)

End If

Return

End Sub



as you can see, it is exactly the same as yours, excluding the sub on the
first line.



However, when this runs, I'll step thru it in debug mode, and it'll only
return a blank page. It appears that it's just empty. Do not know why. It
does appear to iterate thru the loop in the clearcontrols section. When it
gets to response.end though, it just sits there.

I'm trying to return a customer list, with names, addresses, etc. on it.



Steve

It's working for me in VB.NET as well.
Can you post your code? Maybe some C# wasn't coverted properly.

Suresh.

FYI: (in VB.NET from the code that worked for me)
Private Sub btnConvert_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles btnConvert.Click

Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False

Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter()
Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)

Me.ClearControls(DataGrid1)
DataGrid1.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString())

Response.End()
End Sub

Private Sub ClearControls(ByVal ctrl As Control)
Dim i As Int32

For i = ctrl.Controls.Count - 1 To 0 Step -1
ClearControls(ctrl.Controls(i))
Next

If ctrl.GetType().ToString() <> "TableCell" Then
If Not ctrl.GetType().GetProperty("SelectedItem") Is Nothing Then
Dim literal As LiteralControl = New LiteralControl()
ctrl.Parent.Controls.Add(literal)
Try
literal.Text =

CType(ctrl.Controls.GetType().GetProperty("SelectedItem").GetValue(ctrl,
Nothing), System.String)

Catch
End Try
ctrl.Parent.Controls.Remove(ctrl)
End If
Else
Dim literal As LiteralControl = New LiteralControl()
ctrl.Parent.Controls.Add(literal)
literal.Text =

CType(ctrl.Controls.GetType().GetProperty("Text").GetValue(ctrl, Nothing),
System.String)

 

[Guest]

Interesting..
This may be something other than just converting the grid to excel

Couple of more checks/tests you can perform to narrow the problem down.

1. Ensure EnableViewState is set to true on the page that has datagrid

2. In the immediate window check if there's anything at all is in thi
oStringWriter.ToString(
This will narrow the problem to the conversion code. If it has your table in HTML format then it might be a browser problem

3. Try creating a test page that simply has a button and a grid. Clicking on the button export the grid to Excel. Leave all the default settings of the page

Suresh

----- (e-mail address removed) wrote: ----

When I put in your code, exactly as you have it below, I change only th
Private Sub btnconvert_click line, to reflect the button I am using. It's
radio button

The code I have executing follows

Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object
ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChange

Response.Clear(

Response.Buffer = Tru

Response.ContentType = "application/vnd.ms-excel

Response.Charset = "

Me.EnableViewState = Fals

Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter(

Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = Ne
System.Web.UI.HtmlTextWriter(oStringWriter

Me.ClearControls(DataGrid1

DataGrid1.RenderControl(oHtmlTextWriter

Response.Write(oStringWriter.ToString()

Response.End(

End Su

Private Sub ClearControls(ByVal ctrl As Control

Dim i As Int3

For i = ctrl.Controls.Count - 1 To 0 Step -

ClearControls(ctrl.Controls(i)

Nex

If ctrl.GetType().ToString() <> "TableCell" The

If Not ctrl.GetType().GetProperty("SelectedItem") Is Nothing The

Dim literal As LiteralControl = New LiteralControl(

ctrl.Parent.Controls.Add(literal

Tr

literal.Text
CType(ctrl.Controls.GetType().GetProperty("SelectedItem").GetValue(ctrl
Nothing), System.String

Catc

End Tr

ctrl.Parent.Controls.Remove(ctrl

End I

Els

Dim literal As LiteralControl = New LiteralControl(

ctrl.Parent.Controls.Add(literal

literal.Text
CType(ctrl.Controls.GetType().GetProperty("Text").GetValue(ctrl, Nothing)
System.String

ctrl.Parent.Controls.Remove(ctrl

End I

Retur

End Su



as you can see, it is exactly the same as yours, excluding the sub on th
first line



However, when this runs, I'll step thru it in debug mode, and it'll onl
return a blank page. It appears that it's just empty. Do not know why. I
does appear to iterate thru the loop in the clearcontrols section. When i
gets to response.end though, it just sits there

I'm trying to return a customer list, with names, addresses, etc. on it



Stev

It's working for me in VB.NET as well
Can you post your code? Maybe some C# wasn't coverted properly
Private Sub btnConvert_Click(ByVal sender As System.Object, ByVal e A

System.EventArgs) Handles btnConvert.Clic

Response.Clear(
Response.Buffer = Tru
Response.ContentType = "application/vnd.ms-excel
Response.Charset = "
Me.EnableViewState = Fals System.IO.StringWriter(
Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = Ne System.Web.UI.HtmlTextWriter(oStringWriter
If Not ctrl.GetType().GetProperty("SelectedItem") Is Nothing Then
Dim literal As LiteralControl = New LiteralControl()
ctrl.Parent.Controls.Add(literal)
Try
literal.Text =

CType(ctrl.Controls.GetType().GetProperty("SelectedItem").GetValue(ctrl,
Nothing), System.String)

Catch
End Try
ctrl.Parent.Controls.Remove(ctrl)
End If
Else
Dim literal As LiteralControl = New LiteralControl()
ctrl.Parent.Controls.Add(literal)
literal.Text =

CType(ctrl.Controls.GetType().GetProperty("Text").GetValue(ctrl, Nothing),
System.String)

 

[jim corey]

One problem with exporting the datagrid to excel is that it only works
for one page -- if you ever set up the datagrid with paging, and it's
more than one page, it's not going to work.

What I've been doing is iterating through the datatable or collection
that
is the datasource for the grid, assembling an html table in a string,
and doing a response.write as excel (don't have the syntax in front of
me, but you can find this in ASP groups).

It's a bit of coding, but if you have to do this many times you'll
probably see
a pattern and find a way to create a funtion to do this.

 

[Guest]

<<1. Ensure EnableViewState is set to true on the page that has datagrid.>>

I just clicked on the aspx page for the one I'm looking at. There are 2
enableviewstate properties - one, enableviewstate = true, the other,
enableviewstatemac = false. In the code for the button index changed, the
enableviewstate is set to false.

<<2. In the immediate window check if there's anything at all is in this
oStringWriter.ToString() This will narrow the problem to the conversion
code. If it has your table in HTML format then it might be a browser
problem.>>

This is what I get when I step thru the code, after the response.write has
been done:

Write Argument not specified for parameter 'FileNumber' of 'Public Sub
Write(FileNumber As Integer, ParamArray Output() As Object)'.

the expression "Tostring" has its value = ToString "ASP.customerlist_aspx"
String

I have my stop points on the button index changed, and then on the
rendercontrol step. I single step (F8) thru the next 2 statements, and what
I get is above.

<<3. Try creating a test page that simply has a button and a grid.
Clicking on the button export the grid to Excel. Leave all the default
settings of the page.>>

I will give this a try, but the display above works fine as far as it is
displaying stuff now. Using my old method, it would export the display to
excel, but would barf if it was over 30 records displayed.


Thanks for the help - any further advice is appreciated.

SC

 

[Guest]

So far, nothing I've tried has worked. Any advice appreciated.

SC