Ejemplo n.º 1
0
        public void TestFormulaString()
        {
            XSSFWorkbook wb     = new XSSFWorkbook();
            XSSFCell     cell   = (XSSFCell)wb.CreateSheet().CreateRow(0).CreateCell(0);
            CT_Cell      ctCell = cell.GetCTCell(); //low-level bean holding cell's xml

            cell.SetCellFormula("A2");
            Assert.AreEqual(CellType.Formula, cell.CellType);
            Assert.AreEqual(cell.CellFormula, "A2");
            //the value is not Set and cell's type='N' which means blank
            Assert.AreEqual(ST_CellType.n, ctCell.t);

            //set cached formula value
            cell.SetCellValue("t='str'");
            //we are still of 'formula' type
            Assert.AreEqual(CellType.Formula, cell.CellType);
            Assert.AreEqual(cell.CellFormula, "A2");
            //cached formula value is Set and cell's type='STR'
            Assert.AreEqual(ST_CellType.str, ctCell.t);
            Assert.AreEqual(cell.StringCellValue, "t='str'");

            //now remove the formula, the cached formula result remains
            cell.SetCellFormula(null);
            Assert.AreEqual(CellType.String, cell.CellType);
            Assert.AreEqual(ST_CellType.str, ctCell.t);
            //the line below failed prior to fix of Bug #47889
            Assert.AreEqual(cell.StringCellValue, "t='str'");

            //revert to a blank cell
            cell.SetCellValue((String)null);
            Assert.AreEqual(CellType.Blank, cell.CellType);
            Assert.AreEqual(ST_CellType.n, ctCell.t);
            Assert.AreEqual(cell.StringCellValue, "");
        }
Ejemplo n.º 2
0
        public void TestMissingRAttribute()
        {
            XSSFWorkbook wb    = new XSSFWorkbook();
            XSSFSheet    sheet = (XSSFSheet)wb.CreateSheet();
            XSSFRow      row   = (XSSFRow)sheet.CreateRow(0);
            XSSFCell     a1    = (XSSFCell)row.CreateCell(0);

            a1.SetCellValue("A1");
            XSSFCell a2 = (XSSFCell)row.CreateCell(1);

            a2.SetCellValue("B1");
            XSSFCell a4 = (XSSFCell)row.CreateCell(4);

            a4.SetCellValue("E1");
            XSSFCell a6 = (XSSFCell)row.CreateCell(5);

            a6.SetCellValue("F1");

            assertCellsWithMissingR(row);

            a2.GetCTCell().unsetR();
            a6.GetCTCell().unsetR();

            assertCellsWithMissingR(row);

            wb  = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(wb);
            row = (XSSFRow)wb.GetSheetAt(0).GetRow(0);
            assertCellsWithMissingR(row);
        }
Ejemplo n.º 3
0
        public Ptg[] GetFormulaTokens(IEvaluationCell EvalCell)
        {
            XSSFCell cell = ((XSSFEvaluationCell)EvalCell).GetXSSFCell();
            XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.Create(_uBook);

            return(FormulaParser.Parse(cell.CellFormula, frBook, FormulaType.CELL, _uBook.GetSheetIndex(cell.Sheet)));
        }
Ejemplo n.º 4
0
        /**
         * Synchronize table headers with cell values in the parent sheet.
         * Headers <em>must</em> be in sync, otherwise Excel will display a
         * "Found unreadable content" message on startup.
         */
        public void UpdateHeaders()
        {
            XSSFSheet     sheet = (XSSFSheet)GetParent();
            CellReference ref1  = GetStartCellReference() as CellReference;

            if (ref1 == null)
            {
                return;
            }

            int     headerRow         = ref1.Row;
            int     firstHeaderColumn = ref1.Col;
            XSSFRow row = sheet.GetRow(headerRow) as XSSFRow;

            if (row != null)
            {
                foreach (CT_TableColumn col in GetCTTable().tableColumns.tableColumn)
                {
                    int      colIdx = (int)col.id - 1 + firstHeaderColumn;
                    XSSFCell cell   = row.GetCell(colIdx) as XSSFCell;
                    if (cell != null)
                    {
                        col.name = (cell.StringCellValue);
                    }
                }
            }
        }
