Пример #1
1
        /// <summary>
        /// Gets the value.
        /// </summary>
        /// <param name="cell">The ICell object.</param>
        /// <param name="dataFormatter">The data formatter.</param>
        /// <param name="formulaEvaluator">The formula evaluator.</param>
        /// <returns>Value string.</returns>
        public static string GetValue(ICell cell, DataFormatter dataFormatter, IFormulaEvaluator formulaEvaluator)
        {
            string ret = string.Empty;

            if (null == cell)
            {
                return ret;
            }

            ret = dataFormatter.FormatCellValue(cell, formulaEvaluator);

            // remove line break
            return ret.Replace("\n", " ");
        }
Пример #2
0
        private void buildWorkbook(IWorkbook wb)
        {
            ISheet sh = wb.CreateSheet();
            IRow row1 = sh.CreateRow(0);
            IRow row2 = sh.CreateRow(1);
            row3 = sh.CreateRow(2);

            row1.CreateCell(0, CellType.Numeric);
            row1.CreateCell(1, CellType.Numeric);

            row2.CreateCell(0, CellType.Numeric);
            row2.CreateCell(1, CellType.Numeric);

            row3.CreateCell(0);
            row3.CreateCell(1);

            CellReference a1 = new CellReference("A1");
            CellReference a2 = new CellReference("A2");
            CellReference b1 = new CellReference("B1");
            CellReference b2 = new CellReference("B2");

            sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(35);
            sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(0);
            sh.GetRow(b1.Row).GetCell(b1.Col).CellFormula = (/*setter*/"A1/A2");
            sh.GetRow(b2.Row).GetCell(b2.Col).CellFormula = (/*setter*/"NA()");

            Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
        }
Пример #3
0
        public void TestValidXSSF()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            Evaluator = new XSSFFormulaEvaluator(wb);

            Confirm(wb);
        }
 //
 // Initialize from a stream of Excel file
 //
 protected void InitializeMembers(Stream excelFileStream)
 {
     this.workbook = WorkbookFactory.Create(excelFileStream);
     if (this.workbook != null)
     {
         this.dataFormatter = new DataFormatter(CultureInfo.InvariantCulture);
         this.formulaEvaluator = WorkbookFactory.CreateFormulaEvaluator(this.workbook);
     }
 }
Пример #5
0
        public void SetUp()
        {
            wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRandBetween.xls");
            Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            ISheet sheet = wb.CreateSheet("RandBetweenSheet");
            IRow row = sheet.CreateRow(0);
            bottomValueCell = row.CreateCell(0);
            topValueCell = row.CreateCell(1);
            formulaCell = row.CreateCell(2, CellType.FORMULA);
        }
        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);
        }
Пример #7
0
        public static IWorkbook CreateExeclFile(string templatePath)
        {
            if (!File.Exists(templatePath))
            {
                NXOpen.UI.GetUI().NXMessageBox.Show("错误", NXMessageBox.DialogType.Error, "没找到模板");
                return(null);
            }
            FileStream        fs1              = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
            IWorkbook         workBook         = null;
            IFormulaEvaluator formulaEvaluator = null;

            if (templatePath.IndexOf(".xlsx") > 0 || templatePath.IndexOf(".xlsm") > 0) // 2007版本
            {
                workBook         = new XSSFWorkbook(fs1);
                formulaEvaluator = new XSSFFormulaEvaluator(workBook);
            }
            else if (templatePath.IndexOf(".xls") > 0) // 2003版本
            {
                workBook         = new HSSFWorkbook(fs1);
                formulaEvaluator = new HSSFFormulaEvaluator(workBook);
            }
            return(workBook);
        }
        public void TestRounding_bug51339()
        {
            IWorkbook wb     = _testDataProvider.CreateWorkbook();
            ISheet    sheet  = wb.CreateSheet("Sheet1");
            IRow      row    = sheet.CreateRow(0);
            ICell     cellA1 = row.CreateCell(0);

            cellA1.SetCellValue(2162.615d);
            ICell cellB1 = row.CreateCell(1);

            cellB1.CellFormula = (/*setter*/ "round(a1,2)");
            ICell cellC1 = row.CreateCell(2);

            cellC1.CellFormula = (/*setter*/ "roundup(a1,2)");
            ICell cellD1 = row.CreateCell(3);

            cellD1.CellFormula = (/*setter*/ "rounddown(a1,2)");
            IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();

            Assert.AreEqual(2162.62, fe.EvaluateInCell(cellB1).NumericCellValue, 0.0);
            Assert.AreEqual(2162.62, fe.EvaluateInCell(cellC1).NumericCellValue, 0.0);
            Assert.AreEqual(2162.61, fe.EvaluateInCell(cellD1).NumericCellValue, 0.0);
        }
