I need to create and then use named ranges with formulas. I am using workbook.insertName(String, String) to create named ranges using information imported from an external source.
Opening the resulting workbook in Excel shows that it contains the named range as expected, but from within Aspose, I am unable to find the named ranges that I have created with this method.
Hi,
Thanks for considering Aspose.
Well, you can use Cells.CreateNamedRange() method for creating named ranges in the Workbook. For retrieving a named range you may use Worksheets.getRangeByName() method. And both method returns NamedRange object.
For further reference, please check:
Thank you.
I apologise, but I did not provide enough information in my original post.
I am involved in a project that aims to replace a COM based interface with Aspose.
I cannot use cells.createNamedRange because these methods only allow cell ranges to be used and I need to be able to use formulas (IE named range page_total = sum(a5:a17)). These formulas are user generated and I cannot enforce that they only refer to cell ranges, or that they are even limited to a single worksheet.
Using the worksheets.insertName method, it seems that I can create named ranges with formulas just as I need, but the problem is that these named ranges cannot be accessed by the worksheets.getRangeByName or worksheets.getNamedRanges methods.
If I open the generated workbook in Excel, the named ranges are all present and correct but even re-opening this workbook in Aspose does not make them available to the getRangeByName or getNamedRanges methods.
A partial solution seems to be to try and determine which references can be handled by the cells.createNameRange method, but this is not a preferred solution.
Any advice would be greatly appreciated
Hi,
The defined name with formula is not a NamedRange object so you can’t get it by getNamedRanges() or getRangeByName(String).
Now we supply a “Name” class to represent defined name object. This class is the super class of NamedRange. And there are two new method added for Worksheets object to get all defined names, including NamedRange and Name object: getName(String) and getNames(). Please try this fix with the new class and methods for your situation.
Thanks for clarifying the difference in how these are handled by Aspose. That makes more sense now.
The new methods look to work correctly as I can now use getNames to list all defined names, including those with formulas.
One observation is that the Name object does not seem to provide access to the worksheet that the name is defined for? At least, Eclipse does not list any method that would look to provide this functionality.
I also have another question: Do I still need to try and identify whether the incoming data is a formula or range and use the appropriate function or will insertName do this for me?
Regards,
Richard
Hi Richard,
Thanks for considering Aspose.
Well, Name objects are global objects, you may use Worksheets.getName()/getNames methods to obtain the Name object(s) which is not conventional NamedRange and it may also refers to formulas. You may utilize the Name object for extracting names which was created using the code like...... Workbook.InsertName(String name, String refersTo) method.
E,g.,
Workbook workbook = new Workbook();
workbook.open("E:\\Files\\bk_data.xls");
Worksheets worksheets = workbook.getWorksheets();
//This line of code is to get the Name object which was created
//using... i.e., workbook.insertName("testrange","=SUM(2+2)");
Name name = worksheets.getName("testrange");
System.out.println(name.getText());
System.out.println(name.getRefersTo());
.
.
.
Thank you.
Thanks for the feedback but unfortunately for me name objects are not always global in Excel. They may be made sheet specific by including the sheetname prefix. IE "!"
If I use workbook.insertName(“sheet1!subTotal”, “=sum(F5:F15)”), then this is a sheet specific name.
When I read the names back via workbook.getNames(), the sheet prefix has been stripped off and there does not seem to be any method that will return it. The Name.getText() method only returns “subTotal” as expected.
Since this functionality is already being used in the existing COM based interface, I need to replicate it in the new replacement.
Your help is greatly appreciated.
Hi,
We will study your requirement and may consider it for implementation.
We will get back to you soon.
Thank you.
Hi Richard,
Please try this attached patch. A method “getWorksheet()” has been added for the “Name” class, by it you can get the worksheet that this Name object being defined for. If the returned value is null, that means the Name object is global.
And for your another question, the “insertName()” method automatically determine to create a Name or NamedRange object so long as the second parameter “referTo” is in proper format
Thankyou very much for the update.
I can confirm that the getWorksheet() method is workingand insertName is indeed generating Name or NamedRange objects depending upon the refersTo parameter.
Unfortunately a new bug seems to have been created by these patches. The Workbook.calculateFormula() method is now raising a NullPointerException.
The stack trace is;
java.lang.NullPointerException
at com.aspose.cells.Name.d(Unknown Source)
at com.aspose.cells.v.b(Unknown Source)
at com.aspose.cells.v.a(Unknown Source)
at com.aspose.cells.Cell.c(Unknown Source)
at com.aspose.cells.Worksheet.a(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
at com.aspose.cells.Workbook.calculateFormula(Unknown Source)
Hi Richard,
Would you please post your template file here? That will help us to figure out the issue soon. Thank you.
I have done a little more research and I believe that I have tracked it down.
The problem is not in my template, but with a name/namedrange reference that I am creating.
After creating a named range using workbooks.insertName(“Sheet1!test”,"=Sheet1!$F$8"), I also set the formula for a cell on Sheet1 as “=test”. In Excel this works and Excel correctly resolves this to “Sheet1!test”, but in Aspose it is causing the NullPointerException.
The reason that I am using workbook.caclulateFormula() is an attempt to update the cells value rather than have it showing “#NAME?”. Otherwise the workaround is to load the workbook into excel and manually update the cell.
Hopefully this will help you to analyse and resolve the issue.
Thanks
Hi,
We will figure out you issue soon.
Thank you.
Hi Richard,
For your situation, please set the formula to “Sheet1!test” for the cell. Currently if without worksheet specified, we take the name as global, but we will look into the function that search Named object defined for both worksheet and global.