Esempio n. 1
0
        private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
        {
            CT_CellFormula f = cell.GetCTCell().f;

            if (f == null)
            {
                return;
            }
            string formula = f.Value;

            if (formula == null || formula.Length <= 0)
            {
                return;
            }
            int sheetIndex = this._wb.GetSheetIndex(cell.Sheet);

            Ptg[]  ptgs          = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.CELL, sheetIndex);
            string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);

            if (formula.Equals(formulaString))
            {
                return;
            }
            f.Value = formulaString;
        }
Esempio n. 2
0
        private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
        {
            foreach (ICell c in row)
            {
                XSSFCell cell = (XSSFCell)c;

                CT_Cell ctCell = cell.GetCTCell();
                if (ctCell.IsSetF())
                {
                    CT_CellFormula f       = ctCell.f;
                    String         formula = f.Value;
                    if (formula.Length > 0)
                    {
                        String ShiftedFormula = ShiftFormula(row, formula, Shifter);
                        if (ShiftedFormula != null)
                        {
                            f.Value = (ShiftedFormula);
                        }
                    }

                    if (f.isSetRef())
                    { //Range of cells which the formula applies to.
                        String ref1       = f.@ref;
                        String ShiftedRef = ShiftFormula(row, ref1, Shifter);
                        if (ShiftedRef != null)
                        {
                            f.@ref = ShiftedRef;
                        }
                    }
                }
            }
        }
Esempio n. 3
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);
        }
Esempio n. 4
0
        public void TestPreserveSpaces()
        {
            String[] samplesWithSpaces =
            {
                " POI",
                "POI ",
                " POI ",
                "\nPOI",
                "\n\nPOI \n",
            };
            foreach (String str in samplesWithSpaces)
            {
                IWorkbook swb   = _testDataProvider.CreateWorkbook();
                ICell     sCell = swb.CreateSheet().CreateRow(0).CreateCell(0);
                sCell.SetCellValue(str);
                Assert.AreEqual(sCell.StringCellValue, str);

                // read back as XSSF and check that xml:spaces="preserve" is Set
                XSSFWorkbook xwb   = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(swb);
                XSSFCell     xCell = xwb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell;

                CT_Rst is1 = xCell.GetCTCell().@is;
                //XmlCursor c = is1.NewCursor();
                //c.ToNextToken();
                //String t = c.GetAttributeText(new QName("http://www.w3.org/XML/1998/namespace", "space"));
                //c.Dispose();


                //write is1 to xml stream writer ,get the xml text and parse it and get space attr.
                //Assert.AreEqual("preserve", t, "expected xml:spaces=\"preserve\" \"" + str + "\"");
                xwb.Close();
                swb.Close();
            }
        }
Esempio n. 5
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, "");
        }
Esempio n. 6
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(cell_0.StringCellValue, "A Very large string in column 1 AAAAAAAAAAAAAAAAAAAAA");

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

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

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

            Assert.AreEqual(ST_CellType.inlineStr, cell_2.GetCTCell().t);
            Assert.IsTrue(cell_2.GetCTCell().IsSetIs());
            Assert.AreEqual(row.GetCell(2).StringCellValue, "bar");
        }
Esempio n. 7
0
        /**
         * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
         *
         * @param cell the cell to update
         * @param frwb the formula rendering workbbok that returns new sheet name
         */
        private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
        {
            CT_CellFormula f = cell.GetCTCell().f;

            if (f != null)
            {
                String formula = f.Value;
                if (formula != null && formula.Length > 0)
                {
                    int    sheetIndex     = _wb.GetSheetIndex(cell.Sheet);
                    Ptg[]  ptgs           = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
                    String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
                    if (!formula.Equals(updatedFormula))
                    {
                        f.Value = (updatedFormula);
                    }
                }
            }
        }
Esempio n. 8
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);
                    }
                }
            }
        }
