Solved

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

Posted on 2013-01-10
10
5,214 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

624 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