I have a need to copy a rather complex range function in my output report. So I actually added the range into an xltm file and use aspose to generate excel files from the template.
My problem are 2.
Problem 1: On generating the .xlsx file, I cannot locate the named range using the API sheet.Workbook.Worksheets.GetRangeByName(“TotalProfitByLineItem”)
- BUT, it is indeed present in the xlsx file. Because if I open the file in Microsoft excel, I can clearly see my named range in the xlsx file. How can I get hold of that range using the API? IS there any other API I could use?
Problem 2: Using MS Excel, I find that the links in this named range have become External Links. How can I keep them internal?
[This second issue is a lower priority because it might be happening as I delete and add worksheets in the program]
1) Your named range in your template file might be defined Names (NameCollection --> in Name Manager in MS Excel), you should use the following sample code to obtain names with its criteria for your requirement:
Sample code:
Workbook book = new Workbook(“e:\test2\Offset+range+example.xlsx”);
WorksheetCollection sheets = book.Worksheets;
NameCollection ranges = sheets.Names;
MessageBox.Show(ranges.Count.ToString());
for(int i =0; i<ranges.Count; i++)
{
MessageBox.Show(ranges[i].Text);
MessageBox.Show(ranges[i].RefersTo);
}
If you still find any issue, give us your template file with sample code, we will check your issue soon.
2) Aspose.Cells works fine in accordance with MS Excel. If you find it works in opposite, let us know with sample code, files and screen shots, we will check it soon. To change the area/criteria of the defined Names, please use Name.RefersTo attribute.