Export Gridview Data to Excel File Inside Ajax Update Panel Using ASP.NET and C#

Jaipal Reddy
8y
12.7k
0
0

Blog

ExportGridviewData.zip

In this article, we will discuss,

Create Table, Insert multiple records with single insert statement, Create Procedure.
How to Bind GridView using stored procedure.
How to resolve RegisterforEventValidation can only be called during Render().
How to Export Gridview data to excel inside Ajax UpdatePanel.

Deasin your DataBase :

Use the Following script to create Mas_Employee table.

CreateTable Mas_Employee(
ID IntPrimaryKeyIdentity,
Name Varchar(50),
Gender Varchar(50),
DeptId Int
)

Insert multiple records with single insert statement,

InsertInto Mas_Employee(Name,Gender,DeptId)Values
('Kishore','Male',1),
('Ashwin','Male',1),
('Sravan','Male',2),
('SumanGupta','Male',2),
('Ragavendra','Male',3),
('Anil','Male',3),
('Suman','Male',4),
('Jaipal','Male',4),
('Sudha','Male',4),
('Chary','Male',4),
('Muni Kumar','Male',5),
('Karthik','Male',5),
('Bhasker','Male',6),
('Karthik','Male',6),
('Shanmuk','Male',6)

Create a simple stored procedure to get all employee records from Mas_Employee table.

CreateProc USP_GetAllEmployees
As
Begin
Select Id,Name,Gender,DeptId From Mas_Employee
End

Application Chamber:

To create the project:

Go to Start, then All Programs and click Microsoft Visual Studio 2010.
Go to File, New, Project..., Visual C# , Web. Then select ASP.NET Empty Web Application.
Provide the project a name and specify the location.

Web.Config:
Create the connection string in the Web.Config file as in the following code snippet:

<connectionStrings>
<addname="conStr" connectionString="Data Source=.; Database=DB_Jai; Password=1234; User ID=sa;" providerName="System.Data.SqlClient" />
</connectionStrings>

Next Right-click on Solution Explorer and add a web form to your project.

Webform Design: Write the following code .aspx page.

<body>
<formid="form1" runat="server">
<asp:ScriptManagerID="ScriptManager1" runat="server"> </asp:ScriptManager>
<asp:UpdatePanelID="up1" runat="server">
<ContentTemplate>
<fieldsetstyle="border: 1px dashed #ccc; margin-left: 10px; margin-top: 0px; text-align: right;
padding-bottom: 10px; width: 20%; text-align: center;">
<legendstyle="margin-right: 10px;">
<asp:LabelID="lblLegend" runat="server" Text="Export Gridview Data to Excel"></asp:Label>
</legend>
<tablewidth="80%" align="center">
<tr>
<td>
<asp:GridViewID="gvExport" runat="server"> </asp:GridView>
</td>
</tr>
<tr>
<td>
<asp:ButtonID="btnExcel" runat="server" Text="Excel" OnClick="btnExcel_Click" </td>
</tr>
</table>
</fieldset>
</ContentTemplate>
</asp:UpdatePanel>
</form>
</body>

In CodeBehind:
Invoke the ConnectionString from Web.Config as in the following.

SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);

User Defined Functions:

#region User Defined Methods
//Bind Gridview using stored procedure
privatevoid bindGridview()
{
DataSet ds = newDataSet();
SqlDataAdapter da = newSqlDataAdapter("USP_GetAllEmployees", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(ds);
gvExport.DataSource = ds;
gvExport.DataBind();
}
//Export Gridview data to Excel
protectedvoid ExportToExcel()
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=Jaipal_GridDataexportDemo.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using(StringWriter sw = newStringWriter())
{
HtmlTextWriter hw = newHtmlTextWriter(sw);
gvExport.AllowPaging = false;
gvExport.RenderControl(hw);
string style = @ "<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
#endregion

Page Event Handlers:

#region Page Event Handlers
protectedvoid Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) bindGridview();
}
protectedvoid btnExcel_Click(object sender, EventArgs e)
{
ExportToExcel();
}#endregion

Run the Application by pressing Ctrl+F5, at this point you will get the following error RegisterforEventValidation,

ERROR

To resolve the above Issue RegisterforEventValidation can only be called during Render() :

Set EnableEventValidation flag to FALSE in the Web.Config in the following way,

<pagesenableEventValidation="false"></pages>

(Or) you can also set it in the @Page Directive of the page,

<%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="GridDataExport.aspx.cs"
Inherits="ExportGridviewData.Application.GridDataExport"EnableEventValidation="false"%>

And also override VerifyRenderingInServerForm in code behind,

publicoverridevoid VerifyRenderingInServerForm(Control control)
{
//
}

Ajax UpdatePanel Issue:
At this point If you run the application by pressing on Ctrl+F5 it doesn't work as expected. It means if you are clicking on the excel button, it doesn’t export the gridview data into any excel file.

To avoid this, add a PostBackTrigger and give it’s ControlID as the excel export button’s ID or you can move your excel export button out side of the update panel as in the following.