R1C1 Formulas always evaluate to Absolute Reference

Aspose.Cells Product Family
1

Hi,

I am currently sampling Aspose.Cells for .NET and I am using R1C1 Formulas quite heavily but I have noticed they always seem to evaluate to an absolute cell reference rather than a relative cell reference.

For example, if I say:

xlSheet.Cells[0, 0].R1C1Formula = “=R5C5”;

In Excel, the resulting formula will show as “=$E$5” but I need it to be "=E5"

How do I make it evaluate as a relative cell reference…?

Cheers,
Jack

2

Hi,

xlSheet.Cells[0, 0].R1C1Formula = “=R5C5”;

In Excel, the resulting formula will show as “=$E$5” but I need it to be “=E5"

This is what MS Excel behaves, so nothing to do with Aspose.Cells, it works the same as MS Excel does.

I think for your need, you may remove the ‘$’ manually, after setting the R1C1 formula, e.g see the example below:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
cells[“A1”].R1C1Formula = “=R5C5”;
cells[“A1”].Formula = cells[“A1”].Formula.Replace(”$", string.Empty);
workbook.Save(“e:\test\outbook.xls”);


Thank you.

3

Okay thanks I wasn’t aware that this was actually Excel behaviour. If anyone else is interested, the way to use relative cell references with R1C1 notation is to use square brackets around the row and column reference. So instead of:

xlSheet.Cells[0, 0].R1C1Formula = “=R5C5”;

You use:

xlSheet.Cells[0, 0].R1C1Formula = “=R[5]C[5]”;

4

Hi,

Thanks for the solution. We appreciate it.

Don’t you think you should use the following line to get your desired relative reference :):

xlSheet.Cells[0, 0].R1C1Formula = “=R[4]C[4]”; ------------------> "=E5"


Thank you.


5

Hi Amjad,

Thanks for the correction. Yes that would be the appropriate reference given that we are now using relative referencing.

Cheers.