Ejemplo n.º 5
0
        /**
         * Returns the cell at the given (0 based) index, with the specified {@link NPOI.SS.usermodel.Row.MissingCellPolicy}
         *
         * @return the cell at the given (0 based) index
         * @throws ArgumentException if cellnum < 0 or the specified MissingCellPolicy is invalid
         * @see Row#RETURN_NULL_AND_BLANK
         * @see Row#RETURN_BLANK_AS_NULL
         * @see Row#CREATE_NULL_AS_BLANK
         */
        public ICell GetCell(int cellnum, MissingCellPolicy policy)
        {
            if (cellnum < 0)
            {
                throw new ArgumentException("Cell index must be >= 0");
            }

            XSSFCell cell = (XSSFCell)RetrieveCell(cellnum);

            if (policy == MissingCellPolicy.RETURN_NULL_AND_BLANK)
            {
                return(cell);
            }
            if (policy == MissingCellPolicy.RETURN_BLANK_AS_NULL)
            {
                if (cell == null)
                {
                    return(cell);
                }
                if (cell.CellType == CellType.Blank)
                {
                    return(null);
                }
                return(cell);
            }
            if (policy == MissingCellPolicy.CREATE_NULL_AS_BLANK)
            {
                if (cell == null)
                {
                    return(CreateCell(cellnum, CellType.Blank));
                }
                return(cell);
            }
            throw new ArgumentException("Illegal policy " + policy + " (" + policy.id + ")");
        }
Ejemplo n.º 6
0
        /**
         * update cell references when Shifting rows
         *
         * @param n the number of rows to move
         */
        internal void Shift(int n)
        {
            int rownum = RowNum + n;
            CalculationChain calcChain = ((XSSFWorkbook)_sheet.Workbook).GetCalculationChain();
            int    sheetId             = (int)_sheet.sheet.sheetId;
            String msg = "Row[rownum=" + RowNum + "] contains cell(s) included in a multi-cell array formula. " +
                         "You cannot change part of an array.";

            foreach (ICell c in this)
            {
                XSSFCell cell = (XSSFCell)c;
                if (cell.IsPartOfArrayFormulaGroup)
                {
                    cell.NotifyArrayFormulaChanging(msg);
                }

                //remove the reference in the calculation chain
                if (calcChain != null)
                {
                    calcChain.RemoveItem(sheetId, cell.GetReference());
                }

                CT_Cell CT_Cell = cell.GetCTCell();
                String  r       = new CellReference(rownum, cell.ColumnIndex).FormatAsString();
                CT_Cell.r = r;
            }
            RowNum = rownum;
        }
        public void TestXSSFSetArrayFormula_multiCell()
        {
            ICellRange <ICell> cells;

            String       formula2 = "456";
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet    sheet    = (XSSFSheet)workbook.CreateSheet();

            CellRangeAddress range = CellRangeAddress.ValueOf("C4:C6");

            cells = sheet.SetArrayFormula(formula2, range);
            Assert.AreEqual(3, cells.Size);

            // sheet.SetArrayFormula Creates rows and cells for the designated range

            /*
             * From the spec:
             * For a multi-cell formula, the c elements for all cells except the top-left
             * cell in that range shall not have an f element;
             */
            // Check that each cell exists and that the formula text is Set correctly on the first cell
            XSSFCell firstCell = (XSSFCell)cells.TopLeftCell;

            ConfirmArrayFormulaCell(firstCell, "C4", formula2, "C4:C6");
            ConfirmArrayFormulaCell(cells.GetCell(1, 0), "C5");
            ConfirmArrayFormulaCell(cells.GetCell(2, 0), "C6");

            Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell));
        }