Пример #9
0
        /**
         * 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));
        }
Пример #10
0
        private static void checkAndFillDataRow(int rowIndex, IRow rowObj, IFormulaEvaluator evaluator, DataTable dt)
        {
            if (rowObj != null)
            {
                bool          hasValue = false;
                List <object> cells    = new List <object>();

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    object objValue = null;
                    ICell  cell     = rowObj.GetCell(j);
                    if (cell != null)
                    {
                        if (cell is HSSFCell)
                        {
                            objValue = getValueTypeForXLS((HSSFCell)cell);
                        }
                        else if (cell is XSSFCell)
                        {
                            objValue = getValueTypeForXLSX((XSSFCell)cell);
                        }
                    }

                    cells.Add(objValue != null ? objValue.ToString().Trim() : string.Empty);

                    if (objValue != null && !string.IsNullOrEmpty(objValue.ToString().Trim()))
                    {
                        hasValue = true;
                    }
                }

                if (hasValue)
                {
                    dt.Rows.Add(cells.ToArray());
                }
            }
        }
Пример #11
0
        public string tableStyle = "border-collapse: collapse;";//font-family: helvetica, arial, sans-serif;
        public string GetHtMLNPOI(string excelfilePath)
        {
            Workbook = GetWorkbook(excelfilePath);
            var numberofSheets          = Workbook.NumberOfSheets;
            var worksheet               = Workbook.GetSheetAt(0);
            IFormulaEvaluator evaluator = Workbook.GetCreationHelper().CreateFormulaEvaluator();
            var rowbreaks               = worksheet.RowBreaks;

            StringBuilder builder = new StringBuilder();

            for (int i = 0; i <= worksheet.LastRowNum; i++)
            {
                var row = worksheet.GetRow(i);
                if (row == null)
                {
                    builder.AppendLine("<tr></tr>");
                    continue;
                }
                if (row.Hidden.HasValue)
                {
                    if (!row.Hidden.Value)
                    {
                        builder.AppendLine("<tr>");

                        foreach (var cell in row)
                        {
                            var columnWidth = worksheet.GetColumnWidthInPixels(cell.ColumnIndex);
                            var style       = GetCellStyle(cell, columnWidth);
                            builder.AppendLine($"<td style=\"{style}\">{cell.GetFormattedCellValue(evaluator)}</td>");
                        }
                        builder.AppendLine("</tr>");
                    }
                }
            }

            return($"<table style=\"{tableStyle}\">{builder}</table>");
        }
Пример #12
0
        /// <summary>
        /// 获取单元格的显示值
        /// </summary>
        /// <param name="evaluator">单元格公式计算器</param>
        /// <param name="cell">单元格</param>
        /// <returns>单元格显示的值</returns>
        private string GetCellValue(IFormulaEvaluator evaluator, ICell cell)
        {
            switch (cell.CellType)
            {
            case CellType.Blank:
                return(string.Empty);

            case CellType.Boolean:
                return(cell.BooleanCellValue.ToString());

            case CellType.Error:
                return(cell.ErrorCellValue.ToString());

            case CellType.Formula:
                cell = evaluator.EvaluateInCell(cell);
                return(GetCellValue(evaluator, cell));

            case CellType.Numeric:
                if (DateUtil.IsCellDateFormatted(cell))
                {
                    return(cell.DateCellValue.ToString());
                }
                else
                {
                    return(cell.NumericCellValue.ToString());
                }

            case CellType.String:
                return(cell.StringCellValue);

            case CellType.Unknown:
                return("Unknow");

            default:
                return("");
            }
        }
Пример #13
0
        /// <summary>
        /// Get the index value of the last row with actual data in it from the specified sheet.
        /// </summary>
        /// <param name="sheet"> the sheet containing the data. </param>
        /// <returns> index of the last row with actual data in it.</returns>
        public static int GetLastRowWithData(ISheet sheet)
        {
            IFormulaEvaluator evaluator = sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
            DataFormatter     formatter = new DataFormatter(true);

            int lastRowIndex = -1;

            if (sheet.PhysicalNumberOfRows > 0)
            {
                // getLastRowNum() actually returns an index, not a row number
                lastRowIndex = sheet.LastRowNum;

                // now, start at end of spreadsheet and work our way backwards until we find a row having data
                for ( ; lastRowIndex >= 0; lastRowIndex--)
                {
                    IRow row = sheet.GetRow(lastRowIndex);
                    if (!IsRowEmpty(row, evaluator, formatter))
                    {
                        break;
                    }
                }
            }
            return(lastRowIndex);
        }
Пример #14
0
        public void TestFullColumnRefs()
        {
            IWorkbook wb    = _testDataProvider.CreateWorkbook();
            ISheet    sheet = wb.CreateSheet("Sheet1");
            IRow      row   = sheet.CreateRow(0);
            ICell     cell0 = row.CreateCell(0);

            cell0.CellFormula = (/*setter*/ "sum(D:D)");
            ICell cell1 = row.CreateCell(1);

            cell1.CellFormula = (/*setter*/ "sum(D:E)");

            // some values in column D
            SetValue(sheet, 1, 3, 5.0);
            SetValue(sheet, 2, 3, 6.0);
            SetValue(sheet, 5, 3, 7.0);
            SetValue(sheet, 50, 3, 8.0);

            // some values in column E
            SetValue(sheet, 1, 4, 9.0);
            SetValue(sheet, 2, 4, 10.0);
            SetValue(sheet, 30000, 4, 11.0);

            // some other values
            SetValue(sheet, 1, 2, 100.0);
            SetValue(sheet, 2, 5, 100.0);
            SetValue(sheet, 3, 6, 100.0);


            IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();

            Assert.AreEqual(26.0, fe.Evaluate(cell0).NumberValue, 0.0);
            Assert.AreEqual(56.0, fe.Evaluate(cell1).NumberValue, 0.0);

            wb.Close();
        }
