コード例 #1
0
 public XSSFDxfStyleProvider(CT_Dxf dxf, int stripeSize, IIndexedColorMap colorMap)
 {
     this.stripeSize = stripeSize;
     this.colorMap   = colorMap;
     if (dxf == null)
     {
         border = null;
         font   = null;
         number = null;
         fill   = null;
     }
     else
     {
         border = dxf.IsSetBorder() ? new XSSFBorderFormatting(dxf.border) : null;
         font   = dxf.IsSetFont() ? new XSSFFontFormatting(dxf.font) : null;
         if (dxf.IsSetNumFmt())
         {
             CT_NumFmt numFmt = dxf.numFmt;
             number = new ExcelNumberFormat((int)numFmt.numFmtId, numFmt.formatCode);
         }
         else
         {
             number = null;
         }
         fill = dxf.IsSetFill() ? new XSSFPatternFormatting(dxf.fill) : null;
     }
 }
コード例 #2
0
        public void TestShiftRows()
        {
            IWorkbook wb    = _testDataProvider.CreateWorkbook();
            ISheet    sheet = wb.CreateSheet();

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.Between, "SUM(A10:A15)", "1+SUM(B16:B30)");
            IFontFormatting fontFmt = rule1.CreateFontFormatting();

            fontFmt.SetFontStyle(true, false);

            IPatternFormatting patternFmt = rule1.CreatePatternFormatting();

            patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index);

            IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.Between, "SUM(A10:A15)", "1+SUM(B16:B30)");
            IBorderFormatting borderFmt = rule2.CreateBorderFormatting();

            borderFmt.BorderDiagonal = BorderStyle.Medium;


            CellRangeAddress[] regions =
            {
                new CellRangeAddress(2, 4, 0, 0), // A3:A5
            };
            sheetCF.AddConditionalFormatting(regions, rule1);
            sheetCF.AddConditionalFormatting(regions, rule2);

            // This row-shift should destroy the CF region
            sheet.ShiftRows(10, 20, -9);
            Assert.AreEqual(0, sheetCF.NumConditionalFormattings);

            // re-add the CF
            sheetCF.AddConditionalFormatting(regions, rule1);
            sheetCF.AddConditionalFormatting(regions, rule2);

            // This row shift should only affect the formulas
            sheet.ShiftRows(14, 17, 8);
            IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);

            Assert.AreEqual("SUM(A10:A23)", cf1.GetRule(0).Formula1);
            Assert.AreEqual("1+SUM(B24:B30)", cf1.GetRule(0).Formula2);
            IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);

            Assert.AreEqual("SUM(A10:A23)", cf2.GetRule(0).Formula1);
            Assert.AreEqual("1+SUM(B24:B30)", cf2.GetRule(0).Formula2);

            sheet.ShiftRows(0, 8, 21);
            cf1 = sheetCF.GetConditionalFormattingAt(0);
            Assert.AreEqual("SUM(A10:A21)", cf1.GetRule(0).Formula1);
            Assert.AreEqual("1+SUM(#REF!)", cf1.GetRule(0).Formula2);
            cf2 = sheetCF.GetConditionalFormattingAt(1);
            Assert.AreEqual("SUM(A10:A21)", cf2.GetRule(0).Formula1);
            Assert.AreEqual("1+SUM(#REF!)", cf2.GetRule(0).Formula2);
        }
コード例 #3
0
        public void TestCreateBorderFormatting()
        {
            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet    sheet    = workbook.CreateSheet();

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1     = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "7");
            IBorderFormatting          borderFmt = rule1.CreateBorderFormatting();

            Assert.AreEqual(BorderStyle.None, borderFmt.BorderBottom);
            borderFmt.BorderBottom = (/*setter*/ BorderStyle.Dotted);
            Assert.AreEqual(BorderStyle.Dotted, borderFmt.BorderBottom);
            borderFmt.BorderBottom = (/*setter*/ BorderStyle.None);
            Assert.AreEqual(BorderStyle.None, borderFmt.BorderBottom);
            borderFmt.BorderBottom = (/*setter*/ BorderStyle.Thick);
            Assert.AreEqual(BorderStyle.Thick, borderFmt.BorderBottom);

            Assert.AreEqual(BorderStyle.None, borderFmt.BorderTop);
            borderFmt.BorderTop = (/*setter*/ BorderStyle.Dotted);
            Assert.AreEqual(BorderStyle.Dotted, borderFmt.BorderTop);
            borderFmt.BorderTop = (/*setter*/ BorderStyle.None);
            Assert.AreEqual(BorderStyle.None, borderFmt.BorderTop);
            borderFmt.BorderTop = (/*setter*/ BorderStyle.Thick);
            Assert.AreEqual(BorderStyle.Thick, borderFmt.BorderTop);

            Assert.AreEqual(BorderStyle.None, borderFmt.BorderLeft);
            borderFmt.BorderLeft = (/*setter*/ BorderStyle.Dotted);
            Assert.AreEqual(BorderStyle.Dotted, borderFmt.BorderLeft);
            borderFmt.BorderLeft = (/*setter*/ BorderStyle.None);
            Assert.AreEqual(BorderStyle.None, borderFmt.BorderLeft);
            borderFmt.BorderLeft = (/*setter*/ BorderStyle.Thin);
            Assert.AreEqual(BorderStyle.Thin, borderFmt.BorderLeft);

            Assert.AreEqual(BorderStyle.None, borderFmt.BorderRight);
            borderFmt.BorderRight = (/*setter*/ BorderStyle.Dotted);
            Assert.AreEqual(BorderStyle.Dotted, borderFmt.BorderRight);
            borderFmt.BorderRight = (/*setter*/ BorderStyle.None);
            Assert.AreEqual(BorderStyle.None, borderFmt.BorderRight);
            borderFmt.BorderRight = (/*setter*/ BorderStyle.Hair);
            Assert.AreEqual(BorderStyle.Hair, borderFmt.BorderRight);

            IConditionalFormattingRule[] cfRules = { rule1 };

            CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A5") };

            sheetCF.AddConditionalFormatting(regions, cfRules);

            // Verification
            IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);

            Assert.IsNotNull(cf);

            Assert.AreEqual(1, cf.NumberOfRules);

            IBorderFormatting r1fp = cf.GetRule(0).GetBorderFormatting();

            Assert.IsNotNull(r1fp);
            Assert.AreEqual(BorderStyle.Thick, r1fp.BorderBottom);
            Assert.AreEqual(BorderStyle.Thick, r1fp.BorderTop);
            Assert.AreEqual(BorderStyle.Thin, r1fp.BorderLeft);
            Assert.AreEqual(BorderStyle.Hair, r1fp.BorderRight);
        }
コード例 #4
0
        public void TestCreateCF()
        {
            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet    sheet    = workbook.CreateSheet();
            String    formula  = "7";

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1   = sheetCF.CreateConditionalFormattingRule(formula);
            IFontFormatting            fontFmt = rule1.CreateFontFormatting();

            fontFmt.SetFontStyle(true, false);

            IBorderFormatting bordFmt = rule1.CreateBorderFormatting();

            bordFmt.BorderBottom = (/*setter*/ BorderStyle.Thin);
            bordFmt.BorderTop    = (/*setter*/ BorderStyle.Thick);
            bordFmt.BorderLeft   = (/*setter*/ BorderStyle.Dashed);
            bordFmt.BorderRight  = (/*setter*/ BorderStyle.Dotted);

            IPatternFormatting patternFmt = rule1.CreatePatternFormatting();

            patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index);


            IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");

            IConditionalFormattingRule[] cfRules =
            {
                rule1, rule2
            };

            short col = 1;

            CellRangeAddress[] regions =
            {
                new CellRangeAddress(0, 65535, col, col)
            };

            sheetCF.AddConditionalFormatting(regions, cfRules);
            sheetCF.AddConditionalFormatting(regions, cfRules);

            // Verification
            Assert.AreEqual(2, sheetCF.NumConditionalFormattings);
            sheetCF.RemoveConditionalFormatting(1);
            Assert.AreEqual(1, sheetCF.NumConditionalFormattings);
            IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);

            Assert.IsNotNull(cf);

            regions = cf.GetFormattingRanges();
            Assert.IsNotNull(regions);
            Assert.AreEqual(1, regions.Length);
            CellRangeAddress r = regions[0];

            Assert.AreEqual(1, r.FirstColumn);
            Assert.AreEqual(1, r.LastColumn);
            Assert.AreEqual(0, r.FirstRow);
            Assert.AreEqual(65535, r.LastRow);

            Assert.AreEqual(2, cf.NumberOfRules);

            rule1 = cf.GetRule(0);
            Assert.AreEqual("7", rule1.Formula1);
            Assert.IsNull(rule1.Formula2);

            IFontFormatting r1fp = rule1.GetFontFormatting();

            Assert.IsNotNull(r1fp);

            Assert.IsTrue(r1fp.IsItalic);
            Assert.IsFalse(r1fp.IsBold);

            IBorderFormatting r1bf = rule1.GetBorderFormatting();

            Assert.IsNotNull(r1bf);
            Assert.AreEqual(BorderStyle.Thin, r1bf.BorderBottom);
            Assert.AreEqual(BorderStyle.Thick, r1bf.BorderTop);
            Assert.AreEqual(BorderStyle.Dashed, r1bf.BorderLeft);
            Assert.AreEqual(BorderStyle.Dotted, r1bf.BorderRight);

            IPatternFormatting r1pf = rule1.GetPatternFormatting();

            Assert.IsNotNull(r1pf);
            //        Assert.AreEqual(HSSFColor.Yellow.index,r1pf.FillBackgroundColor);

            rule2 = cf.GetRule(1);
            Assert.AreEqual("2", rule2.Formula2);
            Assert.AreEqual("1", rule2.Formula1);
        }
