Datagridview programmatically select multiple rows and sum up values in one column

Question

Column1        Column 2
Payment 2001   $45.9
Refund 2002    $56.8 
Refund 2003    $67.9
Payment 2004   $88.0
Payment 2006   $39.9

Hi. I have a DataGridView with two columns. One column is a text description and the other column is the actual values. I want to sum up just the payment values and display the decimal sum to a TextBox by button click. My payment sum from above is $173.8 and this is what I want to display in my TextBox. I've managed to cobble together enough code to select my initial payment value but I've been stuck since. :)

Any help will be appreciated. My code so far:

foreach (DataGridViewRow row in dataGridView1.Rows)
{
    for (int index = 0; index < dataGridView1.ColumnCount - 1; index++)
    {
        DataGridViewCell cell = row.Cells[index];
        if (cell.Value == DBNull.Value || cell.Value == null)
            continue;
        if (cell.Value.ToString().Contains("Payment"))
        {
            DataGridViewCell next = row.Cells[index + 1];

OhBeWise · Accepted Answer · 2015-06-24 16:40:42Z

1

Solution

Declare a variable before your loops:

decimal sum = 0m;

Then, using your same code, within your loops just parse out the amounts (with the appropriate CultureInfo) and add them up as you loop:

sum += Decimal.Parse(next.Value.ToString(), NumberStyles.Currency, myCultureInfo);

Example

I created the following test to show how it should parse out the same for any culture. ~~(I couldn't test GHS as I couldn't find the correlating CultureInfo for it, but I still tested another multi-character one for demo purposes.)~~ For GHS, you can create a custom culture. A fully customized example can be seen below, though for GHS you will probably only need change the custom.NumberFormat.CurrencySymbol property.

decimal amount = 1234.56m;

CultureInfo us = new CultureInfo("en-US");
CultureInfo gb = new CultureInfo("en-GB");
CultureInfo by = new CultureInfo("be-BY");
                                                            // Values for...
CultureInfo custom = (CultureInfo)us.Clone();               // USD      GHS
custom.NumberFormat.CurrencyDecimalDigits = 2;              // 2        2
custom.NumberFormat.CurrencyDecimalSeparator = " DECIMAL "; // "."      "."
custom.NumberFormat.CurrencyGroupSeparator = " GROUP ";     // ","      ","
custom.NumberFormat.CurrencyGroupSizes = new int[] { 2 };   // { 3 }    { 3 }
custom.NumberFormat.CurrencyNegativePattern = 0;            // 0        0
custom.NumberFormat.CurrencyPositivePattern = 0;            // 0        0
custom.NumberFormat.CurrencySymbol = "SYMBOL ";             // "$"      "₵" or "GH₵" ?

NumberFormatInfo nf1 = us.NumberFormat;
NumberFormatInfo nf2 = gb.NumberFormat;
NumberFormatInfo nf3 = by.NumberFormat;
NumberFormatInfo nf4 = custom.NumberFormat;

string s1 = amount.ToString("c", nf1);
string s2 = amount.ToString("c", nf2);
string s3 = amount.ToString("c", nf3);
string s4 = amount.ToString("c", nf4);

Console.WriteLine(s1);    // $1,234.56
Console.WriteLine(s2);    // £1,234.56
Console.WriteLine(s3);    // 1 234,56 Br
Console.WriteLine(s4);    // SYMBOL 12 GROUP 34 DECIMAL 56

decimal d1 = Decimal.Parse(s1, NumberStyles.Currency, us);
decimal d2 = Decimal.Parse(s2, NumberStyles.Currency, gb);
decimal d3 = Decimal.Parse(s3, NumberStyles.Currency, by);
decimal d4 = Decimal.Parse(s4, NumberStyles.Currency, custom);

Console.WriteLine(d1);    // 1234.56
Console.WriteLine(d2);    // 1234.56
Console.WriteLine(d3);    // 1234.56
Console.WriteLine(d4);    // 1234.56

edited Jun 24, 2015 at 16:40

answered Jun 22, 2015 at 16:20

OhBeWise

5,4745 gold badges36 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gonja Over a year ago

Thanks OhBeWise. That worked well for me. However i noticed if i change the dollar sign to another currency say GBP or GHS then i get a too many characters in character literal error. i tried to use " " instead of ' ' but no luck. I got an implicit conversion error

OhBeWise Over a year ago

@Gonja Good catch. That method will fail for numerous other currency types since the currency symbol may be multiple characters. I did more research based off this additional requirement and found a better way to parse out the numeric amounts. I also found that it's better to convert to decimal instead of double. I've updated my answer to reflect these changes.

Gonja Over a year ago

Appreciate the help @ OhBeWise. Your example was very helpful. I'm currently working with the GHS currency so i did a little research myself and came up with no cultreinfo for it. I'm trying to use invariant culture to solve the problem but i'm getting an input string was not in a correct format error from the line below. Any ideas? sum += Decimal.Parse(next.Value.ToString(),NumberStyles.Currency, CultureInfo.InvariantCulture);

OhBeWise Over a year ago

@Gonja You are getting that error because next.Value.ToString() probably doesn't look like this: ¤1,234.56, which was my output when I used an InvariantCulture. Regardless, I believe using a customized NumberFormat will work. I've updated my example to demonstrate.

Pang · Accepted Answer · 2015-06-28 04:56:16Z

@OhBeWise That worked flawlessly. Really appreciate the help. Thank you for your patience. Full code below

private void button8_Click(object sender, EventArgs e)
{

   decimal amount = 0;

   CultureInfo us = new CultureInfo("en-US");
   CultureInfo custom = (CultureInfo)us.Clone();
   custom.NumberFormat.CurrencySymbol = "GHS ";


   foreach (DataGridViewRow row in dataGridView1.Rows)
   {
       for (int index = 0; index < dataGridView1.ColumnCount - 1; index++)
       {
           DataGridViewCell cell = row.Cells[index];
           if (cell.Value == DBNull.Value || cell.Value == null)
               continue;
           if (cell.Value.ToString().Contains("Payment"))
           {
               DataGridViewCell next = row.Cells[index + 1];
               string s4 = next.Value.ToString();
               amount+= Decimal.Parse(s4, NumberStyles.Currency, custom);

                    textBox22.Text = amount.ToString();
               }



           }


       }

Collectives™ on Stack Overflow

Datagridview programmatically select multiple rows and sum up values in one column

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related