Пример #15
0
        private void TestIFEqualsFormulaEvaluation_evaluateInCell(
            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();
            ICell             result = eval.EvaluateInCell(D1);

            // Call should modify the contents and replace the formula with the result
            Assert.AreSame(D1, result); // returns the same cell that was provided as an argument so that calls can be chained.
            try
            {
                string tmp = D1.CellFormula;
                Assert.Fail("cell formula should be overwritten with formula result");
            }
            catch (InvalidOperationException)
            {
            }
            Assert.AreEqual(CellType.Numeric, D1.CellType);
            Assert.AreEqual(expectedResult, D1.NumericCellValue, EPSILON);

            TestIFEqualsFormulaEvaluation_teardown(wb);
        }
Пример #16
0
        /**
         * Builds new numeric cache Container.
         * @param marker data marker to use for cache Evaluation
         * @param ctNumRef parent number reference
         * @return numeric cache instance
         */
        internal static XSSFNumberCache BuildCache(DataMarker marker, CT_NumRef ctNumRef)
        {
            CellRangeAddress range = marker.Range;
            int numOfPoints        = range.NumberOfCells;

            if (numOfPoints == 0)
            {
                // Nothing to do.
                return(null);
            }

            XSSFNumberCache cache = new XSSFNumberCache(ctNumRef.AddNewNumCache());

            cache.SetPointCount(numOfPoints);

            IWorkbook         wb        = marker.Sheet.Workbook;
            IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            CellWalk            cellWalk        = new CellWalk(marker);
            NumCacheCellHandler numCacheHandler = new NumCacheCellHandler(Evaluator, cache.ctNumData);

            cellWalk.Traverse(numCacheHandler);
            return(cache);
        }
        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);
        }
Пример #18
0
 private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
 {
     using (DataTable dt = new DataTable())
     {
         IRow firstRow = sheet.GetRow(0) as IRow; int cellCount = GetCellCount(sheet);
         for (int i = 0; i < cellCount; i++)
         {
             if (firstRow.GetCell(i) != null)
             {
                 dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
             }
             else
             {
                 dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
             }
         }
         for (int i = 1; i <= sheet.LastRowNum; i++)
         {
             IRow row = sheet.GetRow(i) as IRow; DataRow dr = dt.NewRow();
             FillDataRowByHSSFRow(row, evaluator, ref dr); dt.Rows.Add(dr);
         }
         dt.TableName = sheet.SheetName; return(dt);
     }
 }
Пример #19
0
        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 TestSheetLevelFormulas()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();

            IRow   row;
            ISheet sh1 = wb.CreateSheet("Sheet1");
            IName  nm1 = wb.CreateName();

            nm1.NameName        = (/*setter*/ "sales_1");
            nm1.SheetIndex      = (/*setter*/ 0);
            nm1.RefersToFormula = (/*setter*/ "Sheet1!$A$1");
            row = sh1.CreateRow(0);
            row.CreateCell(0).SetCellValue(3);
            row.CreateCell(1).CellFormula = (/*setter*/ "sales_1");
            row.CreateCell(2).CellFormula = (/*setter*/ "sales_1*2");

            ISheet sh2 = wb.CreateSheet("Sheet2");
            IName  nm2 = wb.CreateName();

            nm2.NameName        = (/*setter*/ "sales_1");
            nm2.SheetIndex      = (/*setter*/ 1);
            nm2.RefersToFormula = (/*setter*/ "Sheet2!$A$1");

            row = sh2.CreateRow(0);
            row.CreateCell(0).SetCellValue(5);
            row.CreateCell(1).CellFormula = (/*setter*/ "sales_1");
            row.CreateCell(2).CellFormula = (/*setter*/ "sales_1*3");

            IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            Assert.AreEqual(3.0, Evaluator.Evaluate(sh1.GetRow(0).GetCell(1)).NumberValue, 0.0);
            Assert.AreEqual(6.0, Evaluator.Evaluate(sh1.GetRow(0).GetCell(2)).NumberValue, 0.0);

            Assert.AreEqual(5.0, Evaluator.Evaluate(sh2.GetRow(0).GetCell(1)).NumberValue, 0.0);
            Assert.AreEqual(15.0, Evaluator.Evaluate(sh2.GetRow(0).GetCell(2)).NumberValue, 0.0);
        }
Пример #21
0
        public void TestEvaluate()
        {
            TestCases.CultureShim.SetCurrentCulture("en-US");

            HSSFWorkbook wb    = new HSSFWorkbook();
            ISheet       sh    = wb.CreateSheet();
            ICell        cell1 = sh.CreateRow(0).CreateCell(0);

            cell1.CellFormula = ("MROUND(10, 3)");
            ICell cell2 = sh.CreateRow(0).CreateCell(0);

            cell2.CellFormula = ("MROUND(-10, -3)");
            ICell cell3 = sh.CreateRow(0).CreateCell(0);

            cell3.CellFormula = ("MROUND(1.3, 0.2)");
            ICell cell4 = sh.CreateRow(0).CreateCell(0);

            cell4.CellFormula = ("MROUND(5, -2)");
            ICell cell5 = sh.CreateRow(0).CreateCell(0);

            cell5.CellFormula = ("MROUND(5, 0)");

            double accuracy = 1E-9;

            IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            Assert.AreEqual(9.0, Evaluator.Evaluate(cell1).NumberValue, accuracy, "Rounds 10 to a nearest multiple of 3 (9)");

            Assert.AreEqual(-9.0, Evaluator.Evaluate(cell2).NumberValue, accuracy, "Rounds -10 to a nearest multiple of -3 (-9)");

            Assert.AreEqual(1.4, Evaluator.Evaluate(cell3).NumberValue, accuracy, "Rounds 1.3 to a nearest multiple of 0.2 (1.4)");

            Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, Evaluator.Evaluate(cell4).ErrorValue, "Returns an error, because -2 and 5 have different signs (#NUM!)");

            Assert.AreEqual(0.0, Evaluator.Evaluate(cell5).NumberValue, "Returns 0 because the multiple is 0");
        }
        private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                if (sheet.LastRowNum != 0)
                {
                    int cellCount = GetCellCount(sheet);

                    for (int i = 0; i < cellCount; i++)
                    {
                        dt.Columns.Add(string.Format("F{0}", i), typeof(string));
                    }

                    for (int i = 0; i < sheet.FirstRowNum; ++i)
                    {
                        DataRow dr = dt.NewRow();
                        dt.Rows.Add(dr);
                    }

                    for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        //skip hidden rows, shiqin, 20141121
                        if (row != null && !row.ZeroHeight)
                        {
                            DataRow dr = dt.NewRow();
                            FillDataRowByHSSFRow(row, evaluator, ref dr, sheet);
                            dt.Rows.Add(dr);
                        }
                    }
                }

                dt.TableName = sheet.SheetName;
                return(dt);
            }
        }