コード例 #5
0
        public void TestCreateBorderFormatting()
        {
            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet    sheet    = workbook.CreateSheet();

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1     = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
            IBorderFormatting          borderFmt = rule1.CreateBorderFormatting();

            Assert.AreEqual(BorderFormatting.BORDER_NONE, borderFmt.BorderBottom);
            borderFmt.BorderBottom = (/*setter*/ BorderFormatting.BORDER_DOTTED);
            Assert.AreEqual(BorderFormatting.BORDER_DOTTED, borderFmt.BorderBottom);
            borderFmt.BorderBottom = (/*setter*/ BorderFormatting.BORDER_NONE);
            Assert.AreEqual(BorderFormatting.BORDER_NONE, borderFmt.BorderBottom);
            borderFmt.BorderBottom = (/*setter*/ BorderFormatting.BORDER_THICK);
            Assert.AreEqual(BorderFormatting.BORDER_THICK, borderFmt.BorderBottom);

            Assert.AreEqual(BorderFormatting.BORDER_NONE, borderFmt.BorderTop);
            borderFmt.BorderTop = (/*setter*/ BorderFormatting.BORDER_DOTTED);
            Assert.AreEqual(BorderFormatting.BORDER_DOTTED, borderFmt.BorderTop);
            borderFmt.BorderTop = (/*setter*/ BorderFormatting.BORDER_NONE);
            Assert.AreEqual(BorderFormatting.BORDER_NONE, borderFmt.BorderTop);
            borderFmt.BorderTop = (/*setter*/ BorderFormatting.BORDER_THICK);
            Assert.AreEqual(BorderFormatting.BORDER_THICK, borderFmt.BorderTop);

            Assert.AreEqual(BorderFormatting.BORDER_NONE, borderFmt.BorderLeft);
            borderFmt.BorderLeft = (/*setter*/ BorderFormatting.BORDER_DOTTED);
            Assert.AreEqual(BorderFormatting.BORDER_DOTTED, borderFmt.BorderLeft);
            borderFmt.BorderLeft = (/*setter*/ BorderFormatting.BORDER_NONE);
            Assert.AreEqual(BorderFormatting.BORDER_NONE, borderFmt.BorderLeft);
            borderFmt.BorderLeft = (/*setter*/ BorderFormatting.BORDER_THIN);
            Assert.AreEqual(BorderFormatting.BORDER_THIN, borderFmt.BorderLeft);

            Assert.AreEqual(BorderFormatting.BORDER_NONE, borderFmt.BorderRight);
            borderFmt.BorderRight = (/*setter*/ BorderFormatting.BORDER_DOTTED);
            Assert.AreEqual(BorderFormatting.BORDER_DOTTED, borderFmt.BorderRight);
            borderFmt.BorderRight = (/*setter*/ BorderFormatting.BORDER_NONE);
            Assert.AreEqual(BorderFormatting.BORDER_NONE, borderFmt.BorderRight);
            borderFmt.BorderRight = (/*setter*/ BorderFormatting.BORDER_HAIR);
            Assert.AreEqual(BorderFormatting.BORDER_HAIR, borderFmt.BorderRight);

            IConditionalFormattingRule[] cfRules = { rule1 };

            CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A5") };

            sheetCF.AddConditionalFormatting(regions, cfRules);

            // Verification
            IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);

            Assert.IsNotNull(cf);

            Assert.AreEqual(1, cf.NumberOfRules);

            IBorderFormatting r1fp = cf.GetRule(0).GetBorderFormatting();

            Assert.IsNotNull(r1fp);
            Assert.AreEqual(BorderFormatting.BORDER_THICK, r1fp.BorderBottom);
            Assert.AreEqual(BorderFormatting.BORDER_THICK, r1fp.BorderTop);
            Assert.AreEqual(BorderFormatting.BORDER_THIN, r1fp.BorderLeft);
            Assert.AreEqual(BorderFormatting.BORDER_HAIR, r1fp.BorderRight);
        }