How to Export GridView Data to Excel with custom Number DateTime Format Asp.Net C# VB.Net

Exporting GridView data to Excel file is a very common task which we need to perform in most of the Asp.net web applications to meet the customized reporting purposes. There are various techniques of exporting GridView Data to Excel and it needs to be highly customizable depends on the application requirements. I will show you that how you can Export your GridView Data to Excel File with a very limited code. When exporting data to excel, keep the Format of Data is a very common requirement and also user might want to set different format which I will also discuss in the later section of this post.

If the GridView has Paging Enabled then please go here.

Objective/Outcome:
Export GridView Data to Excel Asp.Net CSharp VB.Net

First create a DataBase Table:

CREATE TABLE [dbo].[tblSupplier](
	[Id] [int] NOT NULL,
	[Code] [char](6) NOT NULL,
	[Name] [varchar](200) NOT NULL,
	[Address] [varchar](200) NOT NULL,
	[ContactNo] [varchar](20) NOT NULL,
	[LastDelivery] [datetime] NULL,
 CONSTRAINT [PK_tblSupplier] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now enter some sample Data to Test:

INSERT INTO tblSupplier VALUES(1,'st001','S.R. STeel','Uttara, Dhaka','01711xxxxxx',GETDATE()-27)
INSERT INTO tblSupplier VALUES(2,'st002','Shadesh Builders','Badda, Dhaka','01611xxxxxx',GETDATE()-10)
INSERT INTO tblSupplier VALUES(3,'st003','Orchard Confec','Rampura, Dhaka','01811xxxxxx',GETDATE()-17)
INSERT INTO tblSupplier VALUES(4,'st004','WindBlow','Uttara, Dhanmondi','01911xxxxxx',GETDATE()-5)
INSERT INTO tblSupplier VALUES(5,'st005','REX','Bashundhara, Dhaka','01511xxxxxx',GETDATE())

Now need to Modify Web.Config file to Connect to Database:

<configuration>
  <connectionStrings>
    <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>
    <!--<add name="BONConnection" connectionString="Data Source=XXX.com;Initial Catalog=DBNAME;User Id=UserName;Password=YourPassword;" providerName="System.Data.SqlClient" />-->
  </connectionStrings>
...................
...................

Note: If you use windows authentication then use the first key. Otherwise use second key line. Update server name, Database name, User name, Password as per your settings.

Now add a GridView Control & a Button Control Like Below:

    <asp:GridView ID="GridView1" runat="server" HeaderStyle-BackColor="CornflowerBlue" HeaderStyle-Font-Bold="true" HeaderStyle-ForeColor="White" CellPadding="5">
    </asp:GridView>
    <br />
    <asp:Button runat="server" ID="cmdExport" Text="Export to Excel" 
        onclick="cmdExport_Click" />

Now bind Database Table to GridView:
C# Code:

   protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
            {
                SqlDataAdapter ad = new SqlDataAdapter("SELECT * from tblSupplier", conn);
                ad.Fill(dt);
            }

            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
    }

Note: Don’t forget to add “System.Data”,”System.Configuration”,”System.Data.SqlClient” namespaces.

VB.Net Code:

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim dt As DataTable = New DataTable()
            Using conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnection").ConnectionString)
                Dim ad As SqlDataAdapter = New SqlDataAdapter("SELECT * from tblSupplier", conn)
                ad.Fill(dt)
            End Using

            GridView1.DataSource = dt
            GridView1.DataBind()
        End If
    End Sub

Note: Don’t forget to add “System.Data”,”System.Configuration”,”System.Data.SqlClient” namespaces.

Now to Export Data into Excel write Below code under the Button:
C# Code:

    protected void cmdExport_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment; filename=SupplierList.xls");
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter WriteItem = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlText = new HtmlTextWriter(WriteItem);
        GridView1.RenderControl(htmlText);
        Response.Write(WriteItem.ToString());
        Response.End();
    }

VB.Net Code:

    Protected Sub cmdExport_Click(sender As Object, e As System.EventArgs) Handles cmdExport.Click
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment; filename=SupplierList.xls")
        Response.ContentType = "application/vnd.xls"
        Dim WriteItem As System.IO.StringWriter = New System.IO.StringWriter()
        Dim htmlText As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(WriteItem)
        GridView1.RenderControl(htmlText)
        Response.Write(WriteItem.ToString())
        Response.End()
    End Sub

Don’t forget to override the VerifyRenderingInServerForm method. For exporting each page must have override this method:

