Validate Range.Value2 on Excel

[HJohn1]

I need to read coordinates (x,y) from excel workbooks, where x coordinates are stored on column B and y on column C on worksheets. I need to read these values for further process in AutoCAD. My problem is that I can't find a way to properly validate these values from Excel. Both columns in the worksheet are number formatted with two decimal places. Initially, I thought well this is not a big deal I will check, (Range.Value2 != Null && Range.Value2 != string.Empty), to make sure the range is not blank. As you can see on the picture below the stored data contains 0.00 values and spaces. I only need to read the ranges (cells) which contain a valid values. I don't know how many rows contain values, so I need to go down on column B until all rows containing values have been read, say stop after reading 5 blank rows. My initial validation check generated errors like "Operator != cannot be applied to operands of type double and string" I also tried many combinations of checks and I have not been able to find one that I can use. Storing the range.value2 on a variable and checking after did not work because the number formatted cells return 0.0 which conflicts with the valid values.  I have always copied data from AutoCAD to Excel, this is the first time I need to copy from Excel to AutoCAD.  I hope some people here might have done this. Any suggestions will be greatly appreciated.

Please if anyone can suggest a way to do this properly, I will be greatly appreciated.

[hosneyalaa]

Hi

Can you attach your code and excel file? So we work on it until we get best results

[HJohn1]

public Point2dCollection getCoordinates(Excel.Worksheet sheet)
{
Point2dCollection points = new Point2dCollection();
double X, Y;

Excel.Range rg = sheet.Range["B:B"];

for (int j = 1; j <= rg.Count; j++)
	{
	//I need to make sure cells in the worksheet are not empty/blank before I collect the coordinates.
	//Here I get errors when cells contain real coordinates.
	//Operator != cannot be applied to operands of type double and string.
	//Tried storing Value2 on a local variable but blank cells get 0.0.
	if (rg[j].Value2 != null && rg[j].Value2 != string.Empty)
		{
			X = 0;
			Y = 0;
			//More code here.
			points.Add(new Point2d(X,Y));
		}
	}
return points;
}

[hosneyalaa]

Hi
It is better to attach the excel file
He worked with me on this case

public static Point2dCollection getCoordinates(Excel.Worksheet sheet)
{
Point2dCollection points = new Point2dCollection();
double X, Y;

Excel.Range rg = sheet.Range["B:B"];

for (int j = 1; j <= 6; j++)
{
//I need to make sure cells in the worksheet are not empty/blank before I collect the coordinates.
//Here I get errors when cells contain real coordinates.
//Operator != cannot be applied to operands of type double and string.
//Tried storing Value2 on a local variable but blank cells get 0.0.
if (rg[j].Value2 != null )
{
X = 0;
Y = 0;
//More code here.

points.Add(new Point2d(X, Y));
}
}
return points;
}

[HJohn1]

Thank you very much for your help.  I don't understand your results, on your sheet you only have a pair of (0,0) and (30,30) coordinates and on your collection you have two and missing the others, but this could be a minor issue.  How you test for blank cells?  I think a key issue is the number format on Excel.  How you differentiate between a (0,0) coordinate (values) and a blank cell?

[_gile]

Hi,

Something like this ?

if (double.TryParse(sheet.Cells(i, j).value2.ToString(), out double x) &&
    double.TryParse(sheet.Cells(i, j + 1).value2.ToString(), out double y))
    points.Add(new Point2d(x, y);

[hosneyalaa]

Results are just for testing

Because I only tested column b

Did you solve your problem?

if you don't solve it You must attach an excel file to work on it

Thank you

[HJohn1]

_gile, thank you for your time.  I have already tried your suggestion.  I am caught in a catch 22, if I check for Null it fails when the cell is not blank (valid number) and if I don't check for Null it fails when it is  Null.  I am getting these errrors

1- "Cannot perform runtime binding on a null reference"

2- "Operator != cannot be applied to operands of type double and string"

[HJohn1]

I got this approach working.  I don't know if this is the correct way of going about it, but works.  If anyone has a better solution please, let me know.

for (int j = 1; j <= rg.Count; j++)
	{
	 object obj_X = range[i].Value2;

     object obj_Y = range[i].Offset[0, 1].Value2;

		if (obj_X != null && obj_Y != null)
		{
			double.TryParse(obj_X.ToString(), out X);

			double.TryParse(obj_Y.ToString(), out Y);

			points.Add(new Point2d(X, Y));
		}
	}
	return points;
}

[_gile]

I was trying something similar with a Console Application.

double x = 0.0, y = 0.0;
var rgX = sheet.Range["B:B"];
var rgY = sheet.Range["C:C"];
for (int i = 1; i <= rgX.Count; i++)
{
    object objX = rgX[i].Value2;
    object objY = rgY[i].Value2;
    if (objX != null && double.TryParse(objX.ToString(), out x) &&
        objY != null && double.TryParse(objY.ToString(), out y))
    {
        Console.WriteLine("X: {0} Y: {1}", x, y);
    }
}