Format GridView column to display thousand separator using C# and VB.Net in ASP.Net

Hi jqdesigner,

There are two way you can do it.

1. You can do it by looping through each row of GridView after binding the GridView.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" ShowFooter="true" >
    <Columns>
    </Columns>
</asp:GridView>

C#

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

private void BindGrid()
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);
    SqlDataAdapter oAdapter = new SqlDataAdapter("SELECT * FROM Test", sqlcon);
    DataSet ds = new DataSet();
    oAdapter.Fill(ds);
    DataTable dt = ds.Tables[0];
    if (dt.Rows.Count > 0)
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();
        // Format GridView cell 3 with thousand separator
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            GridView1.Rows[i].Cells[3].Text = Convert.ToDouble(GridView1.Rows[i].Cells[3].Text).ToString("N2");
        }
        //here add code for column total sum and show in footer 
        GridView1.FooterRow.Cells[0].Text = "Total";
        GridView1.FooterRow.Cells[2].Font.Bold = true;
        GridView1.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Left;
        int totalQty = 0;
        for (int k = 2; k < dt.Columns.Count - 1; k++)
        {
            totalQty = dt.AsEnumerable().Sum(row => row.Field<int>(dt.Columns[k].ToString()));
            GridView1.FooterRow.Cells[k].Text = totalQty.ToString();
            GridView1.FooterRow.Cells[k].Font.Bold = true;
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige;
        }
        double totalAmount = 0;
        for (int k = 3; k < dt.Columns.Count; k++)
        {
            totalAmount = dt.AsEnumerable().Sum(row => row.Field<double>(dt.Columns[k].ToString()));
            GridView1.FooterRow.Cells[k].Text = totalAmount.ToString("N2");
            GridView1.FooterRow.Cells[k].Font.Bold = true;
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige;
        }
    }
}

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

VB.Net

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

Private Sub BindGrid()
    Dim sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings(1).ConnectionString)
    Dim oAdapter As New SqlDataAdapter("SELECT * FROM Test", sqlcon)
    Dim ds As New DataSet()
    oAdapter.Fill(ds)
    Dim dt As DataTable = ds.Tables(0)
    If dt.Rows.Count > 0 Then
        GridView1.DataSource = dt
        GridView1.DataBind()
        ' Format GridView cell 3 with thousand separator
        For i As Integer = 0 To GridView1.Rows.Count - 1
            GridView1.Rows(i).Cells(3).Text = Convert.ToDouble(GridView1.Rows(i).Cells(3).Text).ToString("N2")
        Next

        'here add code for column total sum and show in footer 
        GridView1.FooterRow.Cells(0).Text = "Total"
        GridView1.FooterRow.Cells(2).Font.Bold = True
        GridView1.FooterRow.Cells(2).HorizontalAlign = HorizontalAlign.Left
        Dim totalQty As Integer = 0
        For k As Integer = 2 To dt.Columns.Count - 2
            totalQty = dt.AsEnumerable().Sum(Function(row) row.Field(Of Integer)(dt.Columns(k).ToString()))
            GridView1.FooterRow.Cells(k).Text = totalQty.ToString()
            GridView1.FooterRow.Cells(k).Font.Bold = True
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige
        Next
        Dim totalAmount As Double = 0
        For k As Integer = 3 To dt.Columns.Count - 1
            totalAmount = dt.AsEnumerable().Sum(Function(row) row.Field(Of Double)(dt.Columns(k).ToString()))
            GridView1.FooterRow.Cells(k).Text = totalAmount.ToString("N2")
            GridView1.FooterRow.Cells(k).Font.Bold = True
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige
        Next
    End If
End Sub

Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub

2. You can use GridView OnRowDataBound event.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" ShowFooter="true"
    OnRowDataBound="GridView_RowDataBound">
    <Columns>
    </Columns>
</asp:GridView>

C#

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

private void BindGrid()
{
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ConnectionString);
    SqlDataAdapter oAdapter = new SqlDataAdapter("SELECT * FROM Test", sqlcon);
    DataSet ds = new DataSet();
    oAdapter.Fill(ds);
    DataTable dt = ds.Tables[0];
    if (dt.Rows.Count > 0)
    {
        GridView1.DataSource = dt;
        GridView1.DataBind();

        //here add code for column total sum and show in footer 
        GridView1.FooterRow.Cells[0].Text = "Total";
        GridView1.FooterRow.Cells[2].Font.Bold = true;
        GridView1.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Left;
        int totalQty = 0;
        for (int k = 2; k < dt.Columns.Count - 1; k++)
        {
            totalQty = dt.AsEnumerable().Sum(row => row.Field<int>(dt.Columns[k].ToString()));
            GridView1.FooterRow.Cells[k].Text = totalQty.ToString();
            GridView1.FooterRow.Cells[k].Font.Bold = true;
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige;
        }
        double totalAmount = 0;
        for (int k = 3; k < dt.Columns.Count; k++)
        {
            totalAmount = dt.AsEnumerable().Sum(row => row.Field<double>(dt.Columns[k].ToString()));
            GridView1.FooterRow.Cells[k].Text = totalAmount.ToString("N2");
            GridView1.FooterRow.Cells[k].Font.Bold = true;
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige;
        }
    }
}

protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    this.BindGrid();
}

protected void GridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[3].Text = Convert.ToDouble(e.Row.Cells[3].Text).ToString("N2");
    }
}

VB.Net

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

Private Sub BindGrid()
    Dim sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings(1).ConnectionString)
    Dim oAdapter As New SqlDataAdapter("SELECT * FROM Test", sqlcon)
    Dim ds As New DataSet()
    oAdapter.Fill(ds)
    Dim dt As DataTable = ds.Tables(0)
    If dt.Rows.Count > 0 Then
        GridView1.DataSource = dt
        GridView1.DataBind()

        'here add code for column total sum and show in footer 
        GridView1.FooterRow.Cells(0).Text = "Total"
        GridView1.FooterRow.Cells(2).Font.Bold = True
        GridView1.FooterRow.Cells(2).HorizontalAlign = HorizontalAlign.Left
        Dim totalQty As Integer = 0
        For k As Integer = 2 To dt.Columns.Count - 2
            totalQty = dt.AsEnumerable().Sum(Function(row) row.Field(Of Integer)(dt.Columns(k).ToString()))
            GridView1.FooterRow.Cells(k).Text = totalQty.ToString()
            GridView1.FooterRow.Cells(k).Font.Bold = True
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige
        Next
        Dim totalAmount As Double = 0
        For k As Integer = 3 To dt.Columns.Count - 1
            totalAmount = dt.AsEnumerable().Sum(Function(row) row.Field(Of Double)(dt.Columns(k).ToString()))
            GridView1.FooterRow.Cells(k).Text = totalAmount.ToString("N2")
            GridView1.FooterRow.Cells(k).Font.Bold = True
            GridView1.FooterRow.BackColor = System.Drawing.Color.Beige
        Next
    End If
End Sub

Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    Me.BindGrid()
End Sub

Protected Sub GridView_RowDataBound(sender As Object, e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        e.Row.Cells(3).Text = Convert.ToDouble(e.Row.Cells(3).Text).ToString("N2")
    End If
End Sub

OutPut