Format column while exporting from database to Excel in ASP.Net

Hi arvindasp,

Please refer below sample code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Namespace

C#

using System.Data;
using System.Configuration;
using System.Data.SqlClient;

VB.Net

Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient

Code

C#

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

protected void ExportDirect()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    string qry = "SELECT EmployeeID,Extension,FirstName + ' ' + LastName AS Name,CONVERT(VARCHAR(10), [BirthDate], 103) Date FROM Employees";

    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter(qry, con))
        {
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);

                // CALCULATE RUNNING TOTAL (WILL DISPLAY AT THE FOOTER OF EXCEL WORKBOOK.)
                Decimal dTotalPrice = 0;
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    dTotalPrice += Convert.ToDecimal(dt.Rows[i][1]); // 1 is cell no
                }

                // NOW ASSIGN DATA TO A DATAGRID.
                DataGrid dg = new DataGrid();
                dg.DataSource = dt;
                dg.DataBind();

                // THE EXCEL FILE.
                string sFileName = "PaymentRequest-IT-" + System.DateTime.Now.Date + ".xls";
                sFileName = sFileName.Replace("/", "");

                // SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT".
                Response.ClearContent();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment; filename=" + sFileName);
                Response.ContentType = "application/vnd.ms-excel";
                EnableViewState = false;
                System.IO.StringWriter objSW = new System.IO.StringWriter();
                System.Web.UI.HtmlTextWriter objHTW = new System.Web.UI.HtmlTextWriter(objSW);
                dg.HeaderStyle.Font.Bold = true; // SET EXCEL HEADERS AS BOLD.
                dg.RenderControl(objHTW);

                // STYLE THE SHEET AND WRITE DATA TO IT.
                Response.Write("<style> TABLE { border:dotted 1px #999; } " +
                "TD { border:dotted 1px #D5D5D5; text-align:center } </style>");
                Response.Write(objSW.ToString());

                // ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE.
                Response.Write("<table><tr><td><b>Total: </b></td><td></td><td><b>" +
                dTotalPrice.ToString("N2") + "</b></td></tr></table>");
                Response.End();
                dg = null;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        ExportDirect()
    End If
End Sub

Protected Sub ExportDirect()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim qry As String = "SELECT EmployeeID,Extension,FirstName + ' ' + LastName AS Name,CONVERT(VARCHAR(10), [BirthDate], 103) Date FROM Employees"

    Using con As SqlConnection = New SqlConnection(constr)
        Using sda As SqlDataAdapter = New SqlDataAdapter(qry, con)
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                Dim dTotalPrice As Decimal = 0

                For i As Integer = 0 To dt.Rows.Count - 1
                    dTotalPrice += Convert.ToDecimal(dt.Rows(i)(1))
                Next

                Dim dg As DataGrid = New DataGrid()
                dg.DataSource = dt
                dg.DataBind()
                Dim sFileName As String = "PaymentRequest-IT-" & System.DateTime.Now.Date & ".xls"
                sFileName = sFileName.Replace("/", "")
                Response.ClearContent()
                Response.Buffer = True
                Response.AddHeader("content-disposition", "attachment; filename=" & sFileName)
                Response.ContentType = "application/vnd.ms-excel"
                EnableViewState = False
                Dim objSW As System.IO.StringWriter = New System.IO.StringWriter()
                Dim objHTW As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(objSW)
                dg.HeaderStyle.Font.Bold = True
                dg.RenderControl(objHTW)
                Response.Write("<style> TABLE { border:dotted 1px #999; } " & "TD { border:dotted 1px #D5D5D5; text-align:center } </style>")
                Response.Write(objSW.ToString())
                Response.Write("<table><tr><td><b>Total: </b></td><td></td><td><b>" & dTotalPrice.ToString("N2") & "</b></td></tr></table>")
                Response.End()
                dg = Nothing
            End Using
        End Using
    End Using
End Sub

Screenshot