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);
}
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 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 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);
}
[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 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 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);
}
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,
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 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 TestRangeUsingOffsetFunc_bug46948()
{
HSSFWorkbook wb = new HSSFWorkbook();
IRow row = wb.CreateSheet("Sheet1").CreateRow(0);
ICell cellA1 = row.CreateCell(0);
ICell cellB1 = row.CreateCell(1);
row.CreateCell(2).SetCellValue(5.0); // C1
row.CreateCell(3).SetCellValue(7.0); // D1
row.CreateCell(4).SetCellValue(9.0); // E1
cellA1.CellFormula = ("SUM(C1:OFFSET(C1,0,B1))");
cellB1.SetCellValue(1.0); // range will be C1:D1
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv;
try
{
cv = fe.Evaluate(cellA1);
}
catch (ArgumentException e)
{
if (e.Message.Equals("Unexpected ref arg class (NPOI.SS.Formula.LazyAreaEval)"))
{
throw new AssertionException("Identified bug 46948");
}
throw e;
}
Assert.AreEqual(12.0, cv.NumberValue, 0.0);
cellB1.SetCellValue(2.0); // range will be C1:E1
fe.NotifyUpdateCell(cellB1);
cv = fe.Evaluate(cellA1);
Assert.AreEqual(21.0, cv.NumberValue, 0.0);
cellB1.SetCellValue(0.0); // range will be C1:C1
fe.NotifyUpdateCell(cellB1);
cv = fe.Evaluate(cellA1);
Assert.AreEqual(5.0, cv.NumberValue, 0.0);
}
/**
* 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 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());
}
}
public void Test3DArea()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet1 = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
wb.CreateSheet();
wb.SetSheetName(1, "Sheet2");
IRow row = sheet1.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.CellFormula=("isblank(Sheet2!A1:A1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
cell.CellFormula=("isblank(D7:D7)");
result = fe.Evaluate(cell);
Assert.AreEqual(CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
}
public void TestBug43093()
{
HSSFWorkbook xlw = new HSSFWorkbook();
AddNewSheetWithCellsA1toD4(xlw, 1);
AddNewSheetWithCellsA1toD4(xlw, 2);
AddNewSheetWithCellsA1toD4(xlw, 3);
AddNewSheetWithCellsA1toD4(xlw, 4);
NPOI.SS.UserModel.ISheet s2 = xlw.GetSheet("s2");
IRow s2r3 = s2.GetRow(3);
ICell s2E4 = s2r3.CreateCell(4);
s2E4.CellFormula = ("SUM(s3!B2:C3)");
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(xlw);
double d = eva.Evaluate(s2E4).NumberValue;
// internalEvaluate(...) Area3DEval.: 311+312+321+322 expected
Assert.AreEqual(d, (311 + 312 + 321 + 322), 0.0000001);
// System.out.println("Area3DEval ok.: 311+312+321+322=" + d);
}
private static void ConfirmCellEval(ISheet sheet, int rowIx, int colIx,
HSSFFormulaEvaluator fe, String expectedFormula, double expectedResult)
{
ICell cell = sheet.GetRow(rowIx).GetCell(colIx);
Assert.AreEqual(expectedFormula, cell.CellFormula);
CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell((short)0);
cell.CellFormula = ("SUM(A32769:A32770)");
// put some values in the cells to make the evaluation more interesting
sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);
//HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
NPOI.SS.UserModel.CellValue result;
try
{
result = fe.Evaluate(cell);
}
catch (Exception e)
{
if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
{
Assert.Fail("Identifed bug 44539");
}
throw;
}
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, result.CellType);
Assert.AreEqual(42.0, result.NumberValue, 0.0);
}
private static void ConfirmCellEvaluation(IWorkbook wb, ICell cell, double expectedValue)
{
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.NUMERIC, cv.CellType);
Assert.AreEqual(expectedValue, cv.NumberValue, 0.0);
}
public void Test42448()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("Sheet1");
IRow row = sheet1.CreateRow(0);
ICell cell = row.CreateCell(0);
// it's important to create the referenced sheet first
ISheet sheet2 = wb.CreateSheet("A"); // note name 'A'
// TODO - POI crashes if the formula is Added before this sheet
// RuntimeException("Zero length string is an invalid sheet name")
// Excel doesn't crash but the formula doesn't work until it is
// re-entered
String inputFormula = "SUMPRODUCT(A!C7:A!C67, B8:B68) / B69"; // as per bug report
try
{
cell.CellFormula = (inputFormula);
}
catch (IndexOutOfRangeException)
{
throw new AssertionException("Identified bug 42448");
}
Assert.AreEqual("SUMPRODUCT(A!C7:A!C67,B8:B68)/B69", cell.CellFormula);
// might as well Evaluate the sucker...
AddCell(sheet2, 5, 2, 3.0); // A!C6
AddCell(sheet2, 6, 2, 4.0); // A!C7
AddCell(sheet2, 66, 2, 5.0); // A!C67
AddCell(sheet2, 67, 2, 6.0); // A!C68
AddCell(sheet1, 6, 1, 7.0); // B7
AddCell(sheet1, 7, 1, 8.0); // B8
AddCell(sheet1, 67, 1, 9.0); // B68
AddCell(sheet1, 68, 1, 10.0); // B69
double expectedResult = (4.0 * 8.0 + 5.0 * 9.0) / 10.0;
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.Numeric, cv.CellType);
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
private int ProcessTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName)
{
ISheet sheet = workbook.GetSheetAt(sheetIndex);
HSSFFormulaEvaluator Evaluator = new HSSFFormulaEvaluator(workbook);
int maxRows = sheet.LastRowNum + 1;
int result = Result.NO_EVALUATIONS_FOUND; // so far
String currentGroupComment = null;
for (int rowIndex = SS.START_TEST_CASES_ROW_INDEX; rowIndex < maxRows; rowIndex++)
{
IRow r = sheet.GetRow(rowIndex);
String newMarkerValue = GetMarkerColumnValue(r);
if (r == null)
{
continue;
}
if (SS.TEST_CASES_END_MARKER.Equals(newMarkerValue, StringComparison.OrdinalIgnoreCase))
{
// normal exit point
return result;
}
if (SS.SKIP_CURRENT_TEST_CASE_MARKER.Equals(newMarkerValue, StringComparison.OrdinalIgnoreCase))
{
// currently disabled Test case row
continue;
}
if (newMarkerValue != null)
{
currentGroupComment = newMarkerValue;
}
ICell c = r.GetCell(SS.COLUMN_INDEX_EVALUATION);
if (c == null || c.CellType != CellType.FORMULA)
{
continue;
}
CellValue actualValue = Evaluator.Evaluate(c);
ICell expectedValueCell = r.GetCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
String rowComment = GetRowCommentColumnValue(r);
String msgPrefix = FormatTestCaseDetails(sheetName, r.RowNum, c, currentGroupComment, rowComment);
try
{
ConfirmExpectedResult(msgPrefix, expectedValueCell, actualValue);
_EvaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (RuntimeException e)
{
_EvaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
catch (AssertionException e)
{
_EvaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
throw new Exception("Missing end marker '" + SS.TEST_CASES_END_MARKER
+ "' on sheet '" + sheetName + "'");
}
public void TestFromFile()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("sumifs.xls");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
HSSFSheet example1 = (HSSFSheet)wb.GetSheet("Example 1");
HSSFCell ex1cell1 = (HSSFCell)example1.GetRow(10).GetCell(2);
fe.Evaluate(ex1cell1);
Assert.AreEqual(20.0, ex1cell1.NumericCellValue);
HSSFCell ex1cell2 = (HSSFCell)example1.GetRow(11).GetCell(2);
fe.Evaluate(ex1cell2);
Assert.AreEqual(30.0, ex1cell2.NumericCellValue);
HSSFSheet example2 = (HSSFSheet)wb.GetSheet("Example 2");
HSSFCell ex2cell1 = (HSSFCell)example2.GetRow(6).GetCell(2);
fe.Evaluate(ex2cell1);
Assert.AreEqual(500.0, ex2cell1.NumericCellValue);
HSSFCell ex2cell2 = (HSSFCell)example2.GetRow(7).GetCell(2);
fe.Evaluate(ex2cell2);
Assert.AreEqual(8711.0, ex2cell2.NumericCellValue);
HSSFSheet example3 = (HSSFSheet)wb.GetSheet("Example 3");
HSSFCell ex3cell = (HSSFCell)example3.GetRow(5).GetCell(2);
fe.Evaluate(ex3cell);
Assert.AreEqual(8, 8, ex3cell.NumericCellValue);
HSSFSheet example4 = (HSSFSheet)wb.GetSheet("Example 4");
HSSFCell ex4cell = (HSSFCell)example4.GetRow(8).GetCell(2);
fe.Evaluate(ex4cell);
Assert.AreEqual(3.5, ex4cell.NumericCellValue);
HSSFSheet example5 = (HSSFSheet)wb.GetSheet("Example 5");
HSSFCell ex5cell = (HSSFCell)example5.GetRow(8).GetCell(2);
fe.Evaluate(ex5cell);
Assert.AreEqual(625000.0, ex5cell.NumericCellValue);
}
public void Test51024()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet s = wb.CreateSheet();
IRow r1 = s.CreateRow(0);
IRow r2 = s.CreateRow(1);
r1.CreateCell(0).SetCellValue("v A1");
r2.CreateCell(0).SetCellValue("v A2");
r1.CreateCell(1).SetCellValue("v B1");
ICell c = r1.CreateCell(4);
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
c.CellFormula = ("VLOOKUP(\"v A1\", A1:B2, 1)");
Assert.AreEqual("v A1", eval.Evaluate(c).StringValue);
c.CellFormula = ("VLOOKUP(\"v A1\", A1:B2, 1, 1)");
Assert.AreEqual("v A1", eval.Evaluate(c).StringValue);
c.CellFormula = ("VLOOKUP(\"v A1\", A1:B2, 1, )");
Assert.AreEqual("v A1", eval.Evaluate(c).StringValue);
c.CellFormula = ("HLOOKUP(\"v A1\", A1:B2, 1)");
Assert.AreEqual("v A1", eval.Evaluate(c).StringValue);
c.CellFormula = ("HLOOKUP(\"v A1\", A1:B2, 1, 1)");
Assert.AreEqual("v A1", eval.Evaluate(c).StringValue);
c.CellFormula = ("HLOOKUP(\"v A1\", A1:B2, 1, )");
Assert.AreEqual("v A1", eval.Evaluate(c).StringValue);
}
public void TestDefinedNameWithComplexFlag_bug47048()
{
// Mock up a spreadsheet to match the critical details of the sample
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("Input");
IName definedName = wb.CreateName();
definedName.NameName = ("Is_Multicar_Vehicle");
definedName.RefersToFormula = ("Input!$B$17:$G$17");
// Set up some data and the formula
IRow row17 = sheet.CreateRow(16);
row17.CreateCell(0).SetCellValue(25.0);
row17.CreateCell(1).SetCellValue(1.33);
row17.CreateCell(2).SetCellValue(4.0);
IRow row = sheet.CreateRow(0);
ICell cellA1 = row.CreateCell(0);
cellA1.CellFormula = ("SUM(Is_Multicar_Vehicle)");
// Set the complex flag - POI doesn't usually manipulate this flag
NameRecord nameRec = TestHSSFName.GetNameRecord(definedName);
nameRec.OptionFlag = (short)0x10; // 0x10 -> complex
HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
CellValue value;
try
{
value = hsf.Evaluate(cellA1);
}
catch (Exception e)
{
if (e.Message.Equals("Don't now how to evalate name 'Is_Multicar_Vehicle'"))
{
throw new AssertionException("Identified bug 47048a");
}
throw e;
}
Assert.AreEqual(CellType.Numeric, value.CellType);
Assert.AreEqual(5.33, value.NumberValue, 0.0);
}
public void Test27405()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("input");
// input row 0
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell = row.CreateCell(1);
cell.SetCellValue(1); // B1
// input row 1
row = sheet.CreateRow(1);
cell = row.CreateCell(1);
cell.SetCellValue(999); // B2
int rno = 4;
row = sheet.CreateRow(rno);
cell = row.CreateCell(1); // B5
cell.CellFormula = ("isnumber(b1)");
cell = row.CreateCell(3); // D5
cell.CellFormula = ("IF(ISNUMBER(b1),b1,b2)");
#if !HIDE_UNREACHABLE_CODE
if (false)
{ // Set true to check excel file manually
// bug report mentions 'Editing the formula in excel "fixes" the problem.'
try
{
FileStream fileOut = new FileStream("27405output.xls", FileMode.Create);
wb.Write(fileOut);
fileOut.Close();
}
catch (IOException e)
{
throw new SystemException(e.Message);
}
}
#endif
// use POI's Evaluator as an extra sanity check
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv;
cv = fe.Evaluate(cell);
Assert.AreEqual(CellType.Numeric, cv.CellType);
Assert.AreEqual(1.0, cv.NumberValue, 0.0);
cv = fe.Evaluate(row.GetCell(1));
Assert.AreEqual(CellType.Boolean, cv.CellType);
Assert.AreEqual(true, cv.BooleanValue);
}
public void TestXRefs()
{
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalc.xls");
IWorkbook wbData = HSSFTestDataSamples.OpenSampleWorkbook("XRefCalcData.xls");
ICell cell;
// VLookup on a name in another file
cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("VLOOKUP(PART,COSTS,2,FALSE)", cell.CellFormula);
// Simple reference to a name in another file
cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// WARNING - this is wrong!
// The file name should be Showing, but bug #45970 is fixed
// we seem to loose it
Assert.AreEqual("Cost*Markup_Cost", cell.CellFormula);
// Evaluate the cells
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData)
}
);
eval.EvaluateFormulaCell(
wb.GetSheetAt(0).GetRow(1).GetCell(2)
);
eval.EvaluateFormulaCell(
wb.GetSheetAt(0).GetRow(1).GetCell(4)
);
// Re-check VLOOKUP one
cell = wb.GetSheetAt(0).GetRow(1).GetCell(2);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(12.30, cell.NumericCellValue, 0.0001);
// Re-check ref one
cell = wb.GetSheetAt(0).GetRow(1).GetCell(4);
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(CellType.Numeric, cell.CachedFormulaResultType);
Assert.AreEqual(36.90, cell.NumericCellValue, 0.0001);
// Add a formula that refers to one of the existing external workbooks
cell = wb.GetSheetAt(0).GetRow(1).CreateCell(40);
cell.CellFormula = (/*setter*/"Cost*[XRefCalcData.xls]MarkupSheet!$B$1");
// Check is was stored correctly
Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);
// Check it Evaluates correctly
eval.EvaluateFormulaCell(cell);
Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue);
// Try to add a formula for a new external workbook, won't be allowed to start
try
{
cell = wb.GetSheetAt(0).GetRow(1).CreateCell(42);
cell.CellFormula = (/*setter*/"[alt.xls]Sheet0!$A$1");
Assert.Fail("New workbook not linked, shouldn't be able to Add");
}
catch (Exception e) { }
// Link our new workbook
HSSFWorkbook alt = new HSSFWorkbook();
alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
wb.LinkExternalWorkbook("alt.xls", alt);
// Now add a formula that refers to our new workbook
cell.CellFormula = (/*setter*/"[alt.xls]Sheet0!$A$1");
Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);
// Evaluate it, without a link to that workbook
try
{
eval.Evaluate(cell);
Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate");
}
catch (Exception e) { }
// Add a link, check it does
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData),
new HSSFFormulaEvaluator(alt)
}
);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual("In another workbook", cell.StringCellValue);
// Save and re-load
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook);
eval = new HSSFFormulaEvaluator(wb);
HSSFFormulaEvaluator.SetupEnvironment(
new String[] { "XRefCalc.xls", "XRefCalcData.xls", "alt.xls" },
new HSSFFormulaEvaluator[] {
eval,
new HSSFFormulaEvaluator(wbData),
new HSSFFormulaEvaluator(alt)
}
);
// Check the one referring to the previously existing workbook behaves
cell = wb.GetSheetAt(0).GetRow(1).GetCell(40);
Assert.AreEqual("Cost*[XRefCalcData.xls]MarkupSheet!$B$1", cell.CellFormula);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual(24.60 * 1.8, cell.NumericCellValue);
// Now check the newly Added reference
cell = wb.GetSheetAt(0).GetRow(1).GetCell(42);
Assert.AreEqual("[alt.xls]Sheet0!$A$1", cell.CellFormula);
eval.EvaluateFormulaCell(cell);
Assert.AreEqual("In another workbook", cell.StringCellValue);
}
public void bug49612_part()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("49612.xls");
HSSFSheet sh = wb.GetSheetAt(0) as HSSFSheet;
HSSFRow row = sh.GetRow(0) as HSSFRow;
HSSFCell c1 = row.GetCell(2) as HSSFCell;
HSSFCell d1 = row.GetCell(3) as HSSFCell;
HSSFCell e1 = row.GetCell(2) as HSSFCell;
Assert.AreEqual("SUM(BOB+JIM)", c1.CellFormula);
// Problem 1: See TestUnfixedBugs#test49612()
// Problem 2: TestUnfixedBugs#test49612()
// Problem 3: These used to fail, now pass
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
Assert.AreEqual(30.0, eval.Evaluate(c1).NumberValue, 0.001, "Evaluating c1");
Assert.AreEqual(30.0, eval.Evaluate(d1).NumberValue, 0.001, "Evaluating d1");
Assert.AreEqual(30.0, eval.Evaluate(e1).NumberValue, 0.001, "Evaluating e1");
}
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);
}
}
private static void ProcessRow(IRow row, ICell cell, HSSFFormulaEvaluator formulaEvaluator)
{
double startDate = MakeDate(row, SS.START_YEAR_COLUMN);
double endDate = MakeDate(row, SS.END_YEAR_COLUMN);
int basis = GetIntCell(row, SS.BASIS_COLUMN);
double expectedValue = GetDoubleCell(row, SS.EXPECTED_RESULT_COLUMN);
double actualValue;
try
{
actualValue = YearFracCalculator.Calculate(startDate, endDate, basis);
}
catch (EvaluationException e)
{
throw e;
}
if (expectedValue != actualValue)
{
throw new Exception("Direct calculate failed - row " + (row.RowNum + 1) +
"excepted value " + expectedValue.ToString() + "actual value " + actualValue.ToString());
}
actualValue = formulaEvaluator.Evaluate(cell).NumberValue;
if (expectedValue != actualValue)
{
throw new Exception("Formula Evaluate failed - row " + (row.RowNum + 1) +
"excepted value " + expectedValue.ToString() + "actual value " + actualValue.ToString());
}
}
/**
*
* @return a constant from the local Result class denoting whether there were any Evaluation
* cases, and whether they all succeeded.
*/
private int ProcessFunctionRow(HSSFFormulaEvaluator Evaluator, String targetFunctionName,
IRow formulasRow, IRow expectedValuesRow)
{
int result = Result.NO_EVALUATIONS_FOUND; // so far
short endcolnum = (short)formulasRow.LastCellNum;
// iterate across the row for all the Evaluation cases
for (int colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
{
ICell c = formulasRow.GetCell(colnum);
if (c == null || c.CellType != CellType.FORMULA)
{
continue;
}
CellValue actualValue = Evaluator.Evaluate(c);
ICell expectedValueCell = GetExpectedValueCell(expectedValuesRow, colnum);
try
{
ConfirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.CellFormula + " @ " + formulasRow.RowNum + ":" + colnum,
expectedValueCell, actualValue);
_EvaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (AssertionException e)
{
_EvaluationFailureCount++;
printshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
return result;
}
