Fun with Excel

Question

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.&nbsp; I'm taking the results from our application and writing them to another set of columns.&nbsp; The logic in the test compares the actual results with the expected results that are already in the spreadsheet.&nbsp; 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"){&nbsp;&nbsp; Log.Message("Accecptable Error, test passed");}else{ &nbsp;&nbsp;&nbsp; var numOne = (aqConvert.StrToFloat(Excel.Cells(i, j+24))) ;}(i and j are defined beforehand.&nbsp; 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 ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; // j = columnfor (i = 3; i&lt;=17; i++)&nbsp;&nbsp;&nbsp; // i = row{&nbsp;&nbsp;&nbsp;&nbsp; columnCounter = 23 ;&nbsp;&nbsp;&nbsp;&nbsp; while(columnCounter&lt;=26)&nbsp;&nbsp;&nbsp;&nbsp; {&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Excel.Cells(i, j+columnCounter).Interior.ColorIndex = 0 ;&nbsp;&nbsp;&nbsp;&nbsp; //&nbsp; Reset Cell background color to WHITE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Excel.Cells(i, j+columnCounter).ClearContents ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //&nbsp; Clear Cell data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; columnCounter++ ;&nbsp;&nbsp;&nbsp;&nbsp; }} After this new loop is added to the beginning of the script, I get an error:&nbsp; "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 · Answer

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 · Answer

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 · Answer

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 · Answer

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 · Answer

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

Forum Discussion

Fun with Excel

7 Replies

Recent Discussions

Outlook 365 automation

Regional Checkpoint

How can we fetch ColumnIndex based on Column Name for Syncfusion Grid Control in Python

Related Content

Issue using Excel and variables in name mapping

Excel-Write value

Save API Response as Excel for Content-Type: application/ms-excel