We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Range and Offset

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.

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.

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.

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.

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