Post
Ask Question

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

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.

  1. CreateTable Mas_Employee(  
  2. ID IntPrimaryKeyIdentity,  
  3. Name Varchar(50),  
  4. Gender Varchar(50),  
  5. DeptId Int  
  6. )  
Insert multiple records with single insert statement,
  1. InsertInto Mas_Employee(Name,Gender,DeptId)Values  
  2. ('Kishore','Male',1),  
  3. ('Ashwin','Male',1),  
  4. ('Sravan','Male',2),  
  5. ('SumanGupta','Male',2),  
  6. ('Ragavendra','Male',3),  
  7. ('Anil','Male',3),  
  8. ('Suman','Male',4),  
  9. ('Jaipal','Male',4),  
  10. ('Sudha','Male',4),  
  11. ('Chary','Male',4),  
  12. ('Muni Kumar','Male',5),  
  13. ('Karthik','Male',5),  
  14. ('Bhasker','Male',6),  
  15. ('Karthik','Male',6),  
  16. ('Shanmuk','Male',6)  
Create a simple stored procedure to get all employee records from Mas_Employee table.
  1. CreateProc USP_GetAllEmployees  
  2. As  
  3. Begin  
  4. Select Id,Name,Gender,DeptId From Mas_Employee  
  5. 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:

  1. <connectionStrings>  
  2.     <addname="conStr" connectionString="Data Source=.; Database=DB_Jai; Password=1234; User ID=sa;" providerName="System.Data.SqlClient" />   
  3. </connectionStrings>  
Next Right-click on Solution Explorer and add a web form to your project.

Webform Design: Write the following code .aspx page.
  1. <body>  
  2.     <formid="form1" runat="server">  
  3.         <asp:ScriptManagerID="ScriptManager1" runat="server"> </asp:ScriptManager>  
  4.             <asp:UpdatePanelID="up1" runat="server">  
  5.                 <ContentTemplate>  
  6.                     <fieldsetstyle="border: 1px dashed #ccc; margin-left: 10px; margin-top: 0px; text-align: right;  
  7. padding-bottom: 10px; width: 20%; text-align: center;">  
  8.                         <legendstyle="margin-right: 10px;">  
  9.                             <asp:LabelID="lblLegend" runat="server" Text="Export Gridview Data to Excel"></asp:Label>  
  10.                                 </legend>  
  11.                                 <tablewidth="80%" align="center">  
  12.                                     <tr>  
  13.                                         <td>  
  14.                                             <asp:GridViewID="gvExport" runat="server"> </asp:GridView>  
  15.                                         </td>  
  16.                                     </tr>  
  17.                                     <tr>  
  18.                                         <td>  
  19.                                             <asp:ButtonID="btnExcel" runat="server" Text="Excel" OnClick="btnExcel_Click" </td>  
  20.                                     </tr>  
  21.                           </table>  
  22.                       </fieldset>  
  23.                 </ContentTemplate>  
  24.        </asp:UpdatePanel>  
  25.    </form>  
  26. </body>  
In CodeBehind:
Invoke the ConnectionString from Web.Config as in the following.
  1. SqlConnection con = newSqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);  
User Defined Functions:
  1. #region User Defined Methods  
  2. //Bind Gridview using stored procedure  
  3. privatevoid bindGridview()  
  4. {  
  5.         DataSet ds = newDataSet();  
  6.         SqlDataAdapter da = newSqlDataAdapter("USP_GetAllEmployees", con);  
  7.         da.SelectCommand.CommandType = CommandType.StoredProcedure;  
  8.         da.Fill(ds);  
  9.         gvExport.DataSource = ds;  
  10.         gvExport.DataBind();  
  11.     }  
  12.     //Export Gridview data to Excel  
  13. protectedvoid ExportToExcel()  
  14. {  
  15.     Response.Clear();  
  16.     Response.Buffer = true;  
  17.     Response.AddHeader("content-disposition", "attachment;filename=Jaipal_GridDataexportDemo.xls");  
  18.     Response.Charset = "";  
  19.     Response.ContentType = "application/vnd.ms-excel";  
  20.     using(StringWriter sw = newStringWriter())  
  21.     {  
  22.         HtmlTextWriter hw = newHtmlTextWriter(sw);  
  23.         gvExport.AllowPaging = false;  
  24.         gvExport.RenderControl(hw);  
  25.         string style = @ "<style> .textmode { } </style>";  
  26.         Response.Write(style);  
  27.         Response.Output.Write(sw.ToString());  
  28.         Response.Flush();  
  29.         Response.End();  
  30.     }  
  31. }

  33. #endregion  
Page Event Handlers:
  1. #region Page Event Handlers  
  2. protectedvoid Page_Load(object sender, EventArgs e)  
  3. {  
  4.     if (!IsPostBack) bindGridview();  
  5. }  
  6. protectedvoid btnExcel_Click(object sender, EventArgs e)   
  7. {  
  8.     ExportToExcel();  
  9. }#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,
  1. <pagesenableEventValidation="false"></pages>  
(Or) you can also set it in the @Page Directive of the page,
  1. <%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="GridDataExport.aspx.cs"  
  2. Inherits="ExportGridviewData.Application.GridDataExport"EnableEventValidation="false"%>
And also override VerifyRenderingInServerForm in code behind,
  1. publicoverridevoid VerifyRenderingInServerForm(Control control)  
  2. {  
  3.   //
  4. }  
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.
  1. <Triggers>  
  2.     <asp:PostBackTriggerControlID="btnEXCEL" />   
  3. </Triggers> 
Run the Application:

Application

Just click on Excel button, then we can see the expected result of gridview data in Excel file.
 
excel

I hope you enjoyed it.
Next Recommended Reading Export GridView Data to Excel, Word, Pdf, Text and Csv Format and Print in Asp.Net