Range and Offset

Aspose.Cells Product Family
1

I have two questions:
1) How can I retrieve a cell that I have defined a range name for?

2) If I have a cell object, how can I get the cell that is i columns and j rows away from it? An offset method like below is what I am looking for:
myCell = cell.offset(i,j)

What I am trying to do is to retrieve a cell that I have defined a range name and loop through all the cells i rows, j columns from it and set these cells to some value.

Thanks.

2

Hi,

Well, You can identify and insert data into the individual cells of a range following the pattern. i.e., Range[row,column] (C#) / Range(row,column)(VB). Suppose, you have a named range of cells.i.e., A1:C4. So the matrix would make 4 * 3 = 12 cells and the individual range cells are arranged sequentially i.e., Range[0,0],Range[0,1],Range[0,2],Range[1,0],Range[1,1],Range[1,2],Range[2,0],Range[2,1],Range[2,2],Range[3,0],Range[3,1],Range[3,2].

May the following sample code help you for your requirement.

Sample code:

//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get the first worksheet in the workbook.
Worksheet worksheet1 = workbook.Worksheets[0];
//Create a range of cells based on H1:J4.
Range range = worksheet1.Cells.CreateRange("H1", "J4");
//Name the range.
range.Name = "MyRange";
//Identify range cells.
int firstrow = range.FirstRow;
int firstcol = range.FirstColumn;
int trows = range.RowCount;
int tcols = range.ColumnCount;
//Input some data into cells in the range.

for (int i = 0; i < trows; i++)
{
for (int j = 0; j < tcols; j++)
{

range[i, j].PutValue(i.ToString() + "," +j.ToString());

}

}
//Save the excel file.
workbook.Save("f:\\test\\namrangecells.xls");

For further reference, please check the following documenation topic:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/named-ranges.html

Thank you.

3

Thanks for your reply.

I cannot define the range in the code. It is already defined in the spreadsheet.
i.e., I cannot have this statement,
Range range = worksheet1.Cells.CreateRange(“H1”, “J4”);
Is there a way to retrieve the range (that is already defined in the worksheet) by passing the range name as a paramter?
It looks like I have to call Cells.Ranges() to get the collection of ranges and compare the name of each one to get the one I want. Can you confirm this?

I think it would be nice if Aspose provides an offset method to retrieve a cell relative to the current cell:
cell.Offset(i, j)

NOTE: programmers typically define the range in the worksheet so to avoid hardcoded definitions in the code. That is why I have the range defined in the worksheet. If the range changes, one just update the spreadsheet without touching the code because the range name remains the same.

Thanks.

4

Hi,

Thanks for considering Aspose.

Is there a way to retrieve the range (that is already defined in the worksheet) by passing the range name as a paramter?

Well, you may use Worksheets.GetRangeByName(string rangename) method for your task.

e.g..,

Range myRange = workbook.Worksheets.GetRangeByName("MyRange");

For getting offset value from a cell, I think you may try to use OFFSET worksheet formula as Aspose.Cells for .NET supports to calculate the the formula.

e.g..,

..........
sheet.Cells["F5"].PutValue(34);
sheet.Cells["D3"].Formula = "=OFFSET(C3,2,3,1,1)";
workbook.CalculateFormula();
string cellval = sheet.Cells["D3"].StringValue;

Thank you.

5

GetRangeByName was the method I was looking for. Thanks very much. That was helpful.