Exemple #1
0
        public void TestShiftWithComments()
        {
            HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("comments.xls");

            Npoi.Core.SS.UserModel.ISheet sheet = wb.GetSheet("Sheet1");
            Assert.AreEqual(3, sheet.LastRowNum);

            // Verify comments are in the position expected
            Assert.IsNotNull(sheet.GetCellComment(0, 0));
            Assert.IsNull(sheet.GetCellComment(1, 0));
            Assert.IsNotNull(sheet.GetCellComment(2, 0));
            Assert.IsNotNull(sheet.GetCellComment(3, 0));

            String comment1 = sheet.GetCellComment(0, 0).String.String;

            Assert.AreEqual(comment1, "comment top row1 (index0)\n");
            String comment3 = sheet.GetCellComment(2, 0).String.String;

            Assert.AreEqual(comment3, "comment top row3 (index2)\n");
            String comment4 = sheet.GetCellComment(3, 0).String.String;

            Assert.AreEqual(comment4, "comment top row4 (index3)\n");

            // Shifting all but first line down to Test comments shifting
            sheet.ShiftRows(1, sheet.LastRowNum, 1, true, true);
            MemoryStream outputStream = new MemoryStream();

            wb.Write(outputStream);

            // Test that comments were shifted as expected
            Assert.AreEqual(4, sheet.LastRowNum);
            Assert.IsNotNull(sheet.GetCellComment(0, 0));
            Assert.IsNull(sheet.GetCellComment(1, 0));
            Assert.IsNull(sheet.GetCellComment(2, 0));
            Assert.IsNotNull(sheet.GetCellComment(3, 0));
            Assert.IsNotNull(sheet.GetCellComment(4, 0));

            String comment1_shifted = sheet.GetCellComment(0, 0).String.String;

            Assert.AreEqual(comment1, comment1_shifted);
            String comment3_shifted = sheet.GetCellComment(3, 0).String.String;

            Assert.AreEqual(comment3, comment3_shifted);
            String comment4_shifted = sheet.GetCellComment(4, 0).String.String;

            Assert.AreEqual(comment4, comment4_shifted);

            // Write out and read back in again
            // Ensure that the changes were persisted
            wb    = new HSSFWorkbook(new MemoryStream(outputStream.ToArray()));
            sheet = wb.GetSheet("Sheet1");
            Assert.AreEqual(4, sheet.LastRowNum);

            // Verify comments are in the position expected after the shift
            Assert.IsNotNull(sheet.GetCellComment(0, 0));
            Assert.IsNull(sheet.GetCellComment(1, 0));
            Assert.IsNull(sheet.GetCellComment(2, 0));
            Assert.IsNotNull(sheet.GetCellComment(3, 0));
            Assert.IsNotNull(sheet.GetCellComment(4, 0));

            comment1_shifted = sheet.GetCellComment(0, 0).String.String;
            Assert.AreEqual(comment1, comment1_shifted);
            comment3_shifted = sheet.GetCellComment(3, 0).String.String;
            Assert.AreEqual(comment3, comment3_shifted);
            comment4_shifted = sheet.GetCellComment(4, 0).String.String;
            Assert.AreEqual(comment4, comment4_shifted);
        }
        private static void ConfirmEmptyRow(Npoi.Core.SS.UserModel.ISheet s, int rowIx)
        {
            IRow row = s.GetRow(rowIx);

            Assert.IsTrue(row == null || row.PhysicalNumberOfCells == 0);
        }
        public void TestEvaluateAll()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            Npoi.Core.SS.UserModel.ISheet s1 = wb.CreateSheet();
            Npoi.Core.SS.UserModel.ISheet s2 = wb.CreateSheet();
            wb.SetSheetName(0, "S1");
            wb.SetSheetName(1, "S2");

            IRow s1r1 = s1.CreateRow(0);
            IRow s1r2 = s1.CreateRow(1);
            IRow s2r1 = s2.CreateRow(0);

            ICell s1r1c1 = s1r1.CreateCell(0);
            ICell s1r1c2 = s1r1.CreateCell(1);
            ICell s1r1c3 = s1r1.CreateCell(2);

            s1r1c1.SetCellValue(22.3);
            s1r1c2.SetCellValue(33.4);
            s1r1c3.CellFormula = ("SUM(A1:B1)");

            ICell s1r2c1 = s1r2.CreateCell(0);
            ICell s1r2c2 = s1r2.CreateCell(1);
            ICell s1r2c3 = s1r2.CreateCell(2);

            s1r2c1.SetCellValue(-1.2);
            s1r2c2.SetCellValue(-3.4);
            s1r2c3.CellFormula = ("SUM(A2:B2)");

            ICell s2r1c1 = s2r1.CreateCell(0);

            s2r1c1.CellFormula = ("S1!A1");

            // Not Evaluated yet
            Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
            Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
            Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);

            // Do a full Evaluate, as per our docs
            // uses EvaluateFormulaCell()
            for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
            {
                Npoi.Core.SS.UserModel.ISheet sheet     = wb.GetSheetAt(sheetNum);
                HSSFFormulaEvaluator          evaluator = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
                {
                    IRow r = (IRow)rit.Current;

                    for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext();)
                    {
                        ICell c = (ICell)cit.Current;
                        if (c.CellType == Npoi.Core.SS.UserModel.CellType.Formula)
                        {
                            evaluator.EvaluateFormulaCell(c);

                            // For Testing - all should be numeric
                            Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, evaluator.EvaluateFormulaCell(c));
                        }
                    }
                }
            }

            // Check now as expected
            Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
            Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Formula, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);

            Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
            Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Formula, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);

            Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
            Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
            Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Formula, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);


            // Now do the alternate call, which zaps the formulas
            // uses EvaluateInCell()
            for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
            {
                Npoi.Core.SS.UserModel.ISheet sheet     = wb.GetSheetAt(sheetNum);
                HSSFFormulaEvaluator          evaluator = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
                {
                    IRow r = (IRow)rit.Current;

                    for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext();)
                    {
                        ICell c = (ICell)cit.Current;
                        if (c.CellType == Npoi.Core.SS.UserModel.CellType.Formula)
                        {
                            evaluator.EvaluateInCell(c);
                        }
                    }
                }
            }

            Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);

            Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);

            Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
            Assert.AreEqual(Npoi.Core.SS.UserModel.CellType.Numeric, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
        }
