Cell Coordinates conversion

[Andrea Perazzolo]

Hi,

I am used to access the cells of a IRange object through the row and column index instead of the Named coordinates (eg A1, B4, A1:B4 etc).

I noticed that in the formulas are always specified with this last format.

There is a builtin function that maps the two coordinate systems ?

     Thanks in advance

    Andrea Perazzolo

[Abirami Varadharajan]

Hi Andrea,

 Yes, you can set or get the formula for a range in two coordinate system by using FormulaR1C1 property of IRange. Kindly refer the following code example which illustrates this behaviour.

Code Example:

                        

ExcelEngine excelEngine = new ExcelEngine();

IApplication application = excelEngine.Excel;

IWorkbook workbook = application.Workbooks.Create(2);

IWorksheet worksheet = workbook.Worksheets[0];

           

worksheet.Range[1, 1].Value = "10";

worksheet.Range[2, 1].Value = "20";

worksheet.Range[3, 1].Value = "30";

worksheet.Range[4, 1].Value = "40";

worksheet.Range[5, 1].FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"; // equivalent to "=SUM(A1:A4)"

worksheet.Range[5, 2].Value = "50";

worksheet.Range[6, 2].Formula = "=SUM(A5:B5)";

string formulaR1C1 = worksheet.GetFormula(6, 2, true); //this function returns the string as : "=SUM(R[-1]C[-1]:R[-1]C)"

workbook.Version = ExcelVersion.Excel2013;

workbook.SaveAs("CellCoordinatesSample.xlsx");

workbook.Close();

excelEngine.Dispose();

Please let us know if you have any concerns.

Regards,
Abirami.