Ejemplo n.º 8
0
        private void assertCellsWithMissingR(XSSFRow row)
        {
            XSSFCell a1 = (XSSFCell)row.GetCell(0);

            Assert.IsNotNull(a1);
            XSSFCell a2 = (XSSFCell)row.GetCell(1);

            Assert.IsNotNull(a2);
            XSSFCell a5 = (XSSFCell)row.GetCell(4);

            Assert.IsNotNull(a5);
            XSSFCell a6 = (XSSFCell)row.GetCell(5);

            Assert.IsNotNull(a6);

            Assert.AreEqual(6, row.LastCellNum);
            Assert.AreEqual(4, row.PhysicalNumberOfCells);

            Assert.AreEqual(a1.StringCellValue, "A1");
            Assert.AreEqual(a2.StringCellValue, "B1");
            Assert.AreEqual(a5.StringCellValue, "E1");
            Assert.AreEqual(a6.StringCellValue, "F1");

            // even if R attribute is not set,
            // POI is able to re-construct it from column and row indexes
            Assert.AreEqual(a1.GetReference(), "A1");
            Assert.AreEqual(a2.GetReference(), "B1");
            Assert.AreEqual(a5.GetReference(), "E1");
            Assert.AreEqual(a6.GetReference(), "F1");
        }
Ejemplo n.º 9
0
        /**
         * Construct a XSSFRow.
         *
         * @param row the xml bean Containing all cell defInitions for this row.
         * @param sheet the parent sheet.
         */
        public XSSFRow(CT_Row row, XSSFSheet sheet)
        {
            _row   = row;
            _sheet = sheet;
            _cells = new SortedDictionary <int, ICell>();
            if (0 < row.SizeOfCArray())
            {
                foreach (CT_Cell c in row.c)
                {
                    XSSFCell cell = new XSSFCell(this, c);
                    _cells.Add(cell.ColumnIndex, cell);
                    sheet.OnReadCell(cell);
                }
            }

            if (!row.IsSetR())
            {
                // Certain file format writers skip the row number
                // Assume no gaps, and give this the next row number
                int nextRowNum = sheet.LastRowNum + 2;
                if (nextRowNum == 2 && sheet.PhysicalNumberOfRows == 0)
                {
                    nextRowNum = 1;
                }
                row.r = (uint)nextRowNum;
            }
        }
Ejemplo n.º 10
0
        /**
         * Returns the cell at the given (0 based) index, with the specified {@link NPOI.SS.usermodel.Row.MissingCellPolicy}
         *
         * @return the cell at the given (0 based) index
         * @throws ArgumentException if cellnum < 0 or the specified MissingCellPolicy is invalid
         * @see Row#RETURN_NULL_AND_BLANK
         * @see Row#RETURN_BLANK_AS_NULL
         * @see Row#CREATE_NULL_AS_BLANK
         */
        public ICell GetCell(int cellnum, MissingCellPolicy policy)
        {
            if (cellnum < 0)
            {
                throw new ArgumentException("Cell index must be >= 0");
            }

            XSSFCell cell = (XSSFCell)RetrieveCell(cellnum);

            switch (policy)
            {
            case MissingCellPolicy.RETURN_NULL_AND_BLANK:
                return(cell);

            case MissingCellPolicy.RETURN_BLANK_AS_NULL:
                bool isBlank = (cell != null && cell.CellType == CellType.Blank);
                return((isBlank) ? null : cell);

            case MissingCellPolicy.CREATE_NULL_AS_BLANK:
                return((cell == null) ? CreateCell(cellnum, CellType.Blank) : cell);

            default:
                throw new ArgumentException("Illegal policy " + policy + " (" + policy + ")");
            }
        }
Ejemplo n.º 11
0
        public void UpdateHeaders()
        {
            XSSFSheet     parent             = (XSSFSheet)this.GetParent();
            CellReference startCellReference = this.GetStartCellReference();

            if (startCellReference == null)
            {
                return;
            }
            int     row1 = startCellReference.Row;
            int     col  = (int)startCellReference.Col;
            XSSFRow row2 = parent.GetRow(row1) as XSSFRow;

            if (row2 == null)
            {
                return;
            }
            foreach (CT_TableColumn ctTableColumn in this.GetCTTable().tableColumns.tableColumn)
            {
                int      cellnum = (int)ctTableColumn.id - 1 + col;
                XSSFCell cell    = row2.GetCell(cellnum) as XSSFCell;
                if (cell != null)
                {
                    ctTableColumn.name = cell.StringCellValue;
                }
            }
        }
Ejemplo n.º 12
0
 internal void OnDeleteFormula(XSSFCell cell)
 {
     if (this.calcChain == null)
     {
         return;
     }
     this.calcChain.RemoveItem((int)((XSSFSheet)cell.Sheet).sheet.sheetId, cell.GetReference());
 }
