static void Main(string[] args)
{
if (args.Length < 4)
{
Usage(args);
return;
}
string uri = args[1];
string sheetName = args[2];
string cellString = args[3];
int row = 0;
int col = 0;
if (!ExcelFormulaEvaluator.ParseCell(cellString, out row, out col))
{
Usage(args);
return;
}
m_WorkBook = new XSSFWorkbook(uri);
ExcelFormulaEvaluator excelFormulaEvaluator = new ExcelFormulaEvaluator(m_WorkBook);
ICell cell = excelFormulaEvaluator.FetchCellFromSheet(sheetName, row, col);
if (cell == null)
{
Console.WriteLine($"Could not get cell {row},{col} from sheet \"{sheetName}\"");
return;
}
if (cell.CellType == CellType.Formula)
{
FormulaReturnValue cellValue = ExcelFormulaEvaluator.EvaluateCellFormula(m_WorkBook, cell);
if (cellValue == null)
{
Console.WriteLine("Cell value NULL");
}
else
{
if (cellValue.returnType == FormulaReturnType.stringFormula)
{
Console.WriteLine("cell = \"" + cellValue.stringValue + "\"");
}
else
{
Console.WriteLine("cell = " + cellValue.floatValue.ToString("R"));
}
}
}
else
{
Console.WriteLine("cell = " + ExcelFormulaEvaluator.CellValueAsString(cell));
}
}
FormulaReturnValue MakeFormulaCall(string formulaName, List <ExcelFormulaToken> tokens)
{
// Due to recursion, this formula should not contain other formulas.
// Find any arguments that are NOT Operand or Argument
List <ExcelFormulaToken> resolvedTokens = new List <ExcelFormulaToken>();
// TODO - resolve not Operand or Argument tokens
for (int i = 0; i < tokens.Count; i++)
{
resolvedTokens.Add(tokens[i]);
}
// Now build the argument list and invoke the method
Type thisType = this.GetType();
MethodInfo theMethod = thisType.GetMethod(formulaName);
if (theMethod == null)
{
// is this just a cell lookup?
Console.WriteLine("UNKNOWN FORMULA \"" + formulaName + "\"");
return(null);
}
else
{
List <object> formulaArguments = new List <object>();
for (int i = 0; i < resolvedTokens.Count; i++)
{
if (resolvedTokens[i].Type == ExcelFormulaTokenType.Operand)
{
formulaArguments.Add(resolvedTokens[i].Value);
}
}
FormulaReturnValue retValue = (FormulaReturnValue)theMethod.Invoke(this, formulaArguments.ToArray());
return(retValue);
}
}
public static FormulaReturnValue EvaluateCellFormula(XSSFWorkbook workbook, ICell cell)
{
if (cell.CellType != CellType.Formula)
{
return(null);
}
ISheet sheet = cell.Sheet;
string formula = "=" + cell.CellFormula;
ExcelFormula excelFormula = new ExcelFormula(formula);
List <ExcelFormulaToken> tokens = new List <ExcelFormulaToken>();
foreach (ExcelFormulaToken token in excelFormula)
{
//Console.WriteLine("Token type \"" + token.Type.ToString() + "\" value \"" + token.Value + "\"");
tokens.Add(token);
}
ExcelFormulaEvaluator formulaEvaluator = new ExcelFormulaEvaluator(workbook);
FormulaReturnValue retValue = formulaEvaluator.EvaluateFormulaFromTokens(sheet, tokens, string.Empty);
return(retValue);
}
public FormulaReturnValue EvaluateFormulaFromTokens(ISheet sheet, List <ExcelFormulaToken> tokens, string formulaName)
{
m_Sheet = sheet;
List <ExcelFormulaToken> tokensToEvaluate = new List <ExcelFormulaToken>();
List <ExcelFormulaToken> tokensInFormula = new List <ExcelFormulaToken>();
bool insideFormula = false;
for (int i = 0; i < tokens.Count; i++)
{
ExcelFormulaToken token = tokens[i];
switch (token.Type)
{
case ExcelFormulaTokenType.Noop:
break;
case ExcelFormulaTokenType.Operand:
if (insideFormula)
{
tokensInFormula.Add(token);
}
else
{
tokensToEvaluate.Add(token);
}
break;
case ExcelFormulaTokenType.Function:
{
if (token.Value == string.Empty)
{
if (!insideFormula)
{
Console.WriteLine("Got end of formula outside of formula");
}
else
{
//float val = EvaluateFormulaFromTokens(workbook, sheet, tokensToEvaluate, formulaName);
//ExcelFormulaToken fToken = new ExcelFormulaToken(val.ToString("R"), ExcelFormulaTokenType.Operand);
//tokensToEvaluate.Add(fToken);
FormulaReturnValue retValue = MakeFormulaCall(formulaName, tokensInFormula);
if (retValue != null)
{
ExcelFormulaToken fToken = new ExcelFormulaToken(string.Empty, ExcelFormulaTokenType.Noop);
if (retValue.returnType == FormulaReturnType.floatFormula)
{
fToken = new ExcelFormulaToken(retValue.floatValue.ToString("R"), ExcelFormulaTokenType.Operand);
}
else
{
fToken = new ExcelFormulaToken(retValue.stringValue, ExcelFormulaTokenType.Operand);
}
tokensToEvaluate.Add(fToken);
}
insideFormula = false;
}
}
else
{
formulaName = token.Value;
insideFormula = true;
}
}
break;
case ExcelFormulaTokenType.Subexpression:
if (insideFormula)
{
tokensInFormula.Add(token);
}
else
{
tokensToEvaluate.Add(token);
}
break;
case ExcelFormulaTokenType.Argument:
if (insideFormula)
{
tokensInFormula.Add(token);
}
else
{
tokensToEvaluate.Add(token);
}
break;
case ExcelFormulaTokenType.OperatorPrefix:
if (insideFormula)
{
tokensInFormula.Add(token);
}
else
{
tokensToEvaluate.Add(token);
}
break;
case ExcelFormulaTokenType.OperatorInfix:
if (insideFormula)
{
tokensInFormula.Add(token);
}
else
{
tokensToEvaluate.Add(token);
}
break;
case ExcelFormulaTokenType.OperatorPostfix:
if (insideFormula)
{
tokensInFormula.Add(token);
}
else
{
tokensToEvaluate.Add(token);
}
break;
case ExcelFormulaTokenType.Whitespace:
break;
case ExcelFormulaTokenType.Unknown:
break;
default:
break;
}
//Console.WriteLine("Token type \"" + token.Type.ToString() + "\" value \"" + token.Value + "\"");
}
bool formulaIsNumeric = true;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < tokensToEvaluate.Count; i++)
{
ExcelFormulaToken token = tokensToEvaluate[i];
switch (token.Type)
{
case ExcelFormulaTokenType.Noop:
break;
case ExcelFormulaTokenType.Operand:
{
float val = 0;
if (float.TryParse(token.Value, out val))
{
sb.Append(token.Value);
}
else
{
CellArgument cellArg = ParseCellArgument(token.Value);
switch (cellArg.caType)
{
case CellArgumentType.NA:
break;
case CellArgumentType.CAString:
formulaIsNumeric = false;
sb.Append(token.Value);
break;
case CellArgumentType.CAValue:
sb.Append(token.Value);
break;
case CellArgumentType.CACell:
{
string sheetName = m_Sheet.SheetName;
int row = cellArg.row0;
int col = cellArg.col0;
ICell cell = FetchCellFromSheet(sheetName, row, col);
if (cell != null)
{
if (cell.CellType != CellType.Numeric)
{
formulaIsNumeric = false;
sb.Append(CellValueAsString(cell));
}
else
{
sb.Append(cell.NumericCellValue.ToString("R"));
}
}
}
break;
case CellArgumentType.CARange:
sb.Append(token.Value);
break;
case CellArgumentType.CASheetCell:
{
string sheetName = cellArg.sheetName;
int row = cellArg.row0;
int col = cellArg.col0;
ICell cell = FetchCellFromSheet(sheetName, row, col);
if (cell != null)
{
if (cell.CellType != CellType.Numeric)
{
formulaIsNumeric = false;
sb.Append(CellValueAsString(cell));
}
else
{
sb.Append(cell.NumericCellValue.ToString("R"));
}
}
}
break;
case CellArgumentType.CASheetRange:
sb.Append(token.Value);
break;
default:
break;
}
}
if (i < (tokensToEvaluate.Count - 1))
{
sb.Append(" ");
}
}
break;
case ExcelFormulaTokenType.Function:
break;
case ExcelFormulaTokenType.Subexpression:
break;
case ExcelFormulaTokenType.Argument:
break;
case ExcelFormulaTokenType.OperatorPrefix:
sb.Append(token.Value);
break;
case ExcelFormulaTokenType.OperatorInfix:
sb.Append(token.Value);
if (i < (tokensToEvaluate.Count - 1))
{
sb.Append(" ");
}
break;
case ExcelFormulaTokenType.OperatorPostfix:
sb.Append(token.Value);
if (i < (tokensToEvaluate.Count - 1))
{
sb.Append(" ");
}
break;
case ExcelFormulaTokenType.Whitespace:
break;
case ExcelFormulaTokenType.Unknown:
break;
default:
break;
}
}
FormulaReturnValue returnValue = new FormulaReturnValue();
if (formulaIsNumeric)
{
string formulaString = sb.ToString();
//Console.WriteLine("formulaString \"" + formulaString + "\"");
ShuntingYardSimpleMath SY = new ShuntingYardSimpleMath();
List <String> ss = formulaString.Split(' ').ToList();
//for (int i = 0; i < ss.Count; i++)
//{
// Console.WriteLine("ss " + i.ToString() + ": \"" + ss[i] + "\"");
//}
Double res = SY.Execute(ss, null);
//Console.WriteLine("SY = " + res.ToString("R"));
returnValue.returnType = FormulaReturnType.floatFormula;
returnValue.floatValue = (float)res;
}
else
{
returnValue.returnType = FormulaReturnType.stringFormula;
returnValue.stringValue = sb.ToString();
}
return(returnValue);
}
public FormulaReturnValue VLOOKUP(string arg0, string arg1, string arg2, string arg3)
{
FormulaReturnValue lookupVal = new FormulaReturnValue();
CellArgument cellArg = ParseCellArgument(arg0);
switch (cellArg.caType)
{
case CellArgumentType.NA:
break;
case CellArgumentType.CAString:
lookupVal.returnType = FormulaReturnType.stringFormula;
lookupVal.stringValue = cellArg.stringVal;
break;
case CellArgumentType.CAValue:
lookupVal.returnType = FormulaReturnType.stringFormula;
lookupVal.stringValue = cellArg.stringVal;
break;
case CellArgumentType.CACell:
{
string sheetName = m_Sheet.SheetName;
int row = cellArg.row0;
int col = cellArg.col0;
ICell cell = FetchCellFromSheet(sheetName, row, col);
if (cell != null)
{
if (cell.CellType != CellType.Numeric)
{
if (cell.CellType == CellType.Formula)
{
lookupVal = EvaluateCellFormula(m_WorkBook, cell);
}
else
{
lookupVal.returnType = FormulaReturnType.stringFormula;
lookupVal.stringValue = CellValueAsString(cell);
}
}
else
{
lookupVal.returnType = FormulaReturnType.floatFormula;
lookupVal.floatValue = (float)(cell.NumericCellValue);
}
}
}
break;
case CellArgumentType.CARange:
Console.WriteLine("Invalid argument");
return(null);
case CellArgumentType.CASheetCell:
{
string sheetName = cellArg.sheetName;
int row = cellArg.row0;
int col = cellArg.col0;
ICell cell = FetchCellFromSheet(sheetName, row, col);
if (cell != null)
{
if (cell.CellType != CellType.Numeric)
{
lookupVal.returnType = FormulaReturnType.stringFormula;
lookupVal.stringValue = CellValueAsString(cell);
}
else
{
lookupVal.returnType = FormulaReturnType.floatFormula;
lookupVal.floatValue = (float)(cell.NumericCellValue);
}
}
}
break;
case CellArgumentType.CASheetRange:
Console.WriteLine("Invalid argument");
return(null);
default:
break;
}
CellArgument cellRangeArg = ParseCellArgument(arg1);
switch (cellRangeArg.caType)
{
case CellArgumentType.NA:
Console.WriteLine("Invalid argument");
return(null);
case CellArgumentType.CAString:
Console.WriteLine("Invalid argument");
return(null);
case CellArgumentType.CAValue:
Console.WriteLine("Invalid argument");
return(null);
case CellArgumentType.CACell:
Console.WriteLine("Invalid argument");
return(null);
case CellArgumentType.CARange:
break;
case CellArgumentType.CASheetCell:
Console.WriteLine("Invalid argument");
return(null);
case CellArgumentType.CASheetRange:
break;
default:
Console.WriteLine("Invalid argument");
return(null);
}
int valueCol = 0;
if (!int.TryParse(arg2, out valueCol))
{
Console.WriteLine("Invalid argument");
return(null);
}
valueCol = cellRangeArg.col0 + valueCol - 1;
string rangeSheetName = m_Sheet.SheetName;
if (cellRangeArg.caType == CellArgumentType.CASheetRange)
{
rangeSheetName = cellRangeArg.sheetName;
}
Console.WriteLine("Looking for \"" + (lookupVal.returnType == FormulaReturnType.stringFormula ? lookupVal.stringValue : lookupVal.floatValue.ToString("R")) + "\"");
for (int i = cellRangeArg.row0; i <= cellRangeArg.row1; i++)
{
ICell cell = FetchCellFromSheet(rangeSheetName, i, cellRangeArg.col0);
if (lookupVal.returnType == FormulaReturnType.stringFormula)
{
if (cell.CellType == CellType.Numeric)
{
if (Math.Abs(cell.NumericCellValue - (double)lookupVal.floatValue) < 0.000001f)
{
Console.WriteLine("FOUND VALUE row " + i.ToString() + " cell \"" + (cell == null ? "null" : CellValueAsString(cell)) + "\"");
ICell returnCell = FetchCellFromSheet(rangeSheetName, i, valueCol);
if (returnCell == null)
{
return(null);
}
FormulaReturnValue retValue = new FormulaReturnValue();
if (returnCell.CellType == CellType.Numeric)
{
retValue.returnType = FormulaReturnType.floatFormula;
retValue.floatValue = (float)(returnCell.NumericCellValue);
return(retValue);
}
else
{
retValue.returnType = FormulaReturnType.stringFormula;
retValue.stringValue = CellValueAsString(returnCell);
return(retValue);
}
}
}
else
{
if (CellValueAsString(cell) == lookupVal.stringValue)
{
Console.WriteLine("FOUND STRING row " + i.ToString() + " cell \"" + (cell == null ? "null" : CellValueAsString(cell)) + "\"");
ICell returnCell = FetchCellFromSheet(rangeSheetName, i, valueCol);
if (returnCell == null)
{
return(null);
}
FormulaReturnValue retValue = new FormulaReturnValue();
if (returnCell.CellType == CellType.Numeric)
{
retValue.returnType = FormulaReturnType.floatFormula;
retValue.floatValue = (float)(returnCell.NumericCellValue);
return(retValue);
}
else
{
retValue.returnType = FormulaReturnType.stringFormula;
retValue.stringValue = CellValueAsString(returnCell);
return(retValue);
}
}
}
}
Console.WriteLine("searching row " + i.ToString() + " cell \"" + (cell == null ? "null" : CellValueAsString(cell)) + "\"");
}
return(null);
}
public FormulaReturnValue SUM(string arg)
{
CellArgument cellArg = ParseCellArgument(arg);
FormulaReturnValue retValue = new FormulaReturnValue();
retValue.returnType = FormulaReturnType.floatFormula;
switch (cellArg.caType)
{
case CellArgumentType.NA:
break;
case CellArgumentType.CAString:
{
Console.WriteLine("Invalid Cell Type " + cellArg.caType.ToString() + " \"" + arg + "\"");
retValue.floatValue = 0;
return(retValue);
}
break;
case CellArgumentType.CAValue:
retValue.floatValue = cellArg.val;
return(retValue);
break;
case CellArgumentType.CACell:
{
int row = cellArg.row0;
int col = cellArg.col0;
string sheetName = m_Sheet.SheetName;
ExcelFormulaEvaluator excelFormulaEvaluator = new ExcelFormulaEvaluator(m_WorkBook);
ICell cell = excelFormulaEvaluator.FetchCellFromSheet(sheetName, row, col);
bool valid = false;
float val = FetchNumericValueFromCell(cell, out valid);
if (!valid)
{
Console.WriteLine($"Could not numeric value cell {row},{col} from sheet \"{sheetName}\"");
retValue.floatValue = 0;
return(retValue);
}
retValue.floatValue = val;
return(retValue);
}
break;
case CellArgumentType.CARange:
{
float sum = 0;
string sheetName = m_Sheet.SheetName;
ExcelFormulaEvaluator excelFormulaEvaluator = new ExcelFormulaEvaluator(m_WorkBook);
for (int row = cellArg.row0; row <= cellArg.row1; row++)
{
for (int col = cellArg.col0; col <= cellArg.col1; col++)
{
ICell cell = excelFormulaEvaluator.FetchCellFromSheet(sheetName, row, col);
if (cell != null)
{
bool valid = false;
float val = FetchNumericValueFromCell(cell, out valid);
if (!valid)
{
Console.WriteLine($"Could not numeric value cell {row},{col} from sheet \"{sheetName}\"");
}
else
{
sum += val;
}
}
}
}
retValue.floatValue = sum;
return(retValue);
}
break;
case CellArgumentType.CASheetCell:
{
int row = cellArg.row0;
int col = cellArg.col0;
string sheetName = cellArg.sheetName;
ExcelFormulaEvaluator excelFormulaEvaluator = new ExcelFormulaEvaluator(m_WorkBook);
ICell cell = excelFormulaEvaluator.FetchCellFromSheet(sheetName, row, col);
bool valid = false;
float val = FetchNumericValueFromCell(cell, out valid);
if (!valid)
{
Console.WriteLine($"Could not numeric value cell {row},{col} from sheet \"{sheetName}\"");
retValue.floatValue = 0;
return(retValue);
}
retValue.floatValue = val;
return(retValue);
}
break;
case CellArgumentType.CASheetRange:
{
string sheetName = cellArg.sheetName;
float sum = 0;
ExcelFormulaEvaluator excelFormulaEvaluator = new ExcelFormulaEvaluator(m_WorkBook);
for (int row = cellArg.row0; row <= cellArg.row1; row++)
{
for (int col = cellArg.col0; col <= cellArg.col1; col++)
{
ICell cell = excelFormulaEvaluator.FetchCellFromSheet(sheetName, row, col);
if (cell != null)
{
bool valid = false;
float val = FetchNumericValueFromCell(cell, out valid);
if (!valid)
{
Console.WriteLine($"Could not numeric value cell {row},{col} from sheet \"{sheetName}\"");
}
else
{
sum += val;
}
}
}
}
retValue.floatValue = sum;
return(retValue);
}
break;
default:
break;
}
retValue.floatValue = 0;
return(retValue);
}
