How To: Make "Export to Excel" always open excel in a separate Window - ng.asp-net-forum.faq_frequently_asked

CodeVerge.Net ^Beta
Explore Item Entry Register Login

Novell Forums

Can Reply: No

Members Can Edit: No

Online: Yes

Zone:

> NEWSGROUP > Asp.Net Forum > general_asp.net.faq_frequently_asked_questions

Tags:

Item Type:

NewsGroup

Date Entered:

1/24/2006 1:13:28 PM

Date Modified:

Subscribers:

Subscribe Alert

Rate It:

XPoints:

N/A

Replies:

Views:

117

Favorited:

Favorite

49 Items, 3 Pages 1 2 3 |< << Go >> >|

	laksk
	Asp.Net User

How To: Make "Export to Excel" always open excel in a separate Window

1/24/2006 1:13:28 PM

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 =@" ..text { mso-number-format:\@; }
";
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.

Regards
laksk

	StrongTypes
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

1/24/2006 2:16:11 PM

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();
    }
}

Ryan Olshan
Microsoft MVP, ASP.NET
Blog | Group | Website | Strong Coders Community

How to ask a question

	GaryBartlett
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

2/16/2006 12:29:29 AM

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:

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

	StrongTypes
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

2/16/2006 2:44:18 AM

Hi Gary,

Give me about an hour or so and I'll create a server control out of this.

Ryan

Ryan Olshan
Microsoft MVP, ASP.NET
Blog | Group | Website | Strong Coders Community

How to ask a question

	StrongTypes
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

2/16/2006 4:29:35 AM

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;
   }
  }

Ryan Olshan
Microsoft MVP, ASP.NET
Blog | Group | Website | Strong Coders Community

How to ask a question

	GaryBartlett
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

2/16/2006 5:19:26 PM

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:

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

	StrongTypes
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

2/16/2006 5:32:35 PM

Sorry for leaving out the namespace declarations.

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'

Add the following using statements:

<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>

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

HTH,
Ryan

Ryan Olshan
Microsoft MVP, ASP.NET
Blog | Group | Website | Strong Coders Community

How to ask a question

	worldoferic
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

5/25/2006 10:47:10 AM

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

[email protected]

	jVela
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

6/5/2006 2:59:20 PM

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

	Stone Deng
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

11/3/2006 8:44:36 AM

study only

Gambler is the real life

	Jamie_78
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

11/3/2006 12:57:31 PM

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!

	haoest
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

11/20/2006 2:06:55 AM

I have a question.

Is it better to buffer the Excel file to the file system? or to write the content straight into response stream?

Writing into the file system poses a two problems: performance and garbage.

Writing straight into stream sounds good. But I've had some annoying experience in the past: if i hit download button and choose to Open the file without any instances of Excel open, IE would have to open an instance of Excel, and then after Excel opens, it would give an error saying something like "temporary file xxx is not found..." But if there's an existing instance of Excel when I hit the "open" button, it displays the file correctly.

So, what do you think?

Debugger is my best friend.

	samirindia
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

11/24/2006 10:31:25 AM

good stuff but i want something diff.. i want make one query and direct download all data to client system in "Excel sheet" so plz.. give me answer..

Thanks,

http://dotnetuncut.blogspot.com
Asp.net uncut
add url
freelance web developer

	caokou
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

12/6/2006 3:20:09 AM

samirindia:

good stuff but i want something diff.. i want one query and direct download all data to client system in "Excel sheet" so plz.. give me answer..

Thanks,

Are you wanting to to put the sheet on the client machine or prompt for download?

	c.ratnakar
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

12/7/2006 9:39:24 AM

Just copy paste this piece of information in ur buttin click autmaticall a new excel sheet will be displayed on ur monitor in a new window from ur datagrid

Response.Clear();

Response.AddHeader("Content-Disposition", "Attachment; FileName = FileName.xls");

Response.Charset = "";

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "Application/vnd.xls";

System.IO.StringWriter stringWrite =

new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite =

new HtmlTextWriter(stringWrite);

DataGrid1.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

all da best

cheers frm Rutts

Tension Nahi Leneka Apun Hai Nah...

	mukeshajmera
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

1/24/2007 5:27:10 AM

Hello ,

I tried using the example for excel, and can successfully
"save" the spreadsheet to my machine,but the "open" option fails with this error:
"... \Temporary Internet Files\content.IE5\A5GR6p25\POWERbase.xls[1].xls' could not be found.
Check the spelling... If you are trying to open the file from your list of most recently
used files on the file menu, make sur that the file has not been renamed, moved, or deleted."

help, please???

Regards & Thanks
please replay back

Mukesh

[email protected]

	karthik_regina
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

1/24/2007 2:24:07 PM

Mukesh,

I am also having the same problem . Can anyone Help .

Thanks

Karthik

[email protected]

	mukeshajmera
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

1/25/2007 5:49:21 AM

Hello Everyone

when i use this code for excel file download using IE Browse then

Response.Clear();

Response.AddHeader("Content-Disposition", "Attachment; FileName = FileName.xls");

Response.Charset = "";

Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "Application/vnd.xls";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite =new HtmlTextWriter(stringWrite);

DataGrid1.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

i am getting bellow error and open button to be clicked twice

I tried using the example for excel, and can successfully

"save" the spreadsheet to my machine,but the "open" option fails with this error:

"... \Temporary Internet Files\content.IE5\A5GR6p25\POWERbase.xls[1].xls' could not be found.

Check the spelling... If you are trying to open the file from your list of most recently

used files on the file menu, make sur that the file has not been renamed, moved, or deleted."

Please Help

Regards

Mukesh

[email protected]

	AmitTiwari
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

2/23/2007 10:50:06 AM

Hi,

Can i save file without show save as dialog?

Thanks,

Amit Tiwari

Thanks,
Amit Tiwari

	AmitTiwari
	Asp.Net User

Re: How To: Make "Export to Excel" always open excel in a separate Window

2/23/2007 10:51:10 AM

Hi,

Can i save file without show save as dialog?

Thanks,

Amit Tiwari

Thanks,
Amit Tiwari

49 Items, 3 Pages 1 2 3 |< << Go >> >|

Free Download:

.NET Framework Class Library API

Invite

Members

SiteMap

ActivatedServiceTypeEntry Constructor (Type)

ConstructionCall Constructor (IMessage)

AddResource Method (String, String),2

AttributeUsageAttribute Class

ToString Method (Int32, Int32)

Privacy | Contact Us
All Times Are GMT