//Create a new workbook
Workbook workbook = new Workbook();
//Load sample data from excel file
workbook.open("SampleData.xlsx");

//Fetch worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
//Fetch the original formula which needs to be parsed.
String originalFormula = worksheet.getRange("H3").getFormula().substring(1);

public static class Tuple {
    String TypeName;
    int IndentLevel;
    String Content;

    Tuple(String TypeName, int IndentLevel, String Content) {
        this.TypeName = TypeName;
        this.IndentLevel = IndentLevel;
        this.Content = Content;
    }
}

public static void flatten(SyntaxNode node, int level, List<Tuple> displayItems) {
    displayItems.add(new Tuple(node.getClass().getSimpleName(), level, node.toString()));
    for (SyntaxNode child : node.getChildren()) {
        flatten(child, level + 1, displayItems);
    }
}

//Method to parse a formula and print the syntax tree
public static void ParseAndPrint(IWorksheet worksheet, String formula)
{
    // Get syntax tree
    FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(formula);

    // Flatten nodes
    List<Tuple> displayItems = new ArrayList<>();
    flatten(syntaxTree.getRoot(), 0, displayItems);

    // Output
    worksheet.setShowRowOutline(false);
    worksheet.getOutlineColumn().setColumnIndex(1);

    // Header
    worksheet.getRange("A1").setValue("Formula");
    worksheet.getRange("A3").setValue("Syntax node");
    worksheet.getRange("B3").setValue("Part");

    // Values
    worksheet.getRange("B1").setValue("'=" + formula);
    for (int i = 0; i < displayItems.size(); i++)
    {
        Tuple item = displayItems.get(i);
        String text = "'" + item.TypeName;

        worksheet.getRange(i + 4, 0).setValue(text);
        worksheet.getRange(i + 4, 0).setIndentLevel(item.IndentLevel);
        worksheet.getRange(i + 4, 1).setValue("'" + item.Content);
    }

    //Apply styling
    worksheet.getRange("A1:B3").getInterior().setColor(Color.FromArgb(68, 114, 196));
    worksheet.getRange("A1:B3").getFont().setColor(Color.GetWhite());
    worksheet.getRange("A1:B3").getBorders().setColor(Color.FromArgb(91, 155, 213));
    worksheet.getRange("A1:B3").getBorders().setLineStyle(BorderLineStyle.Thin);
    worksheet.getRange("A1,A3,B3").getFont().setSize(14);
    worksheet.getRange("A1,A3,B3").getFont().setBold(true);
    worksheet.getRange("A:C").getEntireColumn().autoFit();
}

//Find and replace
public static void replaceNode(SyntaxNode lookIn, SyntaxNode find, SyntaxNode replacement)
{
    List<SyntaxNode> children = lookIn.getChildren();

    for (int i = 0; i < children.size(); i++)
    {
        SyntaxNode child = children.get(i);
        if (child.equals(find))
        {
            children.set(i, replacement);
        }
        else
        {
            replaceNode(child, find, replacement);
        }
    }
}
//Method to parse and modify the formula
public static void ModifyFormula(IWorksheet worksheet, String originalFormula)
{
    //Apply UNIQUE formula to get unique sales representatives list
    worksheet.getRange("F1").setValue("Unique Rep");
    worksheet.getRange("F2").setFormula2("=UNIQUE(A2:A19)");
    IRange uniqueRep = worksheet.getRange("F2#");
    // Apply Styling
    worksheet.getRange("F:F").getEntireColumn().autoFit();
    worksheet.getRange("F1").getInterior().setColor(Color.FromArgb(68, 114, 196));
    worksheet.getRange("F1").getFont().setColor(Color.GetWhite());
    worksheet.getRange("F2#").getBorders().setColor(Color.FromArgb(91, 155, 213));
    worksheet.getRange("F2#").getBorders().setLineStyle(BorderLineStyle.Thin);

    //Get syntax tree
    FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(originalFormula);

    //Find
    TextNode findText = new TextNode("Fritz");

    //Replacement
    TextNode replaceText = new TextNode("");

    //Loop through names list to modify the formula for each sales representative
    for (int r = 0, resultRow = 3; r < uniqueRep.getCells().getCount(); r++, resultRow = resultRow + 4)
    {
        //Get name to be replaced in the formula
        String cval = uniqueRep.getCells().get(r).getValue().toString();

        if (!Objects.equals(findText.getValue(), cval))
        {
            //Assign name to be replaced to Replace TextNode
            replaceText.setValue(cval);

            //Invoke the recursive method to perform find and replace operation
            replaceNode(syntaxTree.getRoot(), findText, replaceText);

            //Assign the modified formula to a cell in the worksheet
            String resultRange = "H" + resultRow;
            worksheet.getRange(resultRange).setFormula2("=" + syntaxTree.toString());
            worksheet.getRange(resultRange + "#").getBorders().setColor(Color.FromArgb(91, 155, 213));
            worksheet.getRange(resultRange + "#").getBorders().setLineStyle(BorderLineStyle.Thin);

            //Update the value of Find node to perform find and replace operation for next sales representative name
            findText = replaceText;
        }
    }
}

//Save modified Excel file
workbook.save("ModifiedFormula.xlsx", SaveFileFormat.Xlsx);