Simple Named Cells- surely I can do this!

Aspose.Cells Product Family
1

I have been playing with Aspose.Excel all morning and can’t seem to do what I want.

I have a requirement to allow users to create whatever spreadsheets they like, in whatever layout they like but they have to finally generate some cells with defined names.

Let’s say

Result1
Result2
Result3

I then want to upload this spreadhsheet through a website, load the file into Aspose, extract those 3 values by name and insert them into a database.

I keep reading that “Named Ranges” aren’t supported but some of these posts are ages old and I was hoping that this was implemented by now.

Is it just me but this seems like a trivial thing to want to do, does this component allow me to do this?

If I can’t do this is there another component I could get?

thanks

2

Hi,

Currently Aspose.Excel supports “Named Ranges” but doesn’t support to extract value by defined name. If this feature is important to you, I will implement within one week. Can that meet your need?

3

Indeed, that would meet my needs, I have a few weeks to get this working.

When you say it supports Named Ranges, what do you mean? If my import has three named ranges is there any way I can get these names and convert to cell references?

thanks

Cliff

4

Hi Cliff,

There are two approaches to meet your needs.

The simple way is: I can supply a GetRangeByName method to enable you to get a range then you can get cell references.
Another way is: I can suppy a collection which includes these names. You can iterate in the collection to find your expected name then it can be converted to cell references.

Which one is better for you?

5

Either way is fine I think.

I assume a Range returns the first Column, first Row and then a column and row count so that it can handle ranges of multiple cells. I haven’t looked to be honest. For my purposes they will always be single cells rather than ranges.

The first one seems quite simple to use.


Cliff

6

Ok. I will implement the first one. A range object will be returned. And I will add methods in Range to create cell references. It will meet your need as well as other users’ need on a range of cells.

It will be availabe at the start of next week. Thanks for your patience.

7

Please download the latest hotfix.



You can use Worksheets.GetRangeByName method to get the range object then use indexer of Range object to get the cell.

[C#]
Range range = worksheets.GetRangeByName(“Result1”);
Cell cell = range[0, 0];

[VB]
Dim range as Range = worksheets.GetRangeByName(“Result1”)
Dim cell as Cell = range(0, 0)

8

Quick question.

How do I detect whether I have asked for a range that does not exist?

I am basically going to iterate through a collection of names that I have and I am going to attempt to get a matching name. I will obviously ask for ones that do not exist and I need to be able to check that. Does the Range have a flag or something?

While I think about it is the GetRangeByName() case sensitive?

thanks

Cliff

9

I might be being a bit dim here but I have created an Excel2003 spreadsheet with a single populated cell (B4). I have named the cell “Fred” and have saved the file as c:\Fred.xls.

I’ve then used the following code

Excel x = new Excel();
x.Open(“c:\fred.xls”);
Range range = x.Worksheets.GetRangeByName(“Fred”);
Cell cell = range[0, 0];

but the 3rd line call to GetRangeByName() throws an NullReferenceException. I have used exactly the same case “Fred” in the spreadsheet and the call.

Would you like a copy of the Excel file emailing?

thnx

Cliff

10

Hi Cliff,
Please re-download the latest hotfix and have a try.

11

That seems to be working better.

On my first question is the only way for me to detect if the NAME was not found is to wrap the whole thing in a try…catch and catch the NullException?

Cliff

12

Hi Cliff,

Sorry, I forgot to answer your first question. You don’t need to try…catch the exception. It will slow down your program. You can just detect if the returned range object is null.

Range range = worksheets.GetRangeByName(“Fred”);
if(range != null)
{
Cell cell = range[0,0];
}

13

I’m still working through the component, so apologies if there is a documented method for this.

I have a similar need to Charker. I’d like the end-user to be able to upload an excel document that we’ll parse through for the named ranges. I’d then present the named ranges to the user and allow them to assign a data source to each of them. Once they’ve assigned data sources to the ranges, we’ll insert the data into the defined range.

If there is a different way to do this, point me in the right direction.

Thanks!

14

Hi Russell,

I don’t clearly understand your need.

1. Your end-user will upload an excel document and you will parse from the named ranges.
Yes. You can do it by calling the GetRangeByName method.

2. You want to present the named ranges to the user.
What do you mean “present the named ranges to the user”?

3. Your user will assigned data sources to the ranges.
Which kind of data source? How does your user assign them? In the file, or in your web page?

15

Sorry for the confusion.

On point one - if I understand GetRangeByName, it requires you to know the named range. In the senario I have, I won’t have the names. I want to be able to open an Excel sheet and find all the named ranges in the file programmatically.

The other two items I feel pretty comfortable with.

Thanks.

16

To meet your need, I will provide a new method:

public Range[] GetNamedRanges()

Is it enough?

17

Sounds great - will definately meet my needs. Thanks for your help in this!

18

Hi Russell,

Now it’s available. Please download the latest hotfix

and refer to Worksheets.GetNamedRanges method.