public void TestMissingWorkbookMissingOverride()
{
ISheet lSheet = mainWorkbook.GetSheetAt(0);
ICell lA1Cell = lSheet.GetRow(0).GetCell(0);
ICell lB1Cell = lSheet.GetRow(1).GetCell(0);
ICell lC1Cell = lSheet.GetRow(2).GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
Assert.AreEqual(CellType.Formula, lC1Cell.CellType);
// Check cached values
Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(true, lC1Cell.BooleanCellValue);
// Evaluate
IFormulaEvaluator evaluator = mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
evaluator.IgnoreMissingWorkbooks = (true);
Assert.AreEqual(CellType.Numeric, evaluator.EvaluateFormulaCell(lA1Cell));
Assert.AreEqual(CellType.String, evaluator.EvaluateFormulaCell(lB1Cell));
Assert.AreEqual(CellType.Boolean, evaluator.EvaluateFormulaCell(lC1Cell));
Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(true, lC1Cell.BooleanCellValue);
}
/**
* Test invoking saved ATP functions
*
* @param TestFile either atp.xls or atp.xlsx
*/
public void BaseTestInvokeATP(String testFile)
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(testFile);
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.GetSheetAt(0);
// these two are not imlemented in r
Assert.AreEqual("DELTA(1.3,1.5)", sh.GetRow(0).GetCell(1).CellFormula);
Assert.AreEqual("COMPLEX(2,4)", sh.GetRow(1).GetCell(1).CellFormula);
ICell cell2 = sh.GetRow(2).GetCell(1);
Assert.AreEqual("ISODD(2)", cell2.CellFormula);
Assert.AreEqual(false, Evaluator.Evaluate(cell2).BooleanValue);
Assert.AreEqual(CellType.Boolean, Evaluator.EvaluateFormulaCell(cell2));
ICell cell3 = sh.GetRow(3).GetCell(1);
Assert.AreEqual("ISEVEN(2)", cell3.CellFormula);
Assert.AreEqual(true, Evaluator.Evaluate(cell3).BooleanValue);
Assert.AreEqual(CellType.Boolean, Evaluator.EvaluateFormulaCell(cell3));
wb.Close();
}
public void TestSimpleArithmetic()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
ICell c1 = r.CreateCell(0);
c1.CellFormula = (/*setter*/ "1+5");
Assert.AreEqual(0.0, c1.NumericCellValue, 0.0);
ICell c2 = r.CreateCell(1);
c2.CellFormula = (/*setter*/ "10/2");
Assert.AreEqual(0.0, c2.NumericCellValue, 0.0);
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
fe.EvaluateFormulaCell(c1);
fe.EvaluateFormulaCell(c2);
Assert.AreEqual(6.0, c1.NumericCellValue, 0.0001);
Assert.AreEqual(5.0, c2.NumericCellValue, 0.0001);
wb.Close();
}
public void TestExistingWorkbook()
{
ISheet lSheet = mainWorkbook.GetSheetAt(0);
ICell lA1Cell = lSheet.GetRow(0).GetCell(0);
ICell lB1Cell = lSheet.GetRow(1).GetCell(0);
ICell lC1Cell = lSheet.GetRow(2).GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
Assert.AreEqual(CellType.Formula, lC1Cell.CellType);
IFormulaEvaluator lMainWorkbookEvaluator = mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
IFormulaEvaluator lSourceEvaluator = sourceWorkbook.GetCreationHelper().CreateFormulaEvaluator();
Dictionary <String, IFormulaEvaluator> workbooks = new Dictionary <String, IFormulaEvaluator>();
workbooks.Add(MAIN_WORKBOOK_FILENAME, lMainWorkbookEvaluator);
workbooks.Add(SOURCE_DUMMY_WORKBOOK_FILENAME, lSourceEvaluator);
lMainWorkbookEvaluator.SetupReferencedWorkbooks(workbooks);
Assert.AreEqual(CellType.Numeric, lMainWorkbookEvaluator.EvaluateFormulaCell(lA1Cell));
Assert.AreEqual(CellType.String, lMainWorkbookEvaluator.EvaluateFormulaCell(lB1Cell));
Assert.AreEqual(CellType.Boolean, lMainWorkbookEvaluator.EvaluateFormulaCell(lC1Cell));
Assert.AreEqual(20.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("Apache rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(false, lC1Cell.BooleanCellValue);
}
public void TestSumCount()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).SetCellValue(2.5);
r.CreateCell(1).SetCellValue(1.1);
r.CreateCell(2).SetCellValue(3.2);
r.CreateCell(4).SetCellValue(10.7);
r = s.CreateRow(1);
ICell c1 = r.CreateCell(0);
c1.CellFormula = (/*setter*/ "SUM(A1:B1)");
Assert.AreEqual(0.0, c1.NumericCellValue, 0.0);
ICell c2 = r.CreateCell(1);
c2.CellFormula = (/*setter*/ "SUM(A1:E1)");
Assert.AreEqual(0.0, c2.NumericCellValue, 0.0);
ICell c3 = r.CreateCell(2);
c3.CellFormula = (/*setter*/ "COUNT(A1:A1)");
Assert.AreEqual(0.0, c3.NumericCellValue, 0.0);
ICell c4 = r.CreateCell(3);
c4.CellFormula = (/*setter*/ "COUNTA(A1:E1)");
Assert.AreEqual(0.0, c4.NumericCellValue, 0.0);
// Evaluate and Test
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
fe.EvaluateFormulaCell(c1);
fe.EvaluateFormulaCell(c2);
fe.EvaluateFormulaCell(c3);
fe.EvaluateFormulaCell(c4);
Assert.AreEqual(3.6, c1.NumericCellValue, 0.0001);
Assert.AreEqual(17.5, c2.NumericCellValue, 0.0001);
Assert.AreEqual(1, c3.NumericCellValue, 0.0001);
Assert.AreEqual(4, c4.NumericCellValue, 0.0001);
wb.Close();
}
public void TestUpdateCachedFormulaResultFromErrorToNumber_bug46479()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cellA1 = row.CreateCell(0);
ICell cellB1 = row.CreateCell(1);
cellB1.SetCellFormula("A1+1");
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
cellA1.SetCellErrorValue((byte)ErrorConstants.ERROR_NAME);
fe.EvaluateFormulaCell(cellB1);
cellA1.SetCellValue(2.5);
fe.NotifyUpdateCell(cellA1);
try
{
fe.EvaluateInCell(cellB1);
}
catch (InvalidOperationException e)
{
if (e.Message.Equals("Cannot get a numeric value from a error formula cell"))
{
throw new AssertionException("Identified bug 46479a");
}
}
Assert.AreEqual(3.5, cellB1.NumericCellValue, 0.0);
}
/// <summary>
/// Retourne la valeur de la cellule ou le résultat dans le cas d'une formule
/// </summary>
/// <param name="iCell"></param>
/// <param name="iEvalutator"></param>
/// <returns></returns>
public string GetStringVisibleValue(ICell iCell, IFormulaEvaluator iEvalutator)
{
var formulaType = iEvalutator.EvaluateFormulaCell(iCell);
if (formulaType == CellType.Numeric)
{
return(iCell.NumericCellValue.ToString());
}
else if (formulaType == CellType.String)
{
return(iCell.StringCellValue);
}
else if (formulaType == CellType.Blank)
{
return(string.Empty);
}
else if (formulaType == CellType.Boolean)
{
return(iCell.BooleanCellValue.ToString());
}
else if (formulaType == CellType.Unknown)
{
return(iCell.ToString());
}
else if (formulaType == CellType.Error)
{
throw new Exception("Erreur dans la cellule ligne {0}, colonne {1}".FormatString(iCell.RowIndex + 1, iCell.ColumnIndex + 1));
}
else
{
throw new NotSupportedException(formulaType.ToStringWithEnumName());
}
}
public void TestEvaluateRefOutsideWindowFails()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
s.CreateRow(0).CreateCell(0).CellFormula = (/*setter*/ "1+2");
Assert.AreEqual(false, s.AllRowsFlushed);
Assert.AreEqual(-1, s.LastFlushedRowNumber);
for (int i = 1; i <= 19; i++)
{
s.CreateRow(i);
}
ICell c = s.CreateRow(20).CreateCell(0);
c.CellFormula = (/*setter*/ "A1+100");
Assert.AreEqual(false, s.AllRowsFlushed);
Assert.AreEqual(15, s.LastFlushedRowNumber);
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
try
{
eval.EvaluateFormulaCell(c);
Assert.Fail("Evaluate shouldn't work, as reference outside the window");
}
catch (RowFlushedException)
{
// Expected
}
wb.Close();
}
public void Test58648FormulaParsing()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("58648.xlsx");
IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
for (int i = 0; i < wb.NumberOfSheets; i++)
{
ISheet xsheet = wb.GetSheetAt(i);
foreach (IRow row in xsheet)
{
foreach (ICell cell in row)
{
if (cell.CellType == CellType.Formula)
{
try
{
evaluator.EvaluateFormulaCell(cell);
}
catch (Exception e)
{
CellReference cellRef = new CellReference(cell.RowIndex, cell.ColumnIndex);
throw new RuntimeException("error at: " + cellRef.ToString(), e);
}
}
}
}
}
ISheet sheet = wb.GetSheet("my-sheet");
ICell cell1 = sheet.GetRow(1).GetCell(4);
Assert.AreEqual(5d, cell1.NumericCellValue, 0d);
wb.Close();
}
public void TestEvaluateSimple()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
SXSSFCell c = s.CreateRow(0).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "1+2");
Assert.AreEqual(0, (int)c.NumericCellValue);
eval.EvaluateFormulaCell(c);
Assert.AreEqual(3, (int)c.NumericCellValue);
c = s.CreateRow(1).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "CONCATENATE(\"hello\",\" \",\"world\")");
eval.EvaluateFormulaCell(c);
Assert.AreEqual("hello world", c.StringCellValue);
}
public void TestSetTypeStringOnFormulaCell()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICell cellA1 = CreateACell(wb);
IFormulaEvaluator fe = cellA1.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
cellA1.CellFormula = ("\"DEF\"");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.CellFormula = ("25.061");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(25.061, cellA1.NumericCellValue, 0.0);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("25.061", cellA1.StringCellValue);
cellA1.CellFormula = ("TRUE");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(true, cellA1.BooleanCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("TRUE", cellA1.StringCellValue);
cellA1.CellFormula = ("#NAME?");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(FormulaError.NAME, FormulaError.ForInt(cellA1.ErrorCellValue));
cellA1.SetCellType(CellType.String);
Assert.AreEqual("#NAME?", cellA1.StringCellValue);
wb.Close();
}
/**
*
* Returns the Formatted value of a cell as a <c>String</c> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formula cells will be evaluated using the given
* {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
* evaluator is null, then the formula String will be returned. The caller
* is responsible for setting the currentRow on the evaluator
*
*
* @param cell The cell (can be null)
* @param evaluator The HSSFFormulaEvaluator (can be null)
* @return a string value of the cell
*/
public String FormatCellValue(ICell cell, IFormulaEvaluator evaluator)
{
if (cell == null)
{
return("");
}
CellType cellType = cell.CellType;
if (evaluator != null && cellType == CellType.Formula)
{
try
{
cellType = evaluator.EvaluateFormulaCell(cell);
}
catch (Exception e)
{
throw new Exception("Did you forget to set the current" +
" row on the HSSFFormulaEvaluator?", e);
}
}
switch (cellType)
{
case CellType.Formula:
// should only occur if evaluator is null
return(cell.CellFormula);
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return(GetFormattedDateString(cell));
}
return(GetFormattedNumberString(cell));
case CellType.String:
return(cell.RichStringCellValue.String);
case CellType.Boolean:
return(cell.BooleanCellValue.ToString().ToUpper());
case CellType.Blank:
return("");
case CellType.Error:
return(FormulaError.ForInt(cell.ErrorCellValue).String);
}
throw new Exception("Unexpected celltype (" + cellType + ")");
}
public void TestSetTypeStringOnFormulaCell()
{
ICell cellA1 = CreateACell();
IFormulaEvaluator fe = cellA1.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
cellA1.CellFormula = ("\"DEF\"");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.CellFormula = ("25.061");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(25.061, cellA1.NumericCellValue, 0.0);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("25.061", cellA1.StringCellValue);
cellA1.CellFormula = ("TRUE");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(true, cellA1.BooleanCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("TRUE", cellA1.StringCellValue);
cellA1.CellFormula = ("#NAME?");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(ErrorConstants.ERROR_NAME, cellA1.ErrorCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("#NAME?", cellA1.StringCellValue);
}
public void TestATP()
{
//baseTestInvokeATP("atp.xls");
IWorkbook wb = HSSFITestDataProvider.Instance.OpenSampleWorkbook("atp.xls");
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.GetSheetAt(0);
// these two are not imlemented in r
Assert.AreEqual("DELTA(1.3,1.5)", sh.GetRow(0).GetCell(1).CellFormula);
Assert.AreEqual("COMPLEX(2,4)", sh.GetRow(1).GetCell(1).CellFormula);
ICell cell2 = sh.GetRow(2).GetCell(1);
Assert.AreEqual("ISODD(2)", cell2.CellFormula);
Assert.AreEqual(false, Evaluator.Evaluate(cell2).BooleanValue);
Assert.AreEqual(CellType.BOOLEAN, Evaluator.EvaluateFormulaCell(cell2));
ICell cell3 = sh.GetRow(3).GetCell(1);
Assert.AreEqual("ISEVEN(2)", cell3.CellFormula);
Assert.AreEqual(true, Evaluator.Evaluate(cell3).BooleanValue);
Assert.AreEqual(CellType.BOOLEAN, Evaluator.EvaluateFormulaCell(cell3));
}
/**
*
* Returns the Formatted value of a cell as a <c>String</c> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formula cells will be evaluated using the given
* {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
* evaluator is null, then the formula String will be returned. The caller
* is responsible for setting the currentRow on the evaluator
*
*
* @param cell The cell (can be null)
* @param evaluator The HSSFFormulaEvaluator (can be null)
* @return a string value of the cell
*/
public String FormatCellValue(ICell cell, IFormulaEvaluator evaluator)
{
if (cell == null)
{
return("");
}
CellType cellType = cell.CellType;
if (evaluator != null && cellType == CellType.Formula)
{
if (evaluator == null)
{
return(cell.CellFormula);
}
cellType = evaluator.EvaluateFormulaCell(cell);
}
switch (cellType)
{
case CellType.Formula:
// should only occur if evaluator is null
return(cell.CellFormula);
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return(GetFormattedDateString(cell));
}
return(GetFormattedNumberString(cell));
case CellType.String:
return(cell.RichStringCellValue.String);
case CellType.Boolean:
return(cell.BooleanCellValue ? "TRUE" : "FALSE");
case CellType.Blank:
return("");
case CellType.Error:
return(FormulaError.ForInt(cell.ErrorCellValue).String);
}
throw new Exception("Unexpected celltype (" + cellType + ")");
}
protected static void EvaluateAllFormulaCells(IWorkbook wb, IFormulaEvaluator evaluator)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
ISheet sheet = wb.GetSheetAt(i);
foreach (IRow r in sheet)
{
foreach (ICell c in r)
{
if (c.CellType == CellType.Formula)
{
evaluator.EvaluateFormulaCell(c);
}
}
}
}
}
private void TestIFEqualsFormulaEvaluation_evaluateFormulaCell(
String formula, CellType cellType, String expectedFormula, double expectedResult)
{
IWorkbook wb = TestIFEqualsFormulaEvaluation_setup(formula, cellType);
ICell D1 = wb.GetSheet("IFEquals").GetRow(0).GetCell(3);
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
CellType resultCellType = eval.EvaluateFormulaCell(D1);
// Call should modify the contents, but leave the formula intact
Assert.AreEqual(CellType.Formula, D1.CellType);
Assert.AreEqual(expectedFormula, D1.CellFormula);
Assert.AreEqual(CellType.Numeric, resultCellType);
Assert.AreEqual(CellType.Numeric, D1.CachedFormulaResultType);
Assert.AreEqual(expectedResult, D1.NumericCellValue, EPSILON);
TestIFEqualsFormulaEvaluation_teardown(wb);
}
private static void EvaluateAllFormulaCells(IWorkbook wb, IFormulaEvaluator evaluator)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
ISheet sheet = wb.GetSheetAt(i);
for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext();)
{
IRow r = (IRow)it.Current;
foreach (ICell c in r.Cells)
{
if (c.CellType == CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
}
}
}
}
}
public void TestMissingWorkbookMissing()
{
IFormulaEvaluator evaluator = mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
ISheet lSheet = mainWorkbook.GetSheetAt(0);
IRow lARow = lSheet.GetRow(0);
ICell lA1Cell = lARow.GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
try
{
evaluator.EvaluateFormulaCell(lA1Cell);
Assert.Fail("Missing external workbook reference exception expected!");
}
catch (RuntimeException re)
{
Assert.IsTrue(re.Message.IndexOf(SOURCE_DUMMY_WORKBOOK_FILENAME) != -1, "Unexpected exception: " + re);
}
}
// TODO should we have this stuff in the FormulaEvaluator?
private void EvaluateWorkbook(IWorkbook workbook)
{
IFormulaEvaluator eval = workbook.GetCreationHelper().CreateFormulaEvaluator();
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
foreach (IRow r in sheet)
{
foreach (ICell c in r)
{
if (c.CellType == CellType.Formula)
{
eval.EvaluateFormulaCell(c);
}
}
}
}
}
public void TestPlainValueCache()
{
HSSFWorkbook wb = new HSSFWorkbook();
int numberOfSheets = 4098; // Bug 51448 reported that Evaluation Cache got messed up After 256 sheets
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = "SUM(A2:A" + (numberOfSheets + 2) + ")";
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = "new" + i + "!A1";
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
Assert.AreEqual(8394753.0, summaryCell.NumericCellValue);
}
public void TestEvaluateRefInsideWindow()
{
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.CreateSheet() as SXSSFSheet;
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
SXSSFCell c = s.CreateRow(0).CreateCell(0) as SXSSFCell;
c.SetCellValue(1.5);
c = s.CreateRow(1).CreateCell(0) as SXSSFCell;
c.CellFormula = (/*setter*/ "A1*2");
Assert.AreEqual(0, (int)c.NumericCellValue);
eval.EvaluateFormulaCell(c);
Assert.AreEqual(3, (int)c.NumericCellValue);
wb.Close();
}
// TODO should we have this stuff in the FormulaEvaluator?
private void EvaluateWorkbook(IWorkbook workbook)
{
IFormulaEvaluator eval = workbook.GetCreationHelper().CreateFormulaEvaluator();
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
IEnumerator rows = sheet.GetRowEnumerator();
for (IRow r = null; rows.MoveNext();)
{
r = (IRow)rows.Current;
foreach (ICell c in r)
{
if (c.CellType == CellType.Formula)
{
eval.EvaluateFormulaCell(c);
}
}
}
}
}
public void TestConvertStringFormulaCell()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICell cellA1 = CreateACell(wb);
cellA1.CellFormula = ("\"abc\"");
// default cached formula result is numeric zero
Assert.AreEqual(0.0, cellA1.NumericCellValue, 0.0);
IFormulaEvaluator fe = cellA1.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
fe.EvaluateFormulaCell(cellA1);
Assert.AreEqual("abc", cellA1.StringCellValue);
fe.EvaluateInCell(cellA1);
Assert.IsFalse(cellA1.StringCellValue.Equals(""), "Identified bug with writing back formula result of type string");
Assert.AreEqual("abc", cellA1.StringCellValue);
wb.Close();
}
public void TestConvertStringFormulaCell()
{
ICell cellA1 = CreateACell();
cellA1.CellFormula = ("\"abc\"");
// default cached formula result is numeric zero
Assert.AreEqual(0.0, cellA1.NumericCellValue, 0.0);
IFormulaEvaluator fe = cellA1.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
fe.EvaluateFormulaCell(cellA1);
Assert.AreEqual("abc", cellA1.StringCellValue);
fe.EvaluateInCell(cellA1);
if (cellA1.StringCellValue.Equals(""))
{
throw new AssertionException("Identified bug with writing back formula result of type string");
}
Assert.AreEqual("abc", cellA1.StringCellValue);
}
/// <summary>
/// Permite convertir el valor de la celda en el valor apropiado de
/// acuerdo al tipo del atributo.
/// <remarks>Hay que destacar que el value algunas veces se auto-asigna como String en lugar
/// de ICell. Eso le da velocidad de conversión, sin embargo en el caso de que la celda contenga una
/// formula es necesario el objeto ICell como tal.</remarks>
/// </summary>
/// <param name="propertyInfo">Información de la propiedad</param>
/// <param name="value">Valor de la celda</param>
/// <param name="cell">Este objeto </param>
/// <returns></returns>
private object ConvertToAttributeType(PropertyInfo propertyInfo, object value, ICell cell)
{
if (value == null)
{
return(null);
}
//La conversión directa desde object es mucho más eficiente.
//Si falla entonces forza la conversión en las lineas siguientes.
try
{
if (IsString(propertyInfo))
{
if (cell?.CellType == CellType.Formula)
{
_formulaEvaluator.EvaluateFormulaCell(cell);
return(cell.StringCellValue);
}
return(value?.ToString());
}
}
catch (Exception e)
{
Debug.WriteLine("La celda no tiene formato de texto, se forzara su conversión." +
"\n PropertyInfo:" + propertyInfo + "\n Value:" + value + "\n Type:" +
typeof(T).FullName + "\n Message:" + e.Message + "\n Stacktrace:" + e.StackTrace);
}
object dateTimeConvertion;
if (ConvertFromDateTime(propertyInfo, value, out dateTimeConvertion))
{
return(dateTimeConvertion);
}
return(ConvertFromNumericTypes(propertyInfo, value));
}
private static void TestPlainValueCache(HSSFWorkbook wb, int numberOfSheets)
{
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = ("SUM(A2:A" + (numberOfSheets + 2) + ")");
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = ("new" + i + "!A1");
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
}
/**
*
* Returns the Formatted value of a cell as a <c>String</c> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formula cells will be evaluated using the given
* {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
* evaluator is null, then the formula String will be returned. The caller
* is responsible for setting the currentRow on the evaluator
*
*
* @param cell The cell (can be null)
* @param evaluator The HSSFFormulaEvaluator (can be null)
* @return a string value of the cell
*/
public String FormatCellValue(ICell cell, IFormulaEvaluator evaluator)
{
if (cell == null)
{
return "";
}
CellType cellType = cell.CellType;
if (evaluator != null && cellType == CellType.FORMULA)
{
try
{
cellType = evaluator.EvaluateFormulaCell(cell);
}
catch (Exception e)
{
throw new Exception("Did you forget to set the current" +
" row on the HSSFFormulaEvaluator?", e);
}
}
switch (cellType)
{
case CellType.FORMULA:
// should only occur if evaluator is null
return cell.CellFormula;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
return GetFormattedDateString(cell);
}
return GetFormattedNumberString(cell);
case CellType.STRING:
return cell.RichStringCellValue.String;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString().ToUpper();
case CellType.BLANK:
return "";
}
throw new Exception("Unexpected celltype (" + cellType + ")");
}
public void TestJoinSingleLiteralText()
{
evaluator.ClearAllCachedResultValues();
formulaCell.SetCellFormula("TEXTJOIN(\",\", true, \"Text\")");
evaluator.EvaluateFormulaCell(formulaCell);
Assert.AreEqual("Text", formulaCell.StringCellValue);
}
/**
* Should be able to write then read formulas with references
* to cells in other files, eg '[refs/airport.xls]Sheet1'!$A$2
* or 'http://192.168.1.2/[blank.xls]Sheet1'!$A$1 .
* Additionally, if a reference to that file is provided, it should
* be possible to Evaluate them too
* TODO Fix this to Evaluate for XSSF
* TODO Fix this to work at all for HSSF
*/
// [Test]
public void bug46670()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r1 = s.CreateRow(0);
// References to try
String ext = "xls";
if (!(wb is HSSFWorkbook))
{
ext += "x";
}
String refLocal = "'[test." + ext + "]Sheet1'!$A$2";
String refHttp = "'[http://example.com/test." + ext + "]Sheet1'!$A$2";
String otherCellText = "In Another Workbook";
// Create the references
ICell c1 = r1.CreateCell(0, CellType.Formula);
c1.CellFormula = (/*setter*/ refLocal);
ICell c2 = r1.CreateCell(1, CellType.Formula);
c2.CellFormula = (/*setter*/ refHttp);
// Check they were Set correctly
Assert.AreEqual(refLocal, c1.CellFormula);
Assert.AreEqual(refHttp, c2.CellFormula);
// Reload, and ensure they were serialised and read correctly
wb = _testDataProvider.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r1 = s.GetRow(0);
c1 = r1.GetCell(0);
c2 = r1.GetCell(1);
Assert.AreEqual(refLocal, c1.CellFormula);
Assert.AreEqual(refHttp, c2.CellFormula);
// Try to Evalutate, without giving a way to Get at the other file
try
{
EvaluateCell(wb, c1);
Assert.Fail("Shouldn't be able to Evaluate without the other file");
}
catch (Exception) { }
try
{
EvaluateCell(wb, c2);
Assert.Fail("Shouldn't be able to Evaluate without the other file");
}
catch (Exception) { }
// Set up references to the other file
IWorkbook wb2 = _testDataProvider.CreateWorkbook();
wb2.CreateSheet().CreateRow(1).CreateCell(0).SetCellValue(otherCellText);
Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>();
evaluators.Add(refLocal, wb2.GetCreationHelper().CreateFormulaEvaluator());
evaluators.Add(refHttp, wb2.GetCreationHelper().CreateFormulaEvaluator());
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.SetupReferencedWorkbooks(/*setter*/ evaluators);
// Try to Evaluate, with the other file
Evaluator.EvaluateFormulaCell(c1);
Evaluator.EvaluateFormulaCell(c2);
Assert.AreEqual(otherCellText, c1.StringCellValue);
Assert.AreEqual(otherCellText, c2.StringCellValue);
}
public void TestRandBetweenSameValues()
{
Evaluator.ClearAllCachedResultValues();
formulaCell.CellFormula = ("RANDBETWEEN(1,1)");
Evaluator.EvaluateFormulaCell(formulaCell);
Assert.AreEqual(1, formulaCell.NumericCellValue, 0);
Evaluator.ClearAllCachedResultValues();
formulaCell.CellFormula = ("RANDBETWEEN(-1,-1)");
Evaluator.EvaluateFormulaCell(formulaCell);
Assert.AreEqual(-1, formulaCell.NumericCellValue, 0);
}
/**
*
* Returns the Formatted value of a cell as a <c>String</c> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formula cells will be evaluated using the given
* {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
* evaluator is null, then the formula String will be returned. The caller
* is responsible for setting the currentRow on the evaluator
*
*
* @param cell The cell (can be null)
* @param evaluator The HSSFFormulaEvaluator (can be null)
* @return a string value of the cell
*/
public String FormatCellValue(ICell cell, IFormulaEvaluator evaluator)
{
if (cell == null)
{
return "";
}
CellType cellType = cell.CellType;
if (evaluator != null && cellType == CellType.Formula)
{
if (evaluator == null)
{
return cell.CellFormula;
}
cellType = evaluator.EvaluateFormulaCell(cell);
}
switch (cellType)
{
case CellType.Formula:
// should only occur if evaluator is null
return cell.CellFormula;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return GetFormattedDateString(cell);
}
return GetFormattedNumberString(cell);
case CellType.String:
return cell.RichStringCellValue.String;
case CellType.Boolean:
return cell.BooleanCellValue.ToString().ToUpper();
case CellType.Blank:
return "";
case CellType.Error:
return FormulaError.ForInt(cell.ErrorCellValue).String;
}
throw new Exception("Unexpected celltype (" + cellType + ")");
}
private static void EvaluateAllFormulaCells(IWorkbook wb, IFormulaEvaluator evaluator)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
ISheet sheet = wb.GetSheetAt(i);
for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext(); )
{
IRow r = (IRow)it.Current;
foreach (ICell c in r.Cells)
{
if (c.CellType == CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
}
}
}
}
}