Solved

ASP.NET - C# - Problems with Response when exporting to excel

Posted on 2013-01-10
10
5,027 Views
Last Modified: 2013-01-15
I wrote the below code to export data from a database to excel.  When it exports I receive an error that the download failed with a button to retry.  When I click on retry it looks like it will work.  But when it opens I get a copy of my asp.NET page in excel instead of the data.  

I have a try/catch and after the Response.Flush() an error is caught and the error says:

Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.

Any idea how to fix?

I used the below code in another ASP.NET form and it works perfectly but in this form it does not and the code is the same.

private void ExportToExcel(DataTable dt)
{
	string sFileName = "Report_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";

	GridView gv = new GridView();

	gv.Attributes.Add("class", "textmode");
	gv.DataSource = dt;
	gv.DataBind();

	string style = @"<style> .textmode { mso-number-format:\@; } </style>";

	Response.Clear();
	Response.ClearContent();
	Response.ClearHeaders();
	Response.Buffer = true;
	Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", sFileName));
	Response.ContentType = "application/vnd.ms-excel";
	//Response.ContentType = "application/vnd.openxmlformats";

	Response.Charset = "utf-8";
	Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");

	StringWriter sw = new StringWriter();
	HtmlTextWriter htw = new HtmlTextWriter(sw);

	//Change the Header Row Foreground to white color
	gv.HeaderRow.Style.Add("color", "#FFFFFF");
	//Applying stlye to gridview header cells
	for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
	{
		//Change to Blue
		gv.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
	}

	//This loop is used to apply stlye to cells based on particular row
	int j = 1;
	foreach (GridViewRow gvrow in gv.Rows)
	{
		gvrow.BackColor = Color.White;

		//Format Specific Cell as Text
		gvrow.Cells[4].Style.Add("mso-number-format", "\\@");
		gvrow.Cells[7].Style.Add("mso-number-format", "\\#\\,\\#\\#0\\.00");

		if (j <= gv.Rows.Count)
		{
			if (j % 2 != 0)
			{
				for (int k = 0; k < gvrow.Cells.Count; k++)
				{
					gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
				}
			}
		}
		j++;
	}

	gv.RenderControl(htw);

	Response.Write(style);
	Response.Write(sw.ToString());
	Response.Flush();
	Response.End();
}

Open in new window

0
Comment
Question by:CipherIS
  • 7
  • 2
10 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38763248
It appears you are trying to return an Excel file, but you are also trying to render a GridView. What is the goal of this page? If you are trying to return an Excel file, then you should be using Response.BinaryWrite, and you should not be attempting to render a GridView.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 38763277
.I am trying to return an excel file.  Purpose of the gridview is so i Can programatically format the columns so when it exports to excel it is in the correct format (text, decimals, etc).  That is why I have the - gv.RenderControl(htw) code.
0
 
LVL 1

Author Comment

by:CipherIS
ID: 38763320
0
 
LVL 1

Author Comment

by:CipherIS
ID: 38763527
Here is more code.  When the submit button is clicked the following method is called.

private void GetData()
{
	lblMessage.Text = "";
	SqlConnection conn = new SqlConnection(myConString);

	try
	{
		conn.Open();
		SqlCommand cmd = new SqlCommand("mySPROC", conn);
		cmd.Parameters.Clear();
		cmd.CommandType = CommandType.StoredProcedure;

		if (this.txtFrom.Text.Trim() != string.Empty)
		{
			cmd.Parameters.AddWithValue("@dtStart", this.txtFrom.Text.Trim());
		}
		if (this.txtTo.Text.Trim() != string.Empty)
		{
			cmd.Parameters.AddWithValue("@dtEnd", this.txtTo.Text.Trim());
		}

		SqlDataAdapter da = new SqlDataAdapter(cmd);
		DataTable dt = new DataTable();
		da.Fill(dt);

		conn.Close();

		if (dt.Rows.Count > 0)
		{
			this.ExportToExcel(dt);
		}
	}
	catch (ApplicationException ex)
	{
		lblMessage.Text = ex.Message;
		return;
	}
	catch (SqlException ex)
	{
		lblMessage.Text = ex.Message;
		return;
	}
	catch (Exception ex)
	{
		lblMessage.Text = ex.ToString();
	}
	finally
	{
		if (conn.State == ConnectionState.Open)
			conn.Close();
	}
}

