Document Solutions for Excel, Java Edition | Document Solutions
Features / Formulas / Formula Parser
In This Topic
    Formula Parser
    In This Topic

    DsExcel provides GrapeCity.Documents.Excel.Expressions package which allows you to parse formula expressions. The formula expressions are exposed at semantic model level so that you can create, visit and modify the formulas by using syntax tree. The FormulaSyntaxTree class represents a formula and is the entry point for formula expressions API.

    Syntax Tree

    The syntax tree represents semantic model of formulas. The Parse method of FormulaSyntaxTree class can be used to get syntax tree from text. However, the text should not start with "=" and should not be surrounded with "{= }". The getRoot method of FormulaSyntaxTree class can be used to get the root element of syntax tree. An empty syntax tree can be created by using FormulaSyntaxTree constructor.

    Refer to the following example code to generate a formula with syntax tree.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    // Build syntax tree
    OperatorNode multiply = new OperatorNode(OperatorKind.Multiply);
    Reference a1 = new Reference();
    a1.setRow(0);
    a1.setColumn(0);
    Reference a2 = new Reference();
    a2.setRow(1);
    a2.setColumn(0);
    multiply.getChildren().add(new ReferenceNode(a1));
    multiply.getChildren().add(new ReferenceNode(a2));
    
    FormulaSyntaxTree tree = new FormulaSyntaxTree();
    tree.setRoot(multiply);
    
    // Generates A1*A2
    workbook.getActiveSheet().getRange("A1").setValue("'=" + tree.toString());
    
    //save to an excel file
    workbook.save("GenerateFormula.xlsx");

    Syntax Node

    The SyntaxNode class represents a node in the syntax tree. The getChildren method can be used to get children of a non-terminal node. If the type of syntax node is a terminal node, then this collection is read-only. Similar to syntax tree, the Parse method of SyntaxNode class can be used get syntax node from text. An empty syntax node can be created by using SyntaxNode constructor.

    Refer to the following example code to parse formula, modify the syntax tree by replacing the child of syntax node and convert it to a string.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    String originalFormula = "LET(AppUpTime,NOW()-DATE(2020,4,17)+366, YEAR(AppUpTime)-1900-1 & \" years\"";
    
    // Replace NOW() with fixed date
    
    // Get syntax tree
    FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(originalFormula);
    
    // Find
    FunctionNode nowFunction = new FunctionNode("NOW");
    
    // Replacement
    FunctionNode valentine2021 = new FunctionNode("DATE");
    valentine2021.getChildren().add(new NumberNode(2021));
    valentine2021.getChildren().add(new NumberNode(2));
    valentine2021.getChildren().add(new NumberNode(14));
    
    // Find and replace
    
    // Arguments and captures of replaceNode
    Stack<SyntaxNode> replaceNodeLookIn = new Stack<SyntaxNode>();
    SyntaxNode find = nowFunction;
    SyntaxNode replacement = valentine2021;
    
    // Call replaceNode
    replaceNodeLookIn.push(syntaxTree.getRoot());
    
    // Method body of replaceNode
    while (!replaceNodeLookIn.isEmpty()) {
        SyntaxNode lookIn = replaceNodeLookIn.pop();
        
        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 {
                replaceNodeLookIn.push(child);
            }
        }
    }
    
    // Output original and replaced
    IWorksheet sheet1 = workbook.getActiveSheet();
    sheet1.getRange("A1").setValue("Original");
    sheet1.getRange("A2").setValue("'=" + originalFormula.toString());
    sheet1.getRange("A3").setValue("Replaced");
    sheet1.getRange("A4").setValue("'=" + syntaxTree.toString());
    
    // Arrange
    sheet1.getRange("A:A").getEntireColumn().autoFit();
    
    //save to an excel file
    workbook.save("ModifyFormula.xlsx");

    Parse and Unparse Options

    The ParseContext and UnparseContext classes contain options for converting strings to FormulaSyntaxTree and vice versa respectively. The setBaseRow and setBaseColumn methods can be used to specify the location of formula and setIsR1C1 method can be used to specify the reference style.

    Refer to the following example code to specify base row, base column and R1C1 reference style in options.

    Java
    Copy Code
    //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");

    Refer to the following example code to parse formula and then print the syntax tree.

    Java
    Copy Code
    //create a new workbook
    Workbook workbook = new Workbook();
    final String Formula = "RAND()>0.5+0.001";
    
    // Get syntax tree
    FormulaSyntaxTree syntaxTree = FormulaSyntaxTree.Parse(Formula);
    
    // Flatten nodes
    
    // Arguments of flatten
    Stack<SyntaxNode> flattenNode = new Stack<SyntaxNode>();
    Stack<Integer> flattenLevel = new Stack<Integer>();
    
    // Captures of flatten
    ArrayList<String> displayItemsTypeName = new ArrayList<String>();
    ArrayList<Integer> displayItemsIndentLevel = new ArrayList<Integer>();
    ArrayList<String> displayItemsContent = new ArrayList<String>();
    
    // Call flatten
    flattenNode.push(syntaxTree.getRoot());
    flattenLevel.push(0);
    
    // Method body of flatten
    while (!flattenNode.isEmpty()) {
        SyntaxNode node = flattenNode.pop();
        int level = flattenLevel.pop().intValue();
        
        displayItemsTypeName.add(node.getClass().getSimpleName());
        displayItemsIndentLevel.add(level);
        displayItemsContent.add(node.toString());
        
        for (int i = node.getChildren().size() - 1; i >= 0; i--) {
            SyntaxNode child = node.getChildren().get(i);
            flattenNode.push(child);
            flattenLevel.push(level + 1);
        }
    }
    
    // Output
    IWorksheet sheet1 = workbook.getWorksheets().get("Sheet1");
    sheet1.setShowRowOutline(false);
    sheet1.getOutlineColumn().setColumnIndex(1);
    try {
        sheet1.getOutlineColumn()
        .setCollapseIndicator(new ImageSource(new FileInputStream("decreaseIndicator.png"), ImageType.PNG));
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    try {
        sheet1.getOutlineColumn()
        .setExpandIndicator(new ImageSource(new FileInputStream("increaseIndicator.png"), ImageType.PNG));
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    
    // Header
    sheet1.getRange("A1").setValue("Formula");
    sheet1.getRange("B1").setValue("Syntax node");
    sheet1.getRange("C1").setValue("Part");
    
    // Values
    sheet1.getRange("A2").setValue("'=" + Formula);
    for (int i = 0; i < displayItemsTypeName.size(); i++) {
    String typeName = displayItemsTypeName.get(i);
    int indentLevel = displayItemsIndentLevel.get(i).intValue();
    String content = displayItemsContent.get(i);
    
    String text = "'" + typeName;
    
    sheet1.getRange(i + 1, 1).setValue(text);
    sheet1.getRange(i + 1, 1).setIndentLevel(indentLevel);
    sheet1.getRange(i + 1, 2).setValue("'" + content);
    }
    
    // Arrange
    sheet1.getRange("A:C").getEntireColumn().autoFit();
    sheet1.getRange("A:C").getEntireColumn()
    .setColumnWidthInPixel(sheet1.getRange("A:C").getEntireColumn().getColumnWidthInPixel() + 40);
    
    //save to an excel file
    workbook.save("PrintFormulaSyntax.xlsx");

    Other Classes in GrapeCity.Documents.Excel.Expressions Package

    The ReferenceNode class represents a reference expression in the syntax tree.

    The Reference class represents a range reference in formula. The reference can be across a cell, range, cross-worksheet, cross-worksheet 3D or cross-workbook.

    Note: If a row or column index is relative, setBaseRow or setBaseColumn methods should be used to convert to absolute index.

    The WorkbookReference class is an immutable class which represents a reference to an external workbook by name or local file path. If the workbook reference is from file path, the setBaseDirectory method contains the directory information.

    Note: The path separator is platform specific and affects the result of workbook reference. For example, 'C:\Temp\[Book1.xlsx]Sheet1'!A2 is a valid reference on Windows but invalid on Linux.

    For example, the parsed object for  a workbook referenced by name: [Book1]Sheet1!A2 will look like below:

    Java
    Copy Code
    Reference ref = new Reference();
            ref.setWorkbook(WorkbookReference.FromName("Book1"));
            ref.setWorksheetName("Sheet1");
            ref.setRow(1);
            ref.setColumn(0);

    The parsed object for a workbook referenced by file path: 'C:\Temp\[Book1.xlsx]Sheet1'!A2 will look like below:

    Java
    Copy Code
    Reference ref = new Reference();
            ref.setWorkbook(WorkbookReference.FromFilePath("C:\\Temp\\Book1.xlsx"));
            ref.setWorksheetName("Sheet1");
            ref.setRow(1);
            ref.setColumn(0);

    The parsed object for a workbook referenced from a web URI will look like below:

    Java
    Copy Code
    Reference ref = new Reference();       
                                    ref.setWorkbook(WorkbookReference.FromUri("https://somesite.com/files/sample.xlsx"));      
                                    ref.setWorksheetName("Sheet1");       
                                    ref.setRow(8);       
                                    ref.setColumn(1);

    The FunctionNode class represents a function invocation expression in the syntax tree.

    For example, the parsed object for Excel formula: COUNTIF(A:A,"*?") will look like below:

    Java
    Copy Code
    Reference ref = new Reference();
                    ref.setHasRow(false);
                    ref.setLastColumn(0);
                    ReferenceNode refNode = new ReferenceNode(ref);
                    TextNode txtNode = new TextNode("*?");
                    FunctionNode funcNode = new FunctionNode("COUNTIF");

    The NameNode class represents the name in a syntax tree.

    For example, the parsed object for a workbook referenced by name: '[BuildingSales]JanIn2021'!RawData will look like below:

    Java
    Copy Code
    new NameNode("RawData", WorkbookReference.FromName("BuildingSales"), "JanIn2021", null);

    The parsed object for a workbook referenced by file path: 'E:\[BuildingSales.xlsx]JanIn2021'!RawData will look like below:

    Java
    Copy Code
    new NameNode("RawData", WorkbookReference.FromFilePath("C:\\Temp\\Book1.xlsx"), "JanIn2021", null);

    The ErrorNode class represents an error literal node in the syntax tree. The following error types are not supported:

    The ArrayNode class represents an array literal in the syntax tree. There are following array constraints:

    To know more about other classes, please refer GrapeCity.Documents.Excel.Expressions API documentation.

    Limitations