CipherIS
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:
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.
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();
}
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.
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.
ASKER
I am trying to do something similar to
http://www.c-sharpcorner.com/UploadFile/DipalChoksi/exportxl_asp2_dc11032006003657AM/exportxl_asp2_dc.aspx
http://www.c-sharpcorner.com/UploadFile/DipalChoksi/exportxl_asp2_dc11032006003657AM/exportxl_asp2_dc.aspx
ASKER
Here is more code. When the submit button is clicked the following method is called.
Error02.jpg
Error03.jpg
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();
}
}
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();
}
Error01.jpgError02.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");
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",
ASKER
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.
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");
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");
Did u remove either one of these lines and see what is causing the issue?
gvrow.Cells[4].Style.Add("
gvrow.Cells[7].Style.Add("
Shouldn't this line be?
gvrow.Cells[7].Style.Add("
ASKER
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.
gvrow.Cells[4].Style.Add("
gvrow.Cells[7].Style.Add("
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I rebooted my comp and it resolved it. Not a clue what the issue was.