Пример #23
0
        /**
         * Loops over all cells in all sheets of the supplied
         *  workbook.
         * For cells that contain formulas, their formulas are
         *  evaluated, and the results are saved. These cells
         *  remain as formula cells.
         * For cells that do not contain formulas, no changes
         *  are made.
         * This is a helpful wrapper around looping over all
         *  cells, and calling evaluateFormulaCell on each one.
         */
        public static void EvaluateAllFormulaCells(IWorkbook wb)
        {
            IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            EvaluateAllFormulaCells(wb, evaluator);
        }
Пример #24
0
        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);
                        }
                    }
                }
            }
        }
Пример #25
0
        public static DataTable ExcelImportToDataTable(FileStream file)
        {
            DataTable dt = new DataTable();

            //.xlsx  //.xlsm
            #region .xlsx文件处理:XSSFWorkbook
            try
            {
                IWorkbook wb    = WorkbookFactory.Create(file);
                ISheet    sheet = wb.GetSheetAt(0);//.GetSheet(sheetName);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                IRow headerRow = sheet.GetRow(0);
                //一行最后一个方格的编号 即总的列数
                for (int j = 0; j < sheet.GetRow(0).LastCellNum; j++)
                {
                    //SET EVERY COLUMN NAME
                    ICell cell = headerRow.GetCell(j);
                    if (cell != null && cell.ToString() != "")
                    {
                        dt.Columns.Add(cell.ToString());
                    }
                    else
                    {
                        continue;
                    }
                }
                int colCount = dt.Columns.Count;
                while (rows.MoveNext())
                {
                    //IRow row = (XSSFRow)sheet.GetRow(j);
                    IRow    row   = (IRow)rows.Current;
                    DataRow dr    = dt.NewRow();
                    bool    addDR = false;
                    if (row.RowNum == 0)
                    {
                        continue;//The firt row is title,no need import
                    }
                    for (int i = 0; i < colCount; i++)
                    {
                        //cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213                 
                        //cell count>column count,then break //每条记录的单元格数量不能大于DataTable的title
                        if (i >= colCount)
                        {
                            break;
                        }

                        ICell cell = row.GetCell(i);

                        if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
                        {
                            break;
                        }
                        if (cell != null)
                        {
                            object o = cell;
                            //读取Excel格式,根据格式读取数据类型
                            switch (cell.CellType)
                            {
                            case CellType.Blank:     //空数据类型处理
                                o = "";
                                break;

                            case CellType.String:     //字符串类型
                                o = cell.StringCellValue;
                                break;

                            case CellType.Numeric:     //数字类型
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    o = cell.DateCellValue;
                                }
                                else
                                {
                                    o = cell.ToString();
                                }
                                break;

                            case CellType.Formula:
                                //HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
                                IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(wb);
                                o = e.Evaluate(cell).StringValue;
                                break;

                            default:
                                o = "";
                                break;
                            }
                            dr[i] = Convert.ToString(o);//row.GetCell(j).StringCellValue;
                            addDR = true;
                        }
                    }
                    if (addDR)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            return(dt);
        }
Пример #26
0
        /**
         * 
         * @return a constant from the local Result class denoting whether there were any Evaluation
         * cases, and whether they all succeeded.
         */
        private int ProcessFunctionRow(IFormulaEvaluator Evaluator, String targetFunctionName,
                IRow formulasRow, IRow expectedValuesRow)
        {

            int result = Result.NO_EVALUATIONS_FOUND; // so far
            short endcolnum = formulasRow.LastCellNum;

            // iterate across the row for all the Evaluation cases
            for (short colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
            {
                ICell c = formulasRow.GetCell(colnum);
                if (c == null || c.CellType != CellType.Formula)
                {
                    continue;
                }
                if (IsIgnoredFormulaTestCase(c.CellFormula))
                {
                    continue;
                }

                CellValue actualValue;
                try
                {
                    actualValue = Evaluator.Evaluate(c);
                }
                catch (RuntimeException e)
                {
                    _EvaluationFailureCount++;
                    PrintshortStackTrace(System.Console.Error, e);
                    result = Result.SOME_EVALUATIONS_FAILED;
                    continue;
                }

                ICell expectedValueCell = GetExpectedValueCell(expectedValuesRow, colnum);
                try
                {
                    ConfirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.CellFormula + " @ " + formulasRow.RowNum + ":" + colnum,
                            expectedValueCell, actualValue);
                    _EvaluationSuccessCount++;
                    if (result != Result.SOME_EVALUATIONS_FAILED)
                    {
                        result = Result.ALL_EVALUATIONS_SUCCEEDED;
                    }
                }
                catch (AssertionException e)
                {
                    _EvaluationFailureCount++;
                    PrintshortStackTrace(System.Console.Error, e);
                    result = Result.SOME_EVALUATIONS_FAILED;
                }
            }
            return result;
        }
Пример #27
0
 public NumCacheCellHandler(IFormulaEvaluator Evaluator, CT_NumData ctnumdata)
 {
     this.Evaluator = Evaluator;
     this.ctNumData = ctnumdata;
 }
        /**
         *
         * @return a constant from the local Result class denoting whether there were any Evaluation
         * cases, and whether they all succeeded.
         */
        private int ProcessFunctionRow(IFormulaEvaluator Evaluator, String targetFunctionName,
                String targetTestName, IRow formulasRow, ICell expectedValueCell)
        {

            int result = Result.NO_EVALUATIONS_FOUND; // so far

            ICell c = formulasRow.GetCell(SS.COLUMN_INDEX_ACTUAL_VALUE);
            if (c == null || c.CellType != CellType.Formula)
            {
                return result;
            }

            CellValue actualValue = Evaluator.Evaluate(c);

            try
            {
                ConfirmExpectedResult("Function '" + targetFunctionName + "': Test: '" + targetTestName + "' Formula: " + c.CellFormula
                + " @ " + formulasRow.RowNum + ":" + SS.COLUMN_INDEX_ACTUAL_VALUE,
                        expectedValueCell, actualValue);
                _EvaluationSuccessCount++;
                if (result != Result.SOME_EVALUATIONS_FAILED)
                {
                    result = Result.ALL_EVALUATIONS_SUCCEEDED;
                }
            }
            catch (Exception e)
            {
                _EvaluationFailureCount++;
                //printshortStackTrace(System.err, e);
                Console.WriteLine(e.StackTrace);
                result = Result.SOME_EVALUATIONS_FAILED;
            }

            return result;
        }
Пример #29
0
    private void ExportSheet(ISheet sheet, StreamWriter sw, IFormulaEvaluator formulaEvaluator)
    {
        int cellCount = sheet.GetRow(3).LastCellNum;

        CellInfo[] cellInfos = new CellInfo[cellCount];

        for (int i = 2; i < cellCount; i++)
        {
            string fieldDesc = GetCellString(sheet, 2, i);
            string fieldName = GetCellString(sheet, 3, i);
            string fieldType = GetCellString(sheet, 4, i);
            cellInfos[i] = new CellInfo() { Name = fieldName, Type = fieldType, Desc = fieldDesc };
        }

        for (int i = 5; i <= sheet.LastRowNum; ++i)
        {
            if (GetCellString(sheet, i, 2, formulaEvaluator) == "")
            {
                continue;
            }
            StringBuilder sb = new StringBuilder();
            sb.Append("{");
            IRow row = sheet.GetRow(i);
            for (int j = 2; j < cellCount; ++j)
            {
                string desc = cellInfos[j].Desc.ToLower();
                if (desc.StartsWith("#"))
                {
                    continue;
                }

                // s开头表示这个字段是服务端专用
                if (desc.StartsWith("s") && this.isClient)
                {
                    continue;
                }

                // c开头表示这个字段是客户端专用
                if (desc.StartsWith("c") && !this.isClient)
                {
                    continue;
                }

                string fieldValue = GetCellString(row, j, formulaEvaluator);
                //if (fieldValue == "")
                //{
                //    throw new Exception($"sheet: {sheet.SheetName} 中有空白字段 {GetCellPosition(i, j)}");
                //}

                if (j > 2)
                {
                    sb.Append(",");
                }

                string fieldName = cellInfos[j].Name;

                if (fieldName == "Id" || fieldName == "_id")
                {
                    if (this.isClient)
                    {
                        fieldName = "Id";
                    }
                    else
                    {
                        fieldName = "_id";
                    }
                }

                string fieldType = cellInfos[j].Type;
                sb.Append($"\"{fieldName}\":{Convert(fieldType, fieldValue)}");
            }
            sb.Append("}");
            sw.WriteLine(sb.ToString());
        }
    }
Пример #30
0
        private object GetValue(ICell cell, Type type, IFormulaEvaluator evaluator)
        {
            if (cell == null)
            {
                return(null);
            }

            if (cell.CellType == CellType.Blank)
            {
                return(null);
            }

            if (type == typeof(string))
            {
                if (cell.CellType == CellType.Numeric)
                {
                    double cellVal      = cell.NumericCellValue;
                    int    convertedVal = Convert.ToInt32(cell.NumericCellValue);

                    if (convertedVal != cellVal)
                    {
                        return(cellVal.ToString());
                    }

                    return(convertedVal.ToString());
                }

                cell.SetCellType(CellType.String);
                if (string.IsNullOrEmpty(cell.StringCellValue))
                {
                    return("");
                }

                return(cell.StringCellValue);
            }

            if (type == typeof(float))
            {
                if (cell.CellType == CellType.Numeric)
                {
                    return(Convert.ToSingle(cell.NumericCellValue));
                }

                try
                {
                    return(Convert.ToSingle(this.GetStringVal(cell, evaluator)));
                }
                catch (Exception e)
                {
                    this.DebugLogICell(cell, type, e);
                    return(0);
                }
            }

            if (type == typeof(int))
            {
                if (cell.CellType == CellType.Numeric)
                {
                    return(Convert.ToInt32(cell.NumericCellValue));
                }

                try
                {
                    return(Convert.ToInt32(this.GetStringVal(cell, evaluator)));
                }
                catch (Exception e)
                {
                    this.DebugLogICell(cell, type, e);
                    return(0);
                }
            }

            if (type == typeof(double))
            {
                return(Convert.ToDouble(this.GetStringVal(cell, evaluator)));
            }

            if (type == typeof(long))
            {
                return(Convert.ToDouble(this.GetStringVal(cell, evaluator)));
            }

            if (type == typeof(bool))
            {
                return(Convert.ToBoolean(this.GetStringVal(cell, evaluator)));
            }

            if (type.IsEnum)
            {
                //return 5;
                //return System.Convert.ToInt32(System.Convert.ToDouble(GetStringVal(cell, evaluator)));
                cell.SetCellType(CellType.String);
                return(Convert.ToInt32(Enum.Parse(type, cell.StringCellValue)));
            }

            return(null);
        }
Пример #31
0
        private static void ConfirmExpectedResult(IFormulaEvaluator Evaluator, String msg, ICell cell, double expected)
        {

            CellValue value = Evaluator.Evaluate(cell);
            if (value.ErrorValue != 0)
                throw new Exception(msg + ": " + value.FormatAsString());
            Assert.AreEqual(expected, value.NumberValue, msg);
        }
Пример #32
0
		public object GetValor(ICell celda) {
			if (celda == null)
				return null;

			switch (celda.CellType) {
				case CellType.STRING:
					//return celda.RichStringCellValue;
					return celda.StringCellValue;
				case CellType.NUMERIC:
					if (DateUtil.IsCellDateFormatted(celda))
						return celda.DateCellValue;
					if (celda.CellStyle != null) {
						var format = celda.CellStyle.GetDataFormatString();
						//if (Regex.IsMatch(format, @"h+:mm(:ss)?"))
						if (_timeRegex.IsMatch(format))
							return celda.DateCellValue;
					}
					return celda.NumericCellValue;
				/*var iformat = celda.CellStyle.DataFormat;
				if(DateUtil.IsADateFormat(iformat, format)){
					Console.Write("fecha");
				}
				if (DateUtil.IsInternalDateFormat(iformat))
					return celda.DateCellValue;*/

				/*if (DateUtil.IsCellDateFormatted(celda) || DateUtil.IsCellInternalDateFormatted(celda)) {
					if (DateUtil.IsValidExcelDate(celda.NumericCellValue)) {
						var dt = celda.DateCellValue;
						return dt;
					}
				}
				return celda.NumericCellValue;*/
				case CellType.BOOLEAN:
					return celda.BooleanCellValue;
				case CellType.FORMULA:
					try {
						if (_evaluator == null)
							_evaluator = Book.GetCreationHelper().CreateFormulaEvaluator();
						var valorCelda = _evaluator.Evaluate(celda);
						return GetValorCelda(valorCelda, celda);
					} catch (Exception ex) {
						return "Error en formula " + celda.CellFormula;
					}
				default:

					break;
			}
			return null;
		}
Пример #33
0
 private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
         double expectedResult)
 {
     fe.ClearAllCachedResultValues();
     cell.CellFormula = (formula);
     CellValue cv = fe.Evaluate(cell);
     if (cv.CellType != CellType.Numeric)
     {
         throw new AssertionException("expected numeric cell type but got " + cv.FormatAsString());
     }
     Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
 }
