Export to Excel in ASP.NET is a very common feature, which I'm sure everyone who has worked in ASP.NET would have had the chance to implement.
Whenever we choose the Export to Excel option from our Application, a dialog box pops us with the option to Open or to Save.
By chance if the user checks off the option "Always ask before opening this type of file" that is shown in the dialog box, from next time the user will not be able to see the dialog box. Instead, the
excel file opens up in the same window.
To set back this option, the following steps can be followed:
1. Go to Windows Explorer.
2. On the Tools menu, click Folder Options, and then click on the
File Types tab.
3. From the Registered file types list box, select the XLS extension, and then click
Advanced.
4. In the Edit File Type dialog box, set the Confirm open after download to selected.
5. Make sure the Browse in same window option is not selected, and then click
OK.
The above steps will make sure that we get the dialog box as shown above. However, since this is an option set at the client computer, these steps cannot be mandated to be followed in every computer that browses the application.
So, from the code level, we must make sure that the excel file is opened in a separate window. One possible option for this is to Save the file to the web server, and then open the file in a separate window.
In the above method, the file is saved to the Web Server inside the folder "Excel". Of course, this folder must have write permissions for the user. But it will definitely ensure that the excel file is
opened in a new window in the client computer.
Excellent. Now that we're on this topic, here's a helper class that I use in regards to exporting of a DataGrid. It is updated to reflect a GridView for an application I'm upgrading, but I haven't tested it out on the GridView
yet.
public sealed class ExportHelper
{
public static void ExportToCsv(string gridViewText, string contentType, HttpResponse response)
{
const string m_Delimiter_Column = ",";
string m_Delimiter_Row = Environment.NewLine;
response.ContentType = contentType;
Regex m_RegEx = new Regex(@"(>\s+<)", RegexOptions.IgnoreCase);
gridViewText = m_RegEx.Replace(gridViewText, "><");
This looks exactly like what I'm after - but I'm a .NET/VWDE newbie and don't know where to start to implement it on my website.
I have a number of Pages with GridViews on them and would like to add an [Export to Excel] button above each one. I'd be very grateful for the steps I need to go through to do so with your code - safely assuming
that I don't have a cooking clue where to start. Here's how far I've got within VWD Express:
Add a Webform page to the site, specifying Visual C# as the Language to use.
Paste Ryan's Code between the
<script
runat="server">
and </script> at the top of the page - above the <head> tags.
Switch to Design View.
Add the Gridview to the page.
Add a Button above the Gridview.
Change the Button Text to "Export to Excel".
Double-click on the button in Design View.
Type ExportHelper
.ExportToExcelWord(I don't know what these parameters are or how to format them) between the braces.
I hope that you - or some other competent person - can help me implement this properly, by either telling me how to complete the parameters (Gridview name, HTML, ?? I suppose)
correctly, or correcting the above.
Thanks very much, Ryan.
Regards
Gary
Gary Bartlett
gb at prodsol dot co dot nz
+64 9 473-9530
+64 21 776-390
http://www.prodsol.co.nz - Dramatic improvement through pattern-level intervention
The server control is taking a little longer than expected, so it's just easier to paste what I pass to the helper function. Sorry if it's a little messy. I'm in the process of revamping the app where I took the code from. Basically,
in ExportGridView you pass the GridView control, the type of file you want exported, and a file name. It should work right off the bat, but if something is messed up let me know and I'll fix it.
public enum ExportType
{
CSV, Excel, Word
}
private static void ClearControls(Control control)
{
for(int index = control.Controls.Count-1; index >= 0; index--)
{
ClearControls(control.Controls[index]);
}
if(!(control is TableCell))
{
if(control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl m_Literal = new LiteralControl();
control.Parent.Controls.Add(m_Literal);
I pasted your second block of code below the first (as described in my previous email).
Here's what I got:
Error 1 The type or namespace name 'StringWriter' could not be found (are you missing a using directive or an assembly reference?)
Error 2 The type or namespace name 'TextWriter' could not be found (are you missing a using directive or an assembly reference?)
Error 3 The best overloaded method match for 'System.Web.UI.HtmlTextWriter.HtmlTextWriter(System.IO.TextWriter)' has some invalid arguments
Error 4 Argument '1': cannot convert from 'StringWriter' to 'System.IO.TextWriter'
Error 5 The best overloaded method match for 'ASP.xportxl_aspx.ExportGridView(System.Web.UI.WebControls.GridView, string, ASP.xportxl_aspx.ExportType)' has some invalid arguments
Error 6 Argument '1': cannot convert from 'string' to 'ASP.xportxl_aspx.ExportType'
Error 7 Argument '3': cannot convert from 'string' to c:\inetpub\wwwroot\intranet\XportXL.aspx 128 49
I tried adding various using directives (System, System.IO) at the start of the Script block, without any joy. I'm too new at this to know what to do and where to do it!
Please confirm/correct what I've done in VWD Express:
Add a Webform page to the site, specifying Visual C# as the Language to use.
Paste Ryan's First block of code between the
<script
runat="server">
and </script> at the top of the page - above the <head> tags.
Pase Ryans's second block of code after the first block.
Switch to Design View.
Add the Gridview (GridView1) to the page.
Add a Button above the Gridview.
Change the Button Text to "Export to Excel".
Double-click on the button in Design View.
Add ExportGridView("GridView1",
"Test.xls", "Excel"); between the braces.
Thanks very much, Ryan - I hope that you aren;t kicking yourself for responding to my email!
Regards
Gary
Gary Bartlett
gb at prodsol dot co dot nz
Gary Bartlett
gb at prodsol dot co dot nz
+64 9 473-9530
+64 21 776-390
http://www.prodsol.co.nz - Dramatic improvement through pattern-level intervention
Error 5 The best overloaded method match for 'ASP.xportxl_aspx.ExportGridView(System.Web.UI.WebControls.GridView, string, ASP.xportxl_aspx.ExportType)' has some invalid arguments
Error 6 Argument '1': cannot convert from 'string' to 'ASP.xportxl_aspx.ExportType'
Error 7 Argument '3': cannot convert from 'string' to c:\inetpub\wwwroot\intranet\XportXL.aspx 128 49
ExportType should be passed as ExportType.Excel, not "Excel" as it is an enum, not a string.
Feel free to email me any other errors to Ryan(Dot)Olshan(at)StrongTypes(Dot)com
I can export an image url and then I can see it in excel document, but all images are on others images and I want to save them into excel file (no internet connection to view images).
If you use this code with a site the has an SSL certificate in IE, it wont work. You will be prompted with a msgbox 'Internet Explorer was not able to open this Internet Site. The requested site is either unavailable or cannot be found. Please try again
later'
laksk
Member
250 Points
50 Posts
How To: Make "Export to Excel" always open excel in a separate Window
Jan 24, 2006 12:13 PM|LINK
Export to Excel in ASP.NET is a very common feature, which I'm sure everyone who has worked in ASP.NET would have had the chance to implement.
Whenever we choose the Export to Excel option from our Application, a dialog box pops us with the option to Open or to Save.
By chance if the user checks off the option "Always ask before opening this type of file" that is shown in the dialog box, from next time the user will not be able to see the dialog box. Instead, the excel file opens up in the same window.
To set back this option, the following steps can be followed:
1. Go to Windows Explorer.
2. On the Tools menu, click Folder Options, and then click on the File Types tab.
3. From the Registered file types list box, select the XLS extension, and then click Advanced.
4. In the Edit File Type dialog box, set the Confirm open after download to selected.
5. Make sure the Browse in same window option is not selected, and then click OK.
The above steps will make sure that we get the dialog box as shown above. However, since this is an option set at the client computer, these steps cannot be mandated to be followed in every computer that browses the application.
So, from the code level, we must make sure that the excel file is opened in a separate window. One possible option for this is to Save the file to the web server, and then open the file in a separate window.
The code for this is given below:
private void ExportToExcel(DataGrid dgExport)
{
try
{
string strFileName = String.Empty, strFilePath= String.Empty;
strFilePath = Server.MapPath(@"../Excel/") + "ExcelFileName" + ".xls";
if (File.Exists(strFilePath))
{
File.Delete(strFilePath);
}
System.IO.StringWriter oStringWriter =new StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
StreamWriter objStreamWriter;
string strStyle =@"
";
objStreamWriter = File.AppendText(strFilePath);
dgExport.RenderControl(oHtmlTextWriter);
objStreamWriter.WriteLine(strStyle);
objStreamWriter.WriteLine(oStringWriter.ToString());
objStreamWriter.Close();
string strScript = "<script language=JavaScript>window.open('../Excel/" + "ExcelFileName" +
".xls','dn','width=1,height=1,toolbar=no,top=300,left=400,right=1,
scrollbars=no,locaton=1,resizable=1');</script>";
if(!Page.IsStartupScriptRegistered("clientScript"))
{
Page.RegisterStartupScript("clientScript", strScript);
}
}
catch(Exception)
{
//Handle Exception
}
}
In the above method, the file is saved to the Web Server inside the folder "Excel". Of course, this folder must have write permissions for the user. But it will definitely ensure that the excel file is opened in a new window in the client computer.
Cheers.
laksk
StrongTypes
All-Star
30801 Points
6013 Posts
ASPInsiders
Re: How To: Make "Export to Excel" always open excel in a separate Window
Jan 24, 2006 01:16 PM|LINK
Excellent. Now that we're on this topic, here's a helper class that I use in regards to exporting of a DataGrid. It is updated to reflect a GridView for an application I'm upgrading, but I haven't tested it out on the GridView yet.
public sealed class ExportHelper
{
public static void ExportToCsv(string gridViewText, string contentType, HttpResponse response)
{
const string m_Delimiter_Column = ",";
string m_Delimiter_Row = Environment.NewLine;
response.ContentType = contentType;
Regex m_RegEx = new Regex(@"(>\s+<)", RegexOptions.IgnoreCase);
gridViewText = m_RegEx.Replace(gridViewText, "><");
gridViewText = gridViewText.Replace(m_Delimiter_Row, String.Empty);
gridViewText = gridViewText.Replace("</td></tr>", m_Delimiter_Row);
gridViewText = gridViewText.Replace("<tr><td>", String.Empty);
gridViewText = gridViewText.Replace(m_Delimiter_Column, "\\" + m_Delimiter_Column);
gridViewText = gridViewText.Replace("</td><td>", m_Delimiter_Column);
m_RegEx = new Regex(@"<[^>]*>", RegexOptions.IgnoreCase);
gridViewText = m_RegEx.Replace(gridViewText, String.Empty);
gridViewText = HttpUtility.HtmlDecode(gridViewText);
response.Write(gridViewText);
response.End();
}
public static void ExportToExcelWord(string gridViewText, string contentType, HttpResponse response)
{
response.ContentType = contentType;
response.Write(gridViewText);
response.End();
}
}
GaryBartlett
Member
580 Points
116 Posts
Re: How To: Make "Export to Excel" always open excel in a separate Window
Feb 15, 2006 11:29 PM|LINK
Hi Ryan,
This looks exactly like what I'm after - but I'm a .NET/VWDE newbie and don't know where to start to implement it on my website.
I have a number of Pages with GridViews on them and would like to add an [Export to Excel] button above each one. I'd be very grateful for the steps I need to go through to do so with your code - safely assuming that I don't have a cooking clue where to start. Here's how far I've got within VWD Express:
Type ExportHelper
.ExportToExcelWord(I don't know what these parameters are or how to format them) between the braces.I hope that you - or some other competent person - can help me implement this properly, by either telling me how to complete the parameters (Gridview name, HTML, ?? I suppose) correctly, or correcting the above.
Thanks very much, Ryan.
Regards
Gary
gb at prodsol dot co dot nz
+64 9 473-9530
+64 21 776-390
http://www.prodsol.co.nz - Dramatic improvement through pattern-level intervention
StrongTypes
All-Star
30801 Points
6013 Posts
ASPInsiders
Re: How To: Make "Export to Excel" always open excel in a separate Window
Feb 16, 2006 01:44 AM|LINK
Hi Gary,
Give me about an hour or so and I'll create a server control out of this.
Ryan
StrongTypes
All-Star
30801 Points
6013 Posts
ASPInsiders
Re: How To: Make "Export to Excel" always open excel in a separate Window
Feb 16, 2006 03:29 AM|LINK
The server control is taking a little longer than expected, so it's just easier to paste what I pass to the helper function. Sorry if it's a little messy. I'm in the process of revamping the app where I took the code from. Basically, in ExportGridView you pass the GridView control, the type of file you want exported, and a file name. It should work right off the bat, but if something is messed up let me know and I'll fix it.
public enum ExportType
{
CSV, Excel, Word
}
private static void ClearControls(Control control)
{
for(int index = control.Controls.Count-1; index >= 0; index--)
{
ClearControls(control.Controls[index]);
}
if(!(control is TableCell))
{
if(control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl m_Literal = new LiteralControl();
control.Parent.Controls.Add(m_Literal);
m_Literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
else
{
if(control.GetType().GetProperty("Text") != null)
{
LiteralControl m_Literal = new LiteralControl();
control.Parent.Controls.Add(m_Literal);
m_Literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
}
}
public static void ExportGridView(GridView gridView, string fileName, ExportType exportType)
{
const string m_Http_Attachment = "attachment;filename=";
const string m_Http_Content = "content-disposition";
HttpResponse m_Response = HttpContext.Current.Response;
m_Response.Clear();
m_Response.ClearContent();
m_Response.ClearHeaders();
m_Response.Buffer = true;
m_Response.AddHeader(m_Http_Content, m_Http_Attachment + fileName);
m_Response.ContentEncoding = Encoding.UTF8;
m_Response.Cache.SetCacheability(HttpCacheability.NoCache);
StringWriter m_StringWriter = new StringWriter();
HtmlTextWriter m_HtmlWriter = new HtmlTextWriter(m_StringWriter);
gridView.AllowPaging = false;
gridView.HeaderStyle.Font.Bold = true;
gridView.DataBind();
ClearControls(gridView);
gridView.RenderControl(m_HtmlWriter);
string m_gridViewText = m_StringWriter.ToString();
switch(exportType)
{
case ExportType.Excel:
ExportHelper.ExportToExcelWord(m_gridViewText, "application/vnd.ms-excel", m_Response);
break;
case ExportType.CSV:
ExportHelper.ExportToCsv(m_gridViewText, "application/csv", m_Response);
break;
case ExportType.Word:
ExportHelper.ExportToExcelWord(m_gridViewText, "application/vnd.ms-word", m_Response);
break;
default:
ExportHelper.ExportToExcelWord(m_gridViewText, "application/vnd.ms-excel", m_Response);
break;
}
}
GaryBartlett
Member
580 Points
116 Posts
Re: How To: Make "Export to Excel" always open excel in a separate Window
Feb 16, 2006 04:19 PM|LINK
Hi Ryan,
Thanks very much for the help!
I pasted your second block of code below the first (as described in my previous email).
Here's what I got:
Error 1 The type or namespace name 'StringWriter' could not be found (are you missing a using directive or an assembly reference?)
Error 2 The type or namespace name 'TextWriter' could not be found (are you missing a using directive or an assembly reference?)
Error 3 The best overloaded method match for 'System.Web.UI.HtmlTextWriter.HtmlTextWriter(System.IO.TextWriter)' has some invalid arguments
Error 4 Argument '1': cannot convert from 'StringWriter' to 'System.IO.TextWriter'
Error 5 The best overloaded method match for 'ASP.xportxl_aspx.ExportGridView(System.Web.UI.WebControls.GridView, string, ASP.xportxl_aspx.ExportType)' has some invalid arguments
Error 6 Argument '1': cannot convert from 'string' to 'ASP.xportxl_aspx.ExportType'
Error 7 Argument '3': cannot convert from 'string' to c:\inetpub\wwwroot\intranet\XportXL.aspx 128 49
I tried adding various using directives (System, System.IO) at the start of the Script block, without any joy. I'm too new at this to know what to do and where to do it!
Please confirm/correct what I've done in VWD Express:
Thanks very much, Ryan - I hope that you aren;t kicking yourself for responding to my email!
Regards
Gary
Gary Bartlett
gb at prodsol dot co dot nz
gb at prodsol dot co dot nz
+64 9 473-9530
+64 21 776-390
http://www.prodsol.co.nz - Dramatic improvement through pattern-level intervention
StrongTypes
All-Star
30801 Points
6013 Posts
ASPInsiders
Re: How To: Make "Export to Excel" always open excel in a separate Window
Feb 16, 2006 04:32 PM|LINK
Sorry for leaving out the namespace declarations.
Add the following using statements:
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
ExportType should be passed as ExportType.Excel, not "Excel" as it is an enum, not a string.
Feel free to email me any other errors to Ryan(Dot)Olshan(at)StrongTypes(Dot)com
HTH,
Ryan
worldoferic
Member
15 Points
3 Posts
Re: How To: Make "Export to Excel" always open excel in a separate Window
May 25, 2006 10:47 AM|LINK
Hi,
How to export the displayed webform to excel or word as it is.
Finally , Your sample code only exports text to excel.
How can you export images and charts to word or excel?
Your co - operation will be highly appreciated.
Regards
Eric
songezo@optisolutions.co.za
jVela
Member
127 Points
30 Posts
Re: How To: Make "Export to Excel" always open excel in a separate Window
Jun 05, 2006 02:59 PM|LINK
Hello, can I export images to excel?.
I can export an image url and then I can see it in excel document, but all images are on others images and I want to save them into excel file (no internet connection to view images).
Thanks and regards
Jamie_78
Member
5 Points
1 Post
Re: How To: Make "Export to Excel" always open excel in a separate Window
Nov 03, 2006 12:57 PM|LINK
For information people.
If you use this code with a site the has an SSL certificate in IE, it wont work. You will be prompted with a msgbox 'Internet Explorer was not able to open this Internet Site. The requested site is either unavailable or cannot be found. Please try again later'
To fix this comment out:
'm_Response.Cache.SetCacheability(HttpCacheability.NoCache);
and add:
m_response.addHeader("Cache-Control", "max-age=0");
A full explanation of this workaround can be found here: http://forum.java.sun.com/thread.jspa?threadID=233446&tstart=0
It explains that this has been a bug in IE since V4.0!