Exemple #1
0
 public void AddPivotCache()
 {
     XSSFWorkbook wb = new XSSFWorkbook();
     try
     {
         CT_Workbook ctWb = wb.GetCTWorkbook();
         CT_PivotCache pivotCache = wb.AddPivotCache("0");
         //Ensures that pivotCaches is Initiated
         Assert.IsTrue(ctWb.IsSetPivotCaches());
         Assert.AreSame(pivotCache, ctWb.pivotCaches.GetPivotCacheArray(0));
         Assert.AreEqual("0", pivotCache.id);
     }
     finally
     {
         wb.Close();
     }
 }
Exemple #2
0
        public void TestXSSFTextParagraph()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;

                XSSFTextParagraph para = shape.AddNewTextParagraph();
                para.AddNewTextRun().Text=("Line 1");

                List<XSSFTextRun> Runs = para.TextRuns;
                Assert.AreEqual(1, Runs.Count);
                XSSFTextRun run = Runs[0];
                Assert.AreEqual("Line 1", run.Text);

                //Assert.IsNotNull(run.ParentParagraph);
                //Assert.IsNotNull(run.XmlObject);
                Assert.IsNotNull(run.GetRPr());

                Assert.AreEqual(Color.FromArgb(0, 0, 0), run.FontColor);

                Color color = Color.FromArgb(0, 255, 255);
                run.FontColor = (/*setter*/color);
                Assert.AreEqual(color, run.FontColor);

                Assert.AreEqual(11.0, run.FontSize, 0.01);
                run.FontSize = (/*setter*/12.32);
                Assert.AreEqual(12.32, run.FontSize, 0.01);
                run.FontSize = (/*setter*/-1.0);
                Assert.AreEqual(11.0, run.FontSize, 0.01);
                run.FontSize = (/*setter*/-1.0);
                Assert.AreEqual(11.0, run.FontSize, 0.01);
                try
                {
                    run.FontSize = (/*setter*/0.9);
                    Assert.Fail("Should fail");
                }
                catch (ArgumentException e)
                {
                    Assert.IsTrue(e.Message.Contains("0.9"));
                }
                Assert.AreEqual(11.0, run.FontSize, 0.01);

                Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
                run.CharacterSpacing = (/*setter*/12.31);
                Assert.AreEqual(12.31, run.CharacterSpacing, 0.01);
                run.CharacterSpacing = (/*setter*/0.0);
                Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
                run.CharacterSpacing = (/*setter*/0.0);
                Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);

                Assert.AreEqual("Calibri", run.FontFamily);
                run.SetFontFamily("Arial", (byte)1, (byte)1, false);
                Assert.AreEqual("Arial", run.FontFamily);
                run.SetFontFamily("Arial", unchecked((byte)-1), (byte)1, false);
                Assert.AreEqual("Arial", run.FontFamily);
                run.SetFontFamily("Arial", (byte)1, unchecked((byte)-1), false);
                Assert.AreEqual("Arial", run.FontFamily);
                run.SetFontFamily("Arial", (byte)1, (byte)1, true);
                Assert.AreEqual("Arial", run.FontFamily);
                run.SetFontFamily(null, (byte)1, (byte)1, false);
                Assert.AreEqual("Calibri", run.FontFamily);
                run.SetFontFamily(null, (byte)1, (byte)1, false);
                Assert.AreEqual("Calibri", run.FontFamily);

                run.SetFont("Arial");
                Assert.AreEqual("Arial", run.FontFamily);

                Assert.AreEqual((byte)0, run.PitchAndFamily);
                run.SetFont(null);
                Assert.AreEqual((byte)0, run.PitchAndFamily);

                Assert.IsFalse(run.IsStrikethrough);
                run.IsStrikethrough = (/*setter*/true);
                Assert.IsTrue(run.IsStrikethrough);
                run.IsStrikethrough = (/*setter*/false);
                Assert.IsFalse(run.IsStrikethrough);

                Assert.IsFalse(run.IsSuperscript);
                run.IsSuperscript = (/*setter*/true);
                Assert.IsTrue(run.IsSuperscript);
                run.IsSuperscript = (/*setter*/false);
                Assert.IsFalse(run.IsSuperscript);

                Assert.IsFalse(run.IsSubscript);
                run.IsSubscript = (/*setter*/true);
                Assert.IsTrue(run.IsSubscript);
                run.IsSubscript = (/*setter*/false);
                Assert.IsFalse(run.IsSubscript);

                Assert.AreEqual(TextCap.NONE, run.TextCap);

                Assert.IsFalse(run.IsBold);
                run.IsBold = (/*setter*/true);
                Assert.IsTrue(run.IsBold);
                run.IsBold = (/*setter*/false);
                Assert.IsFalse(run.IsBold);

                Assert.IsFalse(run.IsItalic);
                run.IsItalic = (/*setter*/true);
                Assert.IsTrue(run.IsItalic);
                run.IsItalic = (/*setter*/false);
                Assert.IsFalse(run.IsItalic);

                Assert.IsFalse(run.IsUnderline);
                run.IsUnderline = (/*setter*/true);
                Assert.IsTrue(run.IsUnderline);
                run.IsUnderline = (/*setter*/false);
                Assert.IsFalse(run.IsUnderline);

                Assert.IsNotNull(run.ToString());
            }
            finally
            {
                wb.Close();
            }
        }
        public void XSSFTextParagraph_()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
                XSSFRichTextString rt = new XSSFRichTextString("Test String");

                XSSFFont font = wb.CreateFont() as XSSFFont;
                Color color = Color.FromArgb(0, 255, 255);
                font.SetColor(new XSSFColor(color));
                font.FontName = (/*setter*/"Arial");
                rt.ApplyFont(font);

                shape.SetText(rt);

                List<XSSFTextParagraph> paras = shape.TextParagraphs;
                Assert.AreEqual(1, paras.Count);

                XSSFTextParagraph text = paras[(0)];
                Assert.AreEqual("Test String", text.Text);

                Assert.IsFalse(text.IsBullet);
                Assert.IsNotNull(text.GetXmlObject());
                Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
                Assert.IsNotNull(text.GetEnumerator());
                Assert.IsNotNull(text.AddLineBreak());

                Assert.IsNotNull(text.TextRuns);
                Assert.AreEqual(2, text.TextRuns.Count);
                text.AddNewTextRun();
                Assert.AreEqual(3, text.TextRuns.Count);

                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
                text.TextAlign = TextAlign.None;
                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
                text.TextAlign = (/*setter*/TextAlign.CENTER);
                Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
                text.TextAlign = (/*setter*/TextAlign.RIGHT);
                Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
                text.TextAlign = TextAlign.None;
                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);

                text.TextFontAlign = (/*setter*/TextFontAlign.BASELINE);
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
                text.TextFontAlign = (/*setter*/TextFontAlign.BOTTOM);
                Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
                text.TextFontAlign = TextFontAlign.None;
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
                text.TextFontAlign = TextFontAlign.None;
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);

                Assert.IsNull(text.BulletFont);
                text.BulletFont = (/*setter*/"Arial");
                Assert.AreEqual("Arial", text.BulletFont);

                Assert.IsNull(text.BulletCharacter);
                text.BulletCharacter = (/*setter*/".");
                Assert.AreEqual(".", text.BulletCharacter);

                //Assert.IsNull(text.BulletFontColor);
                Assert.AreEqual(Color.Empty, text.BulletFontColor);
                text.BulletFontColor = (/*setter*/color);
                Assert.AreEqual(color, text.BulletFontColor);

                Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/-9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);

                Assert.AreEqual(0.0, text.Indent, 0.01);
                text.Indent = (/*setter*/2.0);
                Assert.AreEqual(2.0, text.Indent, 0.01);
                text.Indent = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.Indent, 0.01);
                text.Indent = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.Indent, 0.01);

                Assert.AreEqual(0.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/3.0);
                Assert.AreEqual(3.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.LeftMargin, 0.01);

                Assert.AreEqual(0.0, text.RightMargin, 0.01);
                text.RightMargin = (/*setter*/4.5);
                Assert.AreEqual(4.5, text.RightMargin, 0.01);
                text.RightMargin = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.RightMargin, 0.01);
                text.RightMargin = (/*setter*/-1.0);
                Assert.AreEqual(0.0, text.RightMargin, 0.01);

                Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);

                Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
                text.AddTabStop(3.14);
                Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);

                Assert.AreEqual(100.0, text.LineSpacing, 0.01);
                text.LineSpacing = (/*setter*/3.15);
                Assert.AreEqual(3.15, text.LineSpacing, 0.01);
                text.LineSpacing = (/*setter*/-2.13);
                Assert.AreEqual(-2.13, text.LineSpacing, 0.01);

                Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
                text.SpaceBefore = (/*setter*/3.17);
                Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
                text.SpaceBefore = (/*setter*/-4.7);
                Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);

                Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
                text.SpaceAfter = (/*setter*/6.17);
                Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
                text.SpaceAfter = (/*setter*/-8.17);
                Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);

                Assert.AreEqual(0, text.Level);
                text.Level = (/*setter*/1);
                Assert.AreEqual(1, text.Level);
                text.Level = (/*setter*/4);
                Assert.AreEqual(4, text.Level);

                Assert.IsTrue(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.IsBullet = (false);
                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.IsBullet = (true);
                Assert.IsTrue(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                Assert.AreEqual(0, text.BulletAutoNumberStart);
                Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);

                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
                Assert.IsTrue(text.IsBullet);
                Assert.IsTrue(text.IsBulletAutoNumber);
                
                //Assert.AreEqual(0, text.BulletAutoNumberStart);
                //This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1;
                Assert.AreEqual(1, text.BulletAutoNumberStart);


                Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
                Assert.IsTrue(text.IsBullet);
                Assert.IsTrue(text.IsBulletAutoNumber);
                Assert.AreEqual(10, text.BulletAutoNumberStart);
                Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);


                Assert.IsNotNull(text.ToString());

                new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #4