Ejemplo n.º 13
0
        public override Ptg[] GetFormulaTokens(IEvaluationCell evalCell)
        {
            XSSFCell cell       = ((XSSFEvaluationCell)evalCell).GetXSSFCell();
            int      sheetIndex = _uBook.GetSheetIndex(cell.Sheet);
            int      rowIndex   = cell.RowIndex;

            return(FormulaParser.Parse(cell.GetCellFormula(this), this, FormulaType.Cell, sheetIndex, rowIndex));
        }
Ejemplo n.º 14
0
        public void TestMultisheetFormulaEval()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet;
                XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet;
                XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet;

                // sheet1 A1
                XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(1.0);

                // sheet2 A1
                cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(1.0);

                // sheet2 B1
                cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell;
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(1.0);

                // sheet3 A1
                cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(1.0);

                // sheet1 A2 formulae
                cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Formula);
                cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1)");

                // sheet1 A3 formulae
                cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell;
                cell.SetCellType(CellType.Formula);
                cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1:B1)");

                wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();

                cell = sheet1.GetRow(1).GetCell(0) as XSSFCell;
                Assert.AreEqual(3.0, cell.NumericCellValue);

                cell = sheet1.GetRow(2).GetCell(0) as XSSFCell;
                Assert.AreEqual(4.0, cell.NumericCellValue);
            }
            finally
            {
                wb.Close();
            }
        }
Ejemplo n.º 15
0
        public CellRangeAddress GetArrayFormulaRange()
        {
            XSSFCell cell = ((XSSFSheet)Sheet).GetFirstCellInArrayFormula(this);

            if (cell == null)
            {
                throw new InvalidOperationException("Cell " + _cell.r
                                                    + " is not part of an array formula.");
            }
            String formulaRef = cell._cell.f.@ref;

            return(CellRangeAddress.ValueOf(formulaRef));
        }
Ejemplo n.º 16
0
        public void EvaluateInCellReturnsSameDataType()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            wb.CreateSheet().CreateRow(0).CreateCell(0);
            XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
            XSSFCell             cell      = wb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell;
            XSSFCell             same      = evaluator.EvaluateInCell(cell) as XSSFCell;

            //assertSame(cell, same);
            Assert.AreSame(cell, same);
            wb.Close();
        }
Ejemplo n.º 17
0
        public IEvaluationCell GetCell(int rowIndex, int columnIndex)
        {
            // cache for performance: ~30% speedup due to caching
            if (_cellCache == null)
            {
                _cellCache = new Dictionary <CellKey, IEvaluationCell>(_xs.LastRowNum * 3);
                foreach (IRow row in _xs)
                {
                    int rowNum = row.RowNum;
                    foreach (ICell cell in row)
                    {
                        // cast is safe, the iterator is just defined using the interface
                        CellKey         key1      = new CellKey(rowNum, cell.ColumnIndex);
                        IEvaluationCell evalcell1 = new XSSFEvaluationCell((XSSFCell)cell, this);
                        _cellCache.Add(key1, evalcell1);
                    }
                }
            }

            CellKey key = new CellKey(rowIndex, columnIndex);

            IEvaluationCell evalcell = null;

            if (_cellCache.ContainsKey(key))
            {
                evalcell = _cellCache[key];
            }

            // If cache is stale, update cache with this one cell
            // This is a compromise between rebuilding the entire cache
            // (which would quickly defeat the benefit of the cache)
            // and not caching at all.
            // See bug 59958: Add cells on the fly to the evaluation sheet cache on cache miss
            if (evalcell == null)
            {
                XSSFRow row = _xs.GetRow(rowIndex) as XSSFRow;
                if (row == null)
                {
                    return(null);
                }
                XSSFCell cell = row.GetCell(columnIndex) as XSSFCell;
                if (cell == null)
                {
                    return(null);
                }
                evalcell        = new XSSFEvaluationCell(cell, this);
                _cellCache[key] = evalcell;
            }

            return(evalcell);
        }
