Adding up cell contents by row in a gridview

Question

Can someone kindly help me out with this? I have a gridview that returns nmumeric values. First columm is always the category and there are always eleven of them. For illustration purposes I only show three columsa and two rows. The first figure is what I have and the second is what I need.

Event type | JAN-01 | Feb-01 | Mar-01
---------------------------------------
| Item 1   |  21    |    100 |   0
--------------------------------------- 
| Item 2   |  5     |    1   |   67
---------------------------------------


Event type | TOTAL
---------------------
| Item 1   |  121   | 
--------------------- 
| Item 2   |   73   | 
---------------------

The code I'm playing with is this. Gridview markup:

 <td>
     <asp:GridView ID="gvSystemRMRiskRpt" runat="server" AutoGenerateColumns="true" CellPadding="3"
         PageSize="25" BackColor="White" BorderColor="MidnightBlue" BorderStyle="Groove"
         BorderWidth="1px" CssClass="TextCompact" GridLines="Vertical" OnRowDataBound="gvSystemRMRiskRpt_OnDataBound"
          EmptyDataText="Your request has returned zero records">
          <EmptyDataRowStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
          <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
          <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
          <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
          <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
          <AlternatingRowStyle BackColor="Gainsboro" />
          <FooterStyle BackColor="#CCCCCC" ForeColor="Black" VerticalAlign="Top" />
      </asp:GridView>
  </td>

then the code file:

protected void btnSearch_Click(object sender, EventArgs e)
{
    Guid site = new Guid(ddlSites.SelectedValue);
    int interval = Convert.ToInt16(ddlIntervals.SelectedValue);
    string[] startDate = tbStartDate.Text.Split('/');
    string[] stopDate = tbEndDate.Text.Split('/');

    DataTable _dt = RMRiskData.dtESRByMonthYear(connectionManager, site, startDate[1], stopDate[1], interval);
    gvSystemRMRiskRpt.DataSource = _dt;
    gvSystemRMRiskRpt.DataBind();

    lblSelectedSite.Text = ddlSites.SelectedItem.ToString();
}

protected void gvSystemRMRiskRpt_OnDataBound(object sender, GridViewRowEventArgs e)
{
    _totals = 0;
    // Formats columns headers
    if (e.Row.RowType == DataControlRowType.Header)
    {
        string[] _dateParse;
        string _year = string.Empty;
        string _month = string.Empty;


            switch (ddlIntervals.SelectedValue.ToString())
            {
                case "1":
                    for (int i = 1; i <= e.Row.Cells.Count - 1; i++)
                    {
                        _dateParse = e.Row.Cells[i].Text.Split('-');
                        _year = _dateParse[0].ToString().Remove(0, 2);
                        _month = getMonthAccronyms(_dateParse[1].ToString());
                        e.Row.Cells[i].Text = _month + "-" + _year;
                        e.Row.Cells[i].Attributes.Add("style", "white-space: nowrap;");
                        _dateParse = null;
                    }
                    break;

                case "2":
                    for (int i = 1; i <= e.Row.Cells.Count - 1; i++)
                    {
                        _dateParse = e.Row.Cells[i].Text.Split('-');
                        _year = _dateParse[0].ToString();
                        e.Row.Cells[i].Text = _year;
                    }
                    break;

                case "3":
                    e.Row.Cells[1].Text = "Totals";

                    break;

                default:
                    for (int i = 1; i <= e.Row.Cells.Count - 1; i++)
                    {
                        _dateParse = e.Row.Cells[i].Text.Split('-');
                        _year = _dateParse[0].ToString().Remove(0, 2);
                        _month = getMonthAccronyms(_dateParse[1].ToString());
                        e.Row.Cells[i].Text = _month + "-" + _year;
                        _dateParse = null;
                    }
                    break;
            }

    }

    // Formats mumbers to add comma separators
    int _number1 = 0;

    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        if (ddlIntervals.SelectedValue.ToString() == "3")
        {
            CultureInfo ci = new CultureInfo("en-US");

            for (int i = 1; i <= e.Row.Cells.Count - 1; i++)
            {
                if (e.Row.Cells[i].Text.ToString() != "&nbsp;")
                {
                    _number1 = Convert.ToInt32(e.Row.Cells[i].Text);
                    _totals = _totals + _number1;
                }

               e.Row.Cells[i].Text = _totals.ToString("#,##0", ci);
            }
        }
        else
        {
            CultureInfo ci = new CultureInfo("en-US");
            int _number = 0;
            for (int i = 1; i <= e.Row.Cells.Count - 1; i++)
            {
                if (e.Row.Cells[i].Text.ToString() != "&nbsp;")
                {
                    _number = Convert.ToInt32(e.Row.Cells[i].Text);
                    e.Row.Cells[i].Text = _number.ToString("#,##0", ci);
                }
                else
                {
                    e.Row.Cells[i].Text = "0";
                }
            }
        }
    }
}

The result I get now is this:

 Event type | TOTAL| FEB-02
----------------------------
| Item 1   |  121   |  97   |
--------------------- -------
| Item 2   |   73   |  21   |
-----------------------------

I would appreciate any guandance!

Thanks,

Risho

Answer 1

Give the following a try.

ASPX:

<asp:GridView ID="gvData" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="EventType" HeaderText="Event Type" />
        <asp:TemplateField HeaderText="Total">
            <ItemTemplate>
                <asp:Literal ID="litSum" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

C#:

protected void Page_Load(object sender, EventArgs e)
{
    gvData.RowDataBound += gvData_RowDataBound;
    gvData.DataSource = CreateData();
    gvData.DataBind();
}

void gvData_RowDataBound(object sender, GridViewRowEventArgs args)
{
    if (args.Row.RowType == DataControlRowType.DataRow)
    {
        Literal litSum = (Literal)args.Row.FindControl("litSum");
        DataRow data = ((DataRowView)args.Row.DataItem).Row;

        int sum = 0;
        for (int i = 1; i < data.ItemArray.Length; i++)
        {
            sum += Convert.ToInt32(data.ItemArray[i]);
        }

        litSum.Text = sum.ToString();
    }
}

protected static DataTable CreateData()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("EventType", typeof(string));
    dt.Columns.Add("JAN-01", typeof(int));
    dt.Columns.Add("FEB-01", typeof(int));
    dt.Columns.Add("MAR-01", typeof(int));


    dt.Rows.Add("Item 1", 21, 100, 0);
    dt.Rows.Add("Item 2", 5, 1, 67);            

    return dt;
}

I'm not sure if this is the best place to do this, but since this is a small recordset, you should be okay.