0
        public void IncrementSheetId()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                int sheetId = (int)(wb.CreateSheet() as XSSFSheet).sheet.sheetId;
                Assert.AreEqual(1, sheetId);
                sheetId = (int)(wb.CreateSheet() as XSSFSheet).sheet.sheetId;
                Assert.AreEqual(2, sheetId);

                //test file with gaps in the sheetId sequence
                XSSFWorkbook wbBack = XSSFTestDataSamples.OpenSampleWorkbook("47089.xlsm");
                try
                {
                    int lastSheetId = (int)(wbBack.GetSheetAt(wbBack.NumberOfSheets - 1)  as XSSFSheet).sheet.sheetId;
                    sheetId = (int)(wbBack.CreateSheet() as XSSFSheet).sheet.sheetId;
                    Assert.AreEqual(lastSheetId + 1, sheetId);
                }
                finally
                {
                    wbBack.Close();
                }
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #5
0
        public void Test49941()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet s = wb.CreateSheet() as XSSFSheet;
            XSSFRow r = s.CreateRow(0) as XSSFRow;
            XSSFCell c = r.CreateCell(0) as XSSFCell;

            // First without fonts
            c.SetCellValue(
                  new XSSFRichTextString(" with spaces ")
            );
            Assert.AreEqual(" with spaces ", c.RichStringCellValue.ToString());
            Assert.AreEqual(0, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray());
            Assert.AreEqual(true, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().IsSetT());
            // Should have the preserve Set
            //Assert.AreEqual(
            //      1,
            //      (c.RichStringCellValue as XSSFRichTextString).GetCTRst().xgetT().GetDomNode().GetAttributes().GetLength()
            //);
            //Assert.AreEqual(
            //      "preserve",
            //      (c.RichStringCellValue as XSSFRichTextString).GetCTRst().xgetT().GetDomNode().GetAttributes().item(0).GetNodeValue()
            //);

            // Save and check
            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            s = wb.GetSheetAt(0) as XSSFSheet;
            r = s.GetRow(0) as XSSFRow;
            c = r.GetCell(0) as XSSFCell;
            Assert.AreEqual(" with spaces ", c.RichStringCellValue.ToString());
            Assert.AreEqual(0, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray());
            Assert.AreEqual(true, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().IsSetT());

            // Change the string
            c.SetCellValue(
                  new XSSFRichTextString("hello world")
            );
            Assert.AreEqual("hello world", c.RichStringCellValue.ToString());
            // Won't have preserve
            //Assert.AreEqual(
            //      0,
            //      c.RichStringCellValue.GetCTRst().xgetT().GetDomNode().GetAttributes().GetLength()
            //);

            // Apply a font
            XSSFFont f = wb.CreateFont() as XSSFFont;
            f.IsBold = (true);
            c.RichStringCellValue.ApplyFont(0, 5, f);
            Assert.AreEqual("hello world", c.RichStringCellValue.ToString());
            // Does need preserving on the 2nd part
            Assert.AreEqual(2, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray());

            //Assert.AreEqual(
            //      0,
            //      c.RichStringCellValue.GetCTRst().GetRArray(0).xgetT().GetDomNode().GetAttributes().GetLength()
            //);
            //Assert.AreEqual(
            //      1,
            //      c.RichStringCellValue.GetCTRst().GetRArray(1).xgetT().GetDomNode().GetAttributes().GetLength()
            //);
            //Assert.AreEqual(
            //      "preserve",
            //      c.RichStringCellValue.GetCTRst().GetRArray(1).xgetT().GetDomNode().GetAttributes().item(0).GetNodeValue()
            //);

            // Save and check
            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            s = wb.GetSheetAt(0) as XSSFSheet;
            r = s.GetRow(0) as XSSFRow;
            c = r.GetCell(0) as XSSFCell;
            Assert.AreEqual("hello world", c.RichStringCellValue.ToString());

            wb.Close();
        }
        public void TestBug55280()
        {
            XSSFWorkbook w = new XSSFWorkbook();
            try
            {
                ISheet s = w.CreateSheet();
                for (int row = 0; row < 5000; ++row)
                    s.AddMergedRegion(new CellRangeAddress(row, row, 0, 3));

                s.ShiftRows(0, 4999, 1);        // takes a long time...
            }
            finally
            {
                w.Close();
            }
        }
Exemple #7
0
        public void ParseStructuredReferences()
        {
            XSSFWorkbook           wb  = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
            XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);

            Ptg[] ptgs;

            /*
             * The following cases are tested (copied from FormulaParser.parseStructuredReference)
             * 1 Table1[col]
             * 2 Table1[[#Totals],[col]]
             * 3 Table1[#Totals]
             * 4 Table1[#All]
             * 5 Table1[#Data]
             * 6 Table1[#Headers]
             * 7 Table1[#Totals]
             * 8 Table1[#This Row]
             * 9 Table1[[#All],[col]]
             * 10 Table1[[#Headers],[col]]
             * 11 Table1[[#Totals],[col]]
             * 12 Table1[[#All],[col1]:[col2]]
             * 13 Table1[[#Data],[col1]:[col2]]
             * 14 Table1[[#Headers],[col1]:[col2]]
             * 15 Table1[[#Totals],[col1]:[col2]]
             * 16 Table1[[#Headers],[#Data],[col2]]
             * 17 Table1[[#This Row], [col1]]
             * 18 Table1[ [col1]:[col2] ]
             */
            String tbl = "\\_Prime.1";
            String noTotalsRowReason = ": Tables without a Totals row should return #REF! on [#Totals]";

            ////// Case 1: Evaluate Table1[col] with apostrophe-escaped #-signs ////////
            ptgs = Parse(fpb, "SUM(" + tbl + "[calc='#*'#])");
            Assert.AreEqual(2, ptgs.Length);
            // Area3DPxg [sheet=Table ! A2:A7]
            Assert.IsTrue(ptgs[0] is Area3DPxg);
            Area3DPxg ptg0 = (Area3DPxg)ptgs[0];

            Assert.AreEqual("Table", ptg0.SheetName);
            Assert.AreEqual("A2:A7", ptg0.Format2DRefAsString());
            // Note: structured references are evaluated and resolved to regular 3D area references.
            Assert.AreEqual("Table!A2:A7", ptg0.ToFormulaString());
            // AttrPtg [sum ]
            Assert.IsTrue(ptgs[1] is AttrPtg);
            AttrPtg ptg1 = (AttrPtg)ptgs[1];

            Assert.IsTrue(ptg1.IsSum);
            ////// Case 1: Evaluate "Table1[col]" ////////
            ptgs = Parse(fpb, tbl + "[Name]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[col]", "Table!B2:B7", ptgs[0].ToFormulaString());
            ////// Case 2: Evaluate "Table1[[#Totals],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals],[col]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason);
            ////// Case 3: Evaluate "Table1[#Totals]" ////////
            ptgs = Parse(fpb, tbl + "[#Totals]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason);
            ////// Case 4: Evaluate "Table1[#All]" ////////
            ptgs = Parse(fpb, tbl + "[#All]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[#All]", "Table!A1:C7", ptgs[0].ToFormulaString());
            ////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Data rows) ////////
            ptgs = Parse(fpb, tbl + "[#Data]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[#Data]", "Table!A2:C7", ptgs[0].ToFormulaString());
            ////// Case 6: Evaluate "Table1[#Headers]" ////////
            ptgs = Parse(fpb, tbl + "[#Headers]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[#Headers]", "Table!A1:C1", ptgs[0].ToFormulaString());
            ////// Case 7: Evaluate "Table1[#Totals]" ////////
            ptgs = Parse(fpb, tbl + "[#Totals]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[#Totals]" + noTotalsRowReason);
            ////// Case 8: Evaluate "Table1[#This Row]" ////////
            ptgs = Parse(fpb, tbl + "[#This Row]", 2);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[#This Row]", "Table!A3:C3", ptgs[0].ToFormulaString());
            ////// Evaluate "Table1[@]" (equivalent to "Table1[#This Row]") ////////
            ptgs = Parse(fpb, tbl + "[@]", 2);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table!A3:C3", ptgs[0].ToFormulaString());
            ////// Evaluate "Table1[#This Row]" when rowIndex is outside Table ////////
            ptgs = Parse(fpb, tbl + "[#This Row]", 10);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[#This Row]");
            ////// Evaluate "Table1[@]" when rowIndex is outside Table ////////
            ptgs = Parse(fpb, tbl + "[@]", 10);
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.VALUE_INVALID, ptgs[0], "Table1[@]");
            ////// Evaluate "Table1[[#Data],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Data], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[[#Data],[col]]", "Table!C2:C7", ptgs[0].ToFormulaString());
            ////// Case 9: Evaluate "Table1[[#All],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#All], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[[#All],[col]]", "Table!C1:C7", ptgs[0].ToFormulaString());
            ////// Case 10: Evaluate "Table1[[#Headers],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers], [Number]]");
            Assert.AreEqual(1, ptgs.Length);
            // also acceptable: Table1!B1
            Assert.AreEqual("Table1[[#Headers],[col]]", "Table!C1:C1", ptgs[0].ToFormulaString());
            ////// Case 11: Evaluate "Table1[[#Totals],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals],[Name]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]]" + noTotalsRowReason);
            ////// Case 12: Evaluate "Table1[[#All],[col1]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#All], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[[#All],[col1]:[col2]]", "Table!B1:C7", ptgs[0].ToFormulaString());
            ////// Case 13: Evaluate "Table1[[#Data],[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Data], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[[#Data],[col]:[col2]]", "Table!B2:C7", ptgs[0].ToFormulaString());
            ////// Case 14: Evaluate "Table1[[#Headers],[col1]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[[#Headers],[col1]:[col2]]", "Table!B1:C1", ptgs[0].ToFormulaString());
            ////// Case 15: Evaluate "Table1[[#Totals],[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Totals], [Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual(ErrPtg.REF_INVALID, ptgs[0], "Table1[[#Totals],[col]:[col2]]" + noTotalsRowReason);
            ////// Case 16: Evaluate "Table1[[#Headers],[#Data],[col]]" ////////
            ptgs = Parse(fpb, tbl + "[[#Headers],[#Data],[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[[#Headers],[#Data],[col]]", "Table!C1:C7", ptgs[0].ToFormulaString());
            ////// Case 17: Evaluate "Table1[[#This Row], [col1]]" ////////
            ptgs = Parse(fpb, tbl + "[[#This Row], [Number]]", 2);
            Assert.AreEqual(1, ptgs.Length);
            // also acceptable: Table!C3
            Assert.AreEqual("Table1[[#This Row], [col1]]", "Table!C3:C3", ptgs[0].ToFormulaString());
            ////// Case 18: Evaluate "Table1[[col]:[col2]]" ////////
            ptgs = Parse(fpb, tbl + "[[Name]:[Number]]");
            Assert.AreEqual(1, ptgs.Length);
            Assert.AreEqual("Table1[[col]:[col2]]", "Table!B2:C7", ptgs[0].ToFormulaString());
            wb.Close();
        }
Exemple #8
0
        public void Bug56468()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
            XSSFRow row = sheet.CreateRow(0) as XSSFRow;
            XSSFCell cell = row.CreateCell(0) as XSSFCell;
            cell.SetCellValue("Hi");
            sheet.RepeatingRows = (new CellRangeAddress(0, 0, 0, 0));
            
            MemoryStream bos = new MemoryStream(8096);
            wb.Write(bos);
            byte[] firstSave = bos.ToArray();
            //using (FileStream fs = new FileStream("d:\\save1.xlsx", FileMode.Create, FileAccess.ReadWrite))
            //{
            //    fs.Write(firstSave, 0, firstSave.Length);
            //    fs.Flush();
            //}
            
            MemoryStream bos2 = new MemoryStream(8096);
            wb.Write(bos2);
            byte[] secondSave = bos2.ToArray();
            //using (FileStream fs2 = new FileStream("d:\\save2.xlsx", FileMode.Create, FileAccess.ReadWrite))
            //{
            //    fs2.Write(secondSave, 0, secondSave.Length);
            //    fs2.Flush();
            //}
            

            Assert.That(firstSave, new EqualConstraint(secondSave), 
                "Had: \n" + Arrays.ToString(firstSave) + " and \n" + Arrays.ToString(secondSave));

            wb.Close();
        }
