Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag for United States of America

asked on

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

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

Avatar of kaufmed
kaufmed
Flag of United States of America image

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.
Avatar of CipherIS

ASKER

.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.
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
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");
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

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");
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.
ASKER CERTIFIED SOLUTION
Avatar of CipherIS
CipherIS
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I rebooted my comp and it resolved it.  Not a clue what the issue was.