Check whether a cell is in range or not in Excel using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

I have encountered a bug with using Range.GetCellOrNull(). I am grabbing a named range and checking to see whether a cell is inside that named range. When i pass the cells row and column to GetCellOrNull of my base named range and its outside of the bounds of the base named range i would expect this function to return a null. Instead it throws an exception from the dll of “Value does not fall within the expected range.”.


Example
Range base = workbook.Worksheets.GetRangeByName(“base”); //Has a area of C1:AA134

As I am looping a set of cells to see if the base contains it I do something like the following:

base.GetCellOrNull(139, 7); //This is when the error is then thrown.

For now i can check if the row count and column count are not larger than my base named ranges row and column counts but this should probably just throw a null back.

One other thing that would be nice is to have a Range.Cells collection that could be used. With this i can instantly get the cell objects if i need to or check to see if a cell is part of a named range.

Thanks,
Mark
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells for .NET (Latest Version)
and see if you still face the issue with it. Let us know your feedback.

Please provide us your sample code and source (xls/xlsx) file if you are using any to replicate this issue.

We will look into it and help you asap.

3

Hi,

It is not a bug anyways. Please note that the row/column index is 0-based from top-left corner of the range for Range.GetCellOrNull(), not the “A1” cell of sheet. And this method is just provided for users to get cells in the range. Using range object to access cells out of given range is meanningless. So users should take care of there program not to give invalid cell position for this method.

4

I wouldn’t say it is meaningless by all means, not for my use anyway. I generically parse workbooks at a cell level. They are setup with numerous named ranges, some of which are to constrain what data is parsed. I want to validate that a given cell is in a named range.


Currently there are no available public methods on a Range object besides get cell or null that will tell me what cell objects the range contains. Because of this i have to use getcellornull. I already have the cell i just want to know that it belongs to said named range. The only other way is to iterate over them in a for each loop which is out of the question due to performance.

Yup i understand that the row/column index is 0 based. I would say this is still a bug as if i pass in indexes outside the row and column lengths for a given Range it throws an unhandled error from Aspose instead of a null. I have gotten around this in code by doing checks using the following as well as a try catch just in case.

myRange.RowCount > cell.Row && myRange.ColumnCount > cell.Column

I have not tried latest was there a fix specifically for this?
5

Hi,

To check whether a cell is in a Range, the expression should be:


rowIndex >= r.FirstRow && rowIndex <= r.FirstRow+r.RowCount && colIndex >= r.FirstColumn && colIndex <= r.FirstColumn+r.ColumnCount

Where r is the range and rowIndex/colIndex is the row/column index of cell(Cell.Row/Cell.Column).

6

Yes, sorry i was just giving a small sudo snippet. I am currently using something very similar to do my checks. I was really hoping to get away from a huge line for something very simple and clean

Range.GetCellOrNull() != null or if something opened up like Range.Cells and use it.

No biggie i will continue to use my checks on indexes and counts.

Thanks,
Mark
7

Hi,

Thanks for your feedback.

Let us know if you face any other issue or if you have any other questions, we will be glad to assist you further.

8

This thread is very old but in looking back my thought still remains true. Look at the name of the function GetCellOrNull if i break it down its either return a cell or return a null. So when i pass it something incorrect or out of bounds it just blows up instead of returning a null? Seems like an issue to me.


Thanks,
Mark
9

Hi,

Your point seems to valid. The name of the function is confusing. I will discuss it.

Anyway, I have written the following function for your convenience. Please check the source.xlsx file, there is a named range i.e base.

Then in a code I check which of the cells in first ten rows and columns fall in this range. Then those which fall in this range, I print their names.

I have attached the source xlsx file and screenshot and debug output for your reference.

C#


//It will return null if the cell does not fall in this range

//If the cell is in this range, it will return the cell’s reference

Cell GetCellOrNull(Range r, int rowIndex, int colIndex)

{

int minRow = r.FirstRow;

int maxRow = r.FirstRow + r.RowCount - 1;

int minCol = r.FirstColumn;

int maxCol = r.FirstColumn + r.ColumnCount - 1;


if (rowIndex < minRow || rowIndex > maxRow)

return null;


if (colIndex < minCol || colIndex > maxCol)

return null;


return r.Worksheet.Cells[rowIndex, colIndex];

}


//Test code to check this function

void TestGetCellOrNull()

{

string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Range baase = workbook.Worksheets.GetRangeByName(“base”);


//Testing the function, print the name of the cell if it falls in this range.

for (int r = 0; r < 10; r++)

{

for (int c = 0; c < 10; c++)

{

Cell check = GetCellOrNull(baase, r, c);


if (check != null)

Debug.WriteLine(check.Name);

}//inner for

}//outer for


}

Output:
C2
D2
E2
F2
C3
D3
E3
F3
C4
D4
E4
F4
C5
D5
E5
F5
C6
D6
E6
F6
C7
D7
E7
F7

Screenshot:
10

Right on Thanks!

11

Hi,

Thanks for your feedback and input.

I have had a discussion with the development team about the Range.GetCellOfNull() method.

Since this method belongs to Range object, so it must work within the Range parameters (dimension), therefore it ill not return null if the row or column indices are beyond the bounds of Range rather it will throw exception.

The cases when it will return null is that sometimes cells of range exist but they are not instantiated, so in such cases it returns null.

You can therefore use the above sample code given by me for your needs without having any issue.