Exemple #9
0
        public void TestXSSFTextParagraph()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet   sheet   = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;

                XSSFTextParagraph para    = shape.AddNewTextParagraph();
                para.AddNewTextRun().Text = ("Line 1");

                List <XSSFTextRun> Runs = para.TextRuns;
                Assert.AreEqual(1, Runs.Count);
                XSSFTextRun run = Runs[0];
                Assert.AreEqual("Line 1", run.Text);

                //Assert.IsNotNull(run.ParentParagraph);
                //Assert.IsNotNull(run.XmlObject);
                Assert.IsNotNull(run.GetRPr());

                Assert.AreEqual(Color.FromArgb(0, 0, 0), run.FontColor);

                Color color = Color.FromArgb(0, 255, 255);
                run.FontColor = (/*setter*/ color);
                Assert.AreEqual(color, run.FontColor);

                Assert.AreEqual(11.0, run.FontSize, 0.01);
                run.FontSize = (/*setter*/ 12.32);
                Assert.AreEqual(12.32, run.FontSize, 0.01);
                run.FontSize = (/*setter*/ -1.0);
                Assert.AreEqual(11.0, run.FontSize, 0.01);
                run.FontSize = (/*setter*/ -1.0);
                Assert.AreEqual(11.0, run.FontSize, 0.01);
                try
                {
                    run.FontSize = (/*setter*/ 0.9);
                    Assert.Fail("Should fail");
                }
                catch (ArgumentException e)
                {
                    Assert.IsTrue(e.Message.Contains("0.9"));
                }
                Assert.AreEqual(11.0, run.FontSize, 0.01);

                Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
                run.CharacterSpacing = (/*setter*/ 12.31);
                Assert.AreEqual(12.31, run.CharacterSpacing, 0.01);
                run.CharacterSpacing = (/*setter*/ 0.0);
                Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
                run.CharacterSpacing = (/*setter*/ 0.0);
                Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);

                Assert.AreEqual("Calibri", run.FontFamily);
                run.SetFontFamily("Arial", (byte)1, (byte)1, false);
                Assert.AreEqual("Arial", run.FontFamily);
                run.SetFontFamily("Arial", unchecked ((byte)-1), (byte)1, false);
                Assert.AreEqual("Arial", run.FontFamily);
                run.SetFontFamily("Arial", (byte)1, unchecked ((byte)-1), false);
                Assert.AreEqual("Arial", run.FontFamily);
                run.SetFontFamily("Arial", (byte)1, (byte)1, true);
                Assert.AreEqual("Arial", run.FontFamily);
                run.SetFontFamily(null, (byte)1, (byte)1, false);
                Assert.AreEqual("Calibri", run.FontFamily);
                run.SetFontFamily(null, (byte)1, (byte)1, false);
                Assert.AreEqual("Calibri", run.FontFamily);

                run.SetFont("Arial");
                Assert.AreEqual("Arial", run.FontFamily);

                Assert.AreEqual((byte)0, run.PitchAndFamily);
                run.SetFont(null);
                Assert.AreEqual((byte)0, run.PitchAndFamily);

                Assert.IsFalse(run.IsStrikethrough);
                run.IsStrikethrough = (/*setter*/ true);
                Assert.IsTrue(run.IsStrikethrough);
                run.IsStrikethrough = (/*setter*/ false);
                Assert.IsFalse(run.IsStrikethrough);

                Assert.IsFalse(run.IsSuperscript);
                run.IsSuperscript = (/*setter*/ true);
                Assert.IsTrue(run.IsSuperscript);
                run.IsSuperscript = (/*setter*/ false);
                Assert.IsFalse(run.IsSuperscript);

                Assert.IsFalse(run.IsSubscript);
                run.IsSubscript = (/*setter*/ true);
                Assert.IsTrue(run.IsSubscript);
                run.IsSubscript = (/*setter*/ false);
                Assert.IsFalse(run.IsSubscript);

                Assert.AreEqual(TextCap.NONE, run.TextCap);

                Assert.IsFalse(run.IsBold);
                run.IsBold = (/*setter*/ true);
                Assert.IsTrue(run.IsBold);
                run.IsBold = (/*setter*/ false);
                Assert.IsFalse(run.IsBold);

                Assert.IsFalse(run.IsItalic);
                run.IsItalic = (/*setter*/ true);
                Assert.IsTrue(run.IsItalic);
                run.IsItalic = (/*setter*/ false);
                Assert.IsFalse(run.IsItalic);

                Assert.IsFalse(run.IsUnderline);
                run.IsUnderline = (/*setter*/ true);
                Assert.IsTrue(run.IsUnderline);
                run.IsUnderline = (/*setter*/ false);
                Assert.IsFalse(run.IsUnderline);

                Assert.IsNotNull(run.ToString());
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #10
0
        public void BasicParse()
        {
            XSSFWorkbook           wb  = new XSSFWorkbook();
            XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);

            Ptg[] ptgs;

            ptgs = Parse(fpb, "ABC10");
            Assert.AreEqual(1, ptgs.Length);
            Assert.IsTrue(ptgs[0] is RefPtg, "Had " + Arrays.ToString(ptgs));

            ptgs = Parse(fpb, "A500000");
            Assert.AreEqual(1, ptgs.Length);
            Assert.IsTrue(ptgs[0] is RefPtg, "Had " + Arrays.ToString(ptgs));

            ptgs = Parse(fpb, "ABC500000");
            Assert.AreEqual(1, ptgs.Length);
            Assert.IsTrue(ptgs[0] is RefPtg, "Had " + Arrays.ToString(ptgs));

            //highest allowed rows and column (XFD and 0x100000)
            ptgs = Parse(fpb, "XFD1048576");
            Assert.AreEqual(1, ptgs.Length);
            Assert.IsTrue(ptgs[0] is RefPtg, "Had " + Arrays.ToString(ptgs));


            //column greater than XFD
            try
            {
                ptgs = Parse(fpb, "XFE10");
                Assert.Fail("expected exception");
            }
            catch (FormulaParseException e)
            {
                Assert.AreEqual("Specified named range 'XFE10' does not exist in the current workbook.", e.Message);
            }

            //row greater than 0x100000
            try
            {
                ptgs = Parse(fpb, "XFD1048577");
                Assert.Fail("expected exception");
            }
            catch (FormulaParseException e)
            {
                Assert.AreEqual("Specified named range 'XFD1048577' does not exist in the current workbook.", e.Message);
            }

            // Formula referencing one cell
            ptgs = Parse(fpb, "ISEVEN(A1)");
            Assert.AreEqual(3, ptgs.Length);
            Assert.AreEqual(typeof(NameXPxg), ptgs[0].GetType());
            Assert.AreEqual(typeof(RefPtg), ptgs[1].GetType());
            Assert.AreEqual(typeof(FuncVarPtg), ptgs[2].GetType());
            Assert.AreEqual("ISEVEN", ptgs[0].ToFormulaString());
            Assert.AreEqual("A1", ptgs[1].ToFormulaString());
            Assert.AreEqual("#external#", ptgs[2].ToFormulaString());

            // Formula referencing an area
            ptgs = Parse(fpb, "SUM(A1:B3)");
            Assert.AreEqual(2, ptgs.Length);
            Assert.AreEqual(typeof(AreaPtg), ptgs[0].GetType());
            Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
            Assert.AreEqual("A1:B3", ptgs[0].ToFormulaString());
            Assert.AreEqual("SUM", ptgs[1].ToFormulaString());

            // Formula referencing one cell in a different sheet
            ptgs = Parse(fpb, "SUM(Sheet1!A1)");
            Assert.AreEqual(2, ptgs.Length);
            Assert.AreEqual(typeof(Ref3DPxg), ptgs[0].GetType());
            Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
            Assert.AreEqual("Sheet1!A1", ptgs[0].ToFormulaString());
            Assert.AreEqual("SUM", ptgs[1].ToFormulaString());

            // Formula referencing an area in a different sheet
            ptgs = Parse(fpb, "SUM(Sheet1!A1:B3)");
            Assert.AreEqual(2, ptgs.Length);
            Assert.AreEqual(typeof(Area3DPxg), ptgs[0].GetType());
            Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
            Assert.AreEqual("Sheet1!A1:B3", ptgs[0].ToFormulaString());
            Assert.AreEqual("SUM", ptgs[1].ToFormulaString());

            wb.Close();
        }
        public void TestSetAllowBlankToTrue()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;

                XSSFDataValidation validation = CreateValidation(sheet);
                validation.GetCTDataValidation().allowBlank = (/*setter*/true);

                sheet.AddValidationData(validation);

                List<IDataValidation> dataValidations = sheet.GetDataValidations();
                Assert.AreEqual(true, (dataValidations[0] as XSSFDataValidation).GetCTDataValidation().allowBlank);
            }
            finally
            {
                wb.Close();
            }
        }
        public void Test53965()
        {

            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
                List<IDataValidation> lst = sheet.GetDataValidations();    //<-- works
                Assert.AreEqual(0, lst.Count);

                //create the cell that will have the validation applied
                sheet.CreateRow(0).CreateCell(0);

                IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
                IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
                IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList);
                sheet.AddValidationData(validation);

                // this line caused XmlValueOutOfRangeException , see Bugzilla 3965
                lst = sheet.GetDataValidations();
                Assert.AreEqual(1, lst.Count);
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #13
0
        public void Bug57880()
        {
            Console.WriteLine("long time test, run over 1 minute.");
            int numStyles = 33000;
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet s = wb.CreateSheet("TestSheet") as XSSFSheet;
            XSSFDataFormat fmt = wb.GetCreationHelper().CreateDataFormat() as XSSFDataFormat;
            for (int i = 1; i < numStyles; i++)
            {
                short df = fmt.GetFormat("test" + i);
                // Format indexes will be wrapped beyond 32,676
                Assert.AreEqual(164 + i, df & 0xffff);
                // Create a style and use it
                XSSFCellStyle style = wb.CreateCellStyle() as XSSFCellStyle;
                Assert.AreEqual(i, style.UIndex);
                style.DataFormat = (/*setter*/df);
                XSSFCell c = s.CreateRow(i).CreateCell(0, CellType.Numeric) as XSSFCell;
                c.CellStyle = (/*setter*/style);
                c.SetCellValue(i);
            }

            //wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            // using temp file instead of ByteArrayOutputStream because of OOM in gump run
            FileInfo tmp = TempFile.CreateTempFile("poi-test", ".bug57880");
            FileStream fos = new FileStream(tmp.FullName, FileMode.Create, FileAccess.ReadWrite);
            wb.Write(fos);
            fos.Close();

            wb.Close();
            fmt = null; s = null; wb = null;
            // System.gc();

            wb = new XSSFWorkbook(tmp.FullName);
            fmt = wb.GetCreationHelper().CreateDataFormat() as XSSFDataFormat;
            s = wb.GetSheetAt(0) as XSSFSheet;
            for (int i = 1; i < numStyles; i++)
            {
                XSSFCellStyle style = wb.GetCellStyleAt((short)i) as XSSFCellStyle;
                Assert.IsNotNull(style);
                Assert.AreEqual(i, style.UIndex);
                Assert.AreEqual(164 + i, style.DataFormat & 0xffff);
                Assert.AreEqual("test" + i, style.GetDataFormatString());
            }

            wb.Close();
            tmp.Delete();
        }
        public void TestBug55843()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
                XSSFRow row = sheet.CreateRow(0) as XSSFRow;
                XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
                XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
                XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
                cellB1.SetCellValue(10);
                XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
                cellA2.SetCellFormula("IF(B1=0,\"\",((ROW()-ROW(A$1))*12))");
                CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
                System.Console.WriteLine(Evaluate);
                Assert.AreEqual("12", Evaluate.FormatAsString());

                cellA2.CellFormula = (/*setter*/"IF(NOT(B1=0),((ROW()-ROW(A$1))*12),\"\")");
                CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
                System.Console.WriteLine(EvaluateN);

                Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
                Assert.AreEqual("12", EvaluateN.FormatAsString());
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #15
0
        public void TestBug55752()
        {
            IWorkbook wb = new XSSFWorkbook();

            try
            {
                ISheet sheet = wb.CreateSheet("test");

                for (int i = 0; i < 4; i++)
                {
                    IRow row = sheet.CreateRow(i);
                    for (int j = 0; j < 2; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        cell.CellStyle = (wb.CreateCellStyle());
                    }
                }

                // set content
                IRow row1 = sheet.GetRow(0);
                row1.GetCell(0).SetCellValue("AAA");
                IRow row2 = sheet.GetRow(1);
                row2.GetCell(0).SetCellValue("BBB");
                IRow row3 = sheet.GetRow(2);
                row3.GetCell(0).SetCellValue("CCC");
                IRow row4 = sheet.GetRow(3);
                row4.GetCell(0).SetCellValue("DDD");

                // merge cells
                CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1);
                sheet.AddMergedRegion(range1);
                CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1);
                sheet.AddMergedRegion(range2);
                CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1);
                sheet.AddMergedRegion(range3);
                Assert.AreEqual(0, range3.FirstColumn);
                Assert.AreEqual(1, range3.LastColumn);
                Assert.AreEqual(2, range3.LastRow);
                CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1);
                sheet.AddMergedRegion(range4);

                // set border
                RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range1, sheet, wb);

                row2.GetCell(0).CellStyle.BorderBottom = BorderStyle.Thin;
                row2.GetCell(1).CellStyle.BorderBottom = BorderStyle.Thin;
                ICell cell0 = CellUtil.GetCell(row3, 0);
                CellUtil.SetCellStyleProperty(cell0, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
                ICell cell1 = CellUtil.GetCell(row3, 1);
                CellUtil.SetCellStyleProperty(cell1, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
                RegionUtil.SetBorderBottom((int)BorderStyle.Thin, range4, sheet, wb);

                // write to file
                Stream stream = new FileStream("55752.xlsx", FileMode.Create, FileAccess.ReadWrite);
                try
                {
                    wb.Write(stream);
                }
                finally
                {
                    stream.Close();
                }
            }
            finally
            {
                wb.Close();
            }
        }
        public void TestListOfCustomProperties()
        {
            FileInfo inp = POIDataSamples.GetSpreadSheetInstance().GetFileInfo("ExcelWithAttachments.xlsm");
            OPCPackage pkg = OPCPackage.Open(inp, PackageAccess.READ);
            XSSFWorkbook wb = new XSSFWorkbook(pkg);

            Assert.IsNotNull(wb.GetProperties());
            Assert.IsNotNull(wb.GetProperties().CustomProperties);

            foreach (CT_Property prop in wb.GetProperties().CustomProperties.GetUnderlyingProperties().GetPropertyList())
            {
                Assert.IsNotNull(prop);
            }

            wb.Close();
            pkg.Close();
        }