Пример #34
0
        public void TestSetSheetName()
        {
            IWorkbook wb1 = newSetSheetNameTestingWorkbook();

            ISheet sh1 = wb1.GetSheetAt(0);

            IName sale_2 = wb1.GetNameAt(1);
            IName sale_3 = wb1.GetNameAt(2);
            IName sale_4 = wb1.GetNameAt(3);

            Assert.AreEqual("sale_2", sale_2.NameName);
            Assert.AreEqual("'Testing 47100'!$A$1", sale_2.RefersToFormula);
            Assert.AreEqual("sale_3", sale_3.NameName);
            Assert.AreEqual("'Testing 47100'!$B$1", sale_3.RefersToFormula);
            Assert.AreEqual("sale_4", sale_4.NameName);
            Assert.AreEqual("'To be Renamed'!$A$3", sale_4.RefersToFormula);

            IFormulaEvaluator Evaluator = wb1.GetCreationHelper(/*getter*/).CreateFormulaEvaluator();

            ICell cell0 = sh1.GetRow(0).GetCell(0);
            ICell cell1 = sh1.GetRow(1).GetCell(0);
            ICell cell2 = sh1.GetRow(2).GetCell(0);

            Assert.AreEqual("SUM('Testing 47100'!A1:C1)", cell0.CellFormula);
            Assert.AreEqual("SUM('Testing 47100'!A1:C1,'To be Renamed'!A1:A5)", cell1.CellFormula);
            Assert.AreEqual("sale_2+sale_3+'Testing 47100'!C1", cell2.CellFormula);

            Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
            Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
            Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);

            wb1.SetSheetName(1, "47100 - First");
            wb1.SetSheetName(2, "47100 - Second");

            Assert.AreEqual("sale_2", sale_2.NameName);
            Assert.AreEqual("'47100 - First'!$A$1", sale_2.RefersToFormula);
            Assert.AreEqual("sale_3", sale_3.NameName);
            Assert.AreEqual("'47100 - First'!$B$1", sale_3.RefersToFormula);
            Assert.AreEqual("sale_4", sale_4.NameName);
            Assert.AreEqual("'47100 - Second'!$A$3", sale_4.RefersToFormula);

            Assert.AreEqual("SUM('47100 - First'!A1:C1)", cell0.CellFormula);
            Assert.AreEqual("SUM('47100 - First'!A1:C1,'47100 - Second'!A1:A5)", cell1.CellFormula);
            Assert.AreEqual("sale_2+sale_3+'47100 - First'!C1", cell2.CellFormula);

            Evaluator.ClearAllCachedResultValues();
            Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
            Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
            Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);

            IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb1);

            wb1.Close();

            sh1 = wb2.GetSheetAt(0);

            sale_2 = wb2.GetNameAt(1);
            sale_3 = wb2.GetNameAt(2);
            sale_4 = wb2.GetNameAt(3);

            cell0 = sh1.GetRow(0).GetCell(0);
            cell1 = sh1.GetRow(1).GetCell(0);
            cell2 = sh1.GetRow(2).GetCell(0);

            Assert.AreEqual("sale_2", sale_2.NameName);
            Assert.AreEqual("'47100 - First'!$A$1", sale_2.RefersToFormula);
            Assert.AreEqual("sale_3", sale_3.NameName);
            Assert.AreEqual("'47100 - First'!$B$1", sale_3.RefersToFormula);
            Assert.AreEqual("sale_4", sale_4.NameName);
            Assert.AreEqual("'47100 - Second'!$A$3", sale_4.RefersToFormula);

            Assert.AreEqual("SUM('47100 - First'!A1:C1)", cell0.CellFormula);
            Assert.AreEqual("SUM('47100 - First'!A1:C1,'47100 - Second'!A1:A5)", cell1.CellFormula);
            Assert.AreEqual("sale_2+sale_3+'47100 - First'!C1", cell2.CellFormula);

            Evaluator = wb2.GetCreationHelper(/*getter*/).CreateFormulaEvaluator();
            Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
            Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
            Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);
            wb2.Close();
        }
        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="row"></param>
        /// <param name="evaluator"></param>
        /// <param name="dr"></param>
        private static void FillDataRowByRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
        {
            if (row != null)
            {
                for (int j = 0; j < dr.Table.Columns.Count; j++)
                {
                    ICell cell = row.GetCell(j);

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                        case CellType.Blank:
                        {
                            dr[j] = DBNull.Value;
                            break;
                        }

                        case CellType.Boolean:
                        {
                            dr[j] = cell.BooleanCellValue;
                            break;
                        }

                        case CellType.Numeric:
                        {
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                dr[j] = cell.DateCellValue;
                            }
                            else
                            {
                                dr[j] = cell.NumericCellValue;
                            }
                            break;
                        }

                        case CellType.String:
                        {
                            dr[j] = cell.StringCellValue;
                            break;
                        }

                        case CellType.Error:
                        {
                            dr[j] = cell.ErrorCellValue;
                            break;
                        }

                        case CellType.Formula:
                        {
                            cell  = evaluator.EvaluateInCell(cell) as HSSFCell;
                            dr[j] = cell.ToString();
                            break;
                        }

                        default:
                            throw new NotSupportedException(string.Format("Unsupported format type:{0}", cell.CellType));
                        }
                    }
                }
            }
        }
