Solved

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

Posted on 2013-01-10
10
5,090 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
"Emulate" TAB key when press Enter Key 3 47
bound data table problem 2 33
Adding items to a C# list incrementally 5 33
asp.net mvc 2 25
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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.

828 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