public void TestBug55843b()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
cellB1.SetCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(Evaluate);
Assert.AreEqual("2", Evaluate.FormatAsString());
cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(EvaluateN);
Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
Assert.AreEqual("2", EvaluateN.FormatAsString());
}
finally
{
wb.Close();
}
}
private static AssertionException wrongTypeError(String msgPrefix, ICell expectedCell, CellValue actualValue)
{
return(new AssertionException(msgPrefix + " Result type mismatch. Evaluated result was "
+ actualValue.FormatAsString()
+ " but the expected result was "
+ formatValue(expectedCell)
));
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
ErrorEval expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula = (formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Error)
{
throw new AssertionException("expected error cell type but got " + cv.FormatAsString());
}
int expCode = expectedResult.ErrorCode;
if (cv.ErrorValue != expCode)
{
throw new AssertionException("Expected error '" + ErrorEval.GetText(expCode)
+ "' but got '" + cv.FormatAsString() + "'.");
}
}
private static void ConfirmExpectedResult(IFormulaEvaluator Evaluator, String msg, ICell cell, double expected)
{
CellValue value = Evaluator.Evaluate(cell);
if (value.ErrorValue != 0)
{
throw new Exception(msg + ": " + value.FormatAsString());
}
Assert.AreEqual(expected, value.NumberValue, msg);
}
private static void ConfirmError(IFormulaEvaluator fe, ICell cell, String formulaText, ErrorEval expectedError)
{
fe.DebugEvaluationOutputForNextEval = true;
cell.SetCellFormula(formulaText);
fe.NotifyUpdateCell(cell);
CellValue result = fe.Evaluate(cell);
Assert.AreEqual(CellType.Error, result.CellType, "Testing result type for: " + formulaText);
Assert.AreEqual(expectedError.ErrorString, result.FormatAsString(), "Testing error type for: " + formulaText);
}
private void Confirm(string formulaText, String expectedResult)
{
cell11.CellFormula = (/*setter*/ formulaText);
Evaluator.ClearAllCachedResultValues();
CellValue cv = Evaluator.Evaluate(cell11);
Assert.AreEqual(CellType.String, cv.CellType, "Wrong result type: " + cv.FormatAsString());
String actualValue = cv.StringValue;
Assert.AreEqual(expectedResult, actualValue);
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String expectedResult)
{
fe.ClearAllCachedResultValues();
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.String)
{
Assert.Fail("expected String cell type but got " + cv.FormatAsString());
}
Assert.AreEqual(expectedResult, cv.StringValue);
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, double expectedResult)
{
fe.ClearAllCachedResultValues();
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Numeric)
{
Assert.Fail("expected numeric cell type but got " + cv.FormatAsString());
}
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
double expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula = (formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.NUMERIC)
{
throw new AssertionException("expected numeric cell type but got " + cv.FormatAsString());
}
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
private void Confirm(string formulaText, double expectedResult)
{
cell11.CellFormula = (formulaText);
Evaluator.ClearAllCachedResultValues();
CellValue cv = Evaluator.Evaluate(cell11);
if (cv.CellType != CellType.Numeric)
{
throw new AssertionException("Wrong result type: " + cv.FormatAsString());
}
double actualValue = cv.NumberValue;
Assert.AreEqual(expectedResult, actualValue, 0);
}
private static void ConfirmErrorResult(String msgPrefix, int expectedErrorCode, CellValue actual)
{
if (actual.CellType != CellType.Error)
{
throw new AssertionException(msgPrefix + " Expected cell error ("
+ ErrorEval.GetText(expectedErrorCode) + ") but actual value was "
+ actual.FormatAsString());
}
if (expectedErrorCode != actual.ErrorValue)
{
throw new AssertionException(msgPrefix + " Expected cell error code ("
+ ErrorEval.GetText(expectedErrorCode)
+ ") but actual error code was ("
+ ErrorEval.GetText(actual.ErrorValue)
+ ")");
}
}
public void TestCountifBug51498()
{
int REF_COL = 4;
int EVAL_COL = 3;
HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("51498.xls");
IFormulaEvaluator evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
ISheet sheet = workbook.GetSheetAt(0);
// numeric criteria
for (int i = 0; i < 8; i++)
{
CellValue expected = evaluator.Evaluate(sheet.GetRow(i).GetCell(REF_COL));
CellValue actual = evaluator.Evaluate(sheet.GetRow(i).GetCell(EVAL_COL));
Assert.AreEqual(expected.FormatAsString(), actual.FormatAsString());
}
// boolean criteria
for (int i = 0; i < 8; i++)
{
HSSFCell cellFmla = (HSSFCell)sheet.GetRow(i).GetCell(8);
HSSFCell cellRef = (HSSFCell)sheet.GetRow(i).GetCell(9);
double expectedValue = cellRef.NumericCellValue;
double actualValue = evaluator.Evaluate(cellFmla).NumberValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " +
new CellReference(cellFmla).FormatAsString() + "[" + cellFmla.CellFormula + "] ");
}
// string criteria
for (int i = 1; i < 9; i++)
{
ICell cellFmla = sheet.GetRow(i).GetCell(13);
ICell cellRef = sheet.GetRow(i).GetCell(14);
double expectedValue = cellRef.NumericCellValue;
double actualValue = evaluator.Evaluate(cellFmla).NumberValue;
Assert.AreEqual(expectedValue, actualValue, 0.0001,
"Problem with a formula at " +
new CellReference(cellFmla).FormatAsString() + "[" + cellFmla.CellFormula + "] ");
}
}
private void ConfirmValueError(string formulaText)
{
cell11.CellFormula = (/*setter*/ formulaText);
Evaluator.ClearAllCachedResultValues();
CellValue cv = Evaluator.Evaluate(cell11);
Assert.IsTrue(cv.CellType == CellType.Error &&
cv.ErrorValue == ErrorConstants.ERROR_VALUE, "Wrong result type: " + cv.FormatAsString());
}