We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

How to get named range TableRange using WorksheetCollection.GetRangeByName in .NET

Aspose.Cells Product Family
, ,
#1

Hi,
I am trying to get a range by its name but it returns nulll. The issue is not with all the excel sheets but with the attached excel sheet, I have checked the sheet and it defines the ranges properly and it works fine with the excelInterop library in the code. Not sure why its an issue with aspose.

xyz_upload123.zip (44.1 KB)

static void Main(string[] args)
{
    var templatePath = @"C:\Temp\xyz_upload123.xlsx";
    var fs = File.Open(templatePath, FileMode.Open);
    var fileName = fs.Name.Split('\\')[fs.Name.Split('\\').Length - 1];
    var workbook = new Workbook(fs);
    workbook.FileName = fileName;
    var renderedSheet = workbook.Worksheets["Rendering"];
    var tableRange = renderedSheet.Workbook.Worksheets.GetRangeByName("TableRange");
    renderedSheet.Cells.ClearContents(new CellArea
    {
        StartRow = tableRange.FirstRow,
        EndRow = tableRange.FirstRow + tableRange.RowCount - 1,
        StartColumn = tableRange.FirstColumn,
        EndColumn = tableRange.FirstColumn + tableRange.ColumnCount - 1
    });
    fs.Close();
}

Could you please help ?

Thanks
Shobhit

#2

@hi.shobhit82,
I have opened the Excel file in MS Excel but could not observe the named range “TableRange” as shown in the following image. Could you please verify if the target named range exists in the workbook or not?

#3

Please go to the tab “Formulas” --> then click on “Name Manager” (in the section defined names).You will find the list of all the ranges present within the excel sheet. Refer image below.

Capture.PNG (42.3 KB)

Also, you can use the API method to fetch all the names ranges present in the excel via the code, but it gives all the ranges all at once, i am surprised why names range method doesn’t work.

var allRanges = renderedSheet.Workbook.Worksheets.GetNamedRanges();
#4

@hi.shobhit82,
Thank you for providing more details. This issue is reproduced here and logged in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-48546 - GetRangeByName returning null

#5

@hi.shobhit82,
This defined named range belongs to the worksheet “Rendering”, so please change codes as the following:

var tableRange = renderedSheet.Workbook.Worksheets.GetRangeByName("Rendering!TableRange");
#6

But aren’t the named ranges valid for a workbook and we can’t have a range with the same name within a workbook ?

Also, what I don’t understand is that I why the same code of getting the named range does not fail with another workbook (attached) where the range is again present in another worksheet.

Book1.zip (11.9 KB)

static void Main(string[] args)
{
    var templatePath = @"C:\Temp\book1.xlsx";
    var fs = File.Open(templatePath, FileMode.Open);
    var fileName = fs.Name.Split('\\')[fs.Name.Split('\\').Length - 1];
    var workbook = new Workbook(fs);
    workbook.FileName = fileName;
    var renderedSheet = workbook.Worksheets["Rendering"];
    var tableRange = renderedSheet.Workbook.Worksheets.GetRangeByName("TableRange");
    renderedSheet.Cells.ClearContents(new CellArea
    {
        StartRow = tableRange.FirstRow,
        EndRow = tableRange.FirstRow + tableRange.RowCount - 1,
        StartColumn = tableRange.FirstColumn,
        EndColumn = tableRange.FirstColumn + tableRange.ColumnCount - 1
    });
    fs.Close();
}
#7

@hi.shobhit82,

It has a reason. Book1.xlsx has “TableRange” range whose scope is “Workbook” level. Whereas, your previous file (you attached in the first post) has “TableRange” that has “Worksheet” level scope, so you have to access it with respect it its worksheet (“Rendering” in your case). See the screenshots of both files taken in MS Excel when opening into MS Excel manually.
sc_shot1.png (68.7 KB)
sc_shot2.png (70.9 KB)

#8

Thanks a lot. This has solved my issue.

#9

@hi.shobhit82,

You are welcome and good to know that your issue is sorted out now. In the event of further queries or issue, feel free to write us back.