May 1, 2012

, ,

Grid View with Paging into Excel and Word in ASP.Net

1 comment  
After reading this article, you will come to know the following:
   1.   Using grid view control in the ASP.Net applications
   2.   Creating and connecting the SQL database in the ASP.Net applications
   3.   Creating PAGING in the grid view
   4.   Tabbed navigation in the ASP.Net application with single GRIDVIEW
   5.   Converting the grid view with paging to WORD and EXCEL document

Scenario:
   1.   We have to use grid view to display the details from the SQL database
   2.   We have to use single grid view control to display details from two different tables from the database
   3.   We have to enable PAGING in the grid view control
   4.   We have to convert the details displayed in the grid view to word and excel document

Solution:
   1.   I have created an ASP.Net Web application using Microsoft Visual Studio 2005.   
   2.   I have placed two buttons (as tabbed navigation) for displaying the details from two different tables in two tabs.
   3.   Also, you will find the solution for paging in the grid view control.
   4.  Then, after displaying the details, we can convert the details to word and excel document.
    
Code (.cs file):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
public partial class _Default : System.Web.UI.Page
{ 
    String strDetails = String.Empty;
    SqlConnection conn = new SqlConnection("Data Source=ServerName;Initial 
    Catalog=Database Name;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
   {
        if (!IsPostBack)
        {
            BindData(strDetails);
        }       
        GridView1.PageIndexChanging += new GridViewPageEventHandler(GridView1_PageIndexChanging);
    }        
    protected void btn1_Display_Click(object sender, EventArgs e)
    {
        strDetails = "";
        GridView1.PageIndex = 0;
        BindData(strDetails);
    }
    protected void btn2_Dipslay_Click(object sender, EventArgs e)
    {
        strDetails = "UserGroup";
        GridView1.PageIndex = 0;
        BindData(strDetails);
    }    
    protected void btn_ExportExcel_Click(object sender, EventArgs e)
   {              
        Response.Clear();
        Response.AddHeader("content-disposition","attachment;filename=FileName.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite =new HtmlTextWriter(stringWrite);
        //turn off paging before exporting the details
        GridView1.AllowPaging = false;
        strDetails = ViewState["strDetailsValue"].ToString();
        BindData(strDetails);
        GridView1.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
        //turn the paging on again after writing the values to the excel document
        GridView1.AllowPaging = true;
        BindData(strDetails);
        Response.Flush();
   }
    protected void btn_ExportWord_Click(object sender, EventArgs e)
   {
        Response.Clear();
        Response.AddHeader("content-disposition", attachment;filename=FileName.doc");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-word ";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        //turn off paging before exporting the details
        GridView1.AllowPaging = false;
        strDetails = ViewState["strDetailsValue"].ToString();
        BindData(strDetails);
        GridView1.RenderControl(hw);
        Response.Write(sw.ToString());
        Response.End();
        //turn the paging on again after writing the values to the excel document
        GridView1.AllowPaging = true;
        BindData(strDetails);
        Response.Flush();        
    }
    private void BindData(String strDetails)
    {
        DataSet ds = new DataSet();
        conn.Open();
        SqlCommand cmd;
        if (strDetails == "UserGroup")
        {
            cmd = new SqlCommand("Select * from table1", conn);
        }
        else
        {
            cmd = new SqlCommand("Select * from table2", conn);
        }
        SqlDataAdapter sqlAd = new SqlDataAdapter(cmd);
        sqlAd.Fill(ds);
        GridView1.Controls.Clear();
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
        GridView1.Visible = true;
        ViewState.Add("strDetailsValue", strDetails);
        conn.Close();     
    }
   //Handling the paging event in the grid view control 
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        strDetails = ViewState["strDetailsValue"].ToString();
        BindData(strDetails);
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
      /* Verifies that the control is rendered */
    }
  }
Hope this helps you! Please free to comment and share this post.
If you are getting the following error:
RegisterForEventValidation can only be called during Render();
Read this article to fix the error.
If you are getting the following error:
Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server.
Read this article to fix the issue.

1 comment:

  1. AnonymousDec 6, 2013, 9:52:00 AM

    This comment has been removed by a blog administrator.

    ReplyDelete

Dear Readers,

I LOVE to hear from you! Your feedback is always appreciated. I will try to reply to your query as soon as possible.

1. Make sure to click the "Notify me" check box at the right side to be notified of follow up comments and replies.
2. Please Do Not Spam - Spam comments will be deleted immediately upon review.