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();
}
}
/**
* @param startRowIndex row index in the spreadsheet where the first function/operator is found
* @param testFocusFunctionName name of a single function/operator to test alone.
* Typically pass <code>null</code> to test all functions
*/
private void ProcessFunctionGroup(int startRowIndex, String testFocusFunctionName)
{
IFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
int rowIndex = startRowIndex;
while (true)
{
IRow r = sheet.GetRow(rowIndex);
// only Evaluate non empty row
if (r != null)
{
String targetFunctionName = GetTargetFunctionName(r);
String targetTestName = GetTargetTestName(r);
if (targetFunctionName == null)
{
throw new AssertionException("Test spreadsheet cell empty on row ("
+ (rowIndex + 1) + "). Expected function name or '"
+ SS.FUNCTION_NAMES_END_SENTINEL + "'");
}
if (targetFunctionName.Equals(SS.FUNCTION_NAMES_END_SENTINEL))
{
// found end of functions list
break;
}
if (testFocusFunctionName == null || targetFunctionName.Equals(testFocusFunctionName, StringComparison.CurrentCultureIgnoreCase))
{
// expected results are on the row below
ICell expectedValueCell = r.GetCell(SS.COLUMN_INDEX_EXPECTED_VALUE);
if (expectedValueCell == null)
{
int missingRowNum = rowIndex + 1;
throw new AssertionException("Missing expected values cell for function '"
+ targetFunctionName + ", test" + targetTestName + " (row " +
missingRowNum + ")");
}
switch (ProcessFunctionRow(evaluator, targetFunctionName, targetTestName, r, expectedValueCell))
{
case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break;
case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break;
default:
throw new Exception("unexpected result");
case Result.NO_EVALUATIONS_FOUND: // do nothing
break;
}
}
}
rowIndex++;
}
}
/**
* @param startRowIndex row index in the spreadsheet where the first function/operator is found
* @param TestFocusFunctionName name of a single function/operator to Test alone.
* Typically pass <code>null</code> to Test all functions
*/
private void ProcessFunctionGroup(int startRowIndex, String testFocusFunctionName)
{
IFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook);
int rowIndex = startRowIndex;
while (true)
{
IRow r = sheet.GetRow(rowIndex);
String targetFunctionName = GetTargetFunctionName(r);
if (targetFunctionName == null)
{
throw new AssertionException("Test spreadsheet cell empty on row ("
+ (rowIndex + 1) + "). Expected function name or '"
+ SS.FUNCTION_NAMES_END_SENTINEL + "'");
}
if (targetFunctionName.Equals(SS.FUNCTION_NAMES_END_SENTINEL))
{
// found end of functions list
break;
}
if (testFocusFunctionName == null || targetFunctionName.Equals(testFocusFunctionName, StringComparison.OrdinalIgnoreCase))
{
// expected results are on the row below
IRow expectedValuesRow = sheet.GetRow(rowIndex + 1);
if (expectedValuesRow == null)
{
int missingRowNum = rowIndex + 2; //+1 for 1-based, +1 for next row
throw new AssertionException("Missing expected values row for function '"
+ targetFunctionName + " (row " + missingRowNum + ")");
}
switch (ProcessFunctionRow(evaluator, targetFunctionName, r, expectedValuesRow))
{
case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break;
case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break;
case Result.NO_EVALUATIONS_FOUND: // do nothing
break;
default:
throw new RuntimeException("unexpected result");
}
}
rowIndex += SS.NUMBER_OF_ROWS_PER_FUNCTION;
}
}
public void TestReferencesToOtherWorkbooks()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("ref2-56737.xlsx");
XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
XSSFSheet s = wb.GetSheetAt(0) as XSSFSheet;
// References to a .xlsx file
IRow rXSLX = s.GetRow(2);
ICell cXSLX_cell = rXSLX.GetCell(4);
ICell cXSLX_sNR = rXSLX.GetCell(6);
ICell cXSLX_gNR = rXSLX.GetCell(8);
Assert.AreEqual("[1]Uses!$A$1", cXSLX_cell.CellFormula);
Assert.AreEqual("[1]Defines!NR_To_A1", cXSLX_sNR.CellFormula);
Assert.AreEqual("[1]!NR_Global_B2", cXSLX_gNR.CellFormula);
Assert.AreEqual("Hello!", cXSLX_cell.StringCellValue);
Assert.AreEqual("Test A1", cXSLX_sNR.StringCellValue);
Assert.AreEqual(142.0, cXSLX_gNR.NumericCellValue);
// References to a .xls file
IRow rXSL = s.GetRow(4);
ICell cXSL_cell = rXSL.GetCell(4);
ICell cXSL_sNR = rXSL.GetCell(6);
ICell cXSL_gNR = rXSL.GetCell(8);
Assert.AreEqual("[2]Uses!$C$1", cXSL_cell.CellFormula);
Assert.AreEqual("[2]Defines!NR_To_A1", cXSL_sNR.CellFormula);
Assert.AreEqual("[2]!NR_Global_B2", cXSL_gNR.CellFormula);
Assert.AreEqual("Hello!", cXSL_cell.StringCellValue);
Assert.AreEqual("Test A1", cXSL_sNR.StringCellValue);
Assert.AreEqual(142.0, cXSL_gNR.NumericCellValue);
// Try to Evaluate without references, won't work
// (At least, not unit we fix bug #56752 that is1)
try
{
evaluator.Evaluate(cXSL_cell);
Assert.Fail("Without a fix for #56752, shouldn't be able to Evaluate a " +
"reference to a non-provided linked workbook");
}
catch (Exception) { }
// Setup the environment
Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>();
evaluators.Add("ref2-56737.xlsx", evaluator);
evaluators.Add("56737.xlsx",
_testDataProvider.OpenSampleWorkbook("56737.xlsx").GetCreationHelper().CreateFormulaEvaluator());
evaluators.Add("56737.xls",
HSSFTestDataSamples.OpenSampleWorkbook("56737.xls").GetCreationHelper().CreateFormulaEvaluator());
evaluator.SetupReferencedWorkbooks(evaluators);
// Try Evaluating all of them, ensure we don't blow up
foreach (IRow r in s)
{
foreach (ICell c in r)
{
// TODO Fix and enable
evaluator.Evaluate(c);
}
}
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSLX_cell).FormatAsString());
Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSLX_sNR).FormatAsString());
//Assert.AreEqual("142.0", evaluator.Evaluate(cXSLX_gNR).FormatAsString());
Assert.AreEqual("142", evaluator.Evaluate(cXSLX_gNR).FormatAsString());
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell).FormatAsString());
Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSL_sNR).FormatAsString());
//Assert.AreEqual("142.0", evaluator.Evaluate(cXSL_gNR).FormatAsString());
Assert.AreEqual("142", evaluator.Evaluate(cXSL_gNR).FormatAsString());
// Add another formula referencing these workbooks
ICell cXSL_cell2 = rXSL.CreateCell(40);
cXSL_cell2.CellFormula = (/*setter*/ "[56737.xls]Uses!$C$1");
// TODO Shouldn't it become [2] like the others?
Assert.AreEqual("[56737.xls]Uses!$C$1", cXSL_cell2.CellFormula);
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell2).FormatAsString());
// Now add a formula that refers to yet another (different) workbook
// Won't work without the workbook being linked
ICell cXSLX_nw_cell = rXSLX.CreateCell(42);
try
{
cXSLX_nw_cell.CellFormula = (/*setter*/ "[alt.xlsx]Sheet1!$A$1");
Assert.Fail("New workbook not linked, shouldn't be able to Add");
}
catch (Exception) { }
// Link and re-try
IWorkbook alt = new XSSFWorkbook();
alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
// TODO Implement the rest of this, see bug #57184
/*
* wb.LinkExternalWorkbook("alt.xlsx", alt);
*
* cXSLX_nw_cell.SetCellFormula"[alt.xlsx]Sheet1!$A$1");
* // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ?
* Assert.AreEqual("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.CellFormula);
*
* // Evaluate it, without a link to that workbook
* try {
* Evaluator.Evaluate(cXSLX_nw_cell);
* Assert.Fail("No cached value and no link to workbook, shouldn't Evaluate");
* } catch(Exception e) {}
*
* // Add a link, check it does
* Evaluators.Put("alt.xlsx", alt.GetCreationHelper().CreateFormulaEvaluator());
* Evaluator.SetupReferencedWorkbooks(evaluators);
*
* Evaluator.Evaluate(cXSLX_nw_cell);
* Assert.AreEqual("In another workbook", cXSLX_nw_cell.StringCellValue);
*/
}