Range based on row and clumn

[piotrgrota]

Hello

I got empty Excel with Sheet1

I am trying to build range based on notation C1:R1

Using this function :

As pattern I am passsing

following string ‘Sheet1’!C1:R1 and workbook.

Got from range endRow: 1048575

And EndColumn: 16383

For one column such range?

private Range createR1C1PatternBasedRange(Workbook workbook, String R1C1pattern)
{
    try
    {
        int rangeIndex = workbook.getWorksheets().getNames().add(“dummyrange”);
        Name name = workbook.getWorksheets().getNames().get(rangeIndex);
        name.setR1C1RefersTo(R1C1pattern);
        name = workbook.getWorksheets().getNames().get(rangeIndex);
        return name.getRange();
    }
    catch (Exception e) {}
    return null;
}

[Amjad_Sahi]

Hi,

Please refer to the following sample code for your reference on how to create/ access Named range (with respect to R1C1 formula).

e.g

Sample code:

Workbook workbook = new Workbook();

//Specify the cells range: A1:B2

String R1C1pattern = “=‘Sheet1’!r1c1:r2c2”;

int rangeIndex = workbook.getWorksheets().getNames().add(“dummyrange”);

Name name = workbook.getWorksheets().getNames().get(rangeIndex);

name.setR1C1RefersTo(R1C1pattern);

name = workbook.getWorksheets().getNames().get(rangeIndex);

System.out.println(name.getRefersTo()); //=‘Sheet1’!$A$1:$B$2 - Ok

For further reference, see the document here:

http://www.aspose.com/docs/display/cellsjava/Named+Ranges

Hope, this helps a bit.

Thank you.

[piotrgrota]

Do you support notation C1:R1 exacly ?

I didn’t find int in link from you ?

How many notation are you supporting?

[Amjad_Sahi]

Hi,

Well, Aspose.Cells follows Ms Excel standards and specifications when creating named ranges. We do support “R1C1” formula notation and “A1” cell formula notations, we also do support creating named ranges in other ways as per my suggested document (for your reference). Could you provide a sample Excel file to demonstrate “C1:R1” notation, we will check it soon. You may create the named range in Ms Excel manually, save the Excel file and provide us here with all the details and steps involved (in MS Excel).

Thank you.

[piotrgrota]

I attached Sample Excel and steps what I am doing :

Passed to function :

createR1C1PatternBasedRange introduced in first post:

workbook and ranges:

1. exacly this range

 R1C1pattern: "Sheet1!C1:R1"
return structure:
StartRow:0

EndRow:1048575

StartColumn:0

EndColumn:16383

But for 2 example:
2. R1C1pattern: "Sheet2!C1:R1"
Return null.
How this is possible for first sheet working in this way and for second one working different.
Same range but different spreadsheet.
Regards,
Piotr

[Amjad_Sahi]

Hi,

Thanks for your template file.

Do you need to create range based on cells range “C1:R1” in the worksheet(e.g Sheet1)? I think you should not use “setR1C1RefersTo()” method, instead kindly use “setRefersTo()” method in your code. Could you create your desired named range(s) manually in MS Excel and save the file and post us here again. We will check it and suggest you the code to accomplish the task.

Thank you.

[piotrgrota]

setR1C1RefersTo

I am working with dynamic ranges mostly so I need to be able choose proper method to get range. But still strange for me even if setR1C1RefersTo is wrong method here.

Named ranges just works fine.

For dynamic ranges in Aspose you can work with :

setR1C1RefersTo is for format R1C1:R1C1
setRefersTo - but what is use for ?

createRange is for adress base range like A1:A7

[Amjad_Sahi]

Hi,

Please note:

1) setR1C1RefersTo() method is used to specify range but in “R1C1” style.

2) setRefersTo() method is used to specify range in “A1” style. e.g “A1:D5” etc.

3) createRange() method is used to to create range while specifying the address based range like “A1:A7”, you can also specify the integer values (using it’s overload) for starting row/col and ending row/col etc.

Hope, this helps a bit.

Thank you.