C# Code:

public override void VerifyRenderingInServerForm(Control control) { }

VB.Net Code:

    Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    End Sub

Now run the project hope you will get an Excel sheet containing all GridView Data.

Export GridView Data to Excel with custom Number DateTime Format:

Hope from above example now you can easily export data from GridView. But an important concern is to set the Formatting to the Target Excel File. In this reagard mso-number-format ease our life. Lets we need to display a long date time format in the GridView but we want short date format while exporting. To do that one can use mso-number-format in the following way. Modify your export button click event by the following code segment:

Objective/Outcome:
Export GridView Data to Excel with custom Number DateTime Format

To Format Number or DateTime Data modify below Export Function:
C# Code:

    protected void cmdExport_Click(object sender, EventArgs e)
    {
        string datestyle = @"<style>.date { mso-number-format:'Short Date'; }</style>";
        foreach (GridViewRow oItem in GridView1.Rows)
            oItem.Cells[5].Attributes.Add("class", "date");

        Response.Clear();
        Response.AddHeader("content-disposition", "attachment; filename=SupplierList.xls");
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter WriteItem = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlText = new HtmlTextWriter(WriteItem);
        Response.Write(datestyle);
        GridView1.RenderControl(htmlText);
        Response.Write(WriteItem.ToString());
        Response.End();
    }

Note: In my case DateTime column cell number is 5. Modify the Cell number from above code as per your sequence. Don’t forget to add another new line “Response.Write(datestyle)”.

VB.Net Code:

    Protected Sub cmdExport_Click(sender As Object, e As System.EventArgs) Handles cmdExport.Click
        Dim datestyle As String = "<style>.date { mso-number-format:'Short Date'; }</style>"
        For Each oItem As GridViewRow In GridView1.Rows
            oItem.Cells(5).Attributes.Add("class", "date")
        Next
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment; filename=SupplierList.xls")
        Response.ContentType = "application/vnd.xls"
        Dim WriteItem As System.IO.StringWriter = New System.IO.StringWriter()
        Dim htmlText As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(WriteItem)
        Response.Write(datestyle)
        GridView1.RenderControl(htmlText)
        Response.Write(WriteItem.ToString())
        Response.End()
    End Sub

Note: In my case DateTime column cell number is 5. Modify the Cell number from above code as per your sequence. Don’t forget to add another new line “Response.Write(datestyle)”.

List of some useful mso-number-format:

Format Description
mso-number-format:”0″ NO Decimals
mso-number-format:”0\.000″ 3 Decimals
mso-number-format:”\#\,\#\#0\.000 Comma with 3 dec
mso-number-format:”mm\/dd\/yy” Date7
mso-number-format:”mmmm\ d\,\ yyyy” Date9
mso-number-format:”m\/d\/yy\ h\:mm\ AM\/PM” Date -Time AMPM
mso-number-format:”Short Date” 04/07/2008
mso-number-format:”Medium Date” 04-Jun-08
mso-number-format:”d\-mmm\-yyyy” 04-Jun-2008
mso-number-format:”Short Time” 4:49
mso-number-format:”Medium Time” 4:49 am
mso-number-format:”Long Time” 4:49:13:00
mso-number-format:”Percent” Percent with two dec.
mso-number-format:”0%” Percent with no dec.
mso-number-format:”0\.E+00″ Scientific Notation
mso-number-format:”\@” Text
mso-number-format:”\#\ ???/???” Fractions up to 3 digits
mso-number-format:”\0022£\0022\#\,\#\#0\.00″ £10.52
mso-number-format:”0\.0000″;font-weight:700; 4 dec.+multiple format

Note: If you do not find your required format from above list then create a spreadsheet with a single cell entry as per your format. Then from file menu select the ‘Save as Web Page’ option to create a html file. Read & examine the code and try to find out the mso-number-format from the style declarations. Hope it will be help you a lot.

At a Glance in Video:
MSO_NumBer_Format

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, Asp.net, C#, Gridview, VB.Net
4 comments on “How to Export GridView Data to Excel with custom Number DateTime Format Asp.Net C# VB.Net
  1. raeksh says:

    Helped Good code

  2. Ravinder Kumar says:

    How this code changed when export to excel only for one text box not for Grid View.???/

  3. Vikash Kumar Singh says:

    How we can do if we need text format.

  4. Yogesh says:

    Boss You Make my day…Best solution

Leave a Reply

Your email address will not be published. Required fields are marked *

     

*