//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); // Convert R1C1 to A1 var r1c1Formula = "R1C:R8C[4]*9"; // At H2 var formulaRow = 1; var formulaColumn = 7; // Parse var r1c1Option = new ParseContext { IsR1C1 = true }; var syntaxTree = FormulaSyntaxTree.Parse(r1c1Formula, r1c1Option); // ToString // Specify BaseRow and BaseColumn in a1Option. // Because row and column are absolute index in A1 format. var a1Option = new UnParseContext { BaseColumn = formulaColumn, BaseRow = formulaRow }; var converted = syntaxTree.ToString(a1Option); // Output var sheet1 = workbook.ActiveSheet; sheet1.Range["A1"].Value = "Original formula (at H2)"; sheet1.Range["A2"].Value = "'=" + r1c1Formula.ToString(); sheet1.Range["A3"].Value = "Converted"; sheet1.Range["A4"].Value = "'=" + converted.ToString(); // Arrange sheet1.Range["A:A"].EntireColumn.AutoFit(); // Save to an excel file workbook.Save("ParseAndFormatOptions.xlsx");
' Create a new Workbook Dim workbook As New Workbook ' Convert R1C1 to A1 Dim r1c1Formula = "R1C:R8C[4]*9" ' At H2 Dim formulaRow = 1 Dim formulaColumn = 7 ' Parse Dim r1c1Option As New ParseContext With { .IsR1C1 = True } Dim syntaxTree = FormulaSyntaxTree.Parse(r1c1Formula, r1c1Option) ' ToString ' Specify BaseRow and BaseColumn in a1Option. ' Because row and column are absolute index in A1 format. Dim a1Option As New UnParseContext With { .BaseColumn = formulaColumn, .BaseRow = formulaRow } Dim converted = syntaxTree.ToString(a1Option) ' Output With workbook.ActiveSheet.Range !A1.Value = "Original formula (at H2)" !A2.Value = "'=" & r1c1Formula !A3.Value = "Converted" !A4.Value = "'=" & converted ' Arrange .Item("A:A").EntireColumn.AutoFit() End With ' save to an excel file workbook.Save("ParseAndFormatOptions.xlsx")