Thursday, 11 July 2013

Download Grid Data to excel,Csv and Txt (Tab Delimited File) avoiding Paging



Here I will explain how to export grid view data to excel, CSV, Text ( Tab Delimited File ) documnets using asp.net without paging problem


so in one gridview that has filled with Some details, now we need to export gridview data to user selected document based on selection. To implement this functionality first we need to design aspx page like this


ASPX page



<form id="form1" runat="server">
   <div>
       <table width="100%">
            <tr align="right">
                 <td align="center" width="800">

                     <asp:ImageButton ID="imgbtnxlsx" runat="server" ImageUrl="~/Images/xls_file.png"
                        Height="40" Width="40" ToolTip="xls" OnClick="imgbtnxlsx_Click" />
                  <asp:ImageButton ID="imgbtntxt" runat="server" ImageUrl="~/Images/txt_file.png" Height="40"
                   Width="40" ToolTip="txt" OnClick="imgbtntxt_Click" />
                <asp:ImageButton ID="imgbtncsv" runat="server" ImageUrl="~/Images/csv_file.png" Height="40"
                 Width="40" ToolTip="csv" OnClick="imgbtncsv_Click" />
                  </td>
             </tr>
               <tr>
                   <td align="center" width="800">

                            <asp:GridView ID="Grid1" runat="server" AllowPaging="true"
                               PageSize="10"    OnPageIndexChanging="Grid1_PageIndexChanging"
                               GridLines="None" AutoGenerateColumns="false">

                                         <HeaderStyle HorizontalAlign="Center" Height="35" 
                                                               BorderStyle="None" BackColor="#C6401B"
                                                              Font-Names="arial" Font-Size="16px" Font-Bold="true" />

                                          <RowStyle HorizontalAlign="Center" Font-Names="helvetica, arial" 
                                                             Font-Size="14px" BackColor="#F2F9FF" Wrap="true"
                                                             Height="35" BorderStyle="None" />

                                             <AlternatingRowStyle BackColor="ControlDark" />

                                              <PagerStyle HorizontalAlign="Center" ForeColor="#8D8D8D" 
                                                               Font-Names="helvetica, arial" />

                      <Columns>

                            <asp:BoundField DataField="StateID" HeaderText="StateID" ItemStyle-Width="200" />
                            <asp:BoundField DataField="CountryID" HeaderText="CountryID"/>                                          
                            <asp:BoundField DataField="StateName" HeaderText="StateName" />
                            <asp:BoundField DataField="Abbreviation" HeaderText="Abbreviation" />                                                                                                             

                              </Columns>
                         </asp:GridView>
                      </td>
               </tr>
          </table>
      </div>
</form>