Open in new window


private void ExportToExcel(DataTable dt)
{
	string sFileName = "Report_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";

	GridView gv = new GridView();

	gv.Attributes.Add("class", "textmode");
	gv.DataSource = dt;
	gv.DataBind();

	string style = @"<style> .textmode { mso-number-format:\@; } </style>";

	Response.Clear();
	Response.ClearContent();
	Response.ClearHeaders();
	Response.Buffer = true;
	Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", sFileName));
	Response.ContentType = "application/vnd.ms-excel";
	//Response.ContentType = "application/vnd.openxmlformats";

	Response.Charset = "utf-8";
	Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");

	StringWriter sw = new StringWriter();
	HtmlTextWriter htw = new HtmlTextWriter(sw);

	//Change the Header Row Foreground to white color
	gv.HeaderRow.Style.Add("color", "#FFFFFF");
	//Applying stlye to gridview header cells
	for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
	{
		//Change to Blue
		gv.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
	}

	//This loop is used to apply stlye to cells based on particular row
	int j = 1;
	foreach (GridViewRow gvrow in gv.Rows)
	{
		gvrow.BackColor = Color.White;

		//Format Specific Cell as Text
		gvrow.Cells[4].Style.Add("mso-number-format", "\\@");
		gvrow.Cells[7].Style.Add("mso-number-format", "\\#\\,\\#\\#0\\.00");

		if (j <= gv.Rows.Count)
		{
			if (j % 2 != 0)
			{
				for (int k = 0; k < gvrow.Cells.Count; k++)
				{
					gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
				}
			}
		}
		j++;
	}

	gv.RenderControl(htw);

	Response.Write(style);
	Response.Write(sw.ToString());[embed=file 627030][embed=file 627031][embed=file 627032]
	Response.Flush();
	Response.End();
}

Open in new window

Error01.jpg
Error02.jpg
Error03.jpg
0
 
LVL 20

Expert Comment

by:informaniac
ID: 38763553
Can you check if removing this line helps.

Response.Write(style);

If it does, then add this line a bit above in the code.

before

gv.RenderControl(htw);

Or I guess this line is causing the issue.

gv.Attributes.Add("class", "textmode");
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 1

Author Comment

by:CipherIS
ID: 38763587
I tried commenting it out and also moving it on top of gv.RenderControl.  Still having problems.

What I do not understand is that I am using the exact same code in another ASP.NET page.  The only difference is below.  In the other page I'm formatting different gvrow.Cells.

//Format Specific Cell as Text
gvrow.Cells[4].Style.Add("mso-number-format", "\\@");
gvrow.Cells[7].Style.Add("mso-number-format", "\\#\\,\\#\\#0\\.00");

Open in new window

0
 
LVL 20

Expert Comment

by:informaniac
ID: 38764059
Did you remove the class line also?

Did u remove either one of these lines and see what is causing the issue?
gvrow.Cells[4].Style.Add("mso-number-format", "\\@");
gvrow.Cells[7].Style.Add("mso-number-format", "\\#\\,\\#\\#0\\.00");

Shouldn't this line be?
gvrow.Cells[7].Style.Add("mso-number-format", "\\#\\,\\#\\#0\\.000");
0
 
LVL 1

Author Comment

by:CipherIS
ID: 38764104
I removed lines

gvrow.Cells[4].Style.Add("mso-number-format", "\\@");
gvrow.Cells[7].Style.Add("mso-number-format", "\\#\\,\\#\\#0\\.00");

no difference.

When I look at value sw.ToString() in the immediate window - it contains XML data for excel.  but when excel opens after the errors per images it shows my asp.net page in excel.
0
 
LVL 1

Accepted Solution

by:
CipherIS earned 0 total points
ID: 38764959
I rebooted my comp and it resolved it.  Not a clue what the issue was.

Thanks
0
 
LVL 1

Author Closing Comment

by:CipherIS
ID: 38777587
I rebooted my comp and it resolved it.  Not a clue what the issue was.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now