Exemple #17
0
        public void TestXSSFTextParagraph()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet   sheet   = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;

                XSSFRichTextString rt = new XSSFRichTextString("Test String");

                XSSFFont font  = wb.CreateFont() as XSSFFont;
                Color    color = Color.FromArgb(0, 255, 255);
                font.SetColor(new XSSFColor(color));
                font.FontName = (/*setter*/ "Arial");
                rt.ApplyFont(font);

                shape.SetText(/*setter*/ rt);

                Assert.IsNotNull(shape.GetCTShape());
                Assert.IsNotNull(shape.GetEnumerator());
                Assert.IsNotNull(XSSFSimpleShape.GetPrototype());

                foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber)))
                {
                    shape.TextParagraphs[(0)].SetBullet(nr);
                    Assert.IsNotNull(shape.Text);
                }

                shape.TextParagraphs[(0)].SetBullet(false);
                Assert.IsNotNull(shape.Text);

                shape.SetText("testtext");
                Assert.AreEqual("testtext", shape.Text);

                shape.SetText(new XSSFRichTextString());
                Assert.AreEqual("null", shape.Text);

                shape.AddNewTextParagraph();
                shape.AddNewTextParagraph("test-other-text");
                shape.AddNewTextParagraph(new XSSFRichTextString("rtstring"));
                shape.AddNewTextParagraph(new XSSFRichTextString());
                Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text);

                Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
                shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.CLIP);
                Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow);
                shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.OVERFLOW);
                Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
                shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
                Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
                shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
                Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);

                Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
                shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.CLIP);
                Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow);
                shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.OVERFLOW);
                Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
                shape.TextVerticalOverflow = TextVerticalOverflow.None;
                Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
                shape.TextVerticalOverflow = TextVerticalOverflow.None;
                Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);

                Assert.AreEqual((short)VerticalAlignment.Top, shape.VerticalAlignment);
                shape.VerticalAlignment = (short)VerticalAlignment.Bottom;
                Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment);
                shape.VerticalAlignment = (short)VerticalAlignment.Top;
                Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
                shape.VerticalAlignment = (short)VerticalAlignment.None;
                Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
                shape.VerticalAlignment = (short)VerticalAlignment.None;
                Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);

                Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
                shape.TextDirection = (/*setter*/ TextDirection.STACKED);
                Assert.AreEqual(TextDirection.STACKED, shape.TextDirection);
                shape.TextDirection = (/*setter*/ TextDirection.HORIZONTAL);
                Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
                shape.TextDirection = (/*setter*/ TextDirection.None);
                Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
                shape.TextDirection = (/*setter*/ TextDirection.None);
                Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);

                Assert.AreEqual(3.6, shape.BottomInset, 0.01);
                shape.BottomInset = (/*setter*/ 12.32);
                Assert.AreEqual(12.32, shape.BottomInset, 0.01);
                shape.BottomInset = (/*setter*/ -1);
                Assert.AreEqual(3.6, shape.BottomInset, 0.01);
                shape.BottomInset = (/*setter*/ -1);
                Assert.AreEqual(3.6, shape.BottomInset, 0.01);

                Assert.AreEqual(3.6, shape.LeftInset, 0.01);
                shape.LeftInset = (/*setter*/ 12.31);
                Assert.AreEqual(12.31, shape.LeftInset, 0.01);
                shape.LeftInset = (/*setter*/ -1);
                Assert.AreEqual(3.6, shape.LeftInset, 0.01);
                shape.LeftInset = (/*setter*/ -1);
                Assert.AreEqual(3.6, shape.LeftInset, 0.01);

                Assert.AreEqual(3.6, shape.RightInset, 0.01);
                shape.RightInset = (/*setter*/ 13.31);
                Assert.AreEqual(13.31, shape.RightInset, 0.01);
                shape.RightInset = (/*setter*/ -1);
                Assert.AreEqual(3.6, shape.RightInset, 0.01);
                shape.RightInset = (/*setter*/ -1);
                Assert.AreEqual(3.6, shape.RightInset, 0.01);

                Assert.AreEqual(3.6, shape.TopInset, 0.01);
                shape.TopInset = (/*setter*/ 23.31);
                Assert.AreEqual(23.31, shape.TopInset, 0.01);
                shape.TopInset = (/*setter*/ -1);
                Assert.AreEqual(3.6, shape.TopInset, 0.01);
                shape.TopInset = (/*setter*/ -1);
                Assert.AreEqual(3.6, shape.TopInset, 0.01);

                Assert.IsTrue(shape.WordWrap);
                shape.WordWrap = (/*setter*/ false);
                Assert.IsFalse(shape.WordWrap);
                shape.WordWrap = (/*setter*/ true);
                Assert.IsTrue(shape.WordWrap);

                Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
                shape.TextAutofit = (/*setter*/ TextAutofit.NORMAL);
                Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
                shape.TextAutofit = (/*setter*/ TextAutofit.SHAPE);
                Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit);
                shape.TextAutofit = (/*setter*/ TextAutofit.NONE);
                Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit);

                Assert.AreEqual(5, shape.ShapeType);
                shape.ShapeType = (/*setter*/ 23);
                Assert.AreEqual(23, shape.ShapeType);

                // TODO: should this be supported?
                //            shape.ShapeType=(/*setter*/-1);
                //            Assert.AreEqual(-1, shape.ShapeType);
                //            shape.ShapeType=(/*setter*/-1);
                //            Assert.AreEqual(-1, shape.ShapeType);

                Assert.IsNotNull(shape.GetShapeProperties());
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #18
0
        public void TestBug56820_Formula2()
        {
            IWorkbook wb = new XSSFWorkbook();
            try
            {
                IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
                ISheet sh = wb.CreateSheet();

                sh.CreateRow(0).CreateCell(0).SetCellValue(0.0d);
                ICell formulaCell1 = sh.CreateRow(1).CreateCell(0);
                ICell formulaCell2 = sh.CreateRow(2).CreateCell(0);
                formulaCell1.CellFormula = (/*setter*/FORMULA2);
                formulaCell2.CellFormula = (/*setter*/FORMULA2);

                double A1 = Evaluator.Evaluate(formulaCell1).NumberValue;
                double A2 = Evaluator.Evaluate(formulaCell2).NumberValue; //<-- FAILS EVALUATION

                Assert.AreEqual(2, A1, 0);
                Assert.AreEqual(4, A2, 0);
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #19
0
        public void Bug58245_XSSFSheetIterator()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            wb.CreateSheet();

            // =====================================================================
            // Case 1: Existing code uses XSSFSheet for-each loop
            // =====================================================================
            // Original code (no longer valid)

            /*
             * for (XSSFSheet sh : wb) {
             *  sh.createRow(0);
             * }
             */

            // Option A:
            foreach (XSSFSheet sh in wb)
            {
                sh.CreateRow(0);
            }

            // Option B (preferred for new code):
            foreach (ISheet sh in wb)
            {
                sh.CreateRow(0);
            }

            // =====================================================================
            // Case 2: Existing code creates an iterator variable
            // =====================================================================
            // Original code (no longer valid)

            /*
             * Iterator<XSSFSheet> it = wb.iterator();
             * XSSFSheet sh = it.next();
             * sh.createRow(0);
             */

            // Option A:
            {
                IEnumerator <XSSFSheet> it = wb.GetEnumerator() as IEnumerator <XSSFSheet>;
                XSSFSheet sh = it.Current;
                sh.CreateRow(0);
            }

            // Option B:
            {
                //IEnumerator<XSSFSheet> it = wb.XssfSheetIterator();
                //XSSFSheet sh = it.Current;
                //sh.CreateRow(0);
            }

            // Option C (preferred for new code):
            {
                IEnumerator <ISheet> it = wb.GetEnumerator() as IEnumerator <ISheet>;
                ISheet sh = it.Current;
                sh.CreateRow(0);
            }
            wb.Close();
        }
Exemple #20
0
        public void Test48877()
        {
            String text = "Use \n with word wrap on to create a new line.\n" +
               "This line finishes with two trailing spaces.  ";

            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;

            IFont font1 = wb.CreateFont();
            font1.Color = ((short)20);
            IFont font2 = wb.CreateFont();
            font2.Color = (short)(FontColor.Red);
            IFont font3 = wb.GetFontAt((short)0);

            XSSFRow row = sheet.CreateRow(2) as XSSFRow;
            XSSFCell cell = row.CreateCell(2) as XSSFCell;

            XSSFRichTextString richTextString =
               wb.GetCreationHelper().CreateRichTextString(text) as XSSFRichTextString;

            // Check the text has the newline
            Assert.AreEqual(text, richTextString.String);

            // Apply the font
            richTextString.ApplyFont(font3);
            richTextString.ApplyFont(0, 3, font1);
            cell.SetCellValue(richTextString);

            // To enable newlines you need Set a cell styles with wrap=true
            ICellStyle cs = wb.CreateCellStyle();
            cs.WrapText = (true);
            cell.CellStyle = (cs);

            // Check the text has the
            Assert.AreEqual(text, cell.StringCellValue);

            // Save the file and re-read it
            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            sheet = wb.GetSheetAt(0) as XSSFSheet;
            row = sheet.GetRow(2) as XSSFRow;
            cell = row.GetCell(2) as XSSFCell;
            Assert.AreEqual(text, cell.StringCellValue);

            // Now add a 2nd, and check again
            int fontAt = text.IndexOf("\n", 6);
            cell.RichStringCellValue.ApplyFont(10, fontAt + 1, font2);
            Assert.AreEqual(text, cell.StringCellValue);

            Assert.AreEqual(4, (cell.RichStringCellValue as XSSFRichTextString).NumFormattingRuns);
            Assert.AreEqual("Use", (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[0].t);

            String r3 = (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[2].t;
            Assert.AreEqual("line.\n", r3.Substring(r3.Length - 6));

            // Save and re-check
            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            sheet = wb.GetSheetAt(0) as XSSFSheet;
            row = sheet.GetRow(2) as XSSFRow;
            cell = row.GetCell(2) as XSSFCell;
            Assert.AreEqual(text, cell.StringCellValue);
            wb.Close();

            //       FileOutputStream out = new FileOutputStream("/tmp/test48877.xlsx");
            //       wb.Write(out);
            //       out.Close();

        }
Exemple #21
0
        public void Bug49702()
        {
            // First try with a new file
            XSSFWorkbook wb1 = new XSSFWorkbook();

            // Should have one style
            Assert.AreEqual(1, wb1.NumCellStyles);
            wb1.GetCellStyleAt((short)0);
            try
            {
                wb1.GetCellStyleAt((short)1);
                Assert.Fail("Shouldn't be able to get style at 1 that doesn't exist");
            }
            catch (ArgumentOutOfRangeException) { }

            // Add another one
            ICellStyle cs = wb1.CreateCellStyle();

            cs.DataFormat = ((short)11);

            // Re-check
            Assert.AreEqual(2, wb1.NumCellStyles);
            wb1.GetCellStyleAt((short)0);
            wb1.GetCellStyleAt((short)1);
            try
            {
                wb1.GetCellStyleAt((short)2);
                Assert.Fail("Shouldn't be able to get style at 2 that doesn't exist");
            }
            catch (ArgumentOutOfRangeException) { }

            // Save and reload
            XSSFWorkbook nwb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb1);

            Assert.AreEqual(2, nwb.NumCellStyles);
            nwb.GetCellStyleAt((short)0);
            nwb.GetCellStyleAt((short)1);
            try
            {
                nwb.GetCellStyleAt((short)2);
                Assert.Fail("Shouldn't be able to Get style at 2 that doesn't exist");
            }
            catch (ArgumentOutOfRangeException) { }

            // Now with an existing file
            XSSFWorkbook wb2 = XSSFTestDataSamples.OpenSampleWorkbook("sample.xlsx");

            Assert.AreEqual(3, wb2.NumCellStyles);
            wb2.GetCellStyleAt((short)0);
            wb2.GetCellStyleAt((short)1);
            wb2.GetCellStyleAt((short)2);
            try
            {
                wb2.GetCellStyleAt((short)3);
                Assert.Fail("Shouldn't be able to Get style at 3 that doesn't exist");
            }
            catch (ArgumentOutOfRangeException) { }

            wb2.Close();
            wb1.Close();
            nwb.Close();
        }
Exemple #22
0
        public void TestSetColor()
        {
            IWorkbook wb = new XSSFWorkbook();
            ISheet sheet = wb.CreateSheet();
            IRow row = sheet.CreateRow(0);

            //CreationHelper ch = wb.GetCreationHelper();
            IDataFormat format = wb.CreateDataFormat();
            ICell cell = row.CreateCell(1);
            cell.SetCellValue("somEvalue");
            ICellStyle cellStyle = wb.CreateCellStyle();


            cellStyle.DataFormat = (/*setter*/format.GetFormat("###0"));

            cellStyle.FillBackgroundColor = (/*setter*/IndexedColors.DarkBlue.Index);
            cellStyle.FillForegroundColor = (/*setter*/IndexedColors.DarkBlue.Index);
            cellStyle.FillPattern = FillPattern.SolidForeground;

            cellStyle.Alignment = HorizontalAlignment.Right;
            cellStyle.VerticalAlignment = VerticalAlignment.Top;

            cell.CellStyle = (/*setter*/cellStyle);

            /*OutputStream stream = new FileOutputStream("C:\\temp\\CellColor.xlsx");
            try {
                wb.Write(stream);
            } finally {
                stream.Close();
            }*/

            IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb);
            ICell cellBack = wbBack.GetSheetAt(0).GetRow(0).GetCell(1);
            Assert.IsNotNull(cellBack);
            ICellStyle styleBack = cellBack.CellStyle;
            Assert.AreEqual(IndexedColors.DarkBlue.Index, styleBack.FillBackgroundColor);
            Assert.AreEqual(IndexedColors.DarkBlue.Index, styleBack.FillForegroundColor);
            Assert.AreEqual(HorizontalAlignment.Right, styleBack.Alignment);
            Assert.AreEqual(VerticalAlignment.Top, styleBack.VerticalAlignment);
            Assert.AreEqual(FillPattern.SolidForeground, styleBack.FillPattern);

            wbBack.Close();

            wb.Close();
        }
Exemple #23
0
        public void SaveLoadNew()
        {
            XSSFWorkbook wb1 = new XSSFWorkbook();

            //check that the default date system is Set to 1900
            CT_WorkbookPr pr = wb1.GetCTWorkbook().workbookPr;

            Assert.IsNotNull(pr);
            Assert.IsTrue(pr.IsSetDate1904());
            Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");

            ISheet sheet1 = wb1.CreateSheet("sheet1");
            ISheet sheet2 = wb1.CreateSheet("sheet2");

            wb1.CreateSheet("sheet3");

            IRichTextString rts = wb1.GetCreationHelper().CreateRichTextString("hello world");

            sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
            sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
            sheet2.CreateRow(0);

            Assert.AreEqual(0, wb1.GetSheetAt(0).FirstRowNum);
            Assert.AreEqual(1, wb1.GetSheetAt(0).LastRowNum);
            Assert.AreEqual(0, wb1.GetSheetAt(1).FirstRowNum);
            Assert.AreEqual(0, wb1.GetSheetAt(1).LastRowNum);
            Assert.AreEqual(0, wb1.GetSheetAt(2).FirstRowNum);
            Assert.AreEqual(0, wb1.GetSheetAt(2).LastRowNum);

            FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
            Stream   out1 = File.OpenWrite(file.FullName);

            wb1.Write(out1);
            out1.Close();

            // Check the namespace Contains what we'd expect it to
            OPCPackage  pkg       = OPCPackage.Open(file.ToString());
            PackagePart wbRelPart =
                pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));

            Assert.IsNotNull(wbRelPart);
            Assert.IsTrue(wbRelPart.IsRelationshipPart);
            Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);

            PackagePart wbPart =
                pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));

            // Links to the three sheets, shared strings and styles
            Assert.IsTrue(wbPart.HasRelationships);
            Assert.AreEqual(5, wbPart.Relationships.Size);
            wb1.Close();

            // Load back the XSSFWorkbook
            XSSFWorkbook wb2 = new XSSFWorkbook(pkg);

            Assert.AreEqual(3, wb2.NumberOfSheets);
            Assert.IsNotNull(wb2.GetSheetAt(0));
            Assert.IsNotNull(wb2.GetSheetAt(1));
            Assert.IsNotNull(wb2.GetSheetAt(2));

            Assert.IsNotNull(wb2.GetSharedStringSource());
            Assert.IsNotNull(wb2.GetStylesSource());

            Assert.AreEqual(0, wb2.GetSheetAt(0).FirstRowNum);
            Assert.AreEqual(1, wb2.GetSheetAt(0).LastRowNum);
            Assert.AreEqual(0, wb2.GetSheetAt(1).FirstRowNum);
            Assert.AreEqual(0, wb2.GetSheetAt(1).LastRowNum);
            Assert.AreEqual(0, wb2.GetSheetAt(2).FirstRowNum);
            Assert.AreEqual(0, wb2.GetSheetAt(2).LastRowNum);

            sheet1 = wb2.GetSheetAt(0);
            Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
            Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);

            pkg.Close();

            Assert.AreEqual(0, Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "*.tmp").Length, "At Last: There are no temporary files.");
        }
