Hello,
I have some named ranges in an excel sheet and trying to make the cells in those ranges editable and the remaining cells in the sheet as read only. I tried to use the SetEditableRange() function but it doesn’t take the Range object. Is there any other function that I can use to make the cells in a named range only editable on the sheet.
@ashokComdyn,
Would you please try properties of Range such as SetEditableRange(Range.FirstRow, Range.FirstColumn, Range.RowCount, Range.ColumnCount)?
I think you are using Aspose.Cells.GridWeb control, correct us if we are wrong? Currently, there is no overload that accepts named Range as parameter for SetEditableRange method, so you have to set those 4 argument to specify the range to make it editable.
Thank you Johnson and Amjad, the proposed solution worked great. Only issue is that when I tried getting all the ranges from the workbook using the below code:
Workbook workbook = new Workbook(“c:\excel.xlsx”);
Range[] ranges = workbook.Worksheets.GetNamedRanges().Where(nr => nr.Name.StartsWith(“Edit”)).ToArray();
I am only getting the named ranges that have a value. Is there any way that all the named ranges with null or blank values can also be returned. Please see the screenshot.code returned.PNG (17.0 KB)
excel.PNG (11.3 KB)
From the screenshot of “code returned.PNG” we found you can get the list of ranges. We also created one template file with some named ranges, in those ranges there is no values for corresponding cells. The code can list all those ranges too. If you find in your case some ranges got lost from the list, please provide us the template file and the expected list so we can look into it for you.
excel file.zip (2.8 MB)
I just attached the excel file I’m using. Open the excel and go to Formulas and Name Manager to see the full list. I should get all the 24 Named Ranges in the excel.PNG screenshot that starts with “Edit”.
@ashokComdyn
There is no such method to get all the named ranges with null or blank values.
Please check whether the range is empty with the following codes:
private static bool IsBlank(Range r)
{
object v = r.Value;
if (v == null)
{
return true;
}
else
{
object[,] arr = v as object[,];
if(arr != null)
{
int m = arr.GetUpperBound(0) + 1;
int n = arr.GetLowerBound(1) + 1;
for (int i = 0; i < m; i++)
{
for (int j = 0; j < n; j++)
{
if (arr[i, j] != null)
{
string str = arr[i, j] as string;
if (str == null)
{
return false;
}else if(!string.IsNullOrEmpty(str))
{
return false;
}
}
}
}
}
else
{
return false;
}
}
return true;
}
We will add Range.IsBlank to check whether there is no data in the range.
Hello Simon. The issue is not with the values, the ranges are not getting pulled when I used GetNamedRanges() method. Only 3 named ranges were getting, and the common observation is all those 3 have some value in them and the remaining 21 named ranges that didn’t returned are blank or null values.
Most of the defined names contain multiple ranges in your template file and such kind of ranges are not suitable for WorksheetCollection.GetNamedRanges(). Please traverse defined names and get those ranges one by one. Code example:
foreach (Name n in workbook.Worksheets.Names)
{
if (!n.Text.StartsWith("Edit"))
{
continue;
}
Range[] ranges = n.GetRanges();
if (ranges != null)
{
Console.WriteLine(n.Text + ": total " + ranges.Length + " ranges:");
foreach (Range r in ranges)
{
Console.WriteLine(r);
}
}
else
{
Console.WriteLine(n.Text + ": no ranges:");
}
}
Moreover, to get named ranges starting with “Edit”, you may simply try the simplest lines of code:
Workbook workbook = new Workbook("g:\\test2\\excel file.xlsx");
Aspose.Cells.Name[] ranges = workbook.Worksheets.Names.Where(nr => nr.Text.StartsWith("Edit")).ToArray();
it will fetch all 24 ranges.
Please note, we have also supported to check whether the range is empty (we logged a ticket with an id “CELLSNET-54321” for it earlier). We have added Range.IsBlank attribute to confirm whether there is no data in the range. The enhancement will be included in our upcoming release version (Aspose.Cells v23.10) scheduled for release in the next week or so. We will let you know when the next release is published.
The issues you have found earlier (filed as CELLSNET-54321) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi