C# XSSFWorkbook.GetStylesSource方法代码示例

        public void TestCreateSaveLoad()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            StylesTable st = wb.GetStylesSource();

            Assert.IsNotNull(st.GetCTStylesheet());
            Assert.AreEqual(1, st._GetXfsSize());
            Assert.AreEqual(1, st._GetStyleXfsSize());
            Assert.AreEqual(0, st._GetNumberFormatSize());

            st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb)).GetStylesSource();

            Assert.IsNotNull(st.GetCTStylesheet());
            Assert.AreEqual(1, st._GetXfsSize());
            Assert.AreEqual(1, st._GetStyleXfsSize());
            Assert.AreEqual(0, st._GetNumberFormatSize());
        }

        public void TestCloneStyleDiffWB()
        {
            XSSFWorkbook wbOrig = new XSSFWorkbook();
            Assert.AreEqual(1, wbOrig.NumberOfFonts);
            Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);

            XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
            fnt.FontName = ("TestingFont");
            Assert.AreEqual(2, wbOrig.NumberOfFonts);
            Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);

            XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
            fmt.GetFormat("MadeUpOne");
            fmt.GetFormat("MadeUpTwo");

            XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
            orig.Alignment = (HorizontalAlignment.Right);
            orig.SetFont(fnt);
            orig.DataFormat = (fmt.GetFormat("Test##"));

            Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
            Assert.IsTrue(fnt == orig.GetFont());
            Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);

            Assert.AreEqual(2, wbOrig.NumberOfFonts);
            Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);


            // Now a style on another workbook
            XSSFWorkbook wbClone = new XSSFWorkbook();
            Assert.AreEqual(1, wbClone.NumberOfFonts);
            Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
            Assert.AreEqual(1, wbClone.NumCellStyles);

            XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
            XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();

            Assert.AreEqual(1, wbClone.NumberOfFonts);
            Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);

            Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
            Assert.IsFalse("TestingFont" == clone.GetFont().FontName);

            clone.CloneStyleFrom(orig);

            Assert.AreEqual(2, wbClone.NumberOfFonts);
            Assert.AreEqual(2, wbClone.NumCellStyles);
            Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);

            Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
            Assert.AreEqual("TestingFont", clone.GetFont().FontName);
            Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
            Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));

            // Save it and re-check
            XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
            Assert.AreEqual(2, wbReload.NumberOfFonts);
            Assert.AreEqual(2, wbReload.NumCellStyles);
            Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);

            XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
            Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
            Assert.AreEqual("TestingFont", reload.GetFont().FontName);
            Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
            Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
        }

        public void TestGetFillForegroundColor()
        {

            XSSFWorkbook wb = new XSSFWorkbook();
            StylesTable styles = wb.GetStylesSource();
            Assert.AreEqual(1, wb.NumCellStyles);
            Assert.AreEqual(2, styles.GetFills().Count);

            XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
            Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
            Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
            Assert.AreEqual(FillPattern.NoFill, defaultStyle.FillPattern);

            XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();

            customStyle.FillPattern = (FillPattern.SolidForeground);
            Assert.AreEqual(FillPattern.SolidForeground, customStyle.FillPattern);
            Assert.AreEqual(3, styles.GetFills().Count);

            customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
            Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
            Assert.AreEqual(4, styles.GetFills().Count);

            for (int i = 0; i < 3; i++)
            {
                XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();

                style.FillPattern = (FillPattern.SolidForeground);
                Assert.AreEqual(FillPattern.SolidForeground, style.FillPattern);
                Assert.AreEqual(4, styles.GetFills().Count);

                style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
                Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
                Assert.AreEqual(4, styles.GetFills().Count);
            }

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
        }

        public void TestSaveLoadNew()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();

            //check that the default date system is Set to 1900
            CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;
            Assert.IsNotNull(pr);
            Assert.IsTrue(pr.IsSetDate1904());
            Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");

            ISheet sheet1 = workbook.CreateSheet("sheet1");
            ISheet sheet2 = workbook.CreateSheet("sheet2");
            workbook.CreateSheet("sheet3");

            IRichTextString rts = workbook.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, workbook.GetSheetAt(0).FirstRowNum);
            Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
            Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);

            FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
            Stream out1 = File.OpenWrite(file.Name);
            workbook.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);

            // Load back the XSSFWorkbook
            workbook = new XSSFWorkbook(pkg);
            Assert.AreEqual(3, workbook.NumberOfSheets);
            Assert.IsNotNull(workbook.GetSheetAt(0));
            Assert.IsNotNull(workbook.GetSheetAt(1));
            Assert.IsNotNull(workbook.GetSheetAt(2));

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

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

            sheet1 = workbook.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);
        }

        public void TestGetFontAt()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            StylesTable styleSource = workbook.GetStylesSource();
            short i = 0;
            //get default font
            IFont fontAt = workbook.GetFontAt(i);
            Assert.IsNotNull(fontAt);

            //get customized font
            XSSFFont customFont = new XSSFFont();
            customFont.IsItalic = (true);
            int x = styleSource.PutFont(customFont);
            fontAt = workbook.GetFontAt((short)x);
            Assert.IsNotNull(fontAt);
        }

        public void TestGetCellStyleAt()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            short i = 0;
            //get default style
            ICellStyle cellStyleAt = workbook.GetCellStyleAt(i);
            Assert.IsNotNull(cellStyleAt);

            //get custom style
            StylesTable styleSource = workbook.GetStylesSource();
            XSSFCellStyle customStyle = new XSSFCellStyle(styleSource);
            XSSFFont font = new XSSFFont();
            font.FontName = ("Verdana");
            customStyle.SetFont(font);
            int x = styleSource.PutStyle(customStyle);
            cellStyleAt = workbook.GetCellStyleAt((short)x);
            Assert.IsNotNull(cellStyleAt);
        }

        public void TestSetDefaultColumnStyle()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
            CT_Worksheet ctWorksheet = sheet.GetCTWorksheet();
            StylesTable stylesTable = workbook.GetStylesSource();
            XSSFFont font = new XSSFFont();
            font.FontName = ("Cambria");
            stylesTable.PutFont(font);
            CT_Xf cellStyleXf = new CT_Xf();
            cellStyleXf.fontId = (1);
            cellStyleXf.fillId= 0;
            cellStyleXf.borderId= 0;
            cellStyleXf.numFmtId= 0;
            stylesTable.PutCellStyleXf(cellStyleXf);
            CT_Xf cellXf = new CT_Xf();
            cellXf.xfId = (1);
            stylesTable.PutCellXf(cellXf);
            XSSFCellStyle cellStyle = new XSSFCellStyle(1, 1, stylesTable, null);
            Assert.AreEqual(1, cellStyle.FontIndex);

            sheet.SetDefaultColumnStyle(3, cellStyle);
            Assert.AreEqual(1u, ctWorksheet.GetColsArray(0).GetColArray(0).style);
        }

        public void TestGetFillForegroundColor()
        {

            XSSFWorkbook wb = new XSSFWorkbook();
            StylesTable styles = wb.GetStylesSource();
            Assert.AreEqual(1, wb.NumCellStyles);
            Assert.AreEqual(2, styles.GetFills().Count);

            XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
            Assert.AreEqual(IndexedColors.AUTOMATIC.Index, defaultStyle.FillForegroundColor);
            Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
            Assert.AreEqual(FillPatternType.NO_FILL, defaultStyle.FillPattern);

            XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();

            customStyle.FillPattern = (FillPatternType.SOLID_FOREGROUND);
            Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, customStyle.FillPattern);
            Assert.AreEqual(3, styles.GetFills().Count);

            customStyle.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
            Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, customStyle.FillForegroundColor);
            Assert.AreEqual(4, styles.GetFills().Count);

            for (int i = 0; i < 3; i++)
            {
                XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();

                style.FillPattern = (FillPatternType.SOLID_FOREGROUND);
                Assert.AreEqual(FillPatternType.SOLID_FOREGROUND, style.FillPattern);
                Assert.AreEqual(4, styles.GetFills().Count);

                style.FillForegroundColor = (IndexedColors.BRIGHT_GREEN.Index);
                Assert.AreEqual(IndexedColors.BRIGHT_GREEN.Index, style.FillForegroundColor);
                Assert.AreEqual(4, styles.GetFills().Count);
            }
        }

        public void TestPopulateNew()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            StylesTable st = wb.GetStylesSource();

            Assert.IsNotNull(st.GetCTStylesheet());
            Assert.AreEqual(1, st._GetXfsSize());
            Assert.AreEqual(1, st._GetStyleXfsSize());
            Assert.AreEqual(0, st._GetNumberFormatSize());

            int nf1 = st.PutNumberFormat("yyyy-mm-dd");
            int nf2 = st.PutNumberFormat("yyyy-mm-DD");
            Assert.AreEqual(nf1, st.PutNumberFormat("yyyy-mm-dd"));

            st.PutStyle(new XSSFCellStyle(st));

            // Save and re-load
            st = ((XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb)).GetStylesSource();

            Assert.IsNotNull(st.GetCTStylesheet());
            Assert.AreEqual(2, st._GetXfsSize());
            Assert.AreEqual(1, st._GetStyleXfsSize());
            Assert.AreEqual(2, st._GetNumberFormatSize());

            Assert.AreEqual("yyyy-mm-dd", st.GetNumberFormatAt(nf1));
            Assert.AreEqual(nf1, st.PutNumberFormat("yyyy-mm-dd"));
            Assert.AreEqual(nf2, st.PutNumberFormat("yyyy-mm-DD"));
        }

        /**
         * Repeatedly writing the same file which has styles
         * TODO Currently failing
         */
        public void DISABLEDtest49940()
        {
            XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("styles.xlsx");
            Assert.AreEqual(3, wb.NumberOfSheets);
            Assert.AreEqual(10, wb.GetStylesSource().GetNumCellStyles());

            MemoryStream b1 = new MemoryStream();
            MemoryStream b2 = new MemoryStream();
            MemoryStream b3 = new MemoryStream();
            wb.Write(b1);
            wb.Write(b2);
            wb.Write(b3);

            foreach (byte[] data in new byte[][] {
                         b1.ToArray(), b2.ToArray(), b3.ToArray()
                   })
            {
                MemoryStream bais = new MemoryStream(data);
                wb = new XSSFWorkbook(bais);
                Assert.AreEqual(3, wb.NumberOfSheets);
                Assert.AreEqual(10, wb.GetStylesSource().GetNumCellStyles());
            }
        }