public void Test3DArea()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet1 = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
wb.CreateSheet();
wb.SetSheetName(1, "Sheet2");
Row row = sheet1.CreateRow(0);
Cell cell = row.CreateCell((short)0);
cell.CellFormula = ("isblank(Sheet2!A1:A1)");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet1, wb);
//fe.SetCurrentRow(row);
NPOI.SS.UserModel.CellValue result = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
cell.CellFormula = ("isblank(D7:D7)");
result = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, result.CellType);
Assert.AreEqual(true, result.BooleanValue);
}
public void Test27349()
{
// 27349-vlookupAcrossSheets.xls is1 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)
{
throw;
}
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
Row row = sheet.GetRow(1);
Cell 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(sheet, wb);
//fe.SetCurrentRow(row);
NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, cv.CellType);
Assert.AreEqual(3.0, cv.NumberValue, 0.0);
}
private static void SetCellValue(NPOI.SS.UserModel.Cell cell, NPOI.SS.UserModel.CellValue cv)
{
NPOI.SS.UserModel.CellType cellType = cv.CellType;
switch (cellType)
{
case NPOI.SS.UserModel.CellType.BOOLEAN:
cell.SetCellValue(cv.BooleanValue);
break;
case NPOI.SS.UserModel.CellType.ERROR:
cell.CellErrorValue = cv.ErrorValue;
break;
case NPOI.SS.UserModel.CellType.NUMERIC:
cell.SetCellValue(cv.NumberValue);
break;
case NPOI.SS.UserModel.CellType.STRING:
cell.SetCellValue(new HSSFRichTextString(cv.StringValue));
break;
//case NPOI.SS.UserModel.CellType.BLANK:
//// never happens - blanks eventually get translated to zero
//case NPOI.SS.UserModel.CellType.FORMULA:
//// this will never happen, we have already evaluated the formula
default:
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
}
public void TestIndexFormula()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("Sheet1");
short colB = 1;
sheet.CreateRow(0).CreateCell(colB).SetCellValue(1);
sheet.CreateRow(1).CreateCell(colB).SetCellValue(2);
sheet.CreateRow(2).CreateCell(colB).SetCellValue(3);
Row row4 = sheet.CreateRow(3);
Cell testCell = row4.CreateCell((short)0);
// This formula should evaluate to the contents of B2,
testCell.CellFormula = ("INDEX(A1:B4,2,2)");
// However the range A1:B4 also includes the current cell A4. If the other parameters
// were 4 and 1, this would represent a circular reference. Since POI 'fully' evaluates
// arguments before invoking operators, POI must handle such potential cycles gracefully.
NPOI.SS.UserModel.CellValue cellValue = EvaluateWithCycles(wb, testCell);
Assert.IsTrue(cellValue.CellType == NPOI.SS.UserModel.CellType.NUMERIC);
Assert.AreEqual(2, cellValue.NumberValue, 0);
}
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);
}
}
/**
* If cell Contains formula, it Evaluates the formula,
* and saves the result of the formula. The cell
* remains as a formula cell.
* Else if cell does not contain formula, this method leaves
* the cell UnChanged.
* Note that the type of the formula result is returned,
* so you know what kind of value is also stored with
* the formula.
* <pre>
* int EvaluatedCellType = evaluator.EvaluateFormulaCell(cell);
* </pre>
* Be aware that your cell will hold both the formula,
* and the result. If you want the cell Replaced with
* the result of the formula, use {@link #EvaluateInCell(HSSFCell)}
* @param cell The cell to Evaluate
* @return The type of the formula result (the cell's type remains as NPOI.SS.UserModel.CellType.FORMULA however)
*/
public NPOI.SS.UserModel.CellType EvaluateFormulaCell(NPOI.SS.UserModel.Cell cell)
{
if (cell == null || cell.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
return(NPOI.SS.UserModel.CellType.Unknown);
}
NPOI.SS.UserModel.CellValue cv = EvaluateFormulaCellValue(cell);
// cell remains a formula cell, but the cached value is changed
SetCellValue(cell, cv);
return(cv.CellType);
}
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 Test42448()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet1 = wb.CreateSheet("Sheet1");
Row row = sheet1.CreateRow(0);
Cell cell = row.CreateCell((short)0);
// it's important to create the referenced sheet first
NPOI.SS.UserModel.Sheet sheet2 = wb.CreateSheet("A"); // note name 'A'
// TODO - POI crashes if the formula is added before this sheet
// Exception("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 AssertFailedException("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(sheet1, wb);
//fe.SetCurrentRow(row);
NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, cv.CellType);
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
/**
* If cell Contains formula, it Evaluates the formula, and
* puts the formula result back into the cell, in place
* of the old formula.
* Else if cell does not contain formula, this method leaves
* the cell UnChanged.
* Note that the same instance of Cell is returned to
* allow chained calls like:
* <pre>
* int EvaluatedCellType = evaluator.EvaluateInCell(cell).CellType;
* </pre>
* Be aware that your cell value will be Changed to hold the
* result of the formula. If you simply want the formula
* value computed for you, use {@link #EvaluateFormulaCell(HSSFCell)}
* @param cell
*/
public NPOI.SS.UserModel.Cell EvaluateInCell(NPOI.SS.UserModel.Cell cell)
{
if (cell == null)
{
return(null);
}
if (cell.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
NPOI.SS.UserModel.CellValue cv = EvaluateFormulaCellValue(cell);
SetCellValue(cell, cv);
SetCellType(cell, cv); // cell will no longer be a formula cell
}
return(cell);
}
private static String FormatValue(NPOI.SS.UserModel.CellValue actual)
{
switch (actual.CellType)
{
case NPOI.SS.UserModel.CellType.BLANK: return("<blank>");
case NPOI.SS.UserModel.CellType.BOOLEAN: return(actual.BooleanValue.ToString());
case NPOI.SS.UserModel.CellType.NUMERIC: return(actual.NumberValue.ToString());
case NPOI.SS.UserModel.CellType.STRING: return(actual.StringValue);
}
throw new Exception("Unexpected cell type of evaluated value (" + actual.CellType + ")");
}
public void TestSimpleCircularReference()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("Sheet1");
Row row = sheet.CreateRow(0);
Cell testCell = row.CreateCell(0);
testCell.CellFormula = ("A1");
NPOI.SS.UserModel.CellValue cellValue = EvaluateWithCycles(wb, testCell);
ConfirmCycleErrorCode(cellValue);
}
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)
+ ")");
}
}
private static void ConfirmExpectedResult(String msg, Cell expected, NPOI.SS.UserModel.CellValue actual)
{
if (expected == null)
{
throw new AssertFailedException(msg + " - Bad Setup data expected value is1 null");
}
if (actual == null)
{
throw new AssertFailedException(msg + " - actual value was null");
}
if (expected.CellType == NPOI.SS.UserModel.CellType.ERROR)
{
ConfirmErrorResult(msg, Convert.ToInt32(expected.ErrorCellValue), actual);
return;
}
if (actual.CellType == NPOI.SS.UserModel.CellType.ERROR)
{
throw UnexpectedError(msg, expected, actual.ErrorValue);
}
if (actual.CellType != expected.CellType)
{
WrongTypeError(msg, expected, actual);
}
switch (expected.CellType)
{
case NPOI.SS.UserModel.CellType.BOOLEAN:
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case NPOI.SS.UserModel.CellType.FORMULA: // will never be used, since we will call method after formula evaluation
throw new AssertFailedException("Cannot expect formula as result of formula evaluation: " + msg);
case NPOI.SS.UserModel.CellType.NUMERIC:
Assert.AreEqual(expected.NumericCellValue, actual.NumberValue, 0.0);
break;
case NPOI.SS.UserModel.CellType.STRING:
Assert.AreEqual(expected.RichStringCellValue.String, actual.StringValue, msg);
break;
}
}
protected void RunFormatTests(String workbookName, CellValue valueGetter)
{
OpenWorkbook(workbookName);
ReadFlags(workbook);
SortedList<string, object> runCategories = new SortedList<string, object>(StringComparer.InvariantCultureIgnoreCase);
String RunCategoryList = flagString("Categories", "");
Regex regex = new Regex("\\s*,\\s*");
if (RunCategoryList != null)
{
foreach (string s in regex.Split(RunCategoryList))
if (!runCategories.ContainsKey(s))
runCategories.Add(s, null);
runCategories.Remove(""); // this can be found and means nothing
}
ISheet sheet = workbook.GetSheet("Tests");
int end = sheet.LastRowNum;
// Skip the header row, therefore "+ 1"
for (int r = sheet.FirstRowNum + 1; r <= end; r++)
{
IRow row = sheet.GetRow(r);
if (row == null)
continue;
int cellnum = 0;
String expectedText = row.GetCell(cellnum).StringCellValue;
String format = row.GetCell(1).StringCellValue;
String testCategoryList = row.GetCell(3).StringCellValue;
bool byCategory = RunByCategory(runCategories, testCategoryList);
if ((expectedText.Length > 0 || format.Length > 0) && byCategory)
{
ICell cell = row.GetCell(2);
Debug.WriteLine(string.Format("expectedText: {0}, format:{1}", expectedText, format));
if (format == "hh:mm:ss a/p")
expectedText = expectedText.ToUpper();
else if (format == "H:M:S.00 a/p")
expectedText = expectedText.ToUpper();
tryFormat(r, expectedText, format, valueGetter, cell);
}
}
}
private static void ConfirmExpectedResult(String msg, Cell expected, NPOI.SS.UserModel.CellValue actual)
{
if (expected == null)
{
throw new AssertFailedException(msg + " - Bad setup data expected value is null");
}
if (actual == null)
{
throw new AssertFailedException(msg + " - actual value was null");
}
switch (expected.CellType)
{
case NPOI.SS.UserModel.CellType.BLANK:
Assert.AreEqual(NPOI.SS.UserModel.CellType.BLANK, actual.CellType, msg);
break;
case NPOI.SS.UserModel.CellType.BOOLEAN:
Assert.AreEqual(NPOI.SS.UserModel.CellType.BOOLEAN, actual.CellType, msg);
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case NPOI.SS.UserModel.CellType.ERROR:
Assert.AreEqual(NPOI.SS.UserModel.CellType.ERROR, actual.CellType, msg);
Assert.AreEqual(ErrorEval.GetText(expected.ErrorCellValue), ErrorEval.GetText(actual.ErrorValue), msg);
break;
case NPOI.SS.UserModel.CellType.FORMULA: // will never be used, since we will call method after formula evaluation
throw new AssertFailedException("Cannot expect formula as result of formula evaluation: " + msg);
case NPOI.SS.UserModel.CellType.NUMERIC:
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, actual.CellType, msg);
//Assert.AreEqual(msg, expected.NumericCellValue, actual.NumberValue, TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
Assert.AreEqual(expected.NumericCellValue, actual.NumberValue, msg);
break;
case NPOI.SS.UserModel.CellType.STRING:
Assert.AreEqual(NPOI.SS.UserModel.CellType.STRING, actual.CellType, msg);
Assert.AreEqual(expected.RichStringCellValue.String, actual.StringValue, msg);
break;
}
}
private static void ConfirmExpectedResult(String msg, ICell expected, CellValue actual)
{
if (expected == null)
{
throw new AssertionException(msg + " - Bad Setup data expected value is null");
}
if (actual == null)
{
throw new AssertionException(msg + " - actual value was null");
}
if (expected.CellType == CellType.ERROR)
{
ConfirmErrorResult(msg, expected.ErrorCellValue, actual);
return;
}
if (actual.CellType == CellType.ERROR)
{
throw unexpectedError(msg, expected, actual.ErrorValue);
}
if (actual.CellType != expected.CellType)
{
throw wrongTypeError(msg, expected, actual);
}
switch (expected.CellType)
{
case CellType.BOOLEAN:
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case CellType.FORMULA: // will never be used, since we will call method After formula Evaluation
throw new AssertionException("Cannot expect formula as result of formula Evaluation: " + msg);
case CellType.NUMERIC:
Assert.AreEqual(expected.NumericCellValue, actual.NumberValue, 0.0, msg);
break;
case CellType.STRING:
Assert.AreEqual(expected.RichStringCellValue.String, actual.StringValue, msg);
break;
}
}
private static void SetCellType(NPOI.SS.UserModel.Cell cell, NPOI.SS.UserModel.CellValue cv)
{
NPOI.SS.UserModel.CellType cellType = cv.CellType;
switch (cellType)
{
case NPOI.SS.UserModel.CellType.BOOLEAN:
case NPOI.SS.UserModel.CellType.ERROR:
case NPOI.SS.UserModel.CellType.NUMERIC:
case NPOI.SS.UserModel.CellType.STRING:
cell.SetCellType(cellType);
return;
case NPOI.SS.UserModel.CellType.BLANK:
// never happens - blanks eventually get translated to zero
break;
case NPOI.SS.UserModel.CellType.FORMULA:
// this will never happen, we have already evaluated the formula
break;
}
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
/**
*
* @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,
NPOI.SS.UserModel.Row formulasRow, NPOI.SS.UserModel.Row expectedValuesRow)
{
int result = Result.NO_EVALUATIONS_FOUND; // so far
int endcolnum = formulasRow.LastCellNum;
//evaluator.SetCurrentRow(formulasRow);
// iterate across the row for all the evaluation cases
for (short colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
{
Cell c = formulasRow.GetCell(colnum);
if (c == null || c.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
continue;
}
NPOI.SS.UserModel.CellValue actualValue = evaluator.Evaluate(c);
Cell 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 (AssertFailedException)
{
_evaluationFailureCount++;
//printShortStackTrace(System.err, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
return(result);
}
public void TestCountifFromSpreadsheet()
{
String FILE_NAME = "countifExamples.xls";
int START_ROW_IX = 1;
int COL_IX_ACTUAL = 2;
int COL_IX_EXPECTED = 3;
int failureCount = 0;
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook(FILE_NAME);
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
int maxRow = sheet.LastRowNum;
for (int rowIx = START_ROW_IX; rowIx < maxRow; rowIx++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(rowIx);
if (row == null)
{
continue;
}
Cell cell = row.GetCell(COL_IX_ACTUAL);
NPOI.SS.UserModel.CellValue cv = fe.Evaluate(cell);
double actualValue = cv.NumberValue;
double expectedValue = row.GetCell(COL_IX_EXPECTED).NumericCellValue;
if (actualValue != expectedValue)
{
Console.Error.WriteLine("Problem with Test case on row " + (rowIx + 1) + " "
+ "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
failureCount++;
}
}
if (failureCount > 0)
{
throw new AssertFailedException(failureCount + " countif evaluations failed. See stderr for more details");
}
}
public void TestInvoke()
{
HSSFWorkbook wb;
NPOI.SS.UserModel.Sheet sheet ;
Cell cell;
if (false)
{
// TODO - this code won't work until we can create user-defined functions directly with POI
wb = new HSSFWorkbook();
sheet = wb.CreateSheet();
wb.SetSheetName(0, "Sheet1");
NPOI.SS.UserModel.Name hssfName = wb.CreateName();
hssfName.NameName = ("myFunc");
}
else
{
// This sample spreadsheet already has a VB function called 'myFunc'
wb = HSSFTestDataSamples.OpenSampleWorkbook("testNames.xls");
sheet = wb.GetSheetAt(0);
Row row = sheet.CreateRow(0);
cell = row.CreateCell(1);
}
cell.CellFormula = ("myFunc()");
String actualFormula = cell.CellFormula;
Assert.AreEqual("myFunc()", actualFormula);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
NPOI.SS.UserModel.CellValue evalResult = fe.Evaluate(cell);
// Check the return value from ExternalFunction.evaluate()
// TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better
Assert.AreEqual(NPOI.SS.UserModel.CellType.ERROR, evalResult.CellType);
Assert.AreEqual(ErrorEval.FUNCTION_NOT_IMPLEMENTED.ErrorCode, evalResult.ErrorValue);
}
/**
* Makes sure that the specified evaluated cell value represents a circular reference error.
*/
private static void ConfirmCycleErrorCode(NPOI.SS.UserModel.CellValue cellValue)
{
Assert.IsTrue(cellValue.CellType == NPOI.SS.UserModel.CellType.ERROR);
Assert.AreEqual((byte)ErrorEval.CIRCULAR_REF_ERROR.ErrorCode, cellValue.ErrorValue);
}
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 assertFormulaResult(CellValue cv, ICell cell)
{
double actualValue = cv.NumberValue;
double expectedValue = cell.NumericCellValue; // cached formula result calculated by Excel
Assert.AreEqual(CellType.Numeric, cv.CellType, "Invalid formula result: " + cv.ToString());
Assert.AreEqual(expectedValue, actualValue, 1E-4); // should agree within 0.01%
}
private String tryColor(String desc, String cname, CellValue Getter,
Object value, String expectedText, Color expectedColor)
{
if (cname != null)
desc = "[" + cname + "]" + desc;
Color origColor = label.ForeColor;
CellFormatPart format = new CellFormatPart(desc);
if (!format.Apply(label, value).Applies)
{
// If this doesn't Apply, no color change is expected
expectedColor = origColor;
}
String actualText = label.Text;
Color actualColor = label.ForeColor;
Getter.Equivalent(expectedText, actualText, format);
Assert.AreEqual(
expectedColor, actualColor,cname == null ? "no color" : "color " + cname);
return actualText;
}
private void tryFormat(int row, String expectedText, String desc,
CellValue Getter, ICell cell)
{
Object value = Getter.GetValue(cell);
Color testColor = Getter.GetColor(cell);
if (testColor == null)
testColor = TEST_COLOR;
if (label == null)
label = new Label();
label.ForeColor = (/*setter*/testColor);
label.Text = (/*setter*/"xyzzy");
logger.Log(POILogger.INFO, String.Format("Row %d: \"%s\" -> \"%s\": expected \"%s\"", row + 1,
value.ToString(), desc, expectedText));
String actualText = tryColor(desc, null, Getter, value, expectedText,
testColor);
logger.Log(POILogger.INFO, String.Format(", actual \"%s\")%n", actualText));
if (tryAllColors && testColor != TEST_COLOR)
{
for (int i = 0; i < COLOR_NAMES.Length; i++)
{
String cname = COLOR_NAMES[i];
tryColor(desc, cname, Getter, value, expectedText, COLORS[i]);
}
}
}
private static void ConfirmExpectedResult(String msg, ICell expected, CellValue actual)
{
if (expected == null)
{
throw new AssertionException(msg + " - Bad Setup data expected value is null");
}
if (actual == null)
{
throw new AssertionException(msg + " - actual value was null");
}
switch (expected.CellType)
{
case CellType.Blank:
Assert.AreEqual(CellType.Blank, actual.CellType, msg);
break;
case CellType.Boolean:
Assert.AreEqual(CellType.Boolean, actual.CellType, msg);
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case CellType.Error:
Assert.AreEqual(CellType.Error, actual.CellType, msg);
//if (false)
//{ // TODO: fix ~45 functions which are currently returning incorrect error values
// Assert.AreEqual(expected.ErrorCellValue, actual.ErrorValue, msg);
//}
break;
case CellType.Formula: // will never be used, since we will call method After formula Evaluation
throw new AssertionException("Cannot expect formula as result of formula Evaluation: " + msg);
case CellType.Numeric:
Assert.AreEqual(CellType.Numeric, actual.CellType, msg);
AbstractNumericTestCase.AssertEquals(msg, expected.NumericCellValue, actual.NumberValue, TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
// double delta = Math.abs(expected.NumericCellValue-actual.NumberValue);
// double pctExpected = Math.abs(0.00001*expected.NumericCellValue);
// Assert.IsTrue(msg, delta <= pctExpected);
break;
case CellType.String:
Assert.AreEqual(CellType.String, actual.CellType, msg);
Assert.AreEqual(expected.RichStringCellValue.String, actual.StringValue, msg);
break;
}
}
private static void ConfirmErrorResult(String msgPrefix, int expectedErrorCode, NPOI.SS.UserModel.CellValue actual)
{
if (actual.CellType != NPOI.SS.UserModel.CellType.ERROR)
{
throw new AssertFailedException(msgPrefix + " Expected cell error ("
+ ErrorEval.GetText(expectedErrorCode) + ") but actual value was "
+ FormatValue(actual));
}
if (expectedErrorCode != actual.ErrorValue)
{
throw new AssertFailedException(msgPrefix + " Expected cell error code ("
+ ErrorEval.GetText(expectedErrorCode)
+ ") but actual error code was ("
+ ErrorEval.GetText(actual.ErrorValue)
+ ")");
}
}
private static void SetCellType(ICell cell, CellValue cv)
{
CellType cellType = cv.CellType;
switch (cellType)
{
case CellType.BOOLEAN:
case CellType.ERROR:
case CellType.NUMERIC:
case CellType.STRING:
cell.SetCellType(cellType);
return;
case CellType.BLANK:
// never happens - blanks eventually Get translated to zero
case CellType.FORMULA:
// this will never happen, we have already Evaluated the formula
break;
}
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
/**
* Makes sure that the specified Evaluated cell value represents a circular reference error.
*/
private static void ConfirmCycleErrorCode(CellValue cellValue)
{
Assert.IsTrue(cellValue.CellType == CellType.Error);
Assert.AreEqual(ErrorEval.CIRCULAR_REF_ERROR.ErrorCode, cellValue.ErrorValue);
}
private static void ConfirmExpectedResult(String msg, ICell expected, CellValue actual)
{
if (expected == null)
{
throw new AssertFailedException(msg + " - Bad Setup data expected value is null");
}
if (actual == null)
{
throw new AssertFailedException(msg + " - actual value was null");
}
switch (expected.CellType)
{
case CellType.Blank:
Assert.AreEqual(CellType.Blank, actual.CellType, msg);
break;
case CellType.Boolean:
Assert.AreEqual(CellType.Boolean, actual.CellType, msg);
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case CellType.Error:
Assert.AreEqual(CellType.Error, actual.CellType, msg);
Assert.AreEqual(msg, ErrorEval.GetText(expected.ErrorCellValue), ErrorEval.GetText(actual.ErrorValue));
break;
case CellType.Formula: // will never be used, since we will call method After formula Evaluation
throw new AssertFailedException("Cannot expect formula as result of formula Evaluation: " + msg);
case CellType.Numeric:
Assert.AreEqual(CellType.Numeric, actual.CellType, msg);
TestMathX.AssertEquals(msg, expected.NumericCellValue, actual.NumberValue, TestMathX.POS_ZERO, TestMathX.DIFF_TOLERANCE_FACTOR);
break;
case CellType.String:
Assert.AreEqual(CellType.String, actual.CellType, msg);
Assert.AreEqual(msg, expected.RichStringCellValue.String, actual.StringValue);
break;
}
}
protected object GetValorCelda(CellValue valorCelda, ICell container) {
if (valorCelda == null)
return null;
switch (valorCelda.CellType) {
case CellType.STRING:
//return celda.StringValorCelda;
return container.StringCellValue;
case CellType.NUMERIC:
var numValue = container.NumericCellValue;
if (DateUtil.IsCellDateFormatted(container)) {
if (DateUtil.IsValidExcelDate(numValue)) {
var dt = DateUtil.GetJavaDate(numValue);
return dt;
}
}
return numValue;
case CellType.BOOLEAN:
return container.BooleanCellValue;
default:
return null;
}
}
private static void SetCellValue(ICell cell, CellValue cv)
{
CellType cellType = cv.CellType;
switch (cellType)
{
case CellType.Boolean:
cell.SetCellValue(cv.BooleanValue);
break;
case CellType.Error:
cell.SetCellErrorValue((byte)cv.ErrorValue);
break;
case CellType.Numeric:
cell.SetCellValue(cv.NumberValue);
break;
case CellType.String:
cell.SetCellValue(new HSSFRichTextString(cv.StringValue));
break;
//case CellType.Blank:
//// never happens - blanks eventually get translated to zero
//case CellType.Formula:
//// this will never happen, we have already evaluated the formula
default:
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
}
private static AssertFailedException WrongTypeError(String msgPrefix, Cell expectedCell, NPOI.SS.UserModel.CellValue actualValue)
{
return(new AssertFailedException(msgPrefix + " Result type mismatch. Evaluated result was "
+ FormatValue(actualValue)
+ " but the expected result was "
+ FormatValue(expectedCell)
));
}
private static void ConfirmExpectedResult(String msg, ICell expected, CellValue actual)
{
if (expected == null)
{
throw new AssertionException(msg + " - Bad Setup data expected value is null");
}
if (actual == null)
{
throw new AssertionException(msg + " - actual value was null");
}
switch (expected.CellType)
{
case CellType.BLANK:
Assert.AreEqual(CellType.BLANK, actual.CellType, msg);
break;
case CellType.BOOLEAN:
Assert.AreEqual(CellType.BOOLEAN, actual.CellType, msg);
Assert.AreEqual(expected.BooleanCellValue, actual.BooleanValue, msg);
break;
case CellType.ERROR:
Assert.AreEqual(CellType.ERROR, actual.CellType, msg);
Assert.AreEqual(ErrorEval.GetText(expected.ErrorCellValue), ErrorEval.GetText(actual.ErrorValue), msg);
break;
case CellType.FORMULA: // will never be used, since we will call method After formula Evaluation
throw new AssertionException("Cannot expect formula as result of formula Evaluation: " + msg);
case CellType.NUMERIC:
Assert.AreEqual(CellType.NUMERIC, actual.CellType, msg);
AbstractNumericTestCase.AssertEqual(msg, expected.NumericCellValue, actual.NumberValue,
AbstractNumericTestCase.POS_ZERO, AbstractNumericTestCase.DIFF_TOLERANCE_FACTOR);
break;
case CellType.STRING:
Assert.AreEqual(CellType.STRING, actual.CellType, msg);
Assert.AreEqual(expected.RichStringCellValue.String, actual.StringValue, msg);
break;
}
}
private static void SetCellType(ICell cell, CellValue cv)
{
CellType cellType = cv.CellType;
switch (cellType)
{
case CellType.Boolean:
case CellType.Error:
case CellType.Numeric:
case CellType.String:
cell.SetCellType(cellType);
return;
case CellType.Blank:
// never happens - blanks eventually Get translated to zero
case CellType.Formula:
// this will never happen, we have already Evaluated the formula
break;
}
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
private static void SetCellValue(ICell cell, CellValue cv)
{
CellType cellType = cv.CellType;
switch (cellType)
{
case CellType.BOOLEAN:
cell.SetCellValue(cv.BooleanValue);
break;
case CellType.ERROR:
cell.SetCellErrorValue((byte)cv.ErrorValue);
break;
case CellType.NUMERIC:
cell.SetCellValue(cv.NumberValue);
break;
case CellType.STRING:
cell.SetCellValue(new XSSFRichTextString(cv.StringValue));
break;
case CellType.BLANK:
// never happens - blanks eventually Get translated to zero
case CellType.FORMULA:
// this will never happen, we have already Evaluated the formula
default:
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
}
private int ProcessTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName)
{
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(sheetIndex);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, 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++)
{
Row r = sheet.GetRow(rowIndex);
String newMarkerValue = GetMarkerColumnValue(r);
if (r == null)
{
continue;
}
if (SS.TEST_CASES_END_MARKER.Equals(newMarkerValue, StringComparison.InvariantCultureIgnoreCase))
{
// normal exit point
return(result);
}
if (SS.SKIP_CURRENT_TEST_CASE_MARKER.Equals(newMarkerValue, StringComparison.InvariantCultureIgnoreCase))
{
// currently disabled test case row
continue;
}
if (newMarkerValue != null)
{
currentGroupComment = newMarkerValue;
}
Cell c = r.GetCell(SS.COLUMN_INDEX_EVALUATION);
if (c == null || c.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
continue;
}
//evaluator.SetCurrentRow(r);
NPOI.SS.UserModel.CellValue actualValue = evaluator.Evaluate(c);
Cell 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 (AssertFailedException)
{
_evaluationFailureCount++;
//printShortStackTrace(System.err, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
catch (Exception)
{
_evaluationFailureCount++;
//printShortStackTrace(System.err, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
throw new Exception("Missing end marker '" + SS.TEST_CASES_END_MARKER
+ "' on sheet '" + sheetName + "'");
}