Hi,
We greatly appreciate the support, you guys provide and listen to the client requests and take that little extra effort to fulfil the request.
Few of our clients, use Named Range in to their workbook and some advanced users use formulated Named Range.
Recently, we noticed that ASPOSE had issue reading few Named ranges. We found that when Name range uses Formula, thats when ASPOSE is unable to read the named range.
Please find attached workbook, it has named range OffSetRangeName
To extract the range, I use following code:
Workbook wb = Workbook.Open(PathToWorkbook);
Range myRange = wb.Worksheets.GetRangeByName("OffSetRangeName")
Please let me know, If this issue is fixable in hot fix or require major release?
Thanks and regards,
Bhavin
Hi,
Thanks for the sample file.
Your named range in your template file is defined Names (NameCollection --> in Name Manager in MS Excel), you should use the following sample code 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);
}
Thank you.
Hi Amjad,
Thanks for the reply,
I have tried the code in your last reply. It just gives me =OFFSET(Sheet1!$A$2,0,0,10,2) that for OffSetRangeName named range.
My question was, How do I refer to the range represented by =OFFSET(Sheet1!$A$2,0,0,10,2) using ASPOSE?
Is there a way in ASPOSE that will evaluate =OFFSET(Sheet1!$A$2,0,0,10,2) and return me the Range object that points to cell “A2:B11” in worksheet “Sheet1”?
I apologize, If I was not clear enough in my previous post.
Thanks again,
Looking forward to reply,
Bhavin
Hi,
Well, I think there is no direct way to do so since you are using Offset function for “RefersTo” of the defined named range. You have to use your own code to accomplish your task. I have used some agricultural way to perform your task, you may update or add remove my sample code to enhance it more accordingly (if possible).
Sample code:
Workbook wb = new Workbook(“e:\test2\Offset+range+example.xlsx”);
MessageBox.Show(wb.Worksheets.Names.Count.ToString());
wb.CalculateFormula();
Name name = wb.Worksheets.Names[“OffSetRangeName”];
MessageBox.Show(name.RefersTo);
Range[] ranges = name.GetRanges();
int frow, fcol;
int rowcount, colcount;
String mainref = name.RefersTo;
//Extracting last parameter for rows count
int lastcommaind = mainref.LastIndexOf(’,’);
int strindx = lastcommaind - 2;
String strnumrow = mainref.Substring(strindx, lastcommaind - strindx);
//Extracting last parameter for columns count
int lastbracketind = mainref.LastIndexOf(’)’);
String strnumcol = mainref.Substring(lastcommaind +1,lastbracketind -lastcommaind-1);
if (ranges != null)
{
for (int i = 0; i < ranges.Length; i++)
{
frow = ranges[i].FirstRow;
fcol = ranges[i].FirstColumn;
string f1 = CellsHelper.CellIndexToName(frow, fcol);
MessageBox.Show(ranges[i].FirstRow + “:” + ranges[i].FirstColumn);
rowcount = frow + Convert.ToInt16(strnumrow) - 1;
colcount = fcol + Convert.ToInt16(strnumcol) -1;
string f2 = CellsHelper.CellIndexToName(rowcount, colcount);
MessageBox.Show(f1 + “:” + f2); //A2:B11 - OK
}
}
Thank you.
Hi,
And we will soon support dynamic range in the workbook.Worksheets.GetRangeByName() method for your needs.
Thank you.
Hi Amjad,
Thanks for your reply, I appreciate your attitude of taking initiative and resolve the issue as soon as possible.
Our solution architecture is such that we choose not to "selectively apply code patch" for OFFSET function, we would rather choose to use the function you suggested "workbook.Worksheets.GetRangeByName()"
I was wondering, how are you going with support for dynamic range?
Can I have any ETA on that please?
Thanks,
Bhavin
Hi,
Thanks for your comments. We understand your needs. We will let you know the ETA asap.
Also, if we need any further information from your side, we will ask you.