Exemple #24
0
        public void TestBug56835CellComment()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                // first comment works
                IClientAnchor anchor = new XSSFClientAnchor(1, 1, 2, 2, 3, 3, 4, 4);
                XSSFComment comment = Drawing.CreateCellComment(anchor) as XSSFComment;
                Assert.IsNotNull(comment);

                try
                {
                    Drawing.CreateCellComment(anchor);
                    Assert.Fail("Should fail if we try to add the same comment for the same cell");
                }
                catch (ArgumentException e)
                {
                    // expected
                }
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #25
0
        public void TestReferencesToOtherWorkbooks()
        {
            XSSFWorkbook         wb        = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("ref2-56737.xlsx");
            XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
            XSSFSheet            s         = wb.GetSheetAt(0) as XSSFSheet;

            // References to a .xlsx file
            IRow  rXSLX      = s.GetRow(2);
            ICell cXSLX_cell = rXSLX.GetCell(4);
            ICell cXSLX_sNR  = rXSLX.GetCell(6);
            ICell cXSLX_gNR  = rXSLX.GetCell(8);

            Assert.AreEqual("[1]Uses!$A$1", cXSLX_cell.CellFormula);
            Assert.AreEqual("[1]Defines!NR_To_A1", cXSLX_sNR.CellFormula);
            Assert.AreEqual("[1]!NR_Global_B2", cXSLX_gNR.CellFormula);

            Assert.AreEqual("Hello!", cXSLX_cell.StringCellValue);
            Assert.AreEqual("Test A1", cXSLX_sNR.StringCellValue);
            Assert.AreEqual(142.0, cXSLX_gNR.NumericCellValue, 0);

            // References to a .xls file
            IRow  rXSL      = s.GetRow(4);
            ICell cXSL_cell = rXSL.GetCell(4);
            ICell cXSL_sNR  = rXSL.GetCell(6);
            ICell cXSL_gNR  = rXSL.GetCell(8);

            Assert.AreEqual("[2]Uses!$C$1", cXSL_cell.CellFormula);
            Assert.AreEqual("[2]Defines!NR_To_A1", cXSL_sNR.CellFormula);
            Assert.AreEqual("[2]!NR_Global_B2", cXSL_gNR.CellFormula);

            Assert.AreEqual("Hello!", cXSL_cell.StringCellValue);
            Assert.AreEqual("Test A1", cXSL_sNR.StringCellValue);
            Assert.AreEqual(142.0, cXSL_gNR.NumericCellValue, 0);

            // Try to Evaluate without references, won't work
            // (At least, not unit we fix bug #56752 that is1)
            try
            {
                evaluator.Evaluate(cXSL_cell);
                Assert.Fail("Without a fix for #56752, shouldn't be able to Evaluate a " +
                            "reference to a non-provided linked workbook");
            }
            catch (Exception)
            {
            }

            // Setup the environment
            Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>();

            evaluators.Add("ref2-56737.xlsx", evaluator);
            evaluators.Add("56737.xlsx",
                           _testDataProvider.OpenSampleWorkbook("56737.xlsx").GetCreationHelper().CreateFormulaEvaluator());
            evaluators.Add("56737.xls",
                           HSSFTestDataSamples.OpenSampleWorkbook("56737.xls").GetCreationHelper().CreateFormulaEvaluator());
            evaluator.SetupReferencedWorkbooks(evaluators);

            // Try Evaluating all of them, ensure we don't blow up
            foreach (IRow r in s)
            {
                foreach (ICell c in r)
                {
                    // TODO Fix and enable
                    evaluator.Evaluate(c);
                }
            }

            // And evaluate the other way too
            evaluator.EvaluateAll();

            // Static evaluator won't work, as no references passed in
            try
            {
                XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
                Assert.Fail("Static method lacks references, shouldn't work");
            }
            catch (Exception)
            {
                // expected here
            }


            // Evaluate specific cells and check results
            Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSLX_cell).FormatAsString());
            Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSLX_sNR).FormatAsString());
            //Assert.AreEqual("142.0", evaluator.Evaluate(cXSLX_gNR).FormatAsString());
            Assert.AreEqual("142", evaluator.Evaluate(cXSLX_gNR).FormatAsString());

            Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell).FormatAsString());
            Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSL_sNR).FormatAsString());
            //Assert.AreEqual("142.0", evaluator.Evaluate(cXSL_gNR).FormatAsString());
            Assert.AreEqual("142", evaluator.Evaluate(cXSL_gNR).FormatAsString());

            // Add another formula referencing these workbooks
            ICell cXSL_cell2 = rXSL.CreateCell(40);

            cXSL_cell2.CellFormula = (/*setter*/ "[56737.xls]Uses!$C$1");
            // TODO Shouldn't it become [2] like the others?
            Assert.AreEqual("[56737.xls]Uses!$C$1", cXSL_cell2.CellFormula);
            Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell2).FormatAsString());


            // Now add a formula that refers to yet another (different) workbook
            // Won't work without the workbook being linked
            ICell cXSLX_nw_cell = rXSLX.CreateCell(42);

            try
            {
                cXSLX_nw_cell.CellFormula = (/*setter*/ "[alt.xlsx]Sheet1!$A$1");
                Assert.Fail("New workbook not linked, shouldn't be able to Add");
            }
            catch (Exception) { }

            // Link and re-try
            IWorkbook alt = new XSSFWorkbook();

            try
            {
                alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
                // TODO Implement the rest of this, see bug #57184

                /*
                 *          wb.linkExternalWorkbook("alt.xlsx", alt);
                 *
                 *          cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1");
                 *          // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ?
                 *          Assert.AreEqual("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.getCellFormula());
                 *
                 *          // Evaluate it, without a link to that workbook
                 *          try {
                 *              evaluator.evaluate(cXSLX_nw_cell);
                 *              fail("No cached value and no link to workbook, shouldn't evaluate");
                 *          } catch(Exception e) {}
                 *
                 *          // Add a link, check it does
                 *          evaluators.put("alt.xlsx", alt.getCreationHelper().createFormulaEvaluator());
                 *          evaluator.setupReferencedWorkbooks(evaluators);
                 *
                 *          evaluator.evaluate(cXSLX_nw_cell);
                 *          Assert.AreEqual("In another workbook", cXSLX_nw_cell.getStringCellValue());
                 */
            }
            finally
            {
                alt.Close();
            }
            wb.Close();
        }
