How to calculate sum of datatable with conditions C#

Question

0.00/5 (No votes)

See more:

I have a dataTable where i get data from imported excel file using Microsoft Office Interop Excel then show it in datagridview

So am looking for filter and calculate data from datatable and show it 2nd datagridview
This is how the datatable get created

xlRange = xlWorlSheet.UsedRange;
                   dt.Columns.Add("ID", typeof(string));
                   dt.Columns.Add("NA", typeof(string));
                   dt.Columns.Add("ACC", typeof(decimal));
                   dt.Columns.Add("DEP", typeof(string));
                   dt.Columns.Add("AMOUNT", typeof(string));

for (xlRow = 2; xlRow <= xlRange.Rows.Count; xlRow++)
                   {
                           if (xlRange.Cells[xlRow, 2].Text != "")
                       {
                           dt.Rows.Add(new object[] {
                       xlRange.Cells[xlRow, 1].Text,xlRange.Cells[xlRow, 2].Text, xlRange.Cells[xlRow, 3].Text, xlRange.Cells[xlRow, 4].Text,
                       xlRange.Cells[xlRow, 5].Text});

Now i got where work fine but the SUM from select condition always get 0 as sum

What I have tried:

  decimal SumAmnt = (from DataGridViewRow row in dataGridView1.Rows
where row.Cells[0].FormattedValue.ToString() == xlRange.Cells[xlRow, 1].Text
select Convert.ToDecimal(row.Cells["AMOUNT"].FormattedValue, new CultureInfo("en-US"))).Sum();

Posted 4-Jun-20 8:42am

Usarsef

Updated 4-Jun-20 22:46pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2020-06-04T15:26:00

What your issue says to me, is, you need to learn how to use the debugger or learn other methods for debugging. You have 2 areas of concern in your LINQ statement.
1)

Quote:
row.Cells[0].FormattedValue.ToString() == xlRange.Cells[xlRow, 1].Text

is NEVER true and/or

2) this

Quote:
Convert.ToDecimal(row.Cells["AMOUNT"].FormattedValue, new CultureInfo("en-US"))

never produces a value that can be summed, although, I'd expect the compiler to tell you 'no can do'

So, I'd eliminate (1) first - if you can't for some reason check the data using a breakpoint and inspect the relevant items, before your LINQ sum, put a foreach and loop through the data and write it out - to the console for example, eg

foreach (DataGridViewRow row in dataGridView1.Rows)
{
    Console.WriteLine("row.Cells[0].FormattedValue.ToString() : {0}", row.Cells[0].FormattedValue.ToString());
    Console.WriteLine("xlRange.Cells[xlRow, 1].Text : {0}", xlRange.Cells[xlRow, 1].Text);
    Console.WriteLine("Convert.ToDecimal...: {0}", Convert.ToDecimal(row.Cells["AMOUNT"].FormattedValue, new CultureInfo("en-US")));
    Console.WriteLine("--");
}

If the first two items in each row don't 'match' then that's the first issue, and if the second doesn't produce a 'number' representation, then, that's the second

Maciej Los · Answer 2 · 2020-06-04T20:16:00

First of all: use proper data type for each column!

C#

dt.Columns.Add("ID", typeof(string));
dt.Columns.Add("NA", typeof(string));
dt.Columns.Add("ACC", typeof(decimal));
dt.Columns.Add("DEP", typeof(string));
dt.Columns.Add("AMOUNT", typeof(string)); //?!? double or decimal

Then, instead of

C#

dt.Rows.Add(new object[] {xlRange.Cells[xlRow, 1].Text, ...});

use:

C#

dt.Rows.Add(new object[] {xlRange.Cells[xlRow, 1].Value, ...});

Finally, get sum by using datatable object:

C#

DataTable dt = (DataTable)dataGridView1.DataSource;
double SumAmnt = dt.AsEnumerable()
    .Where(x=> x.Field<string>("NA")== "sometextvalue")
    .Sum(x=> x.Field<string>("AMOUNT"));
//now, you can display sum whethever you want

Usarsef · Answer 3 · 2020-06-04T22:48:00

System.Data.DataTable table = new System.Data.DataTable();

                  table.Columns.Add("NA", typeof(decimal));
                  table.Columns.Add("AMOUNT", typeof(decimal));
                  table = dt.AsEnumerable()
                 .GroupBy(r => r["NA"])
                 .Select(g =>
                 {
                     var row = table.NewRow();

                     row["NA"] = g.Key;
                     row["AMOUNT"] = g.Sum(r => (decimal)r["AMOUNT"]);

                     return row;
                 }).CopyToDataTable();

                  dataGridView2.DataSource = table;

                  }

How to calculate sum of datatable with conditions C#

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0