Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-10
10
Medium Priority
?
5,356 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

783 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