Ejemplo n.º 18
0
        public void Bug51158()
        {
            // create a workbook
            XSSFWorkbook wb1   = new XSSFWorkbook();
            XSSFSheet    sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet;
            XSSFRow      row   = sheet.CreateRow(2) as XSSFRow;
            XSSFCell     cell  = row.CreateCell(3) as XSSFCell;

            cell.SetCellValue("test1");

            //XSSFCreationHelper helper = workbook.GetCreationHelper();
            //cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));

            XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;

            Assert.IsNotNull(comment);
            comment.SetString("some comment");

            //        ICellStyle cs = workbook.CreateCellStyle();
            //        cs.ShrinkToFit=(/*setter*/false);
            //        row.CreateCell(0).CellStyle=(/*setter*/cs);

            // write the first excel file
            XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;

            Assert.IsNotNull(wb2);
            sheet = wb2.GetSheetAt(0) as XSSFSheet;
            row   = sheet.GetRow(2) as XSSFRow;
            Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
            Assert.IsNull(row.GetCell(4));

            // add a new cell to the sheet
            cell = row.CreateCell(4) as XSSFCell;
            cell.SetCellValue("test2");

            // write the second excel file
            XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;

            Assert.IsNotNull(wb3);
            sheet = wb3.GetSheetAt(0) as XSSFSheet;
            row   = sheet.GetRow(2) as XSSFRow;

            Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
            Assert.AreEqual("test2", row.GetCell(4).StringCellValue);

            wb3.Close();
            wb2.Close();
            wb1.Close();
        }
Ejemplo n.º 19
0
 /**
  * Construct a XSSFRow.
  *
  * @param row the xml bean Containing all cell defInitions for this row.
  * @param sheet the parent sheet.
  */
 public XSSFRow(CT_Row row, XSSFSheet sheet)
 {
     _row   = row;
     _sheet = sheet;
     _cells = new SortedDictionary <int, ICell>();
     if (0 < row.SizeOfCArray())
     {
         foreach (CT_Cell c in row.c)
         {
             XSSFCell cell = new XSSFCell(this, c);
             _cells.Add(cell.ColumnIndex, cell);
             sheet.OnReadCell(cell);
         }
     }
 }
Ejemplo n.º 20
0
        /**
         * If cell Contains formula, it Evaluates the formula, and
         *  Puts the formula result back into the cell, in place
         *  of the old formula.
         * Else if cell does not contain formula, this method leaves
         *  the cell unChanged.
         * Note that the same instance of HSSFCell is returned to
         * allow chained calls like:
         * <pre>
         * int EvaluatedCellType = Evaluator.EvaluateInCell(cell).CellType;
         * </pre>
         * Be aware that your cell value will be Changed to hold the
         *  result of the formula. If you simply want the formula
         *  value computed for you, use {@link #EvaluateFormulaCell(NPOI.ss.usermodel.Cell)} }
         * @param cell
         */
        public ICell EvaluateInCell(ICell cell)
        {
            if (cell == null)
            {
                return(null);
            }
            XSSFCell result = (XSSFCell)cell;

            if (cell.CellType == CellType.Formula)
            {
                CellValue cv = EvaluateFormulaCellValue(cell);
                SetCellType(cell, cv); // cell will no longer be a formula cell
                SetCellValue(cell, cv);
            }
            return(result);
        }
Ejemplo n.º 21
0
        public ICell EvaluateInCell(ICell cell)
        {
            if (cell == null)
            {
                return((ICell)null);
            }
            XSSFCell xssfCell = (XSSFCell)cell;

            if (cell.CellType == CellType.FORMULA)
            {
                CellValue formulaCellValue = this.EvaluateFormulaCellValue(cell);
                XSSFFormulaEvaluator.SetCellType(cell, formulaCellValue);
                XSSFFormulaEvaluator.SetCellValue(cell, formulaCellValue);
            }
            return((ICell)xssfCell);
        }
Ejemplo n.º 22
0
 public XSSFRow(CT_Row row, XSSFSheet sheet)
 {
     this._row   = row;
     this._sheet = sheet;
     this._cells = new SortedDictionary <int, ICell>();
     if (0 >= row.SizeOfCArray())
     {
         return;
     }
     foreach (CT_Cell cell1 in row.c)
     {
         XSSFCell cell2 = new XSSFCell(this, cell1);
         this._cells.Add(cell2.ColumnIndex, (ICell)cell2);
         sheet.OnReadCell(cell2);
     }
 }