Esempio n. 9
0
        /// <summary>
        /// Update the formulas in specified row using the formula shifting policy specified by shifter
        /// </summary>
        /// <param name="row">the row to update the formulas on</param>
        /// <param name="Shifter">the formula shifting policy</param>
        public override void UpdateRowFormulas(IRow row, FormulaShifter Shifter)
        {
            XSSFSheet sheet = (XSSFSheet)row.Sheet;

            foreach (ICell c in row)
            {
                XSSFCell cell = (XSSFCell)c;

                CT_Cell ctCell = cell.GetCTCell();
                if (ctCell.IsSetF())
                {
                    CT_CellFormula f       = ctCell.f;
                    String         formula = f.Value;
                    if (formula.Length > 0)
                    {
                        String ShiftedFormula = ShiftFormula(row, formula, Shifter);
                        if (ShiftedFormula != null)
                        {
                            f.Value = (ShiftedFormula);
                            if (f.t == ST_CellFormulaType.shared)
                            {
                                int            si = (int)f.si;
                                CT_CellFormula sf = sheet.GetSharedFormula(si);
                                sf.Value = (ShiftedFormula);
                            }
                        }
                    }

                    if (f.isSetRef())
                    { //Range of cells which the formula applies to.
                        String ref1       = f.@ref;
                        String ShiftedRef = ShiftFormula(row, ref1, Shifter);
                        if (ShiftedRef != null)
                        {
                            f.@ref = ShiftedRef;
                        }
                    }
                }
            }
        }
Esempio n. 10
0
        /**
         * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
         *
         * @param cell the cell to update
         * @param frwb the formula rendering workbbok that returns new sheet name
         */
        private void UpdateFormula(XSSFCell cell, String oldName, String newName)
        {
            CT_CellFormula f = cell.GetCTCell().f;

            if (f != null)
            {
                String formula = f.Value;
                if (formula != null && formula.Length > 0)
                {
                    int   sheetIndex = _wb.GetSheetIndex(cell.Sheet);
                    Ptg[] ptgs       = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex, -1);
                    foreach (Ptg ptg in ptgs)
                    {
                        UpdatePtg(ptg, oldName, newName);
                    }
                    String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
                    if (!formula.Equals(updatedFormula))
                    {
                        f.Value = (updatedFormula);
                    }
                }
            }
        }
Esempio n. 11
0
 /**
  * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
  *
  * @param cell the cell to update
  * @param frwb the formula rendering workbbok that returns new sheet name
  */
 private void UpdateFormula(XSSFCell cell, String oldName, String newName)
 {
     CT_CellFormula f = cell.GetCTCell().f;
     if (f != null)
     {
         String formula = f.Value;
         if (formula != null && formula.Length > 0)
         {
             int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
             Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
             foreach (Ptg ptg in ptgs)
             {
                 UpdatePtg(ptg, oldName, newName);
             }
             String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
             if (!formula.Equals(updatedFormula)) f.Value = (updatedFormula);
         }
     }
 }
Esempio n. 12
0
 /**
  * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
  *
  * @param cell the cell to update
  * @param frwb the formula rendering workbbok that returns new sheet name
  */
 private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
 {
     CT_CellFormula f = cell.GetCTCell().f;
     if (f != null)
     {
         String formula = f.Value;
         if (formula != null && formula.Length > 0)
         {
             int sheetIndex = _wb.GetSheetIndex(cell.Sheet);
             Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.CELL, sheetIndex);
             String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
             if (!formula.Equals(updatedFormula)) f.Value = (updatedFormula);
         }
     }
 }
Esempio n. 13
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, "");

            // check behavior with setCellFormulaValidation
            String invalidFormula = "A", validFormula = "A2";
            FormulaParseException fpe = null;

            // check that default is true
            Assert.IsTrue(wb.CellFormulaValidation);

            // check that valid formula does not throw exception
            try
            {
                cell.SetCellFormula(validFormula);
            }
            catch (FormulaParseException e)
            {
                fpe = e;
            }
            Assert.IsNull(fpe);

            // check that invalid formula does throw exception
            try
            {
                cell.SetCellFormula(invalidFormula);
            }
            catch (FormulaParseException e)
            {
                fpe = e;
            }
            Assert.IsNotNull(fpe);
            fpe = null;

            // set cell formula validation to false
            wb.CellFormulaValidation = false;
            Assert.IsFalse(wb.CellFormulaValidation);

            // check that neither valid nor invalid formula throw an exception
            try
            {
                cell.SetCellFormula(validFormula);
                cell.SetCellFormula(invalidFormula);
            }
            catch (FormulaParseException e)
            {
                fpe = e;
            }
            Assert.IsNull(fpe);
        }