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();
}
}
public void Test48539()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48539.xlsx");
Assert.AreEqual(3, wb.NumberOfSheets);
// Try each cell individually
XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator(wb);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
ISheet s = wb.GetSheetAt(i);
foreach (IRow r in s)
{
foreach (ICell c in r)
{
if (c.CellType == CellType.FORMULA)
{
CellValue cv = eval.Evaluate(c);
if (cv.CellType == CellType.NUMERIC)
{
// assert that the calculated value agrees with
// the cached formula result calculated by Excel
double cachedFormulaResult = c.NumericCellValue;
double EvaluatedFormulaResult = cv.NumberValue;
Assert.AreEqual(cachedFormulaResult, EvaluatedFormulaResult, 1E-7, c.CellFormula);
}
}
}
}
}
// Now all of them
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
}
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 e) { }
// 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 e) {}
// 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);
*/
}
public void Bug48539()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("48539.xlsx");
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual(0, wb.NumberOfNames);
// Try each cell individually
XSSFFormulaEvaluator eval = new XSSFFormulaEvaluator(wb);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
ISheet s = wb.GetSheetAt(i);
foreach (IRow r in s)
{
foreach (ICell c in r)
{
if (c.CellType == CellType.Formula)
{
String formula = c.CellFormula;
CellValue cv;
try
{
cv = eval.Evaluate(c);
}
catch (Exception e)
{
throw new RuntimeException("Can't evaluate formula: " + formula, e);
}
if (cv.CellType == CellType.Numeric)
{
// assert that the calculated value agrees with
// the cached formula result calculated by Excel
double cachedFormulaResult = c.NumericCellValue;
double EvaluatedFormulaResult = cv.NumberValue;
Assert.AreEqual(cachedFormulaResult, EvaluatedFormulaResult, 1E-7, c.CellFormula);
}
}
}
}
}
// Now all of them
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
}
public void Test57196_Detail2()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet("Sheet1") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCell cell = row.CreateCell(0) as XSSFCell;
cell.CellFormula = (/*setter*/"DEC2HEX(O2+D2)");
XSSFFormulaEvaluator fe = new XSSFFormulaEvaluator(wb);
CellValue cv = fe.Evaluate(cell);
Assert.IsNotNull(cv);
}
private void CheckValue(XSSFWorkbook excel, String expect)
{
XSSFFormulaEvaluator Evaluator = new XSSFFormulaEvaluator(excel);
Evaluator.EvaluateAll();
XSSFCell cell = excel.GetSheetAt(0).GetRow(1).GetCell(1) as XSSFCell;
CellValue value = Evaluator.Evaluate(cell);
Assert.AreEqual(expect, value.FormatAsString());
}
