Working with named ranges

Jan 19, 2010 at 2:58 PM

How can I reference a named range instead of a GridReference?

 

Coordinator
Jan 19, 2010 at 3:10 PM

Sorry - there is currently no explicit support for named ranges (although it is on the priority list for a forthcoming release). Named ranges in a template are obviously supported and correctly reproduced in the output package, but there is no support within ExtremeML to access them in code. I suggest you subscribe to the blog at www.extrememl.com to be notified as soon as this feature is added. Alternatively, if you are inclined to hack the code yourself, I may be able to guide you.

Jan 19, 2010 at 3:48 PM

Is it possible to use the Open XML SDK to figure this out and combine ExtremeML and the SDK?

 

Coordinator
Jan 19, 2010 at 4:17 PM

ExtremeML is already built on top of the OpenXML SDK and very closely modelled on its internal architecture.

So, adding support for named ranges is nothing more than an evolutionary exercise, adding a little code to one or two of the existing wrapper classes to enable the named range to be referenced via a collection and then exposing some methods to enable it to be manipulated.

The complexity of this work (if you choose to tackle it yourself) will depend on what you want to do with the named range objects that you retrieve. A named range is conceptually similar to a table (in the sense that it spans a rectangular region) so some of the existing table manipulation concepts will be applicable here.

If you are happy to program directly against the underlying SDK classes, you can access all of them via their ExtremeML wrapper classes, so it will be possible to retrieve and manipulate the SDK class instance that represents your named range. However, be aware that your code will need to undertake all the housekeeping that is associated with any data manipulation, otherwise the operation will result in an invalid package.

Jan 19, 2010 at 6:19 PM

For the most part I just want to set a cell value to a single cell named range. Just so I don't have to know where the cell is located. 

For me, the ideal solution would be that the GridReference could accept a string as a parameter and that the could would find what cell reference that name points to. Then I would like to use the SetCellValue as you already support.

I'd be glad to help out with this, buy maybe you could give me a few tips on where to begin and where to find examples on how to do this. I have great knowledge in both C# and Excel VBA, but I've never worked with the Open XML format.

 

Coordinator
Jan 19, 2010 at 7:00 PM

Ok, I'll be happy to advise you. It might be easier if we communicate directly via email. I think you already have my email address (assuming you received my reply to your earlier message). If not, let me know and I'll send a new reply.

Jul 7, 2010 at 8:03 PM

Any progress on working with Named Ranges (defined names)?

First, I completely agree that it is more common to update standard templates than to create "new" spreadsheets, at least in our business environment.

Also, it is far more lileky that we have to interact with named ranges (single cell or not) than with Excel tables.

We have managed to use the SDK (a CTP version, we haven't yet installed the latest RTM version) to interact with our template but had to leave some work to be done with code in the spreadsheet. We encountered many issues along the way. Defined names that reference merged cells, sheet protection issues that result in a corrupt file, cell format issues that result in a corrupt file, text boxes for lengthy text input, etc. Our solution for adding rows to a range involves writing data to an input/output area and letting code in the spreadsheet adjust the actual range dimensions (and copy the data to the actual data entry areas) in the open and close events. Anyway, it works now but it would be great if ExtremeML could handle a lot of this. At this point, I don't know if we will ever have a template that uses Excel tables.

We would be very interested in enhancements in this area!

Thanks,

Glenn G.

Coordinator
Jul 7, 2010 at 8:39 PM
Hi Glenn, Thanks for your comments. In fact you are the second poster today to request progress in this area, so it is clearly a hot topic. For what it's worth, I acknowledge that this is a requirement that deserves some attention but, unfortunately, there are only so many hours in the day (I am still at the office and it is 10:30PM here!) and I am finding it hard to devote the time needed to tackle the various outstanding tasks on ExtremeML. When I started this project, I was kinda hoping there would be some help along the way, but it seems that perhaps the architecture of the library is too complex for most people to want to delve into it, so I am alone in trying to progress it. And, at the moment, I am too busy trying to earn a living to find even a few hours a week to work on it. So, unfortunately, I can't promise to meet your expectations any time soon (although it pains me to admit it). I hope you will continue to stay interested in ExtremeML and that I might find some time to advance it in the near future. All the best, Tim