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