Setting one worksheet cell range reference to another worksheet using aspose.cells

Aspose.Cells Product Family
1

I want to set one worksheet cell range reference to another worksheet using aspose.cells.For example i have two worksheet(wrk1 and wr2). I have some data on wrk1 and wrk2 is balnk.Now on wrk2 i want cells refrence of wrk1(like I want cell reference of wrk1 sheet A1:A10 on wrk2 with same style,format as on wrk1) .

Please suggest.

2

Hi,


I think you may try to use Range.Copy method to copy range of formatted cells to paste into other range. See a topic in this regard:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/copy-range-data-with-style.html

Thank you.

3

Amjad thanks for quick response.

The solution that you provied is partially meeting my requirement, i need that if i change source range then destination range should be changed.

Like in code(as per given link by you)

//Copy the range data with formatting.

range2.Copy(range);

rannge in directly coping into range. here source is range and destination is range2. so if i change any data/value in range then it should automatically change in range2.as in excel we are setting cellreference(B1='SheetName'!A1), this formula will set the B1 value similar to A1, when i change data in A1 , data in B2 will be changed automatically.

Please suggest, this is urgent.

4

Hi,


If MS Excel allows then it can be implementable. Could you create an Excel file containing your desired range and then implement it as your need, save the file and post it here, we will check it how to do it via Aspose.Cells API.

Thank you.
5

Not sure wheather we can do the same in excel or not. I will try it if find any excel soultion then i will let yu knnow.

Meanwhile can you please suggest some other alternate for this issue.

6

Hi,


Well, after some evaluation, I think there is no direct way to do it in MS Excel either. I think you may try to utilize some workaround (using/adding referenced formulas in the destination range) to do your task. See the sample code below for your help.


Sample code:

E.g

// Create a Workbook object
//Open an existing file.
Workbook workbook = new Workbook(“e:\test\book1.xls”);
// Get the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
//Add a worksheet to the workbook.
int index = workbook.Worksheets.Add();
Worksheet mysheet = workbook.Worksheets[index];
//Name the sheet.
mysheet.Name = “MySheet”;
// Create a named range of the Cells
Range range1 = worksheet.Cells.CreateRange(“A1”, “B2”);
//Create another range of cells in the new sheet.
Range range2 = mysheet.Cells.CreateRange(“A1”, “B2”);
// Copy the data with formatting of the range to the second range.
range2.Copy(range1);

//Apply formulas to each cell in cell in the destination range in
//accordance with cells values in the source range cells

for (int i = 0; i < range2.RowCount; i++)
{
for (int j = 0; j < range2.ColumnCount; j++)
{
range2[i, j].Formula = “=” + range1.Worksheet.Name +"!" + range1[i, j].Name;
}
}

workbook.Save(“e:\test2\output.xls”);