public void FindDateFirstLine()
{
string filenamePath = @"TestData\Production NALBANT Jan-Jul16 2015.xls";
FileStream _fileStream = new FileStream(filenamePath, FileMode.Open,
FileAccess.Read);
IWorkbook _workbook = WorkbookFactory.Create(_fileStream);
_fileStream.Close();
//formulas of the Workbook are evaluated and an instance of a data formatter is created
IFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(_workbook);
DataFormatter dataFormatter = new HSSFDataFormatter(new CultureInfo("en-US"));
// End initialize
ISheet _worksheet = _workbook.GetSheet("Iul 2015");
IRow firstRow = _worksheet.GetRow(0);
int numrows = firstRow.LastCellNum;
DateTime now = DateTime.Now;
string result = " ";
foreach (ICell cell in firstRow)
{
if (cell.StringCellValue != "")
{
var Title = cell.StringCellValue;
var Date = Title.Substring(Title.Length - 10, 10);
DateTime dt = Convert.ToDateTime(Date);
int currentYear = now.Year;
int currentMonth = now.Month;
if (dt.Year == currentYear)
{
if (dt.Month == currentMonth)
{
result = Date;
break;
}
}
}
}
Assert.AreEqual(result, "31.07.2015");
}
public void TestExistingWorkbook()
{
HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);
HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0);
HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
Assert.AreEqual(CellType.Formula, lC1Cell.CellType);
HSSFFormulaEvaluator lMainWorkbookEvaluator = new HSSFFormulaEvaluator(mainWorkbook);
HSSFFormulaEvaluator lSourceEvaluator = new HSSFFormulaEvaluator(sourceWorkbook);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { MAIN_WORKBOOK_FILENAME, SOURCE_DUMMY_WORKBOOK_FILENAME },
new HSSFFormulaEvaluator[] { lMainWorkbookEvaluator, lSourceEvaluator });
Assert.AreEqual(CellType.Numeric, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell));
Assert.AreEqual(CellType.String, lMainWorkbookEvaluator.EvaluateFormulaCell(lB1Cell));
Assert.AreEqual(CellType.Boolean, lMainWorkbookEvaluator.EvaluateFormulaCell(lC1Cell));
Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("Apache rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(false, lC1Cell.BooleanCellValue);
}
private static void Process(IRow row, HSSFFormulaEvaluator eval)
{
IEnumerator it = row.GetEnumerator();
while (it.MoveNext())
{
ICell cell = (ICell)it.Current;
if (cell.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
continue;
}
FormulaRecordAggregate record = (FormulaRecordAggregate)((HSSFCell)cell).CellValueRecord;
FormulaRecord r = record.FormulaRecord;
Ptg[] ptgs = r.ParsedExpression;
String cellRef = new CellReference(row.RowNum, cell.ColumnIndex, false, false).FormatAsString();
#if !HIDE_UNREACHABLE_CODE
if (false && cellRef.Equals("BP24"))
{
Console.Write(cellRef);
Console.WriteLine(" - has " + ptgs.Length + " ptgs:");
for (int i = 0; i < ptgs.Length; i++)
{
String c = ptgs[i].GetType().ToString();
Console.WriteLine("\t" + c.Substring(c.LastIndexOf('.') + 1));
}
Console.WriteLine("-> " + cell.CellFormula);
}
#endif
NPOI.SS.UserModel.CellValue evalResult = eval.Evaluate(cell);
Assert.IsNotNull(evalResult);
}
}
public void TestInSpreadSheet()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula=("B1%");
row.CreateCell(1).SetCellValue(50.0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv;
try
{
cv = fe.Evaluate(cell);
}
catch (SystemException e)
{
if (e.InnerException is NullReferenceException)
{
throw new AssertionException("Identified bug 44608");
}
// else some other unexpected error
throw e;
}
Assert.AreEqual(CellType.NUMERIC, cv.CellType);
Assert.AreEqual(0.5, cv.NumberValue, 0.0);
}
public void TestEvaluate()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
HSSFWorkbook wb2 = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
CellReference cellRef = new CellReference(wb.GetName("QUANT").RefersToFormula);
ICell cell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
cell.SetCellValue(NEW_QUANT);
cell = wb2.GetSheet("CostSheet").GetRow(1).GetCell(1);
cell.SetCellValue(NEW_PART_COST);
HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator EvaluatorCost = new HSSFFormulaEvaluator(wb2);
String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" };
HSSFFormulaEvaluator[] Evaluators = { Evaluator, EvaluatorCost, };
HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators);
cellRef = new CellReference(wb.GetName("UNITCOST").RefersToFormula);
ICell uccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
cellRef = new CellReference(wb.GetName("COST").RefersToFormula);
ICell ccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
cellRef = new CellReference(wb.GetName("TOTALCOST").RefersToFormula);
ICell tccell = wb.GetSheet(cellRef.SheetName).GetRow(cellRef.Row).GetCell((int)cellRef.Col);
Evaluator.EvaluateFormulaCell(uccell);
Evaluator.EvaluateFormulaCell(ccell);
Evaluator.EvaluateFormulaCell(tccell);
Assert.AreEqual(NEW_PART_COST, uccell.NumericCellValue);
Assert.AreEqual(NEW_PART_COST * NEW_QUANT, ccell.NumericCellValue);
Assert.AreEqual(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.NumericCellValue);
}
public void TestFromFile()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("rank.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet example1 = (HSSFSheet)wb.GetSheet("Example 1");
HSSFCell ex1cell1 = (HSSFCell)example1.GetRow(7).GetCell(0);
Assert.AreEqual(3.0, fe.Evaluate(ex1cell1).NumberValue);
HSSFCell ex1cell2 = (HSSFCell)example1.GetRow(8).GetCell(0);
Assert.AreEqual(5.0, fe.Evaluate(ex1cell2).NumberValue);
HSSFSheet example2 = (HSSFSheet)wb.GetSheet("Example 2");
for (int rownum = 1; rownum <= 10; rownum++)
{
HSSFCell cell = (HSSFCell)example2.GetRow(rownum).GetCell(2);
double cachedResult = cell.NumericCellValue; //cached formula result
Assert.AreEqual(cachedResult, fe.Evaluate(cell).NumberValue);
}
HSSFSheet example3 = (HSSFSheet)wb.GetSheet("Example 3");
for (int rownum = 1; rownum <= 10; rownum++)
{
HSSFCell cellD = (HSSFCell)example3.GetRow(rownum).GetCell(3);
double cachedResultD = cellD.NumericCellValue; //cached formula result
Assert.AreEqual(cachedResultD, fe.Evaluate(cellD).NumberValue, new CellReference(cellD).FormatAsString());
HSSFCell cellE = (HSSFCell)example3.GetRow(rownum).GetCell(4);
double cachedResultE = cellE.NumericCellValue; //cached formula result
Assert.AreEqual(cachedResultE, fe.Evaluate(cellE).NumberValue, new CellReference(cellE).FormatAsString());
HSSFCell cellF = (HSSFCell)example3.GetRow(rownum).GetCell(5);
double cachedResultF = cellF.NumericCellValue; //cached formula result
Assert.AreEqual(cachedResultF, fe.Evaluate(cellF).NumberValue, new CellReference(cellF).FormatAsString());
}
}
[Ignore] //TestUnfixedBugs
public void Test49612()
{
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("49612.xls");
ISheet sh = wb.GetSheetAt(0);
IRow row = sh.GetRow(0);
ICell c1 = row.GetCell(2);
ICell d1 = row.GetCell(3);
ICell e1 = row.GetCell(2);
Assert.AreEqual("SUM(BOB+JIM)", c1.CellFormula);
// Problem 1: java.lang.ArrayIndexOutOfBoundsException in NPOI.HSSF.Model.LinkTable$ExternalBookBlock.GetNameText
Assert.AreEqual("SUM('49612.xls'!BOB+'49612.xls'!JIM)", d1.CellFormula);
//Problem 2
//junit.framework.ComparisonFailure:
//Expected :SUM('49612.xls'!BOB+'49612.xls'!JIM)
//Actual :SUM(BOB+JIM)
Assert.AreEqual("SUM('49612.xls'!BOB+'49612.xls'!JIM)", e1.CellFormula);
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
Assert.AreEqual(30.0, eval.Evaluate(c1).NumberValue, "Evaluating c1");
//Problem 3: java.lang.Exception: Unexpected arg eval type (NPOI.HSSF.Record.Formula.Eval.NameXEval)
Assert.AreEqual(30, eval.Evaluate(d1).NumberValue, "Evaluating d1");
Assert.AreEqual(30, eval.Evaluate(e1).NumberValue, "Evaluating e1");
}
public void TestEvaluateMissingArgs()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ISheet sheet = wb.CreateSheet("Sheet1");
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula=("if(true,)");
fe.ClearAllCachedResultValues();
CellValue cv;
try
{
cv = fe.Evaluate(cell);
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
throw new AssertionException("Missing args Evaluation not implemented (bug 43354");
}
// MissingArg -> BlankEval -> zero (as formula result)
Assert.AreEqual(0.0, cv.NumberValue, 0.0);
// MissingArg -> BlankEval -> empty string (in concatenation)
cell.CellFormula=("\"abc\"&if(true,)");
fe.ClearAllCachedResultValues();
Assert.AreEqual("abc", fe.Evaluate(cell).StringValue);
}
/// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.BLANK:
return string.Empty;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.ERROR:
return cell.ErrorCellValue.ToString();
case CellType.NUMERIC:
case CellType.Unknown:
default:
return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.STRING:
return cell.StringCellValue;
case CellType.FORMULA:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
public void TestNpvFromSpreadsheet()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("IrrNpvTestCaseData.xls");
ISheet sheet = wb.GetSheet("IRR-NPV");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
StringBuilder failures = new StringBuilder();
int failureCount = 0;
// TODO YK: Formulas in rows 16 and 17 operate with ArrayPtg which isn't yet supported
// FormulaEvaluator as of r1041407 throws "Unexpected ptg class (NPOI.SS.Formula.PTG.ArrayPtg)"
for (int rownum = 9; rownum <= 15; rownum++)
{
IRow row = sheet.GetRow(rownum);
ICell cellB = row.GetCell(1);
try
{
CellValue cv = fe.Evaluate(cellB);
assertFormulaResult(cv, cellB);
}
catch (Exception e)
{
if (failures.Length > 0) failures.Append('\n');
failures.Append("Row[" + (cellB.RowIndex + 1) + "]: " + cellB.CellFormula + " ");
failures.Append(e.Message);
failureCount++;
}
}
if (failures.Length > 0)
{
throw new AssertionException(failureCount + " IRR Evaluations failed:\n" + failures.ToString());
}
}
public void TestEvaluateInSheetExample2()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
sheet.CreateRow(1).CreateCell(0).SetCellValue(0.08d);
sheet.CreateRow(2).CreateCell(0).SetCellValue(-40000d);
sheet.CreateRow(3).CreateCell(0).SetCellValue(8000d);
sheet.CreateRow(4).CreateCell(0).SetCellValue(9200d);
sheet.CreateRow(5).CreateCell(0).SetCellValue(10000d);
sheet.CreateRow(6).CreateCell(0).SetCellValue(12000d);
sheet.CreateRow(7).CreateCell(0).SetCellValue(14500d);
ICell cell = row.CreateCell(8);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
// Enumeration
cell.CellFormula = ("NPV(A2, A4,A5,A6,A7,A8)+A3");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
double res = cell.NumericCellValue;
Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
// Range
cell.CellFormula = ("NPV(A2, A4:A8)+A3");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cell);
res = cell.NumericCellValue;
Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
}
public void TestClean1()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
String[] asserts = {
"aniket\u0007\u0017\u0019", "aniket",
"\u0011aniket\u0007\u0017\u0010", "aniket",
"\u0011aniket\u0007\u0017\u007F", "aniket\u007F",
"\u2116aniket\u2211\uFB5E\u2039", "\u2116aniket\u2211\uFB5E\u2039",
};
for (int i = 0; i < asserts.Length; i += 2)
{
String formulaText = "CLEAN(\"" + asserts[i] + "\")";
ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
}
asserts = new String[] {
"CHAR(7)&\"text\"&CHAR(7)", "text",
"CHAR(7)&\"text\"&CHAR(17)", "text",
"CHAR(181)&\"text\"&CHAR(190)", "\u00B5text\u00BE",
"\"text\"&CHAR(160)&\"'\"", "text\u00A0'",
};
for (int i = 0; i < asserts.Length; i += 2)
{
String formulaText = "CLEAN(" + asserts[i] + ")";
ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
}
}
public void Test27349()
{
// 27349-vLookupAcrossSheets.xls is bugzilla/attachment.cgi?id=10622
Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vLookupAcrossSheets.xls");
HSSFWorkbook wb;
try
{
// original bug may have thrown exception here, or output warning to
// stderr
wb = new HSSFWorkbook(is1);
}
catch (IOException e)
{
throw new SystemException(e.Message);
}
ISheet sheet = wb.GetSheetAt(0);
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(0);
// this defInitely would have failed due to 27349
Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell
.CellFormula);
// We might as well Evaluate the formula
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.Numeric, cv.CellType);
Assert.AreEqual(3.0, cv.NumberValue, 0.0);
}
public void SetUp()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("new sheet");
cell11 = sheet.CreateRow(0).CreateCell(0);
cell11.SetCellType(CellType.FORMULA);
Evaluator = new HSSFFormulaEvaluator(wb);
}
public void SetUp()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("new sheet");
cell11 = sheet.CreateRow(0).CreateCell(0);
cell11.SetCellType(CellType.FORMULA);
Evaluator = new HSSFFormulaEvaluator(wb);
}
private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
int expectedResult)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.NUMERIC);
Assert.AreEqual(expectedResult, result.NumberValue, 0.0);
}
private static void ConfirmError(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
int expectedErrorCode)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.ERROR);
Assert.AreEqual(expectedErrorCode, result.ErrorValue);
}
private static void ConfirmResult(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
String expectedResult)
{
cell.CellFormula=(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(result.CellType, CellType.STRING);
Assert.AreEqual(expectedResult, result.StringValue);
}
public void TestEvaluateSimple()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestNames.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
ICell cell = sheet.GetRow(8).GetCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.Numeric, cv.CellType);
Assert.AreEqual(3.72, cv.NumberValue, 0.0);
}
public void TestBasic()
{
HSSFWorkbook wbA = CreateWBA();
ICell c = wbA.GetSheetAt(0).CreateRow(5).CreateCell(2);
HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA);
// non-error cases
Confirm(feA, c, "INDIRECT(\"C2\")", 23);
Confirm(feA, c, "INDIRECT(\"$C2\")", 23);
Confirm(feA, c, "INDIRECT(\"C$2\")", 23);
Confirm(feA, c, "SUM(INDIRECT(\"Sheet2!B1:C3\"))", 351); // area ref
Confirm(feA, c, "SUM(INDIRECT(\"Sheet2! B1 : C3 \"))", 351); // spaces in area ref
Confirm(feA, c, "SUM(INDIRECT(\"'John''s sales'!A1:C1\"))", 93); // special chars in sheet name
Confirm(feA, c, "INDIRECT(\"'Sheet1'!B3\")", 32); // redundant sheet name quotes
Confirm(feA, c, "INDIRECT(\"sHeet1!B3\")", 32); // case-insensitive sheet name
Confirm(feA, c, "INDIRECT(\" D3 \")", 34); // spaces around cell ref
Confirm(feA, c, "INDIRECT(\"Sheet1! D3 \")", 34); // spaces around cell ref
Confirm(feA, c, "INDIRECT(\"A1\", TRUE)", 11); // explicit arg1. only TRUE supported so far
Confirm(feA, c, "INDIRECT(\"A1:G1\")", 13); // de-reference area ref (note formula is in C4)
Confirm(feA, c, "SUM(INDIRECT(A4))", 50); // indirect defined name
Confirm(feA, c, "SUM(INDIRECT(B4))", 351); // indirect defined name pointinh to other sheet
// simple error propagation:
// arg0 is Evaluated to text first
Confirm(feA, c, "INDIRECT(#DIV/0!)", ErrorEval.DIV_ZERO);
Confirm(feA, c, "INDIRECT(#DIV/0!)", ErrorEval.DIV_ZERO);
Confirm(feA, c, "INDIRECT(#NAME?, \"x\")", ErrorEval.NAME_INVALID);
Confirm(feA, c, "INDIRECT(#NUM!, #N/A)", ErrorEval.NUM_ERROR);
// arg1 is Evaluated to bool before arg0 is decoded
Confirm(feA, c, "INDIRECT(\"garbage\", #N/A)", ErrorEval.NA);
Confirm(feA, c, "INDIRECT(\"garbage\", \"\")", ErrorEval.VALUE_INVALID); // empty string is not valid bool
Confirm(feA, c, "INDIRECT(\"garbage\", \"flase\")", ErrorEval.VALUE_INVALID); // must be "TRUE" or "FALSE"
// spaces around sheet name (with or without quotes Makes no difference)
Confirm(feA, c, "INDIRECT(\"'Sheet1 '!D3\")", ErrorEval.REF_INVALID);
Confirm(feA, c, "INDIRECT(\" Sheet1!D3\")", ErrorEval.REF_INVALID);
Confirm(feA, c, "INDIRECT(\"'Sheet1' !D3\")", ErrorEval.REF_INVALID);
Confirm(feA, c, "SUM(INDIRECT(\"'John's sales'!A1:C1\"))", ErrorEval.REF_INVALID); // bad quote escaping
Confirm(feA, c, "INDIRECT(\"[Book1]Sheet1!A1\")", ErrorEval.REF_INVALID); // unknown external workbook
Confirm(feA, c, "INDIRECT(\"Sheet3!A1\")", ErrorEval.REF_INVALID); // unknown sheet
#if !HIDE_UNREACHABLE_CODE
if (false)
{ // TODO - support Evaluation of defined names
Confirm(feA, c, "INDIRECT(\"Sheet1!IW1\")", ErrorEval.REF_INVALID); // bad column
Confirm(feA, c, "INDIRECT(\"Sheet1!A65537\")", ErrorEval.REF_INVALID); // bad row
}
#endif
Confirm(feA, c, "INDIRECT(\"Sheet1!A 1\")", ErrorEval.REF_INVALID); // space in cell ref
}
/**
* Translates StackOverflowError into AssertionFailedError
*/
private static CellValue EvaluateWithCycles(HSSFWorkbook wb, ICell testCell)
{
HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(wb);
try
{
return Evaluator.Evaluate(testCell);
}
catch (StackOverflowException)
{
throw new AssertionException("circular reference caused stack overflow error");
}
}
public void TestRegisterInRuntime()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1");
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFCell cellA = (HSSFCell)row.CreateCell(0);
cellA.CellFormula = ("FISHER(A5)");
CellValue cv;
try
{
//NPOI
//Run it twice in NUnit Gui Window, the first passed but the second failed.
//Maybe the function was cached. Ignore it.
cv = fe.Evaluate(cellA);
Assert.Fail("expectecd exception");
}
catch (NotImplementedException)
{
;
}
FunctionEval.RegisterFunction("FISHER", new Function1());/*Function() {
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex, int srcColumnIndex) {
return ErrorEval.NA;
}
});*/
cv = fe.Evaluate(cellA);
Assert.AreEqual(ErrorEval.NA.ErrorCode, cv.ErrorValue);
HSSFCell cellB = (HSSFCell)row.CreateCell(1);
cellB.CellFormula = ("CUBEMEMBERPROPERTY(A5)");
try
{
cv = fe.Evaluate(cellB);
Assert.Fail("expectecd exception");
}
catch (NotImplementedException)
{
;
}
AnalysisToolPak.RegisterFunction("CUBEMEMBERPROPERTY", new FreeRefFunction1());/*FreeRefFunction() {
public ValueEval Evaluate(ValueEval[] args, OperationEvaluationContext ec) {
return ErrorEval.NUM_ERROR;
}
});*/
cv = fe.Evaluate(cellB);
Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, cv.ErrorValue);
}
public void TestEvaluate()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("externalFunctionExample.xls");
ISheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ConfirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0 / 90.0);
ConfirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0);
ConfirmCellEval(sheet, 2, 0, fe, "YEARFRAC(B3,C3,D3)", 0.0);
ConfirmCellEval(sheet, 3, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6);
ConfirmCellEval(sheet, 4, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2);
}
public void SetUp()
{
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("new sheet");
style = wb.CreateCellStyle();
IDataFormat fmt = wb.CreateDataFormat();
style.DataFormat=(fmt.GetFormat("hh:mm:ss"));
cell11 = sheet.CreateRow(0).CreateCell(0);
form = new DataFormatter();
Evaluator = new HSSFFormulaEvaluator(wb);
}
public void TestEvaluateMultipleWorkbooks()
{
HSSFWorkbook wbA = HSSFTestDataSamples.OpenSampleWorkbook("multibookFormulaA.xls");
HSSFWorkbook wbB = HSSFTestDataSamples.OpenSampleWorkbook("multibookFormulaB.xls");
HSSFFormulaEvaluator EvaluatorA = new HSSFFormulaEvaluator(wbA);
HSSFFormulaEvaluator EvaluatorB = new HSSFFormulaEvaluator(wbB);
// Hook up the workbook Evaluators to enable Evaluation of formulas across books
String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
HSSFFormulaEvaluator[] Evaluators = { EvaluatorA, EvaluatorB, };
HSSFFormulaEvaluator.SetupEnvironment(bookNames, Evaluators);
ICell cell;
ISheet aSheet1 = wbA.GetSheetAt(0);
ISheet bSheet1 = wbB.GetSheetAt(0);
// Simple case - single link from wbA to wbB
ConfirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
cell = aSheet1.GetRow(0).GetCell(0);
ConfirmEvaluation(35, EvaluatorA, cell);
// more complex case - back link into wbA
// [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
ConfirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
// [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
ConfirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");
cell = aSheet1.GetRow(1).GetCell(0);
ConfirmEvaluation(264, EvaluatorA, cell);
// change [wbB]BSheet1!B3 (from 50 to 60)
ICell cellB3 = bSheet1.GetRow(2).GetCell(1);
cellB3.SetCellValue(60);
EvaluatorB.NotifyUpdateCell(cellB3);
ConfirmEvaluation(274, EvaluatorA, cell);
// change [wbA]ASheet1!A3 (from 100 to 80)
ICell cellA3 = aSheet1.GetRow(2).GetCell(0);
cellA3.SetCellValue(80);
EvaluatorA.NotifyUpdateCell(cellA3);
ConfirmEvaluation(234, EvaluatorA, cell);
// change [wbA]AnotherSheet!A1 (from 2 to 3)
ICell cellA1 = wbA.GetSheetAt(1).GetRow(0).GetCell(0);
cellA1.SetCellValue(3);
EvaluatorA.NotifyUpdateCell(cellA1);
ConfirmEvaluation(235, EvaluatorA, cell);
}
public void TestCountFuncs()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ISheet sheet = wb.CreateSheet("Sheet1");
ICell cell = sheet.CreateRow(0).CreateCell(0);
cell.CellFormula=("COUNT(C5,,,,)"); // 4 missing args, C5 is blank
Assert.AreEqual(4.0, fe.Evaluate(cell).NumberValue, 0.0);
cell.CellFormula=("COUNTA(C5,,)"); // 2 missing args, C5 is blank
fe.ClearAllCachedResultValues();
Assert.AreEqual(2.0, fe.Evaluate(cell).NumberValue, 0.0);
}
public void SetUp()
{
HSSFWorkbook wb = new HSSFWorkbook();
try
{
HSSFSheet sheet = wb.CreateSheet("new sheet") as HSSFSheet;
cell11 = sheet.CreateRow(0).CreateCell(0) as HSSFCell;
cell11.SetCellType(CellType.Formula);
Evaluator = new HSSFFormulaEvaluator(wb);
}
finally
{
//wb.Close();
}
}
private static void Process(HSSFWorkbook wb)
{
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(i);
IEnumerator it = s.GetRowEnumerator();
while (it.MoveNext())
{
IRow r = (IRow)it.Current;
Process(r, eval);
}
}
}
public void TestFromFile()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("finance.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
ISheet example1 = wb.GetSheet("IPMT");
ICell ex1cell1 = example1.GetRow(6).GetCell(0);
fe.Evaluate(ex1cell1);
Assert.AreEqual(-22.41, ex1cell1.NumericCellValue, 0.1);
ICell ex1cell2 = example1.GetRow(7).GetCell(0);
fe.Evaluate(ex1cell2);
Assert.AreEqual(-292.45, ex1cell2.NumericCellValue, 0.1);
}
public void TestAll()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("yearfracExamples.xls");
ISheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb);
int nSuccess = 0;
int nFailures = 0;
int nUnexpectedErrors = 0;
IEnumerator rowIterator = sheet.GetRowEnumerator();
while (rowIterator.MoveNext())
{
IRow row = (IRow)rowIterator.Current;
ICell cell = row.GetCell(SS.YEARFRAC_FORMULA_COLUMN);
if (cell == null || cell.CellType != CellType.FORMULA)
{
continue;
}
try
{
ProcessRow(row, cell, formulaEvaluator);
nSuccess++;
}
catch (SystemException e)
{
nUnexpectedErrors++;
printshortStackTrace(System.Console.Error, e);
}
catch (AssertionException e)
{
nFailures++;
printshortStackTrace(System.Console.Error, e);
}
}
if (nUnexpectedErrors + nFailures > 0)
{
String msg = nFailures + " failures(s) and " + nUnexpectedErrors
+ " unexpected errors(s) occurred. See stderr for details";
throw new AssertionException(msg);
}
if (nSuccess < 1)
{
throw new Exception("No Test sample cases found");
}
}
public override void EvaluateAll()
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(_book, this);
}
public void EvaluateAll()
{
HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook, this);
}