Пример #36
0
        /// <summary>
        ///     GetCellValue
        /// </summary>
        /// <param name="cell">cell</param>
        /// <param name="propertyType">propertyType</param>
        /// <param name="formulaEvaluator">formulaEvaluator</param>
        /// <returns>cellValue</returns>
        public static object GetCellValue([CanBeNull] this ICell cell, Type propertyType, IFormulaEvaluator formulaEvaluator = null)
        {
            if (cell == null || cell.CellType == CellType.Blank || cell.CellType == CellType.Error)
            {
                return propertyType.GetDefaultValue();
            }
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        if (propertyType == typeof(DateTime))
                        {
                            return cell.DateCellValue;
                        }
                        return cell.DateCellValue.ToOrDefault(propertyType);
                    }

                    if (propertyType == typeof(double))
                    {
                        return cell.NumericCellValue;
                    }
                    return cell.NumericCellValue.ToOrDefault(propertyType);

                case CellType.String:
                    return cell.StringCellValue.ToOrDefault(propertyType);

                case CellType.Boolean:
                    if (propertyType == typeof(bool))
                    {
                        return cell.BooleanCellValue;
                    }
                    return cell.BooleanCellValue.ToOrDefault(propertyType);

                case CellType.Formula:
                    try
                    {
                        var evaluatedCellValue = formulaEvaluator?.Evaluate(cell);
                        if (evaluatedCellValue != null)
                        {
                            if (evaluatedCellValue.CellType == CellType.Blank
                                || evaluatedCellValue.CellType == CellType.Error)
                            {
                                return propertyType.GetDefaultValue();
                            }
                            if (evaluatedCellValue.CellType == CellType.Numeric)
                            {
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    if (propertyType == typeof(DateTime))
                                    {
                                        return cell.DateCellValue;
                                    }
                                    return cell.DateCellValue.ToOrDefault(propertyType);
                                }
                                if (propertyType == typeof(double))
                                {
                                    return cell.NumericCellValue;
                                }
                                return evaluatedCellValue.NumberValue.ToOrDefault(propertyType);
                            }
                            if (evaluatedCellValue.CellType == CellType.Boolean)
                            {
                                if (propertyType == typeof(bool))
                                {
                                    return cell.BooleanCellValue;
                                }
                                return evaluatedCellValue.BooleanValue.ToOrDefault(propertyType);
                            }
                            if (evaluatedCellValue.CellType == CellType.String)
                            {
                                return evaluatedCellValue.StringValue.ToOrDefault(propertyType);
                            }
                            return evaluatedCellValue.FormatAsString().ToOrDefault(propertyType);
                        }
                    }
                    catch (Exception e)
                    {
                        InvokeHelper.OnInvokeException?.Invoke(e);
                    }
                    return cell.ToString().ToOrDefault(propertyType);

                default:
                    return cell.ToString().ToOrDefault(propertyType);
            }
        }
