Problem while exporting to xlsx format in asp.net c#

Posted by Prabu_Spark under ASP.NET on 6/11/2013 | Points: 10 | Views : 3578 | Status : [Member] | Replies : 1
Hi sir,
I want to export the gridview data to xlsx format. I am getting the error "Excel cannot open the file file.xlsx because the file format or
file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. I attached the
my code. Kindly give me the solution for this problem.


HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "";
string showDt = null;
showDt = DateTime.Now.ToString("dd-MM-yyyy-hh-mm-ss-tt");

HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=file.xlsx");
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";


System.IO.StringWriter stringWrite = new System.IO.StringWriter();
stringWrite.GetStringBuilder();
stringWrite.WriteLine("<b><center>&nbsp;&nbsp;&nbsp;&nbsp;<font size=3 face=Verdana color=#3593F8>" + reportheading + "</font></center></b>");
stringWrite.WriteLine("<br>");
stringWrite.WriteLine("<b><center><font size=2 face=Verdana color=#0000FF>" + subheading + "</font></center></b>");
stringWrite.WriteLine("<br>");



System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GridView GridView1 = new GridView();
GridView1.DataSource = ds;
GridView1.DataBind();

GridView1.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
GridView1.HeaderStyle.BackColor = System.Drawing.Color.SteelBlue;
GridView1.HeaderStyle.ForeColor = System.Drawing.Color.White;
GridView1.HeaderStyle.Wrap = false;
GridView1.HeaderStyle.Font.Bold = true;
GridView1.RenderControl(htmlWrite);

stringWrite.WriteLine("<br>");
stringWrite.WriteLine("<b><center><font size=2 face=Verdana color=#0000FF> Total Number Of Records :" + count + "</font></center></b>");
stringWrite.WriteLine("<br>");
stringWrite.WriteLine("<b><center><font size=2 face=Verdana color=#0000FF> Report taken on :" + DateTime.Now.ToString("dd/MM/yyyy hh:mm tt") + "</font></center></b>");
stringWrite.WriteLine("<br>");

HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.End();

With regards,
J.Prabu.
[Email:prbspark@gmail.com]



Responses

Posted by: aswinialuri-19361 on: 6/11/2013 [Member] Starter | Points: 25

Up
0
Down
hi prabhu,
Try this it will help you

using System;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page
{
string strcon = ConfigurationManager.ConnectionStrings["sqlconn"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
getdata();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
/// <summary>
/// This event is used to export gridview data to word document
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnWord_Click(object sender, ImageClickEventArgs e)
{
gvdetails.AllowPaging = false;
gvdetails.DataBind();
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.doc"));
Response.Charset = "";
Response.ContentType = "application/ms-word";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gvdetails.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
getdata();
}
/// <summary>
/// This Event is used to export gridview data to Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void getdata()
{
SqlConnection con = new SqlConnection(strcon);
SqlCommand cmd = new SqlCommand("select * from Emp", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
gvdetails.DataSource = ds;
gvdetails.DataBind();

}
protected void btnExcel_Click(object sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gvdetails.AllowPaging = false;
gvdetails.DataBind();
getdata();
//Change the Header Row back to white color
////gvdetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Applying stlye to gridview header cells
////for (int i = 0; i < gvdetails.HeaderRow.Cells.Count; i++)
////{
////gvdetails.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
////}
////int j = 1;
//////This loop is used to apply stlye to cells based on particular row
////foreach (GridViewRow gvrow in gvdetails.Rows)
////{
////gvrow.BackColor = Color.White;
////if (j <= gvdetails.Rows.Count)
////{
////if (j % 2 != 0)
////{
////for (int k = 0; k < gvrow.Cells.Count; k++)
////{
////gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
////}
////}
////}
////j++;
////}
gvdetails.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}


}

Source Code:
<table>
<tr>
<td align="right">
<asp:ImageButton ID="btnExcel" runat="server" ImageUrl="~/ExcelImage.jpg"
onclick="btnExcel_Click" />
<asp:ImageButton ID="btnWord" runat="server" ImageUrl="~/WordImage.jpg"
onclick="btnWord_Click" />
</td>
</tr>
<tr>
<td>
<asp:GridView runat="server" ID="gvdetails" AllowPaging="true" AllowSorting="true" AutoGenerateColumns="false">
<%--<RowStyle BackColor="#EFF3FB" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />--%>
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="salary" HeaderText="salary" />
<asp:BoundField DataField="Department" HeaderText="Department" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
I hope it will help you
Mark as answer if it helps you
Thanks&Regards

Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response