Range GetCellOrNull returns null for known ranges

Aspose.Cells Product Family
1

Apologies up front if this answer is already posted somewhere … or if this is just a plain lack of understanding on my part.

I am attempting to open an existing Excel document (XLS, word 2003 format) and update a number of named ranges. This is a legacy product where I am just trying to duplicate the functionality that currently works using Excel automation so I can not really modify the existing Excel files.

Anyhow, I am using Worksheets.GetNamedRanges() to get all the ranges defined in the document. Then I loop through all ranges to update their values by accessing the range’s cell by calling range.GetCellOrNull. Now this works for a few of the named ranges, but for dozens of fields, even though the range exists in the collection, the call to GetCellOrNull returns Null.

Here is a snippet of the code I am attempting:
foreach(var range in workbook.Worksheets.GetNamedRanges()) {
var cell = range.GetCellOrNull(range.RowCount - 1, range.ColumnCount - 1);
if (cell == null) continue;
if (cell.IsStyleSet) {
var style = cell.GetStyle();
if (style.IsLocked) {
style.IsLocked = false;
cell.SetStyle(style);
}
}
cell.PutValue(“testValue”);
}


Again, my issue here is with the cal lto GetCellOrNull returns null, even the the range is coming from the ranges collection. Any suggestions would be appreciative?

2

Hi,

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

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.1.6
It should fix your problem.

If the problem still occurs, then please provide us your source xls/xlsx file so that we could test your code and report this issue in case we find it as a bug.

3

Hello, Thank you.

I downloaded the latest version and it did not work. I still have regions that have no corresponding cell. Now, I am sure that the team that creates the XLS files was doing something that worked in Excel interrop.

Now the code I am trying to replace from the Excel Interop assemblies works like this:
//field is the name of a region; missing is defined as (Object)Type.Missing
range = excelSheet.get_Range(field, missing);
range.Value2 = newvalue;

With Aspose, i was using GetNamedRanges as in my original post. I even created a new XLSX file (attahced), that even though the regions were defined, they did not have any cells assigned to it. Again, maybe I did something wrong, but range.GetCellOrNull(0,0) returns null!



4

Hi,


Well, I think you may try to change your code to:

foreach(var range in workbook.Worksheets.GetNamedRanges()) {
var cell = range[0,0];
if (cell == null) continue;

var style = cell.GetStyle();
if (style.IsLocked) {
style.IsLocked = false;
cell.SetStyle(style);

}
cell.PutValue(“testValue”);
}

I have tested it and it works fine.

Thank you.
5

Hi,

It seems to me a bug. I have tested it with the following code.

As you can see, all the cells in all the ranges should now have a value Tested but they are not, so it is a bug.

I have logged this issue in our database. We will look into this issue and once it is resolved or we have some other update relating to it, we will let you know asap.

This issue has been logged as CELLSNET-40718.

C#


string filePath = “F:\AlonsTestDocument.xlsx”;


Workbook workbook = new Workbook(filePath);


var ranges = workbook.Worksheets.GetNamedRanges();


//Iterate all cells of all ranges

foreach (Range range in workbook.Worksheets.GetNamedRanges())

{


IEnumerator allCells = range.GetEnumerator();


//Iterate all cells in this range and change their values

while (allCells.MoveNext())

{

Cell yourNeededCell = allCells.Current as Cell;


Debug.WriteLine(yourNeededCell.StringValue);


yourNeededCell.PutValue(“Tested”);

}

}


//Output your workbook

workbook.Save(filePath + “.out.xlsx”);



6

Awesome, thanks guys for your support.

In the meantime, my workaround was to …

var cells = workbook.Worksheets[0].Cells;
…
var cell = range.GetCellOrNull(range.RowCount - 1, range.ColumnCount - 1) ??
cells[range.FirstRow, range.FirstColumn];
…
I will see if the other method using the range is more efficient.

Thanks again!

Alon

7

Hi,

Thanks for your comments.

You can use this workaround for a time being. Once the issue is resolved, your older code should also work fine.

Also, I think, since you are accessing the cell using row index and column index, so it is efficient because cell will not be searched inside a collection and will be directly accessed.

If you find any other problem or get some questions, please feel free to post, we will help you asap.

8

Hi,

a - If an empty cell do not contains value and style, we will not create Aspose.Cells.Cell instance for performance. So Range.GetCellOrNull and Range.GetEnumerator() will skip those empty cells.

b - If you want to access all cells in the range, please use the following code:

C#


foreach (var range in workbook.Worksheets.GetNamedRanges())

{

for (int i = 0; i < range.RowCount; i++)

{

for (int j = 0; j < range.ColumnCount; j++)

{

var cell = range[i, j];

if (cell == null) continue;

if (cell.IsStyleSet)

{

var style = cell.GetStyle();

if (style.IsLocked)

{

style.IsLocked = false;

cell.SetStyle(style);

}

}

cell.PutValue(“testValue”);

}

}

}


c - If you want to set the same style and value to the cells in the range, please use the following simple code:

C#

Style style = workbook.CreateStyle();

style.IsLocked = false;

StyleFlag flag = new StyleFlag();

flag.Locked = true;

foreach (var range in workbook.Worksheets.GetNamedRanges())

{

range.ApplyStyle(style, flag);

range.Value = “testValue”;


}