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));
}
}
public float FetchNumericValueFromCell(ICell cell, out bool valid)
{
valid = false;
ExcelFormulaEvaluator excelFormulaEvaluator = new ExcelFormulaEvaluator(m_WorkBook);
if (cell == null)
{
Console.WriteLine("cell is null");
return(0f);
}
if (cell.CellType != CellType.Numeric)
{
Console.WriteLine("cell is not numeric");
return(0);
}
valid = true;
return((float)(cell.NumericCellValue));
}
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 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);
}