Ep

Ep

Export C# Datatable to Excel Spreadsheet Css Style - Sample Application


In most of the application, we need to have export the data to Excel Spreadsheet file from grid view or data table. In this article we will see how to export datatable to Excel file in a very simple way, find the source code below:-

Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Export C# Datatable to Excel Spreadsheet Css Style - Sample Application</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>

            <br />
            <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            <br />
            <asp:Button ID="lnkbtnExporttoExcel" runat="server" Text="Export Datatable to Formated Excel" OnClick="lnkbtnExporttoExcel_Click" />
        </div>
    </form>
</body>
</html>

Default.aspx.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bindata();
        }
    }

    private void Bindata()
    {
        DataTable productsDataTable = new DataTable();

        // create DataTable object  

        // auto increment column for productsDataTable
        DataColumn dataColumn = new DataColumn("AutoID", System.Type.GetType("System.Int32"));
        dataColumn.AutoIncrement = true;
        dataColumn.AutoIncrementSeed = 1;
        dataColumn.AutoIncrementStep = 1;
        dataColumn.Unique = true;
        productsDataTable.Columns.Add(dataColumn);

        // product id column
        dataColumn = new DataColumn("ProductID", System.Type.GetType("System.Int32"));
        productsDataTable.Columns.Add(dataColumn);

        // product name column
        dataColumn = new DataColumn("ProductName", System.Type.GetType("System.String"));
        productsDataTable.Columns.Add(dataColumn);

        // quantity column
        dataColumn = new DataColumn("ProductQuantity", System.Type.GetType("System.Int32"));
        productsDataTable.Columns.Add(dataColumn);

        // product price column
        dataColumn = new DataColumn("ProductPrice", System.Type.GetType("System.Decimal"));
        productsDataTable.Columns.Add(dataColumn);


        // After Creating columns you can add data rows to store the data dynamically

        DataRow dataRow = productsDataTable.NewRow();
        dataRow["ProductID"] = 43;
        dataRow["ProductName"] = "Ipoh Coffee";
        dataRow["ProductQuantity"] = 1;
        dataRow["ProductPrice"] = 46.00;
        productsDataTable.Rows.Add(dataRow);

        dataRow = productsDataTable.NewRow();
        dataRow["ProductID"] = 40;
        dataRow["ProductName"] = "Boston Crab Meat";
        dataRow["ProductQuantity"] = 1;
        dataRow["ProductPrice"] = 18.40;
        productsDataTable.Rows.Add(dataRow);

        dataRow = productsDataTable.NewRow();
        dataRow["ProductID"] = 45;
        dataRow["ProductName"] = "ABC";
        dataRow["ProductQuantity"] = 12;
        dataRow["ProductPrice"] = 95.00;
        productsDataTable.Rows.Add(dataRow);

        GridView1.DataSource = productsDataTable;
        GridView1.DataBind();

        ViewState["GridData"] = productsDataTable;
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        //
    }

    /// <summary>
    /// Export From Gridview to formated Excel File
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void lnkbtnExporttoExcel_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "ProductMaster.xls"));
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;

        DataTable dtExport = new DataTable();
        dtExport = (DataTable)ViewState["GridData"];

        GridView1.DataSource = dtExport;
        GridView1.DataBind();
        if (dtExport.Rows.Count > 0)
        {
            //Change the Header Row back to white color
            GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
            //Applying stlye to gridview header cells
            for (int i = 0; i < GridView1.HeaderRow.Cells.Count; i++)
            {
                GridView1.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
            }
            int j = 1;
            //This loop is used to apply stlye to cells based on particular row
            foreach (GridViewRow gvrow in GridView1.Rows)
            {
                gvrow.BackColor = Color.White;
                if (j <= GridView1.Rows.Count)
                {
                    if (j % 2 != 0)
                    {
                        for (int k = 0; k < gvrow.Cells.Count; k++)
                        {
                            gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
                        }
                    }
                }
                j++;
            }
            GridView1.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();
        }
        else
        {
            //
        }
    }
}

Output:

Export to EXCEL from Datatable in C#.Net,  Export dataset to the excel, export a dataset to Excel in asp.net C#, Export a DataTable to Excel in ASP.NET

Please leave your comments, suggestions and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading .. :)
Download the source code click here

Export C# Datatable to Excel Spreadsheet Css Style - Sample Application Reviewed by Ravi Kumar on 4:29:00 PM Rating: 5

1 comment:

All Rights Reserved by Etechpulse © 2012 - 2017
Powered By Blogger

Contact Form

Name

Email *

Message *

Powered by Blogger.