How to Generate ExcelSheet in Asp.Net

Description:-

Here we will Generate Excel Sheet from Gridview Data. First we will bind Data in Gridview and on button Click event we will Generate Excel Sheet from Data. Create method for bind Gridview and Code for Excel Sheet generation in dot net.

Default.aspx:-

<div>
  <asp:GridView ID="GridView1" AutoGenerateColumns="False" runat="server" CellPadding="3"
  ForeColor="Black" GridLines="Vertical" BackColor="White"
  BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px">
  <AlternatingRowStyle BackColor="#CCCCCC" />
  <FooterStyle BackColor="#CCCCCC" />
  <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
  <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
  <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
  <SortedAscendingCellStyle BackColor="#F1F1F1" />
  <SortedAscendingHeaderStyle BackColor="#808080" />
  <SortedDescendingCellStyle BackColor="#CAC9C9" />
  <SortedDescendingHeaderStyle BackColor="#383838" />
  <Columns>
    <asp:BoundField DataField="EmpID" HeaderText="id" />
    <asp:BoundField DataField="FirstName" HeaderText="Name" />
    <asp:BoundField DataField="LastName" HeaderText="City" />
    <asp:BoundField DataField="Salary" HeaderText="Address" />
    <asp:BoundField DataField="Address" HeaderText="Designation" />
  </Columns>
  </asp:GridView>
  <br />
  <asp:Button ID="Button1" runat="server" Text="Create Excel File" OnClick="Button1_Click" />
</div>

Default.aspx.cs:-

private SqlConnection con;
private SqlCommand com;
private string constr, query;
private void connection()
{
  constr = ConfigurationManager.ConnectionStrings["DBCS"].ToString();
  con = new SqlConnection(constr);
  con.Open();
}

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

private void Bindgrid()
{
  connection();
  query = "select *from Employee";//not recommended this i have written just for example, write stored procedure for security  
  com = new SqlCommand(query, con);
  SqlDataAdapter da = new SqlDataAdapter(query, con);
  DataSet ds = new DataSet();
  da.Fill(ds);
  GridView1.DataSource = ds;
  GridView1.DataBind();
  con.Close();
  ViewState["DataTable"] = ds.Tables[0];
}

public void CreateExcelFile(DataTable Excel)
{
  Response.ClearContent();
  Response.AddHeader("content-disposition", string.Format("attachment; filename=ExcellSheet.xls"));
  Response.ContentType = "application/vnd.ms-excel";
  string space = "";
  foreach (DataColumn dcolumn in Excel.Columns)
  {
    Response.Write(space + dcolumn.ColumnName);
    space = "\t";
  }
  Response.Write("\n");
  int countcolumn;
  foreach (DataRow dr in Excel.Rows)
  {
    space = "";
    for (countcolumn = 0; countcolumn < Excel.Columns.Count; countcolumn++)
    {
      Response.Write(space + dr[countcolumn].ToString());
      space = "\t";
    }
    Response.Write("\n");
  }
  Response.End();
}

protected void Button1_Click(object sender, EventArgs e)
{
  DataTable dt = (DataTable)ViewState["DataTable"];
  CreateExcelFile(dt);
}

Run your Webpage in browser and Click button to Generate ExcelSheet.

Author : Umesh Patel

Related Posts