Пример #37
0
 private static void ConfirmFormulaWithUnknownUDF(String expectedFormula, ICell cell, IFormulaEvaluator evaluator)
 {
     Assert.AreEqual(expectedFormula, cell.CellFormula);
     try
     {
         evaluator.Evaluate(cell);
         Assert.Fail("Expected NotImplementedFunctionException/NotImplementedException");
     }
     catch (NotImplementedException)
     {
         // expected
     }
 }
Пример #38
0
 /// <summary>
 ///     GetCellValue
 /// </summary>
 /// <typeparam name="T">Type</typeparam>
 /// <param name="cell">cell</param>
 /// <param name="formulaEvaluator"></param>
 /// <returns></returns>
 public static T GetCellValue<T>([CanBeNull] this ICell cell, IFormulaEvaluator formulaEvaluator = null) => (T)cell.GetCellValue(typeof(T), formulaEvaluator);
Пример #39
0
 /// <summary>
 /// Lazily the load workbook.
 /// </summary>
 private void LazyLoadWorkbook()
 {
     // Check to lazily initialize workbook object
     if (_workbook == null)
     {
         // Check if file path is valid
         if (!string.IsNullOrWhiteSpace(_dashboardFile))
         {
             _workbook = this.LoadWorkbook(_dashboardFile);
             _formulaEvaluator = new HSSFFormulaEvaluator(_workbook);
             _dataFormatter = new HSSFDataFormatter(new CultureInfo("en-US"));
         }
     }
 }