Exemple #26
0
        public void WorkbookProperties()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            try
            {
                POIXMLProperties props = workbook.GetProperties();
                Assert.IsNotNull(props);
                //the Application property must be set for new workbooks, see Bugzilla #47559
                Assert.AreEqual("NPOI", props.ExtendedProperties.GetUnderlyingProperties().Application);

                PackagePropertiesPart opcProps = props.CoreProperties.GetUnderlyingProperties();
                Assert.IsNotNull(opcProps);

                opcProps.SetTitleProperty("Testing Bugzilla #47460");
                Assert.AreEqual("NPOI", opcProps.GetCreatorProperty());
                opcProps.SetCreatorProperty("*****@*****.**");

                XSSFWorkbook wbBack = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
                Assert.AreEqual("NPOI", wbBack.GetProperties().ExtendedProperties.GetUnderlyingProperties().Application);
                opcProps = wbBack.GetProperties().CoreProperties.GetUnderlyingProperties();
                Assert.AreEqual("Testing Bugzilla #47460", opcProps.GetTitleProperty());
                Assert.AreEqual("*****@*****.**", opcProps.GetCreatorProperty());
            }
            finally
            {
                workbook.Close();
            }
        }
Exemple #27
0
        public void Test49253()
        {
            XSSFWorkbook wb1 = new XSSFWorkbook();
            XSSFWorkbook wb2 = new XSSFWorkbook();

            // No print Settings before repeating
            XSSFSheet s1 = wb1.CreateSheet() as XSSFSheet;
            Assert.AreEqual(false, s1.GetCTWorksheet().IsSetPageSetup());
            Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageMargins());

            wb1.SetRepeatingRowsAndColumns(0, 2, 3, 1, 2);

            Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageSetup());
            Assert.AreEqual(true, s1.GetCTWorksheet().IsSetPageMargins());

            XSSFPrintSetup ps1 = s1.PrintSetup as XSSFPrintSetup;
            Assert.AreEqual(false, ps1.ValidSettings);
            Assert.AreEqual(false, ps1.Landscape);


            // Had valid print Settings before repeating
            XSSFSheet s2 = wb2.CreateSheet() as XSSFSheet;
            XSSFPrintSetup ps2 = s2.PrintSetup as XSSFPrintSetup;
            Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageSetup());
            Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageMargins());

            ps2.Landscape = (false);
            Assert.AreEqual(true, ps2.ValidSettings);
            Assert.AreEqual(false, ps2.Landscape);

            wb2.SetRepeatingRowsAndColumns(0, 2, 3, 1, 2);

            ps2 = s2.PrintSetup as XSSFPrintSetup;
            Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageSetup());
            Assert.AreEqual(true, s2.GetCTWorksheet().IsSetPageMargins());
            Assert.AreEqual(true, ps2.ValidSettings);
            Assert.AreEqual(false, ps2.Landscape);

            wb1.Close();
            wb2.Close();
        }