Exemple #4
0
        public void TestCreate()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            ICell cell;

            Npoi.Core.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");

            //URL
            cell = sheet.CreateRow(0).CreateCell(0);
            cell.SetCellValue("URL Link");
            IHyperlink link = new HSSFHyperlink(HyperlinkType.Url);

            link.Address   = ("http://poi.apache.org/");
            cell.Hyperlink = link;

            //link to a file in the current directory
            cell = sheet.CreateRow(1).CreateCell(0);
            cell.SetCellValue("File Link");
            link           = new HSSFHyperlink(HyperlinkType.File);
            link.Address   = ("link1.xls");
            cell.Hyperlink = link;

            //e-mail link
            cell = sheet.CreateRow(2).CreateCell(0);
            cell.SetCellValue("Email Link");
            link = new HSSFHyperlink(HyperlinkType.Email);
            //note, if subject contains white spaces, make sure they are url-encoded
            link.Address   = ("mailto:[email protected]?subject=Hyperlinks");
            cell.Hyperlink = link;

            //link to a place in this workbook

            //Create a target sheet and cell
            Npoi.Core.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
            sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");

            cell = sheet.CreateRow(3).CreateCell(0);
            cell.SetCellValue("Worksheet Link");
            link           = new HSSFHyperlink(HyperlinkType.Document);
            link.TextMark  = ("'Target Sheet'!A1");
            cell.Hyperlink = link;

            //serialize and read again
            MemoryStream out1 = new MemoryStream();

            wb.Write(out1);

            wb    = new HSSFWorkbook(new MemoryStream(out1.ToArray()));
            sheet = wb.GetSheet("Hyperlinks");
            cell  = sheet.GetRow(0).GetCell(0);
            link  = cell.Hyperlink;
            Assert.IsNotNull(link);
            Assert.AreEqual("http://poi.apache.org/", link.Address);

            cell = sheet.GetRow(1).GetCell(0);
            link = cell.Hyperlink;
            Assert.IsNotNull(link);
            Assert.AreEqual("link1.xls", link.Address);

            cell = sheet.GetRow(2).GetCell(0);
            link = cell.Hyperlink;
            Assert.IsNotNull(link);
            Assert.AreEqual("mailto:[email protected]?subject=Hyperlinks", link.Address);

            cell = sheet.GetRow(3).GetCell(0);
            link = cell.Hyperlink;
            Assert.IsNotNull(link);
            Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
        }