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