MarcJo
January 23, 2020, 5:04pm
1
I would like to total up all values/cells of a specific column and store the sum in a variable to keep on working with it.
So far I implemented this:
String_var = excel_original.AsEnumerable.Sum(Function(x)Convert.ToDecimal(x(10))).ToString
The problem is that this code does not consider the commas which are the decimal separators.
So in the case below
1,5
2,5
3
2,5
3,5
… the result should actually be 13. But it’s 130 since the comma is not considered.
Can someone please help?
bcorrea
(Bruno Correa)
January 23, 2020, 7:05pm
2
Convert.ToDecimal(value, System.Globalization.CultureInfo(“fr-FR”))
MarcJo
January 23, 2020, 8:23pm
3
@bcorrea
Like this…
String_var = excel_original.AsEnumerable.Sum(Function(x)Convert.ToDecimal(value, System.Globalization.CultureInfo(“fr-FR”)).ToString
???
bcorrea
(Bruno Correa)
January 23, 2020, 8:28pm
4
if you have a format that is different from the region format that your system is running on, than you need to specify the right one…
vickydas
(vicky)
January 24, 2020, 6:48am
5
Hello @MarcJo
You can easily do that using LinQ
Use this Code
DT.AsEnumerable().Select(Function(r) if(CStr(r("A")).Contains(","),cstr(r("A")).Replace(",","."),cstr(r("A")) )).Sum(Function(n) cdec(n) )
This code will replace the Commas ( , ) with Dots (.) and then sum all the value check the code and let me know about it
1 Like
MarcJo
January 24, 2020, 7:25am
6
Hi @vickydas ,
thanks for your support.
The point is that the values’ format has to be kept for following processing.
So is there an opportunity to total it up while keeping the comma?
vickydas
(vicky)
January 24, 2020, 7:28am
7
Hello @MarcJo
By this you mean to keep the comma in the desired output right ? because the input data is not affected by the code…
For this output after getting the sum you can simply just replace the Dot with a Comma
just like this
DT.AsEnumerable().Select(Function(r) if(CStr(r("A")).Contains(","),cstr(r("A")).Replace(",","."),cstr(r("A")) )).Sum(Function(n) cdec(n) ).ToString.Replace(".",",")
1 Like
MarcJo
January 24, 2020, 7:33am
8
@vickydas …sounds good.
And the “A” stands for the column?
So I need to replace it with the desired column I actually want to be totaled up?
vickydas
(vicky)
January 24, 2020, 7:34am
9
@MarcJo
Yes
Yes “A” here is the column name
Let me know about it
MarcJo
January 24, 2020, 7:45am
10
@vickydas … it’s working!
Thank you so much, you’re my savior
MarcJo
January 24, 2020, 9:13am
11
Hi @vickydas ,
sorry for disturbing you again.
But I tried to adopt your code for a similar process and this was not working - see screenshot below:
I guess it’s only a minimal adjustment but I currently don’t know on which position in the code.
Can you please help me once again?
vickydas
(vicky)
January 24, 2020, 10:02am
12
Hello @MarcJo
This error occured because you have blank values in your data SO it cannot blank value to Decimal
DT.AsEnumerable().Select(Function(r) if(CStr(r("A")).Contains(","),cstr(r("A")).Replace(",","."),cstr(r("A")) )).Sum(Function(n) if(string.IsNullOrEmpty(cstr(n)),0,cdec(n)) ).ToString.Replace(".",",")
This code will consider a blank value as 0 and will give you desired output…
Also you can learn basic methods of LinQ from the link below
LINQ samples in LINQ to Objects
MarcJo
January 27, 2020, 11:12am
13
@vickydas thanks a lot for your help
MarcJo
January 27, 2020, 2:10pm
14
@vickydas ,
sorry for asking you again but I got another error message I don’t know how to get rid of:
Could you help one last time please?
Thanks an advance
bcorrea
(Bruno Correa)
January 27, 2020, 2:14pm
15
change this part string.IsNullOrEmpty(cstr(n))
to this n is DBNull.Value Or string.IsNullOrEmpty(cstr(n))
MarcJo
January 27, 2020, 2:28pm
16
@bcorrea I changed the code accordingly but the error message was the same
Any other idea?
bcorrea
(Bruno Correa)
January 27, 2020, 2:35pm
17
ok, then is because the column that is bringing null values is the r(24), you will need to add to that if condition the same as you did for for it…
MarcJo
January 27, 2020, 3:04pm
18
@bcorrea , @vickydas
After changing it accordingly I received the same error message…
excel_original.AsEnumerable().Select(Function(r ) if(CStr(r(24)).Contains(“,”),cstr(r(24)).Replace(“,”,“.”),cstr(r(24)) )).Sum(Function(n) if(n is DBNull.Value Or string.IsNullOrEmpty(cstr(24)),0,cdec(n)) ).ToString.Replace(“.”,“,”)
bcorrea
(Bruno Correa)
January 27, 2020, 3:16pm
19
in here: excel_original.AsEnumerable().Select(Function( r ) if(CStr(r(24)).
MarcJo
January 27, 2020, 3:23pm
20
@bcorrea would you mind sending me the whole code?
For now I am not really sure which changes still need to be done