Export gridview to excel

Question

0.00/5 (No votes)

See more:

Hi,
Please help me.
I am exporting gridview to excel. but exported excel file can not be open.

And one another reason is i have declared .xls in my code but export in .xlsx format.

In my system it export in .xls format but on server export in .xlsx format so occur the problem.
This is My code-

DataSet ds = SqlHelper.ExecuteDataset(ErpGlobal.DBCONNECTIONSTRING, CommandType.Text, "Select EmpName,EmpAdd,sal from EMP");
DGConsumption.DataSource = ds;
DGConsumption.DataBind();
if (DGConsumption.Rows.Count > 0)
{
DataSet ds2 = SqlHelper.ExecuteDataset(ErpGlobal.DBCONNECTIONSTRING, CommandType.Text, "select LocalOrder,CustomerOrderNo,replace(convert(varchar(11),DispatchDate,106),' ','-') as dispachdate from ordermaster where orderid=" + DDOrderNo.SelectedValue + "");
DGConsumption.Style.Add("font-size", "1em");
Response.Clear();
string attachment = "attachment; filename=Production Order Wise.xlsx";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
DGConsumption.GridLines = GridLines.Vertical;
DGConsumption.RenderControl(htmlWrite);
Response.Write(@"

Production Of Items :Performa Invoice No -: " + ds2.Tables[0].Rows[0]["LocalOrder"].ToString() + "
Ship Date :	" + ds2.Tables[0].Rows[0]["dispachdate"].ToString() + "	P.O Number :	" + ds2.Tables[0].Rows[0]["CustomerOrderNo"].ToString() + "

" + stringWrite.ToString());
Response.End();

Posted 12-Jul-13 0:20am

Rajeev prasad yadav

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

maneavnash · Answer 1 · 2013-07-12T02:08:00

C#

try this example 

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td align="right">
<asp:ImageButton ID="btnExcel" runat="server" 
onclick="btnExcel_Click" />

</td>
</tr>
<tr>
<td>
<asp:GridView runat="server" ID="gvdetails" DataSourceID="dsdetails"  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="UserId" HeaderText="UserId" />
<asp:BoundField DataField="UserName" HeaderText="UserName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" />
<asp:BoundField DataField="Location" HeaderText="Location" />
</Columns>
</asp:GridView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="dsdetails" runat="server" ConnectionString="<%$ConnectionStrings:dbconnection %>"
SelectCommand="select * from UserInformation"/>
</div>
</form>
</body>
</html>

maneavnash · Answer 2 · 2013-07-12T02:10:00

C#

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();
//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();
}

Export gridview to excel

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Export gridview to excel

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us