Exemple #28
0
        public void SetFirstVisibleTab_57373()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                /*Sheet sheet1 =*/
                wb.CreateSheet();
                ISheet sheet2 = wb.CreateSheet();
                int idx2 = wb.GetSheetIndex(sheet2);
                ISheet sheet3 = wb.CreateSheet();
                int idx3 = wb.GetSheetIndex(sheet3);

                // add many sheets so "first visible" is relevant
                for (int i = 0; i < 30; i++)
                {
                    wb.CreateSheet();
                }

                wb.FirstVisibleTab = (/*setter*/idx2);
                wb.SetActiveSheet(idx3);

                //wb.Write(new FileOutputStream(new File("C:\\temp\\test.xlsx")));

                Assert.AreEqual(idx2, wb.FirstVisibleTab);
                Assert.AreEqual(idx3, wb.ActiveSheetIndex);

                IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb);

                sheet2 = wbBack.GetSheetAt(idx2);
                sheet3 = wbBack.GetSheetAt(idx3);
                Assert.AreEqual(idx2, wb.FirstVisibleTab);
                Assert.AreEqual(idx3, wb.ActiveSheetIndex);
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #29
0
        public void Test51037()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet s = wb.CreateSheet() as XSSFSheet;

            ICellStyle defaultStyle = wb.GetCellStyleAt((short)0);
            Assert.AreEqual(0, defaultStyle.Index);

            ICellStyle blueStyle = wb.CreateCellStyle();
            blueStyle.FillForegroundColor = (IndexedColors.Aqua.Index);
            blueStyle.FillPattern = (FillPattern.SolidForeground);
            Assert.AreEqual(1, blueStyle.Index);

            ICellStyle pinkStyle = wb.CreateCellStyle();
            pinkStyle.FillForegroundColor = (IndexedColors.Pink.Index);
            pinkStyle.FillPattern = (FillPattern.SolidForeground);
            Assert.AreEqual(2, pinkStyle.Index);

            // Starts empty
            Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray());
            CT_Cols cols = s.GetCTWorksheet().GetColsArray(0);
            Assert.AreEqual(0, cols.sizeOfColArray());

            // Add some rows and columns
            XSSFRow r1 = s.CreateRow(0) as XSSFRow;
            XSSFRow r2 = s.CreateRow(1) as XSSFRow;
            r1.CreateCell(0);
            r1.CreateCell(2);
            r2.CreateCell(0);
            r2.CreateCell(3);

            // Check no style is there
            Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray());
            Assert.AreEqual(0, cols.sizeOfColArray());

            Assert.AreEqual(defaultStyle, s.GetColumnStyle(0));
            Assert.AreEqual(defaultStyle, s.GetColumnStyle(2));
            Assert.AreEqual(defaultStyle, s.GetColumnStyle(3));


            // Apply the styles
            s.SetDefaultColumnStyle(0, pinkStyle);
            s.SetDefaultColumnStyle(3, blueStyle);

            // Check
            Assert.AreEqual(pinkStyle, s.GetColumnStyle(0));
            Assert.AreEqual(defaultStyle, s.GetColumnStyle(2));
            Assert.AreEqual(blueStyle, s.GetColumnStyle(3));

            Assert.AreEqual(1, s.GetCTWorksheet().sizeOfColsArray());
            Assert.AreEqual(2, cols.sizeOfColArray());

            Assert.AreEqual(1, cols.GetColArray(0).min);
            Assert.AreEqual(1, cols.GetColArray(0).max);
            Assert.AreEqual(pinkStyle.Index, cols.GetColArray(0).style);

            Assert.AreEqual(4, cols.GetColArray(1).min);
            Assert.AreEqual(4, cols.GetColArray(1).max);
            Assert.AreEqual(blueStyle.Index, cols.GetColArray(1).style);


            // Save, re-load and re-check 
            wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
            s = wb.GetSheetAt(0) as XSSFSheet;
            defaultStyle = wb.GetCellStyleAt(defaultStyle.Index);
            blueStyle = wb.GetCellStyleAt(blueStyle.Index);
            pinkStyle = wb.GetCellStyleAt(pinkStyle.Index);

            Assert.AreEqual(pinkStyle, s.GetColumnStyle(0));
            Assert.AreEqual(defaultStyle, s.GetColumnStyle(2));
            Assert.AreEqual(blueStyle, s.GetColumnStyle(3));

            wb.Close();
        }
        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();
            }
        }
Exemple #31
0
        public void Bug56468()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
            XSSFRow row = sheet.CreateRow(0) as XSSFRow;
            XSSFCell cell = row.CreateCell(0) as XSSFCell;
            cell.SetCellValue("Hi");
            sheet.RepeatingRows = (new CellRangeAddress(0, 0, 0, 0));

            MemoryStream bos = new MemoryStream(8096);
            wb.Write(bos);
            byte[] firstSave = bos.ToArray();
            MemoryStream bos2 = new MemoryStream(8096);
            wb.Write(bos2);
            byte[] secondSave = bos2.ToArray();

            Assert.That(firstSave, new EqualConstraint(secondSave));

            wb.Close();
        }
        public void TestBug55843f()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
                XSSFRow row = sheet.CreateRow(0) as XSSFRow;
                XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
                XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
                XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
                cellB1.SetCellValue(10);
                XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;

                cellA2.SetCellFormula("IF(B1=0,\"\",IF(B1=10,3,4))");
                CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
                System.Console.WriteLine(Evaluate);
                Assert.AreEqual("3", Evaluate.FormatAsString());
            }
            finally
            {
                wb.Close();
            }
        }
