private static STNode ReadMul(ExcelFormulaLexer lexer)
{
var node = ReadExponent(lexer);
if (node == null) return node;
while (true)
{
STNodeType type = STNodeType.NONE;
if (lexer.IsToken("*"))
type = STNodeType.MUL;
else if (lexer.IsToken("/"))
type = STNodeType.DIV;
else
break;
lexer.NextToken();
var right = ReadExponent(lexer);
if (right == null) throw CreateException(lexer, "expect expression");
node = CreateNode(lexer, type, node.Start, lexer.CommittedLength - node.Start,
new List<STNode> { node, right });
}
return node;
}
private static List <STNode> ReadParameterList(ExcelFormulaLexer lexer)
{
List <STNode> nodes = new List <STNode>();
int i = 0;
while (true)
{
STNode node = ReadExpr(lexer);
nodes.Add(node);
i++;
if (!lexer.SkipToken(Parser.ParameterSeparator))
{
break;
}
}
while (nodes.Count > 0 && nodes[nodes.Count - 1] == null)
{
nodes.RemoveAt(nodes.Count - 1);
}
return(nodes.Count == 0 ? null : nodes);
}
private static STNode CreateNode(ExcelFormulaLexer lexer, STNodeType type, int start, int len, List <STNode> nodes)
{
switch (type)
{
case STNodeType.NUMBER:
string text = lexer.Input.Substring(start, len);
double v = 0;
return(double.TryParse(text, out v) ? new STNumberNode(v, start, len) : null);
case STNodeType.IDENTIFIER:
return(new STIdentifierNode(lexer.Cell == null ? null : lexer.Cell.Worksheet, lexer.Input.Substring(start, len), start, len));
case STNodeType.STRING:
return(new STStringNode(lexer.Input, start, len));
case STNodeType.RANGE:
return(new STRangeNode(null, new RangePosition(lexer.Input.Substring(start, len)), start, len));
case STNodeType.CELL:
return(new STCellNode(null, new CellPosition(lexer.Input.Substring(start, len)), type, start, len));
default:
return(new STNode(type, start, len, nodes));
}
}
private static STNode ReadExponent(ExcelFormulaLexer lexer)
{
var node = ReadPercent(lexer);
if (node == null)
{
return(node);
}
while (lexer.SkipToken("^"))
{
var right = ReadPercent(lexer);
if (right == null)
{
throw CreateException(lexer, "expect expression");
}
node = CreateNode(lexer, STNodeType.POW, node.Start, lexer.CommittedLength - node.Start,
new List <STNode> {
node, right
});
}
return(node);
}
private static STNode ReadCompare(ExcelFormulaLexer lexer)
{
var node = ReadConnect(lexer);
if (node == null) return null;
STNodeType type = STNodeType.NONE;
if (lexer.IsToken("=") || lexer.IsToken("=="))
type = STNodeType.EQUALS;
else if (lexer.IsToken("<>") || lexer.IsToken("!="))
type = STNodeType.NOT_EQUALS;
else if (lexer.IsToken(">"))
type = STNodeType.GREAT_THAN;
else if (lexer.IsToken("<"))
type = STNodeType.LESS_THAN;
else if (lexer.IsToken(">="))
type = STNodeType.GREAT_EQUALS;
else if (lexer.IsToken("<="))
type = STNodeType.LESS_EQUALS;
else
return node;
lexer.NextToken();
var right = ReadExpr(lexer);
if (right == null) throw CreateException(lexer, "expect expression");
node = CreateNode(lexer, type, node.Start, lexer.CommittedLength - node.Start,
new List<STNode> { node, right });
return node;
}
private static STNode ReadFunctionCall(ExcelFormulaLexer lexer)
{
var id = ReadSheetName(lexer);
if (id == null) return null;
string funName = null;
// process function name like 'LOG10'
if (id.Type == STNodeType.CELL)
{
Group mg = null;
if (lexer.CurrentToken == null
|| (mg = lexer.CurrentToken.Groups["token"]) == null
|| !mg.Success
|| mg.Value != "(")
return id;
funName = lexer.Input.Substring(id.Start, id.Length);
lexer.NextToken();
}
else
{
if (id.Type != STNodeType.IDENTIFIER
|| !lexer.SkipToken("(")) return id;
funName = ((STIdentifierNode)id).Identifier;
}
var parameterList = ReadParameterList(lexer);
if (!lexer.SkipToken(")")) throw CreateException(lexer, "expect )");
return new STFunctionNode(funName, id.Start, lexer.CommittedLength - id.Start, parameterList);
}
internal static STNode ParseInterCompareExp(Cell cell, string input)
{
ExcelFormulaLexer lexer = new ExcelFormulaLexer(
cell.Worksheet == null ? null : cell.Worksheet.workbook, cell, input);
STNodeType type = STNodeType.NONE;
if (lexer.IsToken("=") || lexer.IsToken("=="))
{
type = STNodeType.EQUALS;
}
else if (lexer.IsToken("<>") || lexer.IsToken("!="))
{
type = STNodeType.NOT_EQUALS;
}
else if (lexer.IsToken(">"))
{
type = STNodeType.GREAT_THAN;
}
else if (lexer.IsToken("<"))
{
type = STNodeType.LESS_THAN;
}
else if (lexer.IsToken(">="))
{
type = STNodeType.GREAT_EQUALS;
}
else if (lexer.IsToken("<="))
{
type = STNodeType.LESS_EQUALS;
}
if (type != STNodeType.NONE)
{
lexer.NextToken();
var right = ReadExpr(lexer);
if (right != null)
{
return(CreateNode(lexer, type, 0, lexer.CommittedLength, new List <STNode> {
null, right
}));
}
}
STNode node = ReadConnect(lexer);
return(new STNode(STNodeType.EQUALS, 0, 0,
new List <STNode> {
null,
node != null && node.Type != STNodeType.IDENTIFIER ? node :
new STStringNode(input, 0, input.Length)
}));
}
private static STNode ReadPrimary(ExcelFormulaLexer lexer)
{
STNode node;
if (CommitMatchNode(lexer, "string", STNodeType.STRING, out node) ||
CommitMatchNode(lexer, "identifier", STNodeType.IDENTIFIER, out node) ||
CommitMatchNode(lexer, "number", STNodeType.NUMBER, out node) ||
CommitMatchNode(lexer, "cell", STNodeType.CELL, out node) ||
CommitMatchNode(lexer, "range", STNodeType.RANGE, out node) ||
CommitMatchNode(lexer, "true", STNodeType.TRUE, out node) ||
CommitMatchNode(lexer, "false", STNodeType.FALSE, out node) ||
CommitMatchNode(lexer, "union_ranges", STNodeType.INTERSECTION, out node))
{
return(node);
}
//else if (lexer.IsMatch("abs_cell"))
//{
// var g = lexer.CurrentToken;
// int col = RGUtility.GetNumberOfChar(g.Groups["col"].Value) - 1;
// int row = int.Parse(g.Groups["row"].Value) - 1;
// lexer.NextToken();
// return new STCellNode(lexer.Cell.Worksheet, new ReoGridPos(row, col), STNodeType.CELL, g.Index, g.Length);
//}
else if (lexer.IsToken("("))
{
int start = lexer.Index;
lexer.NextToken();
var expr = ReadExpr(lexer);
if (expr == null)
{
throw CreateException(lexer, "expect expression");
}
if (!lexer.SkipToken(")"))
{
throw CreateException(lexer, "expect )");
}
return(CreateNode(lexer, STNodeType.SUB_EXPR, start, lexer.CommittedLength - start,
new List <STNode>()
{
expr
}));
}
else
{
return(null);
}
}
private static STNode ReadMinus(ExcelFormulaLexer lexer)
{
if (!lexer.IsToken("-")) return ReadFunctionCall(lexer);
int start = lexer.Index;
lexer.NextToken();
var node = ReadFunctionCall(lexer);
if (node == null) throw CreateException(lexer, "expect expression");
return CreateNode(lexer, STNodeType.UNARY_MINUS, start, lexer.CommittedLength - start, new List<STNode> { node });
}
/// <summary>
/// Convert formula to syntax tree.
/// </summary>
/// <param name="workbook">Workbook instance.</param>
/// <param name="cell">Cell instance.</param>
/// <param name="input">Formula to be converted.</param>
/// <returns>syntax tree constructed from specified formula.</returns>
public static STNode Parse(IWorkbook workbook, Cell cell, string input)
{
ExcelFormulaLexer lexer = new ExcelFormulaLexer(workbook, cell, input);
var node = ReadExpr(lexer);
if (lexer.CurrentToken != null && lexer.CurrentToken.Success)
{
throw CreateException(lexer, "unexpect token: " + lexer.CurrentToken.Value);
}
return(node);
}
private static STNode ReadPercent(ExcelFormulaLexer lexer)
{
var node = ReadMinus(lexer);
if (node == null) return null;
while (lexer.SkipToken("%"))
{
node = CreateNode(lexer, STNodeType.UNARY_PERCENT, node.Start, lexer.CommittedLength - node.Start,
new List<STNode>() { node });
}
return node;
}
private static bool CommitMatchNode(ExcelFormulaLexer lexer, string groupName, STNodeType type, out STNode node)
{
if (lexer.IsMatch(groupName))
{
var g = lexer.CurrentToken.Groups[groupName];
node = CommitRunAndCreateNode(lexer, type, g.Index, g.Length, null);
return(true);
}
else
{
node = null;
return(false);
}
}
private static STNode ReadConnect(ExcelFormulaLexer lexer)
{
var node = ReadAdd(lexer);
if (node == null) return null;
while (lexer.SkipToken("&"))
{
var right = ReadAdd(lexer);
if (right == null) throw CreateException(lexer, "expect expression");
node = CreateNode(lexer, STNodeType.CONNECT, node.Start, lexer.CommittedLength - node.Start,
new List<STNode> { node, right });
}
return node;
}
private static STNode ReadAdd(ExcelFormulaLexer lexer)
{
var node = ReadMul(lexer);
if (node == null)
{
return(null);
}
while (true)
{
STNodeType type = STNodeType.NONE;
if (lexer.IsToken("+"))
{
type = STNodeType.ADD;
}
else if (lexer.IsToken("-"))
{
type = STNodeType.SUB;
}
else
{
break;
}
lexer.NextToken();
var right = ReadMul(lexer);
if (right == null)
{
throw CreateException(lexer, "expect expression");
}
node = CreateNode(lexer, type, node.Start, lexer.CommittedLength - node.Start,
new List <STNode> {
node, right
});
}
return(node);
}
private static STNode ReadExpr(ExcelFormulaLexer lexer)
{
var node = ReadCompare(lexer);
return(node);
}
private static STNode CreateNode(ExcelFormulaLexer lexer, STNodeType type)
{
return(new STNode(type, lexer.CurrentToken.Index, lexer.CurrentToken.Length, null));
}
private static STNode CommitRunAndCreateNode(ExcelFormulaLexer lexer, STNodeType type,
int start, int len, List <STNode> nodes)
{
lexer.NextToken();
return(CreateNode(lexer, type, start, len, nodes));
}
private static STNode ReadSheetName(ExcelFormulaLexer lexer)
{
var node = ReadPrimary(lexer);
if (node == null)
{
return(null);
}
// neither a sheet name nor a function scope
if (node.Type != STNodeType.IDENTIFIER)
{
return(node);
}
if (lexer.SkipToken("!"))
{
var id = ReadPrimary(lexer);
if (id.Type != STNodeType.CELL &&
id.Type != STNodeType.RANGE &&
id.Type != STNodeType.IDENTIFIER)
{
throw CreateException(lexer, "expect Cell/Range/Name reference");
}
var sheetName = ((STIdentifierNode)node).Identifier;
IWorkbook workbook = lexer.Workbook;
if (workbook == null &&
lexer.Cell != null && lexer.Cell.Worksheet != null && lexer.Cell.Worksheet.workbook != null)
{
workbook = lexer.Cell.Worksheet.workbook;
}
// only set worksheet reference if cell or worksheet associated
if (workbook != null)
{
switch (id.Type)
{
case STNodeType.CELL:
{
STCellNode cellNode = (STCellNode)id;
cellNode.Worksheet = workbook.GetWorksheetByName(sheetName);
}
break;
case STNodeType.RANGE:
{
STRangeNode rangeNode = (STRangeNode)id;
rangeNode.Worksheet = workbook.GetWorksheetByName(sheetName);
}
break;
case STNodeType.IDENTIFIER:
{
STIdentifierNode idNode = (STIdentifierNode)id;
idNode.Worksheet = workbook.GetWorksheetByName(sheetName);
}
break;
}
}
return(id);
}
else if (lexer.SkipToken("."))
{
var id = ReadPrimary(lexer);
if (id.Type != STNodeType.IDENTIFIER)
{
throw CreateException(lexer, "expect identifier");
}
return(id);
}
else
{
return(node);
}
}
private static FormulaParseException CreateException(ExcelFormulaLexer lexer, string msg)
{
return(new FormulaParseException(msg, lexer.CommittedLength));
}