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); */ }