Excel cell dealing with nulls and doubles

AussieBoy

Well-known member
Joined
Sep 7, 2020
Messages
78
Programming Experience
Beginner
Hi, I am attempting to pick up values from an excel spread sheet.
The first cell is null and the code catches it.
The second cell is a double and the same code that checks for null fails.
Can I and how do I get around this. Thanks,
I only want the cell values that are a double and convert these values to string.

C#:
for (int i = 3; i < cnt + 1; i++)
            {
                string TempText = (xlWorkSheet.Cells[i, 5].Value2).ToString();
                MessageBox.Show(TempText);
                if (TempText != null)
                {
                    etc
 
The second cell is a double and the same code that checks for null fails.
Well you are not helping us to help you, so we can only improvise by guessing and throwing irrelevant code at you... In your code. I believe this to be a mistake :
C#:
string TempText = (xlWorkSheet.Cells[i, 5].Value2).ToString();
Why are you setting the TempText as a string?
Leave it as an object and don't call tostring on Value2 and remove the unnecessary open/close parenthesis, and then do your type checking and converting if needed afterwards.

If you only want the doubles, then why not do this :
Screenshot_37.jpg

C#:
            IEnumerable<object> types = __ListOf_Values.Where(n => n != null && n.GetType() == typeof(double));
            using (IEnumerator<object> type = types.GetEnumerator())
            {
                while (type.MoveNext())
                {
                    Console.WriteLine($"Type is : {type.Current.GetType()}. Value is : {type.Current}");
                    /* Do your cast/conversions here */
                }
            }

I don't use excel, so I populated it like so. But Its the same as what you are doing above. The above will pull only doubles :

C#:
            object[] __d1 = { 0.1, 12.1, 2.1, 50, "25", null, 100 };
            List<object> __ListOf_Values = new();

            __ListOf_Values.AddRange(__d1);

If you want to use GroupBy... after some pondering and fresh coffee. Don't use GroupBy. It's handy for grouping each of the cells by type but its not as clean as what you can make do with some other Linq methods. ToLookup is similar in functionality to GroupBy. Basically its a hybrid dictionary which uses IGrouping. It lets you group specific items together via a key, and then access those grouped items via that key in a kinda performance friendly way. These methods add a little extra overhead, as additional iterations are used whereas the above example only pulls what you ask for.

If you are more specific, and share more of your code, we can give you better suggestions.

Next time, it would be more useful to show us the code where you are doing your checking, rather than sharing the start of your statement but not the end. Instead of telling us what you are doing, let us see what you are doing so we can verify that it is exactly what you said you were doing. Often beginner programmers think they are doing one thing, but the outcome is completely different to their expectations. We are not mind readers. Share all relevant code in future, as this also gives us something to work with, instead of throwing probably useless code examples at you.
 
Your input is appreciated but wrong. That doesn't really help. I already explained this above :
In your code. I believe this to be a mistake :
C#:
string TempText = (xlWorkSheet.Cells[i, 5].Value2).ToString();
Why are you setting the TempText as a string?
Leave it as an object and don't call tostring on Value2 and remove the unnecessary open/close parenthesis, and then do your type checking and converting if needed afterwards.
If he leaves it as a string, then all his types will be a string. And the code he was provided with will have no doubles to enumerate over, as can be seen below :

Screenshot_39.jpg

To replicate this yourself, you can change this : object[] __d1 = { 0.1, 12.1, 2.1, 50, "25", null, 100 }; to this : string[] __d1 = { "0.1", "12.1", "2.1", "50", "25", null, "100" }; which is more or less the same as what you are telling the OP to do.

Most types, (almost all) are derived from object. You should read this post by Eric Lippert Not everything derives from object. It will help you too to understand why I told him to do it the way that I did. I wanted them to see their mistake, as I assumed he was checking for a type (double) which was holding the value in a string. So the conditions of their if statement were likely not giving the desired result for this reason.
 
Last edited:
Hi, I am attempting to pick up values from an excel spread sheet.
The first cell is null and the code catches it.
The second cell is a double and the same code that checks for null fails.
Can I and how do I get around this. Thanks,
I only want the cell values that are a double and convert these values to string.

C#:
for (int i = 3; i < cnt + 1; i++)
            {
                string TempText = (xlWorkSheet.Cells[i, 5].Value2).ToString();
                MessageBox.Show(TempText);
                if (TempText != null)
                {
                    etc

I think the problem in your code is the "MessageBox.Show" that throws an error when trying to convert a double to a string.

Which is why ram_rocks said use Convert.ToString - which will convert your double to a string for the purpose of outputting to the MessageBox.

I think this would work for you:

C#:
var testOne = xlRange.Cells[1, 3].Value2;
            
            MessageBox.Show(Convert.ToString(testOne));
            Console.WriteLine(testOne);
            Console.WriteLine(testOne.GetType());
            Console.ReadLine();

Which will tell you testOne is a double, but works with MessageBox.Show because it was converted to a string for the output in MessageBox.
 
I think the problem in your code is the "MessageBox.Show"
No its not.
that throws an error when trying to convert a double to a string.
No it doesn't.
If the OP was trying to pass in a double to the MessageBox.Show method, it would not compile, because it is expecting a string.

A direct replicate of the OP's code bar line 4 :
C#:
            double d = 2.2;
            string s = d.ToString();
            MessageBox.Show(s);
            MessageBox.Show(Convert.ToString(s));
MessageBox.Show popup both boxes as you will find out if you run it. You certainly don't need to convert a string to a string.
The OP is already using a string by the time it reaches the MessageBox.Show method.

If the OP would ever show his if statement, you would find that his issue is exactly as it was explained and answered on #4.
 
The point I made was that you didn't need to make it a string.

His code was creating a system.string - not a double.

By simply removing the ToString from the excel line and changing to a var he can keep the double and whether using Convert.ToString or appending with ToString() - the messagebox works - yet you keep the double.

I also think it is important to show var - because it is dynamic.

Which might be useful for him to know that

In my code, you can now do this:

C#:
Console.WriteLine(testOne + testOne);

And get a number that has been added to itself.

In his code, you get two strings appended to each other.
 
Last edited:
because when it doesn't compile...it throws an error up?
It can't throw an error when the compiler won't run the code on build.

'System.Windows.MessageBox.Show(string)'
...
I don't know what you done to replicate that result nor do i care, as it is unrelated to what is being reported.
What you wrote on p1/#8 is not the problem. The OP also said they were having issues with their if statement :
The first cell is null and the code catches it.
The second cell is a double and the same code that checks for null fails.
Which has already been answered properly on p1/#4 and should be marked as the answer.
 
I have explained why I posted and why I thought it was valuable to do so.

If he doesn't want to use the information, that is his choice.

It isn't for you to police.

If I have told him something wrong, that a var won't be a double, that you can't convert to a string and keep it as a double for other purposes.

Then feel free to correct me.
 
Back
Top Bottom