topic Re: Validate Range.Value2 on Excel in .NET Forum

Validate Range.Value2 on Excel

HJohn1 — Sun, 05 Dec 2021 16:50:21 GMT

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.

Re: Validate Range.Value2 on Excel

hosneyalaa — Sun, 05 Dec 2021 19:11:55 GMT

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

Re: Validate Range.Value2 on Excel

HJohn1 — Mon, 06 Dec 2021 03:30:33 GMT

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; }

Re: Validate Range.Value2 on Excel

hosneyalaa — Mon, 06 Dec 2021 06:22:49 GMT

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; }

Re: Validate Range.Value2 on Excel

HJohn1 — Mon, 06 Dec 2021 13:06:09 GMT

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?

Re: Validate Range.Value2 on Excel

_gile — Mon, 06 Dec 2021 13:44:26 GMT

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);

Re: Validate Range.Value2 on Excel

hosneyalaa — Mon, 06 Dec 2021 13:47:59 GMT

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

Re: Validate Range.Value2 on Excel

HJohn1 — Mon, 06 Dec 2021 17:26:05 GMT

_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"

Re: Validate Range.Value2 on Excel

HJohn1 — Mon, 06 Dec 2021 18:19:34 GMT

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; }

Re: Validate Range.Value2 on Excel

_gile — Mon, 06 Dec 2021 19:05:47 GMT

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); } }