Ejemplo n.º 1
0
        public void TestClone()
        {
            HSSFWorkbook wb      = new HSSFWorkbook();
            HSSFSheet    sheet   = (HSSFSheet)wb.CreateSheet();
            String       formula = "7";

            HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;

            HSSFConditionalFormattingRule rule1   = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula);
            HSSFFontFormatting            fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();

            fontFmt.SetFontStyle(true, false);

            HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();

            patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);


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

            HSSFConditionalFormattingRule[] cfRules =
            {
                rule1, rule2
            };

            short col = 1;

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

            sheetCF.AddConditionalFormatting(regions, cfRules);

            try
            {
                wb.CloneSheet(0);
            }
            catch (Exception e)
            {
                if (e.Message.IndexOf("needs to define a clone method") > 0)
                {
                    throw new AssertionException("Indentified bug 45682");
                }
                throw e;
            }
            Assert.AreEqual(2, wb.NumberOfSheets);
        }
Ejemplo n.º 2
0
        public void TestShiftRows()
        {
            HSSFWorkbook wb    = new HSSFWorkbook();
            HSSFSheet    sheet = (HSSFSheet)wb.CreateSheet();

            HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;

            HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.Between, "sum(A10:A15)", "1+sum(B16:B30)");
            HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();

            fontFmt.SetFontStyle(true, false);

            HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();

            patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);
            HSSFConditionalFormattingRule[] cfRules = { rule1, };

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

            // 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, cfRules);

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

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

            sheet.ShiftRows(0, 8, 21);
            cf = (HSSFConditionalFormatting)sheetCF.GetConditionalFormattingAt(0);
            Assert.AreEqual("SUM(A10:A21)", cf.GetRule(0).Formula1);
            Assert.AreEqual("1+SUM(#REF!)", cf.GetRule(0).Formula2);
        }
Ejemplo n.º 3
0
        static void Main(string[] args)
        {
            InitializeWorkbook();

            HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");

            HSSFSheetConditionalFormatting hscf = sheet1.SheetConditionalFormatting;


            // Define a Conditional Formatting rule, which triggers formatting
            // when cell's value is bigger than 55 and smaller than 500
            // applies patternFormatting defined below.
            HSSFConditionalFormattingRule rule = hscf.CreateConditionalFormattingRule(
                ComparisonOperator.BETWEEN,
                "55", // 1st formula
                "500" // 2nd formula
                );

            // Create pattern with red background
            HSSFPatternFormatting patternFmt = rule.CreatePatternFormatting();

            patternFmt.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;

            //// Define a region containing first column
            CellRangeAddress[] regions =
            {
                new CellRangeAddress(0, 65535, 0, 1)
            };
            // Apply Conditional Formatting rule defined above to the regions
            hscf.AddConditionalFormatting(regions, rule);

            //fill cell with numeric values
            sheet1.CreateRow(0).CreateCell(0).SetCellValue(50);
            sheet1.CreateRow(0).CreateCell(1).SetCellValue(101);
            sheet1.CreateRow(1).CreateCell(1).SetCellValue(25);
            sheet1.CreateRow(1).CreateCell(0).SetCellValue(150);

            WriteToFile();
        }
Ejemplo n.º 4
0
        public void TestCreateCF()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet    sheet    = (HSSFSheet)workbook.CreateSheet();
            String       formula  = "7";

            HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;

            HSSFConditionalFormattingRule rule1   = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula);
            HSSFFontFormatting            fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();

            fontFmt.SetFontStyle(true, false);

            HSSFBorderFormatting bordFmt = (HSSFBorderFormatting)rule1.CreateBorderFormatting();

            bordFmt.BorderBottom = BorderStyle.Thin;
            bordFmt.BorderTop    = BorderStyle.Thick;
            bordFmt.BorderLeft   = BorderStyle.Dashed;
            bordFmt.BorderRight  = BorderStyle.Dotted;

            HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();

            patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);


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

            HSSFConditionalFormattingRule[] 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);
            HSSFConditionalFormatting cf = (HSSFConditionalFormatting)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 = (HSSFConditionalFormattingRule)cf.GetRule(0);
            Assert.AreEqual("7", rule1.Formula1);
            Assert.IsNull(rule1.Formula2);

            HSSFFontFormatting r1fp = (HSSFFontFormatting)rule1.GetFontFormatting();

            Assert.IsNotNull(r1fp);

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

            HSSFBorderFormatting r1bf = (HSSFBorderFormatting)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);

            HSSFPatternFormatting r1pf = (HSSFPatternFormatting)rule1.GetPatternFormatting();

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

            rule2 = (HSSFConditionalFormattingRule)cf.GetRule(1);
            Assert.AreEqual("2", rule2.Formula2);
            Assert.AreEqual("1", rule2.Formula1);
        }