CODE :-


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;



       protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGridd();
            }
        }

        private void BindGridd()
        {
            SqlConnection con = new SqlConnection(@"Data Source=Servername;Initial Catalog=DBname;
                                                                                  User ID=username;Password=password");
            SqlCommand cmd = new SqlCommand("select * from tablename", con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            con.Open();
            da.Fill(ds);
            Session["GridData"] = ds;
            Grid1.DataSource = ds;
            Grid1.DataBind();
            con.Close();

        }

        protected void Grid1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            Grid1.PageIndex = e.NewPageIndex;
            Grid1.DataSource = Session["GridData"]; ;
            Grid1.DataBind();
        }

       protected void imgbtnxlsx_Click(object sender, ImageClickEventArgs e)
        {
             Response.ClearContent();
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "xcell.xls"));
            Response.ContentType = "application/text";
            Grid1.AllowPaging = false;
            Grid1.DataSource = Session["GridData"];
            Grid1.DataBind();
            StringBuilder strbldr = new StringBuilder();
            for (int i = 0; i < Grid1.Columns.Count; i++)
            {
                //separting header columns text with comma operator
                strbldr.Append(Grid1.Columns[i].HeaderText + '\t');
            }
            //appending new line for gridview header row
            strbldr.Append("\r\n");
            for (int j = 0; j < Grid1.Rows.Count; j++)
            {
                for (int k = 0; k < Grid1.Columns.Count; k++)
                {
                    //separating gridview columns with comma
                    strbldr.Append(Grid1.Rows[j].Cells[k].Text + '\t');
                }
                //appending new line for gridview rows
                strbldr.Append("\r\n");
            }
            Response.Write(strbldr.ToString());
            Response.End();

        }

        protected void imgbtntxt_Click(object sender, ImageClickEventArgs e)
        {
            Response.ClearContent();
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "txtfile.txt"));
            Response.ContentType = "application/text";
            Grid1.AllowPaging = false;
            Grid1.DataSource = Session["GridData"];
            Grid1.DataBind();
            StringBuilder strbldr = new StringBuilder();
            for (int i = 0; i < Grid1.Columns.Count; i++)
            {
                //separting header columns text with comma operator
                strbldr.Append(Grid1.Columns[i].HeaderText + ' ');
            }
            //appending new line for gridview header row
            strbldr.Append("\r\n");
            for (int j = 0; j < Grid1.Rows.Count; j++)
            {
                for (int k = 0; k < Grid1.Columns.Count; k++)
                {
                    //separating gridview columns with comma
                    strbldr.Append(Grid1.Rows[j].Cells[k].Text + ' ');
                }
                //appending new line for gridview rows
                strbldr.Append("\r\n");
            }
            Response.Write(strbldr.ToString());
            Response.End();
        }

        protected void imgbtncsv_Click(object sender, ImageClickEventArgs e)
        {
            Response.ClearContent();
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}","csvfil.csv"));
            Response.ContentType = "application/text";
            Grid1.AllowPaging = false;
            Grid1.DataSource = Session["GridData"];
            Grid1.DataBind();
            StringBuilder strbldr = new StringBuilder();
            for (int i = 0; i < Grid1.Columns.Count; i++)
            {
                //separting header columns text with comma operator
                strbldr.Append(Grid1.Columns[i].HeaderText + ',');
            }
            //appending new line for gridview header row
            strbldr.Append("\n");
            for (int j = 0; j < Grid1.Rows.Count; j++)
            {
                for (int k = 0; k < Grid1.Columns.Count; k++)
                {
                    //separating gridview columns with comma
                    strbldr.Append(Grid1.Rows[j].Cells[k].Text + ',');
                }
                //appending new line for gridview rows
                strbldr.Append("\n");
            }
            Response.Write(strbldr.ToString());
            Response.End();
        }

         Note: This function is used to avoid the error like  “control must be placed in inside of form tag”. If we setVerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.

        public override void VerifyRenderingInServerForm(Control control)
        {

            /* method must be implemented when using RenderControl */

        }

And final Output Will be........

OutPut:-





CSV








EXCEL





NOTEPAD



       

Related Posts:

  • Word Documents and Excel Documents in IFRAME In this below example we are converting word OR excel document to HTML. In this case we are converting and giving converted HTML file as ""src"" to ""iframe"". Step By Step Procedure : 1)First step is create one… Read More
  • Convert date format in javascript     Here in this example i am going to explain how to convert date into another format in javascript. Example : Here in this today's date will be obtained in the variable dt in system format and o… Read More
  • Culture info in RadDateTime Picker In this example we can give culture info for localization in RadDatetimePicker. for this below example u can get different localizations according to their country. it is one of the property in RadDateTimePicke… Read More
  • Word document in iframe This is screen shot for iframe.Here In the below Screenshot iframe viewing the word document in the form of html. so i am giving html to the Iframe src. Iframe can view excel,csv,pdf,text file… Read More
  • Different Culture Names CULTURE ||SPEC.CULTURE||ENGLISH NAME af af-ZA Afrikaans af-ZA af-ZA Afrikaans (South Africa) ar ar-SA Arabic ar-AE ar-AE Arabic (U.A.E.) ar-BH ar-BH Arabic … Read More

0 comments:

Post a Comment