[]
GcExcel 提供GrapeCity.Documents.Excel.Expressions包,它允许您解析公式表达式。公式表达式在语义模型级别公开,以便您可以使用语法树创建、访问和修改公式。所述FormulaSyntaxTree类表示一个公式,并且是式表达式API的入口点。
句法树表示公式的语义模型。FormulaSyntaxTree类的Parse方法可用于从文本中获取语法树。但是,文本不应以“=”开头,也不应以“{= }”括起来。FormulaSyntaxTree类的getRoot方法可用于获取语法树的根元素。可以使用FormulaSyntaxTree构造函数创建一个空的语法树。
请参考以下示例代码以生成带有语法树的公式。
//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");
该SyntaxNode类表示语法树的节点。所述的getChildren方法可以用来得到一个非末端节点的子节点。如果语法节点的类型是终端节点,则该集合是只读的。与语法树类似,可以使用SyntaxNode类的Parse方法 从文本中获取语法节点。可以使用SyntaxNode构造函数创建一个空的语法节点。
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 replaceNodeLookIn = new Stack();
SyntaxNode find = nowFunction;
SyntaxNode replacement = valentine2021;
// Call replaceNode
replaceNodeLookIn.push(syntaxTree.getRoot());
// Method body of replaceNode
while (!replaceNodeLookIn.isEmpty()) {
SyntaxNode lookIn = replaceNodeLookIn.pop();
List 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");
ParseContext和UnparseContext类分别包含用于将字符串转换为FormulaSyntaxTree和FormulaSyntaxTree的选项。setBaseRow和setBaseColumn方法可用于指定公式的位置,setIsR1C1方法可用于指定参照样式。
请参考以下示例代码在选项中指定基行、基列和 R1C1 引用样式。
//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
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 flattenNode = new Stack();
Stack flattenLevel = new Stack();
// Captures of flatten
ArrayList displayItemsTypeName = new ArrayList();
ArrayList displayItemsIndentLevel = new ArrayList();
ArrayList displayItemsContent = new ArrayList();
// 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");
ReferenceNode类表示语法树中的引用表达式。
Reference类表示公式中的范围引用。引用可以跨单元格、区域、跨工作表、跨工作表3D或跨工作簿。
注意: 如果行或列索引是相对的,则应使用setBaseRow或setBaseColumn方法转换为绝对索引。
WorkbookReference类是一个不可变类,它按名称或本地文件路径表示对外部工作簿的引用。如果工作簿引用来自文件路径,则setBaseDirectory方法包含目录信息。
注意: 路径分隔符是特定于平台的,会影响工作簿引用的结果。例如,'C:\Temp\[Book1.xlsx]Sheet1'!A2 在Windows上是有效的引用,但在Linux上无效。
例如,通过名称_[Book1]Sheet1!A2_引用的工作簿解析对象如下所示:
Reference ref = new Reference();
ref.setWorkbook(WorkbookReference.FromName("Book1"));
ref.setWorksheetName("Sheet1");
ref.setRow(1);
ref.setColumn(0);
例如,通过文件路径_'C:\Temp\[Book1.xlsx]Sheet1'!A2_引用的工作簿解析对象如下所示:
Reference ref = new Reference();
ref.setWorkbook(WorkbookReference.FromFilePath("C:\\Temp\\Book1.xlsx"));
ref.setWorksheetName("Sheet1");
ref.setRow(1);
ref.setColumn(0);
从web URI引用的工作簿的已解析对象如下所示:
Reference ref = new Reference();
ref.setWorkbook(WorkbookReference.FromUri("https://somesite.com/files/sample.xlsx"));
ref.setWorksheetName("Sheet1");
ref.setRow(8);
ref.setColumn(1);
FunctionNode类表示语法树中的函数调用表达式。
例如,Excel公式_COUNTIF(A:A,"*?")_的解析对象如下所示:
Reference ref = new Reference();
ref.setHasRow(false);
ref.setLastColumn(0);
ReferenceNode refNode = new ReferenceNode(ref);
TextNode txtNode = new TextNode("*?");
FunctionNode funcNode = new FunctionNode("COUNTIF");
NameNode类表示语法树中的名称。
通过名称_'[BuildingSales]JanIn2021'!RawData_引用的工作簿解析对象如下所示:
new NameNode("RawData", WorkbookReference.FromName("BuildingSales"), "JanIn2021", null);
通过文件路径_'E:\[BuildingSales.xlsx]JanIn2021'!RawData_引用的工作簿解析对象如下所示
new NameNode("RawData", WorkbookReference.FromFilePath("C:\\Temp\\Book1.xlsx"), "JanIn2021", null);
ErrorNode类表示语法树中的错误文本节点。不支持以下错误类型:
#BLOCKED!
#CONNECT!
#FIELD!
#UNKNOWN!
#REF! 错误被解析为ReferenceNode
ArrayNode类表示语法树中的数组文字。有以下数组约束:
数组的长度必须大于0
元素可以是Double、String、Boolean或CalcError。基元数类型被隐式转换为双精度。
每个列组的下限必须为0
数组和元素不能为null
要了解更多关于其他类的信息,请参考要了解更多关于其他类的信息,请参考GrapeCity.Documents.Excel.Expressions API 文档。
不支持FormulaSyntaxTree和SyntaxNode类的GetHashCode方法。它们返回常量值,因为所有字段都是可变的。
GcExcel不支持解析OpenXML或JSON文件存储中定义的工作簿索引。按名称将它们视为工作簿引用。