I use this code to cast
my DataGridView
and then display results in another DataGridView
.
Basically, I'm getting some int values and calculating their sum, while also grouping these values with GroupBy
.
I display the results in another DataGridView
like so:
datagridview2.DataSource= Su.ToList();
My problem is that the query seems to stop if the cell has a string value.
I've tried adding a 'where
not' condition to exclude rows that contain string values in several different ways but did not succeed.
Here an example of the condition I've tried:
.Where(row => !row.Cells[2].Value.ToString().Equals("example"))
This code is in the datagridview_cellvaluechanged
method:
try
{
var Su = dataGridView1.Rows.Cast<DataGridViewRow>()
.Where(row => row.Cells[8].Value != null)
.Where(row => row.Cells[2].Value != null)
.GroupBy(row => row.Cells[8].Value.ToString())
.Select(g => new
{
Gruppo = g.Key,
Serie = g.Sum(row => Convert.ToInt32(row.Cells[2].Value)),
Reps = g.Sum(row => Convert.ToInt32(row.Cells[3].Value) * Convert.ToInt32(row.Cells[2].Value)),
Percent = (g.Sum(row => Convert.ToDecimal(row.Cells[2].Value)) / Convert.ToDecimal(label15.Text)) * 100
});
}
catch (FormatException)
{
//MessageBox.Show("Formato non corretto");
}
This is what the DataGridView should look like, if it suceeds; dgv1:
column 1 | column 2 |column 3
2 3 group1
3 5 group1
4 6 group2
3 4 group3
And I see this in the result on dgv2:
column 1 | column 2 |column 3
5 8 group1
4 6 group2
3 4 group3
But if I add a string value in column2 like this:
column 1 | column 2 |column 3
2 3 group1
3 5 group1
4 string group2
3 4 group3
Then I see this in the result on dgv2:
column 1 | column 2 |column 3
5 8 group1
CodePudding user response:
While I'm still not entirely sure what the issue is, I will attempt to answer your question.
First off, if you are not 100% sure that a specific cell contains an Int value, you should not just blindly convert it.
There are ways to check if it is an int before converting it. One way is this:
try
{
var Su = dataGridView1.Rows.Cast<DataGridViewRow>()
.Where(row => row.Cells[8].Value != null)
.Where(row => row.Cells[2].Value != null)
.GroupBy(row => row.Cells[8].Value.ToString())
.Select(g => new
{
Gruppo = g.Key,
Serie = g.Sum(row => {
if(row.Cells[2].Value is int rowInteger)
return rowInteger;
// if it is not an integer then try to convert
// return a default value (in this case -1) if it can't be converted
else
{
try
{
return Convert.ToInt32(row)
}
catch(Exception)
{
return -1;
}
}
},
Reps = g.Sum(row => Convert.ToInt32(row.Cells[3].Value) * Convert.ToInt32(row.Cells[2].Value)),
Percent = (g.Sum(row => Convert.ToDecimal(row.Cells[2].Value)) / Convert.ToDecimal(label15.Text)) * 100
});
}
catch (FormatException)
{
//MessageBox.Show("Formato non corretto");
}
This will make sure that all rows are kept but only the rows that are valid are calculated on.
A simpler approach, that won't keep the original number of rows is to check whether the value of the cell is an int or can be converted into an int is to add the following Where
statement:
Where(row => row.Cells[2].Value is int || int.TryParse((row.Cells[2].Value as string), out var result))
Note that you are actually parsing the value in each iteration and discarding the result, this is of course far from being optimized but will get you the wished result.