Forum Discussion

lrbehmer's avatar
lrbehmer
Contributor
14 years ago

Fun with Excel

Frankly, this is starting to look like a bug but I didn't want to jump the gun.



I'm using a spreadsheet that has input and expected outputs in their respective columns.  I'm taking the results from our application and writing them to another set of columns.  The logic in the test compares the actual results with the expected results that are already in the spreadsheet.  If they don't match, I change the color of the cell for the value in question.



So, I have this bit of logic:

if(Excel.Cells(i, j+24) == "Null")

{

   Log.Message("Accecptable Error, test passed");

}

else

{

    var numOne = (aqConvert.StrToFloat(Excel.Cells(i, j+24))) ;

}

(i and j are defined beforehand.  this code exists in a loop.)

Everything goes fine until I add a loop beforehand that clears the contents of the cells for the new results:

j = 2 ;                                // j = column

for (i = 3; i<=17; i++)    // i = row

{

     columnCounter = 23 ;

     while(columnCounter<=26)

     {   

          Excel.Cells(i, j+columnCounter).Interior.ColorIndex = 0 ;     //  Reset Cell background color to WHITE

          Excel.Cells(i, j+columnCounter).ClearContents ;                   //  Clear Cell data

          columnCounter++ ;

     }

}



After this new loop is added to the beginning of the script, I get an error:  "An exception occurred...The argument is not a number." right at the "numOne = aqConvert.StrToFloat()" spot.



My question is - "Why am I getting this exception?"



TIA.
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    After this new loop is added to the beginning of the script, I get an error:  "An exception occurred...The argument is not a number." right at the "numOne = aqConvert.StrToFloat()" spot.



    My question is - "Why am I getting this exception?"




    I'm curious as to what the actual contents are of the cell in question.  If the contents don't map to a numerical value (like if they contain a comma, an alpha character, etc), then they cannot be converted from the string to the numerical value of the float.



    Have you tried putting a breakpoint on that line and seeing what the value of the cell is?  I'm guessing that it's not "Null" but some other empty object.  You might want to use a different check to bypass "empty" values.  Try Jared's suggestion in http://smartbear.com/forums/forum/post/?mode=singleThread&thread=6e5fb87a-4a1c-45d7-bb64-17a28e8f62cc
  • lrbehmer's avatar
    lrbehmer
    Contributor
    14 years ago
    That's the thing - ALL of these cells are either empty or they have the string "Null" in them or they have an integer or a float.



    It's like it's skipping the IF statement completely.
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    I've found that an "empty" cell in an Excel sheet does not test as "Null".  



    Have you tried using a test similar to what Jared posted in that thread I mentioned?  That might be a better test for "empty" contents than testing for "Null".



    if((aqObject.GetVarType(Excel.Cells(i, j+24)) < 2) || (Excel.Cslls(i,j+24) == "Null"))

    {

       Log.Message("Accecptable Error, test passed");

    }

    else

    { 

        var numOne = (aqConvert.StrToFloat(Excel.Cells(i, j+24))) ;

    }




    See, your "empty" cells are not Null... but they are also not strings, so they pass your previous "if" statement but "blow up" when trying to convert that "empty" value to a float...



    Not sure if the above code will do what you want it to do, but I think that's where you're going to want to aim...


  • lrbehmer's avatar
    lrbehmer
    Contributor
    14 years ago
    Yeah - I'm looking at the method he posted now.  At the least, this will help me troubleshoot, but your suggestion looks like the best solution.



    I'm keeping in mind that "empty" doesn't necessarily mean "Null".  We are using the string "Null" as a value in some of these expected result cells.  That's what the If statement is checking for.  But the entire script stops running when it gets to the "var numOne" point.



    I'll also need to look at what .ClearContents leaves.  Looks like those cells have data type of "9" when I log:

    Log.Message( i + ", " + j + " = " + GetVarType(Excel.Cells(i, j+24))) ;

    What does it mean when the cell is typed "VarDispatch"?



    For my own edification - how are you making those code blocks?? ;-p
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    14 years ago
    I'm not sure exactly what IDispatch represents but I believe it's an object of some sort.  So, what you're getting is not the contents of the cell but the cell itself is my guess.  So, your check needs to look for IDispatch of 9 and, if it's 9, treat it as "empty".



    As for the code blocks, use squarebracket code squarebracket like a wiki-tag and close it with a /code in square brackets.  You can do the same with quotes
  • YMinaev's avatar
    YMinaev
    Staff
    14 years ago
    Hi,



    Robert is right. If you get IDispatch as the value type, this represents the cell object. Use Excel.Cells(i, j+24).Value or .Text instead.



    Also, it is a good practice to deal with cell properties which return its value instead of the cell itself. In most cases, the convertion can work automatically, and just 'Excel.Cells(...)' will work, but addressing the value explicitly is more reliable anyway.
  • lrbehmer's avatar
    lrbehmer
    Contributor
    14 years ago
    Thanks so much guys, this was exactly the issue.



    Normally, we have been using Excel.Cells(i,j) and that worked - so that's what we used.  For some reason, the loop I added changed the context and it started returning the Cell object within that IF statement, and it would always pass because the Cell object would never be "Null".  Now, we will be explicitly using .Text to avoid issues, (at least, that's what I'll be pushing for ;-p).



    But this also points out more functionality to me and opens new avenues.  Up until this point, I've been working with someone else's code and my brain doesn't question what someone else has written because I presume it was the correct/best/only solution.  But issues like this and others I have been posting about have made me go back and really question what's already been written.



    Thanks guys!