Apply FormulaR1C1 over a range of cells in .NET

Aspose.Cells Product Family
1

Received : 2007/11/14 13:56:57
Message : Is there a way to apply a formula to a range of cells?

C# equivilent to:
ExcelSheet.get_Range("B6", "M6").FormulaR1C1 = "=some_formula";

Thanks,
Jim

This message was posted using Aspose.Live 2 Forum
2

Hi Jim,

We do not support this feature.Please try the following codes:

Workbook workbook = new Workbook();

Range range = workbook.Worksheets[0].Cells.CreateRange("B1", "C1");
for (int i = 0; i < range.RowCount; i++)
{
for (int j = 0; j < range.ColumnCount; j++)
{
range[i, j].R1C1Formula = "=A1";
}
}

workbook.Save(@"F:\FileTemp\dest.xls");

3

Thanks for the quick response Warren!

I worked out something similar to what you provided like so...

workSheet.Cells[i, j].R1C1Formula = "=A1";

Is working with the range more efficient?

Any chance adding the ability to provide a formula to a range in the future?

Thanks again!

Jim

4

Hi Jim,

Thanks for your proposal,

We may consider it after checking its feasibility.

Thank you.

5

Hi Jim,

Sorry for my mistake. The R1C1 refrence style formula should not be "=A1";Please change it:

workSheet.Cells[i, j].R1C1Formula = "=R[1]C[1]";

Assigning the formula to each cell of the range is not efficient.

I still not know what do you want to set.In Ms Excel, there are two methods to assign the formula to a range.

1. copy a formula =>select a range =>past it to the range.

2,select a range =>input the formula followed by "fx" in the toolbar =>press the keys :ctrl + shift + enter together.

If you want to do as 1, please use Cell.SetSharedFormula("=A1", 2, 1);

If you want to do as 2,.Please use Cell.SetSharedFormula("=A1:A3",3, 1);

If you want to use the upper method and R1C1 reference style formula,please use CellsHelper.ConvertR1C1FormulaToA1(string r1c1Formula,int row,int column) to convert the formula to A1 style.

6

Hi,

Is there any feature available in Aspose to assign a formula for a range of cells using range object with looping through the each cells in a range.

Thank you,

Kishan

7

Hi,


Please see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Named+Ranges


See the sample code snippet below on how to get named range and then loop through its cells to specify the formula:
E.g

Sample code:

//…
Range range = workbook.Worksheets.GetRangeByName(“TestRange”);
for (int i = range.FirstRow; i < range.RowCount + range.FirstRow; i++)
{

for (int j = range.FirstColumn; j < range.ColumnCount + range.FirstColumn; j++)
{

worksheet.Cells[i, j].Formula = “formulastring”;
}
}


Thank you.


8

Hi Amjad,

Thank you for the quick response.

If we loop assigning formula by looping through cells in the range it is consuming lot of time. to avoid this can't we assign the formula for range off cells with looping.

Thank you,

Kishan

9

Hi,

Thanks for your posting and using Aspose.Cells.

We are afraid, it is not currently possible to assign the formula to range of cells without loop. So you will have to use the loop to assign formulas to range of cells as shown in the code in the above post.