Ejemplo n.º 23
0
        public void RemoveCell(ICell cell)
        {
            if (cell.Row != this)
            {
                throw new ArgumentException("Specified cell does not belong to this row");
            }
            XSSFCell cell1 = (XSSFCell)cell;

            if (cell1.IsPartOfArrayFormulaGroup)
            {
                cell1.NotifyArrayFormulaChanging();
            }
            if (cell.CellType == CellType.FORMULA)
            {
                ((XSSFWorkbook)this._sheet.Workbook).OnDeleteFormula(cell1);
            }
            this._cells.Remove(cell.ColumnIndex);
        }
Ejemplo n.º 24
0
        private void setUp_testCopyCellFrom_CellCopyPolicy()
        {
            XSSFWorkbook wb  = new XSSFWorkbook();
            XSSFRow      row = wb.CreateSheet().CreateRow(0) as XSSFRow;

            srcCell  = row.CreateCell(0) as XSSFCell;
            destCell = row.CreateCell(1) as XSSFCell;

            srcCell.CellFormula = ("2+3");

            ICellStyle style = wb.CreateCellStyle();

            style.BorderTop           = BorderStyle.Thick;
            style.FillBackgroundColor = ((short)5);
            srcCell.CellStyle         = (style);

            destCell.SetCellValue(true);
        }
Ejemplo n.º 25
0
        public void TestMissingRAttributeBug54288()
        {
            // workbook with cells missing the R attribute
            XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288.xlsx");
            // same workbook re-saved in Excel 2010, the R attribute is updated for every cell with the right value.
            XSSFWorkbook wbRef = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288-ref.xlsx");

            XSSFSheet sheet    = (XSSFSheet)wb.GetSheetAt(0);
            XSSFSheet sheetRef = (XSSFSheet)wbRef.GetSheetAt(0);

            Assert.AreEqual(sheetRef.PhysicalNumberOfRows, sheet.PhysicalNumberOfRows);

            // Test idea: iterate over cells in the reference worksheet, they all have the R attribute set.
            // For each cell from the reference sheet find the corresponding cell in the problematic file (with missing R)
            // and assert that POI reads them equally:
            DataFormatter formater = new DataFormatter();

            foreach (IRow r in sheetRef)
            {
                XSSFRow rowRef = (XSSFRow)r;
                XSSFRow row    = (XSSFRow)sheet.GetRow(rowRef.RowNum);

                Assert.AreEqual(rowRef.PhysicalNumberOfCells, row.PhysicalNumberOfCells, "number of cells in row[" + row.RowNum + "]");

                foreach (ICell c in rowRef.Cells)
                {
                    XSSFCell cellRef = (XSSFCell)c;
                    XSSFCell cell    = (XSSFCell)row.GetCell(cellRef.ColumnIndex);

                    Assert.AreEqual(cellRef.ColumnIndex, cell.ColumnIndex);
                    Assert.AreEqual(cellRef.GetReference(), cell.GetReference());

                    if (!cell.GetCTCell().IsSetR())
                    {
                        Assert.IsTrue(cellRef.GetCTCell().IsSetR(), "R must e set in cellRef");

                        String valRef = formater.FormatCellValue(cellRef);
                        String val    = formater.FormatCellValue(cell);
                        Assert.AreEqual(valRef, val);
                    }
                }
            }
        }
        public void TestXSSFSetArrayFormula_SingleCell()
        {
            ICellRange <ICell> cells;

            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet    sheet    = (XSSFSheet)workbook.CreateSheet();

            // 1. Single-cell array formula
            String           formula1 = "123";
            CellRangeAddress range    = CellRangeAddress.ValueOf("C3:C3");

            cells = sheet.SetArrayFormula(formula1, range);
            Assert.AreEqual(1, cells.Size);

            // check GetFirstCell...
            XSSFCell firstCell = (XSSFCell)cells.TopLeftCell;

            Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell));
            //retrieve the range and check it is the same
            Assert.AreEqual(range.FormatAsString(), firstCell.GetArrayFormulaRange().FormatAsString());
            ConfirmArrayFormulaCell(firstCell, "C3", formula1, "C3");
        }
