Monday, July 17, 2006

Export DataGrid to Excel

Most of the applications which are being developed now require data to be exported to various formats. With ASP.Net this has become quite simple

private void Export2Excel(DataGrid grd)
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-Excel";
Response.Charset = "";
EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

// Convert the controls to string literals
ClearControls(grd);
grd.RenderControl(oHtmlTextWriter);

Response.Write(oStringWriter.ToString());
Response.End();
}

private void ClearControls(Control Ctrl)
{
for (int i= Ctrl.Controls.Count - 1; i >= 0; i--)
{
ClearControls(Ctrl.Controls[i]);
}

if (!(Ctrl is TableCell))
{
if (Ctrl.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl Lt = new LiteralControl();
Ctrl.Parent.Controls.Add(Lt);
try
{
Lt.Text = (string)Ctrl.GetType().GetProperty("SelectedItem").GetValue(Ctrl, null);
}
catch {}

Ctrl.Parent.Controls.Remove(Ctrl);
}
else if (Ctrl.GetType().GetProperty("Text") != null)
{
LiteralControl Lt = new LiteralControl();
Ctrl.Parent.Controls.Add(Lt);
Lt.Text = (string)Ctrl.GetType().GetProperty("Text").GetValue(Ctrl, null);

Ctrl.Parent.Controls.Remove(Ctrl);
}
}
return;
}


Hope this post helped you. Please feel free to post your comments.

15 Comments:

Anonymous Anonymous said...

Code works great! I also converted the C# to VB.NET....

Private Sub ExportToExcel()
'export to excel

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(grdResults)
grdResults.RenderControl(oHtmlTextWriter)

Response.Write(oStringWriter.ToString())

Response.End()
End Sub

Private Sub ClearControls(ByVal Ctrl As Control)
Dim i As Integer

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

If Not TypeOf Ctrl Is TableCell Then
If Not Ctrl.GetType().GetProperty("SelectedItem") Is Nothing Then
Dim Lt As LiteralControl = New LiteralControl()
Ctrl.Parent.Controls.Add(Lt)
Try
Lt.Text = CType(Ctrl.GetType().GetProperty("SelectedItem").GetValue(Ctrl, Nothing), String)
Catch

End Try

Ctrl.Parent.Controls.Remove(Ctrl)
ElseIf (Ctrl.GetType().GetProperty("Text") IsNot Nothing) Then
Dim Lt As LiteralControl = New LiteralControl()
Ctrl.Parent.Controls.Add(Lt)
Lt.Text = CType(Ctrl.GetType().GetProperty("Text").GetValue(Ctrl, Nothing), String)
Ctrl.Parent.Controls.Remove(Ctrl)
End If

End If

End Sub

10:52 AM  
Blogger Ganesh Ramamurthy said...

These are simple but really cool stuff in asp.net. ASp.Net gives us a bunch lot of utilities which makes our lives easy...

Watch for more in the ASP.Net Section.

10:02 PM  
Anonymous Anonymous said...

Hi..I tried with following code to export my datagrid to excel sheet..
But it is giving error as follow..
"myDataDrid_ctl0_ctl1.. "DataGridLinkButton" should be inside form tag" ..
Please let me know the answer for this question..

private void Button1_Click(object sender, System.EventArgs e)
{
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Dim stringWrite as new System.IO.StringWriter()
Dim htmlWrite as new HtmlTextWriter(stringWrite)
myDataGrid.RenderControl(htmlWrite) Response.Write(stringWrite.ToString())
Response.End()
}

9:25 PM  
Blogger Ganesh Ramamurthy said...

Can you post your html section also

1:50 PM  
Anonymous Anonymous said...

I need to edit ClearControl to handle CheckBox. Here is the code, it's not perfect but hope it can help somebody.

private void ClearControls(Control Ctrl)
{
for (int i = Ctrl.Controls.Count - 1; i >= 0; i--)
{
ClearControls(Ctrl.Controls[i]);
}
if (!(Ctrl is TableCell))
{
if (Ctrl.GetType().ToString() != "System.Web.UI.WebControls.CheckBox")
{
if (Ctrl.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl Lt = new LiteralControl();
Ctrl.Parent.Controls.Add(Lt);
try
{
Lt.Text = (string)Ctrl.GetType().GetProperty("SelectedItem").GetValue(Ctrl, null);
}
catch
{
}
Ctrl.Parent.Controls.Remove(Ctrl);
}
else if (Ctrl.GetType().GetProperty("Text") != null)
{
LiteralControl Lt = new LiteralControl();
Ctrl.Parent.Controls.Add(Lt);
Lt.Text = (string)Ctrl.GetType().GetProperty("Text").GetValue(Ctrl, null);
Ctrl.Parent.Controls.Remove(Ctrl);
}
}
else
{
LiteralControl Lt = new LiteralControl();
Ctrl.Parent.Controls.Add(Lt);
try
{
Lt.Text = ((CheckBox)Ctrl).Checked.ToString();
}
catch
{
}
Ctrl.Parent.Controls.Remove(Ctrl);
}
}
return;
}

4:26 PM  
Anonymous Anonymous said...

Code works very well if you are only trying to export. However, problems occur when you try to import one of these created Excel files back into your application as you really did not create an .xls file but rather a html version of the .xls file.

Happy coding!

5:59 AM  
Anonymous Raj said...

Hi Ganesh,

Thanks for the gr8 efforts.

I tried ur code but i found following error:

Error line: Ctrl.Parent.Controls.Add(Lt)


The Controls collection cannot be modified because the control contains code blocks (i.e. 'less then sign here percent sign here ... percent sign here greater than sign here').

Raj

rajmca05@yahoo.co.in
rajg@pmam.com

10:20 PM  
Blogger Gigo said...

The Post was Very Usefull, Thankz

11:23 PM  
Blogger unleashments said...

when we do this we get a dialog box asking us to open or save the file.is there a way by which we can avoid this dialog and autosave the file

3:40 AM  
Blogger Ganesh Ramamurthy said...

AutoSave could be made possible. However, since this is browser based we might not know which place to save in and the user might not have appropriate permissions. It is always advisable in browser based application to allow the user to save.

9:39 AM  
Anonymous Anonymous said...

Understandably your article helped me truly much in my college assignment. Hats incorrect to you dispatch, choice look progressive in the direction of more related articles promptly as its anecdote of my pick subject-matter to read.

6:14 PM  
Anonymous Anonymous said...

The same piece of code works of export to excel works fine for me when i rum the code from localhost. But when published on server, the open or save dialog box is not appearing. Ganesh, Can you tell why is it behaving so?

3:29 AM  
Blogger Ganesh Ramamurthy said...

Please make sure that you have appropriate permissions on the server to save the file. You could give aspnet_wp write access to the folder.

9:51 PM  
Blogger manoj said...

Hello,
Friends, it is not working.....

is any body can help....???

Actually, I need a export button which will help me to export data to excel

give me the full and final code.

9:50 PM  
Anonymous Anonymous said...

Hi Ganesh
Problem with mine export to excel is sometimes it works fine and sometimes only few rows are exported.

8:51 PM  

Post a Comment

<< Home