// FIXME: use junit4 parameterization
private static void verifyAllFormulasInWorkbookCanBeEvaluated(String sampleWorkbook)
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook(sampleWorkbook);
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
wb.Close();
}
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, 0);
// 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, 0);
// 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);
}
}
// And evaluate the other way too
evaluator.EvaluateAll();
// Static evaluator won't work, as no references passed in
try
{
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.Fail("Static method lacks references, shouldn't work");
}
catch (Exception)
{
// expected here
}
// Evaluate specific cells and check results
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();
try
{
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.getCellFormula());
*
* // Evaluate it, without a link to that workbook
* try {
* evaluator.evaluate(cXSLX_nw_cell);
* 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.getStringCellValue());
*/
}
finally
{
alt.Close();
}
wb.Close();
}