Ejemplo n.º 27
0
        /**
         * package/hierarchy use only - reuse an existing evaluation workbook if available for caching
         *
         * @param fpb evaluation workbook for reuse, if available, or null to create a new one as needed
         * @return a formula for the cell
         * @throws InvalidOperationException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA
         */
        protected internal String GetCellFormula(XSSFEvaluationWorkbook fpb)
        {
            CellType cellType = CellType;

            if (cellType != CellType.Formula)
            {
                throw TypeMismatch(CellType.Formula, cellType, false);
            }

            CT_CellFormula f = _cell.f;

            if (IsPartOfArrayFormulaGroup && f == null)
            {
                XSSFCell cell = ((XSSFSheet)Sheet).GetFirstCellInArrayFormula(this);
                return(cell.GetCellFormula(fpb));
            }
            if (f.t == ST_CellFormulaType.shared)
            {
                //return ConvertSharedFormula((int)f.si);
                return(ConvertSharedFormula((int)f.si, fpb == null ? XSSFEvaluationWorkbook.Create(Sheet.Workbook) : fpb));
            }
            return(f.Value);
        }
Ejemplo n.º 28
0
        public void TestBug56511()
        {
            XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56511.xlsx");

            foreach (XSSFSheet sheet in wb)
            {
                int lastRow = sheet.LastRowNum;
                for (int rowIdx = sheet.FirstRowNum; rowIdx <= lastRow; rowIdx++)
                {
                    XSSFRow row = sheet.GetRow(rowIdx) as XSSFRow;
                    if (row != null)
                    {
                        int lastCell = row.LastCellNum;

                        for (int cellIdx = row.FirstCellNum; cellIdx <= lastCell; cellIdx++)
                        {
                            XSSFCell cell = row.GetCell(cellIdx) as XSSFCell;
                            if (cell != null)
                            {
                                //System.out.Println("row " + rowIdx + " column " + cellIdx + ": " + cell.CellType + ": " + cell.ToString());

                                XSSFRichTextString richText = cell.RichStringCellValue as XSSFRichTextString;
                                int anzFormattingRuns       = richText.NumFormattingRuns;
                                for (int run = 0; run < anzFormattingRuns; run++)
                                {
                                    /*XSSFFont font =*/
                                    richText.GetFontOfFormattingRun(run);
                                    //System.out.Println("  run " + run
                                    //        + " font " + (font == null ? "<null>" : font.FontName));
                                }
                            }
                        }
                    }
                }
            }
        }
Ejemplo n.º 29
0
        public void TestInlineString()
        {
            XSSFWorkbook wb    = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("xlsx-jdbc.xlsx");
            XSSFSheet    sheet = (XSSFSheet)wb.GetSheetAt(0);
            XSSFRow      row   = (XSSFRow)sheet.GetRow(1);

            XSSFCell cell_0 = (XSSFCell)row.GetCell(0);

            Assert.AreEqual(ST_CellType.inlineStr, cell_0.GetCTCell().t);
            Assert.IsTrue(cell_0.GetCTCell().IsSetIs());
            Assert.AreEqual("A Very large string in column 1 AAAAAAAAAAAAAAAAAAAAA", cell_0.StringCellValue);

            XSSFCell cell_1 = (XSSFCell)row.GetCell(1);

            Assert.AreEqual(ST_CellType.inlineStr, cell_1.GetCTCell().t);
            Assert.IsTrue(cell_1.GetCTCell().IsSetIs());
            Assert.AreEqual("foo", cell_1.StringCellValue);

            XSSFCell cell_2 = (XSSFCell)row.GetCell(2);

            Assert.AreEqual(ST_CellType.inlineStr, cell_2.GetCTCell().t);
            Assert.IsTrue(cell_2.GetCTCell().IsSetIs());
            Assert.AreEqual("bar", row.GetCell(2).StringCellValue);
        }
