|
Scott
Guest
|
Hi,
We are trying to add code to support checking for sheet-scoped name ranges first (if they exist), before using the workbook scopes.
Our product historically only uses/searches workbook-scoped names.
Trouble is, the api doesn't have a "containsName()" type method, everything online just suggests to "try" it and if it's not found it'll throw an exception.
Doing it with exceptions will add many unnecessary exceptions which could be bad for performance, but are also very annoying for debugging. This is the 10% case we are looking to cover, and would love to not have it affect the 90%.
Also didn't really want to crawl through all the names and do a string compare, as we have 1000s of names - fewer sheet-scoped ones, but people do still use a lot of them for other things.
Here is the code we initially thought to use:
public static Name _GetNameByStringKey_TrySheet(this Workbook workbook, string name, string sheet)
{
Worksheet ws = null;
try
{
if (sheet != null)
{
ws = workbook._GetWorksheet(sheet);
var sheetScopedName = String.Format("'{0}'!{1}", sheet, name);
if (ws != null)
{
try
{
if (ws.Names.Count > 0) //only try if there are any -- try fully-sheet-scoped name to see if it exists
{
return workbook.Names().Item(sheetScopedName);
}
}
catch { } //do nothing, continue on to wb names
}
}
return workbook.Names().Item(name);
}
catch
{
return null;
}
finally
{
if (ws != null)
Marshal.ReleaseComObject(ws);
}
}
I tried several other things, none of which worked...
Tried seeing if I could get a null range returned from one of these when passing in "Sheet1!Name1", before trying to get the name...
// still throws exception if not found..
Range r = G.GetExcel().get_Range(sheetScopedName);
// finds workbook scoped range even if there's no sheet-scoped one
Range b = G.GetExcel().Evaluate(sheetScopedName) as Range;
Range r = specificSheet.Evaluate(sheetScopedName) as Range;
//these even doesn't fail -- will find wb-scoped version if no sheet-scoped version
Range r = otherSheet.Evaluate(name) as Range;
Range r2 = otherSheet.Evaluate(sheetScopedName) as Range;
Any other tricks you might know to get around the "Exception" method of this would be great... |
|
|
Andrei Smolin
Add-in Express team
Posts: 18821
Joined: 2006-05-11
|
Hello Scott,
Scott writes:
Also didn't really want to crawl through all the names and do a string compare, as we have 1000s of names - fewer sheet-scoped ones, but people do still use a lot of them for other things.
Still, I suppose this will work fast enough: scan the Worksheet.Names collection, retrieve each Name.Name value to a string array, and compare a set of names against the array. What do you think?
Andrei Smolin
Add-in Express Team Leader |
|
|
Scott
Guest
|
That's not really feasible. Many of our templates have 6-10K named ranges (it's how we organize everything in our product). We already have to go through them and cache stuff several times for speed's sake.
And the methods that require this particular lookup, we have the name we're looking for (reasonably certain it exists as a workbook name, but want to check for a sheet-scoped version to take priority) and are trying to avoid any slowdowns in processing. The fact that Excel has (seemingly) direct lookup based on name is great, but the exceptions thrown when you "miss" is terrible.
We can look to cache this stuff as well, but was hoping for an easier trick to figure out where to look for the name.
- Scott |
|
|
Andrei Smolin
Add-in Express team
Posts: 18821
Joined: 2006-05-11
|
Hello Scott,
You can add an XLL module to your add-in project and check if the function below producing the list of names allows you to work any faster:
public static object xlfNames(string workbookName, string sheetName ) {
object res = Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Names, "[" + workbookName + "]" + sheetName);
return res;
}
You can call this function using ExcelApp.Evaluate() and passing it this string: =xlfNames("Book1", "Sheet1"). The result is an array containing the names of sheet-level names defined on the specified worksheet.
Using an XLL module has several downsides. One of them is: your add-in must be per-user, not per-machine.
Andrei Smolin
Add-in Express Team Leader |
|
|
Scott
Guest
|
That's interesting.
Is that faster than thisWorksheet.Names() iterating? We also weren't sure if worksheet.Names() was just calling workbook.Names() and filtering (thus slower) or actually more efficient
Does it return the strings or the actual name objects? |
|
|
Andrei Smolin
Add-in Express team
Posts: 18821
Joined: 2006-05-11
|
|
Scott writes:
Is that faster than thisWorksheet.Names() iterating?
I can't tell.
Scott writes:
Does it return the strings or the actual name objects?
It returns strings. This is an XLL: it can't return objects from the Excel object model.
When the code is ready, register your add-in from the IDE; this is required to get the XLL registered.
Andrei Smolin
Add-in Express Team Leader |
|
Forums
Topics
Search
Help
Login
Register