Пример #40
0
 private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
         ErrorEval expectedResult)
 {
     fe.ClearAllCachedResultValues();
     cell.CellFormula=(formula);
     CellValue cv = fe.Evaluate(cell);
     if (cv.CellType != CellType.Error)
     {
         throw new AssertionException("expected error cell type but got " + cv.FormatAsString());
     }
     int expCode = expectedResult.ErrorCode;
     if (cv.ErrorValue != expCode)
     {
         throw new AssertionException("Expected error '" + ErrorEval.GetText(expCode)
                 + "' but got '" + cv.FormatAsString() + "'.");
     }
 }
Пример #41
0
 public ExcelLoader(string excel_fpath)
 {
     this._reader    = this.GetExcelDataReader(excel_fpath);
     this._evaluator = this._reader.GetCreationHelper().CreateFormulaEvaluator();
 }
Пример #42
0
 public void Setup()
 {
     wb   = new HSSFWorkbook();
     cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
     fe   = new HSSFFormulaEvaluator(wb);
 }
Пример #43
0
        /**
         * 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);
        }
Пример #44
0
        /// <summary>
        /// 将单元格的值转化为字符串
        /// </summary>
        /// <param name="tCell"></param>
        /// <returns></returns>
        private string getCellStringValueAllCase(ICell tCell)
        {
            string tempValue = "";

            switch (tCell.CellType)
            {
            case CellType.Blank:
                break;

            case CellType.Boolean:
                tempValue = tCell.BooleanCellValue.ToString();
                break;

            case CellType.Error:
                break;

            case CellType.Formula:
                IFormulaEvaluator fe = WorkbookFactory.CreateFormulaEvaluator(tCell.Sheet.Workbook);
                var cellValue        = fe.Evaluate(tCell);
                switch (cellValue.CellType)
                {
                case CellType.Blank:
                    break;

                case CellType.Boolean:
                    tempValue = cellValue.BooleanValue.ToString();
                    break;

                case CellType.Error:
                    break;

                case CellType.Formula:
                    break;

                case CellType.Numeric:
                    tempValue = cellValue.NumberValue.ToString();
                    break;

                case CellType.String:
                    tempValue = cellValue.StringValue.ToString();
                    break;

                case CellType.Unknown:
                    break;

                default:
                    break;
                }
                break;

            case CellType.Numeric:

                if (DateUtil.IsCellDateFormatted(tCell))
                {
                    tempValue = tCell.DateCellValue.ToString("yyyy-MM-dd");
                }
                else
                {
                    tempValue = tCell.NumericCellValue.ToString();
                }
                break;

            case CellType.String:
                tempValue = tCell.StringCellValue.Trim();
                break;

            case CellType.Unknown:
                break;

            default:
                break;
            }
            return(tempValue);
        }
Пример #45
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)
            {
                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 + ")");
        }
Пример #46
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 + ")");
        }