Am I using NamedRanges correctly?

Aug 26, 2011 at 8:29 AM

Hello!

I am trying to create an export that can either be used by filling a template or without an template. If there is no template, I create a default template an fill that. Every template has a NamedRange called TemplateRow that defines the row where data is supposed to be inserted. Adding a NamedRange seems to work, but when I try to fetch the Ranges from a NamedRange I get a "the sequence is empty" exception. Here's what I do:

 

var newTestRange = wb.NamedRanges.Add("TestRange", ws.Range(ws.Cell(1, 1), ws.Cell(1, 7)));
var nR = wb.NamedRange("TestRange");
var range = nR.Ranges.Single()

The last line throws an InvalidOperationException. Looking at the code it seems that in XLNamedRange.cs the Ranges property getter does some filtering-grouping-regex magic I don't quite understand which causes my single range not to be added. In the debugger I can see that the internal list (_rangeList) contains the item I want.

Any Hints? Thank you for your support!

Philipp

Aug 26, 2011 at 11:14 AM

Update: I am now using this approach, which works for me:

var newRange = ws.Range(ws.Cell(1, 1), ws.Cell(1, 7));
newRange.AddToNamed("TestRange");
var nR = wb.Range("TestRange");

I'd still be interested though what is wrong with the code above. Thanks for the great library!

Philipp

Coordinator
Aug 26, 2011 at 1:05 PM

It was a bug when adding a range (not a string) through the NamedRanges collection directly. I just fixed it in the last check-in.

By the way I would write your code in the following way (less typing):

            ws.Range(1, 1, 1, 7).AddToNamed("TestRange");
            var nR = wb.Range("TestRange");
Thanks for the feedback,

Aug 26, 2011 at 1:07 PM

great, thanks! :) (as others mentioned already, your support / response time is stellar..)

Sep 3, 2012 at 1:04 PM

Greetings,

I just have one question related to Named Ranged so I decided not to make a new topic and use this one.

 

Is it possible to extend named range that cointans only:

 - 1 cell (to have many columns and rows)?

 - 1 row (to have more rows)?

 

Kind Regards

PKret

Coordinator
Sep 3, 2012 at 3:49 PM

Just keep adding the cells/ranges to the named range:

var wb = new XLWorkbook();
var ws = wb.AddWorksheet("Sheet1");
 
ws.Cell("A1").AddToNamed("Test");
ws.Cell("A2").AddToNamed("Test");
ws.Range("B1:B2").AddToNamed("Test");
Oct 2, 2015 at 5:15 PM
Maybe too old but I have the following problem.

When I use AddToNamed i get the folloing =Source!$A$1:$E$1,Source!$A$2:$E$63. This wrecks my pivot table data since it is based on the Source Named Range. How can I do it so that after using ws.Range("$A$2:$E$63).AddToNamed("Source") I actually get Source!$A$1:$E$63 only and not the comma separated name range