Create Range based on address

Aspose.Cells Product Family
1

How can I get a range object based on a range address such as…

$A1:$B5

$9:$9

$B$10:$G$20

2

Hi,

Well, there is no such api available for the task, you should use your own code to get/create a named range, see the sample code below:

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;

string rangeCrt = “$A1:$B5”;
string fRangeCrt = rangeCrt.Replace("$", “”);
MessageBox.Show(fRangeCrt);

int index = fRangeCrt.IndexOf(’:’);
string r1 = fRangeCrt.Substring(0, fRangeCrt.Length - index - 1);
string r2 = fRangeCrt.Substring(index +1);

Range range = cells.CreateRange(r1, r2);



Thank you.

3

Thanks.

That works in some cases but not all.

What if my range address is entire rows or columns such as

$9:$9
$C:$C.

The above code does not work properly.

In Excel, to do what I want all I have to write is the following VBA

Dim range As Range

Set range = Sheet1.Range("$A1:$B10)

No need to parse the address and check for colon’s (:slight_smile: or figure out if the address is an entire row or column reference. Excel just figures it out.

Can something equivalent be written for Aspose.Cells? Can a new overload be added for the CreateRange method?


4

Hi,

We have logged your feature request into our issue tracking system with an issue id: CELLSNET-16404. One of our developer would analyze it soon.

Thank you.

5

Hi,

For "What if my range address is entire rows or columns such as

$9:$9
$C:$C.
"

If you want to create a range for the whole row or column, please use CreateRange(int firstIndex, int number, bool isVertical) overloaded version of the method.

And, we will continue to look into how to create a range with a string address for your need.


Thank you.

6

Hi,

Please try the attached version.

We have supported this feature now. Please see the following sample code:

Range range = cells.CreateRange(“A1:A10”);

Thank you.

7

Thank you for the quick turn around for this feature.

I think it almost works. The problem is that the row index is incorrect and off by 1.

For example, I have a range address of $9:$9. A range object is returned however the value of FirstRow is 9 when it should be 8 since the FirstRow property is 0 based.

FirstColumn appears to be correct when I have an address of $C:$C.

Thanks

8

Hi,

We found the issue regarding the range address in the format i.e. $9:$9

We will fix it soon.

Thank you.

9

Hi,

Please try the attached version. We have fixed the issue now.

Thank you.

10

Thanks.


The fix appears to be working.