Formula Parser

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.

//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.

//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.

//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.

//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.

!type=note
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.

!type=note
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:

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:

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:

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:

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:

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:

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:

#BLOCKED!
#CONNECT!
#FIELD!
#UNKNOWN!
#REF! error is parsed to ReferenceNode

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

The length of array must be > 0
Elements can be Double, String, Boolean or CalcError. Primitive number types are converted to double implicitly.
The lower bound of each ranks must be 0
The array and Elements can't be null

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

Limitations

GetHashCode method of FormulaSyntaxTree and SyntaxNode class are not supported. They return constant values because all fields are mutable.
DsExcel does not support resolving workbook index defined in OpenXML or JSON file storage. They are treated as workbook reference by name.

Syntax Tree
Syntax Node
Parse and Unparse Options
Other Classes in GrapeCity.Documents.Excel.Expressions Package
Limitations