Exemple #33
0
        public void Bug57430()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                wb.CreateSheet("Sheet1");

                XSSFName name1 = wb.CreateName() as XSSFName;
                name1.NameName = (/*setter*/"FMLA");
                name1.RefersToFormula = (/*setter*/"Sheet1!$B$3");
            }
            finally
            {
                wb.Close();
            }
        }
        public void TestXSSFTextParagraph()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            try
            {
                XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;

                XSSFRichTextString rt = new XSSFRichTextString("Test String");

                XSSFFont font = wb.CreateFont() as XSSFFont;
                Color color = Color.FromArgb(0, 255, 255);
                font.SetColor(new XSSFColor(color));
                font.FontName = (/*setter*/"Arial");
                rt.ApplyFont(font);

                shape.SetText(/*setter*/rt);

                Assert.IsNotNull(shape.GetCTShape());
                Assert.IsNotNull(shape.GetEnumerator());
                Assert.IsNotNull(XSSFSimpleShape.GetPrototype());

                foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber)))
                {
                    shape.TextParagraphs[(0)].SetBullet(nr);
                    Assert.IsNotNull(shape.Text);
                }

                shape.TextParagraphs[(0)].IsBullet = (false);
                Assert.IsNotNull(shape.Text);

                shape.SetText("testtext");
                Assert.AreEqual("testtext", shape.Text);

                shape.SetText(new XSSFRichTextString());
                //Assert.AreEqual("null", shape.Text);
                Assert.AreEqual(String.Empty, shape.Text);

                shape.AddNewTextParagraph();
                shape.AddNewTextParagraph("test-other-text");
                shape.AddNewTextParagraph(new XSSFRichTextString("rtstring"));
                shape.AddNewTextParagraph(new XSSFRichTextString());
                //Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text);
                Assert.AreEqual("test-other-text\nrtstring\n", shape.Text);

                Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
                shape.TextHorizontalOverflow = (/*setter*/TextHorizontalOverflow.CLIP);
                Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow);
                shape.TextHorizontalOverflow = (/*setter*/TextHorizontalOverflow.OVERFLOW);
                Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
                shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
                Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
                shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
                Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);

                Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
                shape.TextVerticalOverflow = (/*setter*/TextVerticalOverflow.CLIP);
                Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow);
                shape.TextVerticalOverflow = (/*setter*/TextVerticalOverflow.OVERFLOW);
                Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
                shape.TextVerticalOverflow = TextVerticalOverflow.None;
                Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
                shape.TextVerticalOverflow = TextVerticalOverflow.None;
                Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);

                Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
                shape.VerticalAlignment = VerticalAlignment.Bottom;
                Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment);
                shape.VerticalAlignment = VerticalAlignment.Top;
                Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
                shape.VerticalAlignment = VerticalAlignment.None;
                Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
                shape.VerticalAlignment = VerticalAlignment.None;
                Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);

                Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
                shape.TextDirection = (/*setter*/TextDirection.STACKED);
                Assert.AreEqual(TextDirection.STACKED, shape.TextDirection);
                shape.TextDirection = (/*setter*/TextDirection.HORIZONTAL);
                Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
                shape.TextDirection = (/*setter*/TextDirection.None);
                Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
                shape.TextDirection = (/*setter*/TextDirection.None);
                Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);

                Assert.AreEqual(3.6, shape.BottomInset, 0.01);
                shape.BottomInset = (/*setter*/12.32);
                Assert.AreEqual(12.32, shape.BottomInset, 0.01);
                shape.BottomInset = (/*setter*/-1);
                Assert.AreEqual(3.6, shape.BottomInset, 0.01);
                shape.BottomInset = (/*setter*/-1);
                Assert.AreEqual(3.6, shape.BottomInset, 0.01);

                Assert.AreEqual(3.6, shape.LeftInset, 0.01);
                shape.LeftInset = (/*setter*/12.31);
                Assert.AreEqual(12.31, shape.LeftInset, 0.01);
                shape.LeftInset = (/*setter*/-1);
                Assert.AreEqual(3.6, shape.LeftInset, 0.01);
                shape.LeftInset = (/*setter*/-1);
                Assert.AreEqual(3.6, shape.LeftInset, 0.01);

                Assert.AreEqual(3.6, shape.RightInset, 0.01);
                shape.RightInset = (/*setter*/13.31);
                Assert.AreEqual(13.31, shape.RightInset, 0.01);
                shape.RightInset = (/*setter*/-1);
                Assert.AreEqual(3.6, shape.RightInset, 0.01);
                shape.RightInset = (/*setter*/-1);
                Assert.AreEqual(3.6, shape.RightInset, 0.01);

                Assert.AreEqual(3.6, shape.TopInset, 0.01);
                shape.TopInset = (/*setter*/23.31);
                Assert.AreEqual(23.31, shape.TopInset, 0.01);
                shape.TopInset = (/*setter*/-1);
                Assert.AreEqual(3.6, shape.TopInset, 0.01);
                shape.TopInset = (/*setter*/-1);
                Assert.AreEqual(3.6, shape.TopInset, 0.01);

                Assert.IsTrue(shape.WordWrap);
                shape.WordWrap = (/*setter*/false);
                Assert.IsFalse(shape.WordWrap);
                shape.WordWrap = (/*setter*/true);
                Assert.IsTrue(shape.WordWrap);

                Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
                shape.TextAutofit = (/*setter*/TextAutofit.NORMAL);
                Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
                shape.TextAutofit = (/*setter*/TextAutofit.SHAPE);
                Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit);
                shape.TextAutofit = (/*setter*/TextAutofit.NONE);
                Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit);

                Assert.AreEqual(5, shape.ShapeType);
                shape.ShapeType = (/*setter*/23);
                Assert.AreEqual(23, shape.ShapeType);

                // TODO: should this be supported?
                //            shape.ShapeType=(/*setter*/-1);
                //            Assert.AreEqual(-1, shape.ShapeType);
                //            shape.ShapeType=(/*setter*/-1);
                //            Assert.AreEqual(-1, shape.ShapeType);

                Assert.IsNotNull(shape.GetShapeProperties());
            }
            finally
            {
                wb.Close();
            }
        }
        public void XSSFTextParagraph_()
        {
            XSSFWorkbook wb = new XSSFWorkbook();

            try
            {
                XSSFSheet   sheet   = wb.CreateSheet() as XSSFSheet;
                XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;

                XSSFTextBox        shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
                XSSFRichTextString rt    = new XSSFRichTextString("Test String");

                XSSFFont font  = wb.CreateFont() as XSSFFont;
                Color    color = Color.FromArgb(0, 255, 255);
                font.SetColor(new XSSFColor(color));
                font.FontName = (/*setter*/ "Arial");
                rt.ApplyFont(font);

                shape.SetText(rt);

                List <XSSFTextParagraph> paras = shape.TextParagraphs;
                Assert.AreEqual(1, paras.Count);

                XSSFTextParagraph text = paras[(0)];
                Assert.AreEqual("Test String", text.Text);

                Assert.IsFalse(text.IsBullet);
                Assert.IsNotNull(text.GetXmlObject());
                Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
                Assert.IsNotNull(text.GetEnumerator());
                Assert.IsNotNull(text.AddLineBreak());

                Assert.IsNotNull(text.TextRuns);
                Assert.AreEqual(2, text.TextRuns.Count);
                text.AddNewTextRun();
                Assert.AreEqual(3, text.TextRuns.Count);

                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
                text.TextAlign = TextAlign.None;
                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
                text.TextAlign = (/*setter*/ TextAlign.CENTER);
                Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
                text.TextAlign = (/*setter*/ TextAlign.RIGHT);
                Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
                text.TextAlign = TextAlign.None;
                Assert.AreEqual(TextAlign.LEFT, text.TextAlign);

                text.TextFontAlign = (/*setter*/ TextFontAlign.BASELINE);
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
                text.TextFontAlign = (/*setter*/ TextFontAlign.BOTTOM);
                Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
                text.TextFontAlign = TextFontAlign.None;
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
                text.TextFontAlign = TextFontAlign.None;
                Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);

                Assert.IsNull(text.BulletFont);
                text.BulletFont = (/*setter*/ "Arial");
                Assert.AreEqual("Arial", text.BulletFont);

                Assert.IsNull(text.BulletCharacter);
                text.BulletCharacter = (/*setter*/ ".");
                Assert.AreEqual(".", text.BulletCharacter);

                //Assert.IsNull(text.BulletFontColor);
                Assert.AreEqual(Color.Empty, text.BulletFontColor);
                text.BulletFontColor = (/*setter*/ color);
                Assert.AreEqual(color, text.BulletFontColor);

                Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/ 1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/ 1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/ -9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/ -9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/ 1.0);
                Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
                text.BulletFontSize = (/*setter*/ -9.0);
                Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);

                Assert.AreEqual(0.0, text.Indent, 0.01);
                text.Indent = (/*setter*/ 2.0);
                Assert.AreEqual(2.0, text.Indent, 0.01);
                text.Indent = (/*setter*/ -1.0);
                Assert.AreEqual(0.0, text.Indent, 0.01);
                text.Indent = (/*setter*/ -1.0);
                Assert.AreEqual(0.0, text.Indent, 0.01);

                Assert.AreEqual(0.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/ 3.0);
                Assert.AreEqual(3.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/ -1.0);
                Assert.AreEqual(0.0, text.LeftMargin, 0.01);
                text.LeftMargin = (/*setter*/ -1.0);
                Assert.AreEqual(0.0, text.LeftMargin, 0.01);

                Assert.AreEqual(0.0, text.RightMargin, 0.01);
                text.RightMargin = (/*setter*/ 4.5);
                Assert.AreEqual(4.5, text.RightMargin, 0.01);
                text.RightMargin = (/*setter*/ -1.0);
                Assert.AreEqual(0.0, text.RightMargin, 0.01);
                text.RightMargin = (/*setter*/ -1.0);
                Assert.AreEqual(0.0, text.RightMargin, 0.01);

                Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);

                Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
                text.AddTabStop(3.14);
                Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);

                Assert.AreEqual(100.0, text.LineSpacing, 0.01);
                text.LineSpacing = (/*setter*/ 3.15);
                Assert.AreEqual(3.15, text.LineSpacing, 0.01);
                text.LineSpacing = (/*setter*/ -2.13);
                Assert.AreEqual(-2.13, text.LineSpacing, 0.01);

                Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
                text.SpaceBefore = (/*setter*/ 3.17);
                Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
                text.SpaceBefore = (/*setter*/ -4.7);
                Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);

                Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
                text.SpaceAfter = (/*setter*/ 6.17);
                Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
                text.SpaceAfter = (/*setter*/ -8.17);
                Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);

                Assert.AreEqual(0, text.Level);
                text.Level = (/*setter*/ 1);
                Assert.AreEqual(1, text.Level);
                text.Level = (/*setter*/ 4);
                Assert.AreEqual(4, text.Level);

                Assert.IsTrue(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.IsBullet = (false);
                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.IsBullet = (true);
                Assert.IsTrue(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                Assert.AreEqual(0, text.BulletAutoNumberStart);
                Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);

                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
                Assert.IsTrue(text.IsBullet);
                Assert.IsTrue(text.IsBulletAutoNumber);

                //Assert.AreEqual(0, text.BulletAutoNumberStart);
                //This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1;
                Assert.AreEqual(1, text.BulletAutoNumberStart);


                Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
                text.IsBullet = (false);
                Assert.IsFalse(text.IsBullet);
                Assert.IsFalse(text.IsBulletAutoNumber);
                text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
                Assert.IsTrue(text.IsBullet);
                Assert.IsTrue(text.IsBulletAutoNumber);
                Assert.AreEqual(10, text.BulletAutoNumberStart);
                Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);


                Assert.IsNotNull(text.ToString());

                new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
            }
            finally
            {
                wb.Close();
            }
        }