Ejemplo n.º 30
0
        /**
         * Use this to create new cells within the row and return it.
         *
         * @param columnIndex - the column number this cell represents
         * @param type - the cell's data type
         * @return XSSFCell a high level representation of the Created cell.
         * @throws ArgumentException if the specified cell type is invalid, columnIndex < 0
         *   or greater than 16384, the maximum number of columns supported by the SpreadsheetML format (.xlsx)
         * @see Cell#CELL_TYPE_BLANK
         * @see Cell#CELL_TYPE_BOOLEAN
         * @see Cell#CELL_TYPE_ERROR
         * @see Cell#CELL_TYPE_FORMULA
         * @see Cell#CELL_TYPE_NUMERIC
         * @see Cell#CELL_TYPE_STRING
         */
        public ICell CreateCell(int columnIndex, CellType type)
        {
            CT_Cell  ctCell;
            XSSFCell prev = _cells.ContainsKey(columnIndex) ? (XSSFCell)_cells[columnIndex] : null;

            if (prev != null)
            {
                ctCell = prev.GetCTCell();
                ctCell.Set(new CT_Cell());
            }
            else
            {
                ctCell = _row.AddNewC();
            }
            XSSFCell xcell = new XSSFCell(this, ctCell);

            xcell.SetCellNum(columnIndex);
            if (type != CellType.BLANK)
            {
                xcell.SetCellType(type);
            }
            _cells[columnIndex] = xcell;
            return(xcell);
        }
Ejemplo n.º 31
0
 internal void OnReadCell(XSSFCell cell)
 {
     //collect cells holding shared formulas
     CT_Cell ct = cell.GetCTCell();
     CT_CellFormula f = ct.f;
     if (f != null && f.t == ST_CellFormulaType.shared && f.isSetRef() && f.Value != null)
     {
         // save a detached  copy to avoid XmlValueDisconnectedException,
         // this may happen when the master cell of a shared formula is Changed
         sharedFormulas[(int)f.si] = (CT_CellFormula)f.Copy();
     }
     if (f != null && f.t == ST_CellFormulaType.array && f.@ref != null)
     {
         arrayFormulas.Add(CellRangeAddress.ValueOf(f.@ref));
     }
 }
Ejemplo n.º 32
0
 public XSSFEvaluationCell(ICell cell, XSSFEvaluationSheet EvaluationSheet)
 {
     _cell = (XSSFCell)cell;
     _evalSheet = EvaluationSheet;
 }
Ejemplo n.º 33
0
 /**
  * Fired when a formula is deleted from this workbook,
  * for example when calling cell.SetCellFormula(null)
  *
  * @see XSSFCell#setCellFormula(String)
  */
 internal void OnDeleteFormula(XSSFCell cell)
 {
     if (calcChain != null)
     {
         int sheetId = (int)((XSSFSheet)cell.Sheet).sheet.sheetId;
         calcChain.RemoveItem(sheetId, cell.GetReference());
     }
 }
Ejemplo n.º 34
0
 internal void OnReadCell(XSSFCell cell)
 {
     //collect cells holding shared formulas
     CT_Cell ct = cell.GetCTCell();
     CT_CellFormula f = ct.f;
     if (f != null && f.t == ST_CellFormulaType.shared && f.isSetRef() && f.Value != null)
     {
         // save a detached  copy to avoid XmlValueDisconnectedException,
         // this may happen when the master cell of a shared formula is Changed
         CT_CellFormula sf = (CT_CellFormula)f.Copy();
         CellRangeAddress sfRef = CellRangeAddress.ValueOf(sf.@ref);
         CellReference cellRef = new CellReference(cell);
         // If the shared formula range preceeds the master cell then the preceding  part is discarded, e.g.
         // if the cell is E60 and the shared formula range is C60:M85 then the effective range is E60:M85
         // see more details in https://issues.apache.org/bugzilla/show_bug.cgi?id=51710
         if (cellRef.Col > sfRef.FirstColumn || cellRef.Row > sfRef.FirstRow)
         {
             String effectiveRef = new CellRangeAddress(
                     Math.Max(cellRef.Row, sfRef.FirstRow), sfRef.LastRow,
                     Math.Max(cellRef.Col, sfRef.FirstColumn), sfRef.LastColumn).FormatAsString();
             sf.@ref = (effectiveRef);
         }
         sharedFormulas[(int)f.si] = sf;
     }
     if (f != null && f.t == ST_CellFormulaType.array && f.@ref != null)
     {
         arrayFormulas.Add(CellRangeAddress.ValueOf(f.@ref));
     }
 }