// Create a new workbook Workbook workbook = new Workbook(); // Convert R1C1 to A1 String r1c1Formula = "R1C:R8C[4]*9"; // At H2 int formulaRow = 1; int formulaColumn = 7; // Parse ParseContext r1c1Option = new ParseContext(); r1c1Option.setIsR1C1(true); FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(r1c1Formula, r1c1Option); // ToString // Specify BaseRow and BaseColumn in a1Option. // Because row and column are absolute index in A1 format. UnParseContext a1Option = new UnParseContext(); a1Option.setBaseColumn(formulaColumn); a1Option.setBaseRow(formulaRow); String converted = syntaxTree.toString(a1Option); // Output IWorksheet sheet1 = workbook.getActiveSheet(); sheet1.getRange("A1").setValue("Original formula (at H2)"); sheet1.getRange("A2").setValue("'=" + r1c1Formula.toString()); sheet1.getRange("A3").setValue("Converted"); sheet1.getRange("A4").setValue("'=" + converted.toString()); // Arrange sheet1.getRange("A:A").getEntireColumn().autoFit(); // Save to an excel file workbook.save("ParseAndFormatOptions.xlsx");
// Create a new workbook var workbook = Workbook() // Convert R1C1 to A1 val r1c1Formula = "R1C:R8C[4]*9" // At H2 val formulaRow = 1 val formulaColumn = 7 // Parse val r1c1Option = ParseContext() r1c1Option.setIsR1C1(true) val syntaxTree: FormulaSyntaxTree = FormulaSyntaxTree.Parse(r1c1Formula, r1c1Option) // ToString // Specify BaseRow and BaseColumn in a1Option. // Because row and column are absolute index in A1 format. val a1Option = UnParseContext() a1Option.setBaseColumn(formulaColumn) a1Option.setBaseRow(formulaRow) val converted: String = syntaxTree.toString(a1Option) // Output val sheet1: IWorksheet = workbook.getActiveSheet() sheet1.getRange("A1").setValue("Original formula (at H2)") sheet1.getRange("A2").setValue("'=$r1c1Formula") sheet1.getRange("A3").setValue("Converted") sheet1.getRange("A4").setValue("'=$converted") // Arrange sheet1.getRange("A:A").getEntireColumn().autoFit() // Save to an excel file workbook.save("ParseAndFormatOptions.xlsx")