Esempio n. 1
0
        protected List <EvaluationConditionalFormatRule> GetRules(ISheet sheet)
        {
            String sheetName = sheet.SheetName;
            List <EvaluationConditionalFormatRule> rules = formats[sheetName];

            if (rules == null)
            {
                if (formats.ContainsKey(sheetName))
                {
                    return(new List <EvaluationConditionalFormatRule>());
                }
                ISheetConditionalFormatting scf = sheet.SheetConditionalFormatting;
                int count = scf.NumConditionalFormattings;
                rules = new List <EvaluationConditionalFormatRule>(count);
                formats.Add(sheetName, rules);
                for (int i = 0; i < count; i++)
                {
                    IConditionalFormatting f = scf.GetConditionalFormattingAt(i);
                    //optimization, as this may be expensive for lots of ranges
                    CellRangeAddress[] regions = f.GetFormattingRanges();
                    for (int r = 0; r < f.NumberOfRules; r++)
                    {
                        IConditionalFormattingRule rule = f.GetRule(r);
                        rules.Add(new EvaluationConditionalFormatRule(workbookEvaluator, sheet, f, i, rule, r, regions));
                    }
                }
                // need them in formatting and priority order so logic works right
                rules.Sort();
            }
            return(rules);
        }
        public int AddConditionalFormatting(IConditionalFormatting cf)
        {
            XSSFConditionalFormatting conditionalFormatting = (XSSFConditionalFormatting)cf;
            CT_Worksheet ctWorksheet = this._sheet.GetCTWorksheet();

            ctWorksheet.AddNewConditionalFormatting().Set(conditionalFormatting.GetCTConditionalFormatting());
            return(ctWorksheet.SizeOfConditionalFormattingArray() - 1);
        }
        /**
         * Adds a copy of HSSFConditionalFormatting object to the sheet
         * <p>This method could be used to copy HSSFConditionalFormatting object
         * from one sheet to another. For example:
         * <pre>
         * HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
         * newSheet.AddConditionalFormatting(cf);
         * </pre>
         *
         * @param cf HSSFConditionalFormatting object
         * @return index of the new Conditional Formatting object
         */
        public int AddConditionalFormatting(IConditionalFormatting cf)
        {
            XSSFConditionalFormatting xcf = (XSSFConditionalFormatting)cf;
            CT_Worksheet sh = _sheet.GetCTWorksheet();

            sh.AddNewConditionalFormatting().Set(xcf.GetCTConditionalFormatting());//this is already copied in Set -> .Copy()); ommitted
            return(sh.SizeOfConditionalFormattingArray() - 1);
        }
Esempio n. 4
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);
        }
Esempio n. 5
0
        public void TestCreatePatternFormatting()
        {
            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet    sheet    = workbook.CreateSheet();

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1      = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "7");
            IPatternFormatting         patternFmt = rule1.CreatePatternFormatting();

            Assert.AreEqual(0, patternFmt.FillBackgroundColor);
            patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Red.Index);
            Assert.AreEqual(HSSFColor.Red.Index, patternFmt.FillBackgroundColor);

            Assert.AreEqual(0, patternFmt.FillForegroundColor);
            patternFmt.FillForegroundColor = (/*setter*/ HSSFColor.Blue.Index);
            Assert.AreEqual(HSSFColor.Blue.Index, patternFmt.FillForegroundColor);

            Assert.AreEqual((short)FillPattern.NoFill, patternFmt.FillPattern);
            patternFmt.FillPattern = (short)FillPattern.SolidForeground;
            Assert.AreEqual((short)FillPattern.SolidForeground, patternFmt.FillPattern);
            patternFmt.FillPattern = (short)FillPattern.NoFill;
            Assert.AreEqual((short)FillPattern.NoFill, patternFmt.FillPattern);
            if (this._testDataProvider.GetSpreadsheetVersion() == SpreadsheetVersion.EXCEL97)
            {
                patternFmt.FillPattern = (short)FillPattern.Bricks;
                Assert.AreEqual((short)FillPattern.Bricks, patternFmt.FillPattern);
            }

            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);

            IPatternFormatting r1fp = cf.GetRule(0).GetPatternFormatting();

            Assert.IsNotNull(r1fp);

            Assert.AreEqual(HSSFColor.Red.Index, r1fp.FillBackgroundColor);
            Assert.AreEqual(HSSFColor.Blue.Index, r1fp.FillForegroundColor);
            if (this._testDataProvider.GetSpreadsheetVersion() == SpreadsheetVersion.EXCEL97)
            {
                Assert.AreEqual((short)FillPattern.Bricks, r1fp.FillPattern);
            }
        }
 /// <summary>
 /// 单元格是否存在条件格式
 /// </summary>
 /// <param name="cell">单元格</param>
 /// <param name="cf">条件格式</param>
 /// <returns></returns>
 private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
 {
     var cfRangeAddrs = cf.GetFormattingRanges();
     foreach (var cfRangeAddr in cfRangeAddrs)
     {
         if (cell.RowIndex >= cfRangeAddr.FirstRow && cell.RowIndex <= cfRangeAddr.LastRow
             && cell.ColumnIndex >= cfRangeAddr.FirstColumn && cell.ColumnIndex <= cfRangeAddr.LastColumn)
         {
             return true;
         }
     }
     return false;
 }
 /// <summary>
 ///     单元格是否存在条件格式
 /// </summary>
 /// <param name="cell">单元格</param>
 /// <param name="cf">条件格式</param>
 /// <returns></returns>
 private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
 {
     CellRangeAddress[] cfRangeAddrs = cf.GetFormattingRanges();
     foreach (CellRangeAddress cfRangeAddr in cfRangeAddrs)
     {
         if (cell.RowIndex >= cfRangeAddr.FirstRow && cell.RowIndex <= cfRangeAddr.LastRow &&
             cell.ColumnIndex >= cfRangeAddr.FirstColumn && cell.ColumnIndex <= cfRangeAddr.LastColumn)
         {
             return(true);
         }
     }
     return(false);
 }
        public void TestCreatePatternFormatting()
        {
            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet    sheet    = workbook.CreateSheet();

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1      = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
            IPatternFormatting         patternFmt = rule1.CreatePatternFormatting();

            Assert.AreEqual(0, patternFmt.FillBackgroundColor);
            patternFmt.FillBackgroundColor = (/*setter*/ IndexedColors.RED.Index);
            Assert.AreEqual(IndexedColors.RED.Index, patternFmt.FillBackgroundColor);

            Assert.AreEqual(0, patternFmt.FillForegroundColor);
            patternFmt.FillForegroundColor = (/*setter*/ IndexedColors.BLUE.Index);
            Assert.AreEqual(IndexedColors.BLUE.Index, patternFmt.FillForegroundColor);

            Assert.AreEqual(PatternFormatting.NO_Fill, patternFmt.FillPattern);
            patternFmt.FillPattern = (/*setter*/ PatternFormatting.SOLID_FOREGROUND);
            Assert.AreEqual(PatternFormatting.SOLID_FOREGROUND, patternFmt.FillPattern);
            patternFmt.FillPattern = (/*setter*/ PatternFormatting.NO_Fill);
            Assert.AreEqual(PatternFormatting.NO_Fill, patternFmt.FillPattern);
            patternFmt.FillPattern = (/*setter*/ PatternFormatting.BRICKS);
            Assert.AreEqual(PatternFormatting.BRICKS, patternFmt.FillPattern);

            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);

            IPatternFormatting r1fp = cf.GetRule(0).GetPatternFormatting();

            Assert.IsNotNull(r1fp);

            Assert.AreEqual(IndexedColors.RED.Index, r1fp.FillBackgroundColor);
            Assert.AreEqual(IndexedColors.BLUE.Index, r1fp.FillForegroundColor);
            Assert.AreEqual(PatternFormatting.BRICKS, r1fp.FillPattern);
        }
Esempio n. 9
0
        /// <summary>
        /// 获取条件格式规则
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns></returns>
        public static IConditionalFormattingRule[] GetConditionalFormattingRules(this ICell cell)
        {
            var cfrs = new List <IConditionalFormattingRule>();
            ISheetConditionalFormatting scf = cell.Sheet.SheetConditionalFormatting;

            for (var i = 0; i < scf.NumConditionalFormattings; i++)
            {
                IConditionalFormatting cf = scf.GetConditionalFormattingAt(i);
                if (cell.ExistConditionalFormatting(cf))
                {
                    for (var j = 0; j < cf.NumberOfRules; j++)
                    {
                        cfrs.Add(cf.GetRule(j));
                    }
                }
            }
            return(cfrs.ToArray());
        }
Esempio n. 10
0
        public void TestCopy()
        {
            IWorkbook wb     = _testDataProvider.CreateWorkbook();
            ISheet    sheet1 = wb.CreateSheet();
            ISheet    sheet2 = wb.CreateSheet();
            ISheetConditionalFormatting sheet1CF = sheet1.SheetConditionalFormatting;
            ISheetConditionalFormatting sheet2CF = sheet2.SheetConditionalFormatting;

            Assert.AreEqual(0, sheet1CF.NumConditionalFormattings);
            Assert.AreEqual(0, sheet2CF.NumConditionalFormattings);

            IConditionalFormattingRule rule1 = sheet1CF.CreateConditionalFormattingRule(
                ComparisonOperator.Equal, "SUM(A1:A5)+10");

            IConditionalFormattingRule rule2 = sheet1CF.CreateConditionalFormattingRule(
                ComparisonOperator.NotEqual, "15");

            // adjacent Address are merged
            int formatIndex = sheet1CF.AddConditionalFormatting(
                new CellRangeAddress[] {
                CellRangeAddress.ValueOf("A1:A5"),
                CellRangeAddress.ValueOf("C1:C5")
            }, rule1, rule2);

            Assert.AreEqual(0, formatIndex);
            Assert.AreEqual(1, sheet1CF.NumConditionalFormattings);

            Assert.AreEqual(0, sheet2CF.NumConditionalFormattings);
            sheet2CF.AddConditionalFormatting(sheet1CF.GetConditionalFormattingAt(formatIndex));
            Assert.AreEqual(1, sheet2CF.NumConditionalFormattings);

            IConditionalFormatting sheet2cf = sheet2CF.GetConditionalFormattingAt(0);

            Assert.AreEqual(2, sheet2cf.NumberOfRules);
            Assert.AreEqual("SUM(A1:A5)+10", sheet2cf.GetRule(0).Formula1);
            Assert.AreEqual(ComparisonOperator.Equal, sheet2cf.GetRule(0).ComparisonOperation);
            Assert.AreEqual(ConditionType.CellValueIs, sheet2cf.GetRule(0).ConditionType);
            Assert.AreEqual("15", sheet2cf.GetRule(1).Formula1);
            Assert.AreEqual(ComparisonOperator.NotEqual, sheet2cf.GetRule(1).ComparisonOperation);
            Assert.AreEqual(ConditionType.CellValueIs, sheet2cf.GetRule(1).ConditionType);
        }
        public EvaluationConditionalFormatRule(WorkbookEvaluator workbookEvaluator, ISheet sheet, IConditionalFormatting formatting, int formattingIndex, IConditionalFormattingRule rule, int ruleIndex, CellRangeAddress[] regions)
        {
            this.workbookEvaluator = workbookEvaluator;
            this.sheet             = sheet;
            this.formatting        = formatting;
            this.rule            = rule;
            this.formattingIndex = formattingIndex;
            this.ruleIndex       = ruleIndex;

            this.priority = rule.Priority;

            this.regions = regions;
            formula1     = rule.Formula1;
            formula2     = rule.Formula2;

            text      = rule.Text;
            lowerText = text == null ? null : text.ToLowerInvariant();

            numberFormat = rule.NumberFormat;

            @operator = (OperatorEnum)rule.ComparisonOperation;
            type      = rule.ConditionType;
        }
Esempio n. 12
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);
        }
        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);
        }
Esempio n. 14
0
        public void TestCreateFontFormatting()
        {
            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet    sheet    = workbook.CreateSheet();

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1   = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "7");
            IFontFormatting            fontFmt = rule1.CreateFontFormatting();

            Assert.IsFalse(fontFmt.IsItalic);
            Assert.IsFalse(fontFmt.IsBold);
            fontFmt.SetFontStyle(true, true);
            Assert.IsTrue(fontFmt.IsItalic);
            Assert.IsTrue(fontFmt.IsBold);

            Assert.AreEqual(-1, fontFmt.FontHeight); // not modified
            fontFmt.FontHeight = (/*setter*/ 200);
            Assert.AreEqual(200, fontFmt.FontHeight);
            fontFmt.FontHeight = (/*setter*/ 100);
            Assert.AreEqual(100, fontFmt.FontHeight);

            Assert.AreEqual(FontSuperScript.None, fontFmt.EscapementType);
            fontFmt.EscapementType = (/*setter*/ FontSuperScript.Sub);
            Assert.AreEqual(FontSuperScript.Sub, fontFmt.EscapementType);
            fontFmt.EscapementType = (/*setter*/ FontSuperScript.None);
            Assert.AreEqual(FontSuperScript.None, fontFmt.EscapementType);
            fontFmt.EscapementType = (/*setter*/ FontSuperScript.Super);
            Assert.AreEqual(FontSuperScript.Super, fontFmt.EscapementType);

            Assert.AreEqual(FontUnderlineType.None, fontFmt.UnderlineType);
            fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.Single);
            Assert.AreEqual(FontUnderlineType.Single, fontFmt.UnderlineType);
            fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.None);
            Assert.AreEqual(FontUnderlineType.None, fontFmt.UnderlineType);
            fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.Double);
            Assert.AreEqual(FontUnderlineType.Double, fontFmt.UnderlineType);

            Assert.AreEqual(-1, fontFmt.FontColorIndex);
            fontFmt.FontColorIndex = (/*setter*/ HSSFColor.Red.Index);
            Assert.AreEqual(HSSFColor.Red.Index, fontFmt.FontColorIndex);
            fontFmt.FontColorIndex = (/*setter*/ HSSFColor.Automatic.Index);
            Assert.AreEqual(HSSFColor.Automatic.Index, fontFmt.FontColorIndex);
            fontFmt.FontColorIndex = (/*setter*/ HSSFColor.Blue.Index);
            Assert.AreEqual(HSSFColor.Blue.Index, fontFmt.FontColorIndex);

            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);

            IFontFormatting r1fp = cf.GetRule(0).GetFontFormatting();

            Assert.IsNotNull(r1fp);

            Assert.IsTrue(r1fp.IsItalic);
            Assert.IsTrue(r1fp.IsBold);
            Assert.AreEqual(FontSuperScript.Super, r1fp.EscapementType);
            Assert.AreEqual(FontUnderlineType.Double, r1fp.UnderlineType);
            Assert.AreEqual(HSSFColor.Blue.Index, r1fp.FontColorIndex);
        }
Esempio n. 15
0
        //

        public void TestRead(string sampleFile)
        {
            IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleFile);
            ISheet    sh = wb.GetSheet("CF");
            ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;

            Assert.AreEqual(3, sheetCF.NumConditionalFormattings);

            IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);

            Assert.AreEqual(2, cf1.NumberOfRules);

            CellRangeAddress[] regions1 = cf1.GetFormattingRanges();
            Assert.AreEqual(1, regions1.Length);
            Assert.AreEqual("A1:A8", regions1[0].FormatAsString());

            // CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
            IConditionalFormattingRule rule1 = cf1.GetRule(0);

            Assert.AreEqual(ConditionType.CellValueIs, rule1.ConditionType);
            Assert.AreEqual(ComparisonOperator.GreaterThan, rule1.ComparisonOperation);
            Assert.AreEqual("3", rule1.Formula1);
            Assert.IsNull(rule1.Formula2);
            // Fills and borders are not Set
            Assert.IsNull(rule1.GetPatternFormatting());
            Assert.IsNull(rule1.GetBorderFormatting());

            IFontFormatting fmt1 = rule1.GetFontFormatting();

            //        Assert.AreEqual(HSSFColor.GREEN.index, fmt1.FontColorIndex);
            Assert.IsTrue(fmt1.IsBold);
            Assert.IsFalse(fmt1.IsItalic);

            IConditionalFormattingRule rule2 = cf1.GetRule(1);

            Assert.AreEqual(ConditionType.CellValueIs, rule2.ConditionType);
            Assert.AreEqual(ComparisonOperator.LessThan, rule2.ComparisonOperation);
            Assert.AreEqual("-3", rule2.Formula1);
            Assert.IsNull(rule2.Formula2);
            Assert.IsNull(rule2.GetPatternFormatting());
            Assert.IsNull(rule2.GetBorderFormatting());

            IFontFormatting fmt2 = rule2.GetFontFormatting();

            //        Assert.AreEqual(HSSFColor.Red.index, fmt2.FontColorIndex);
            Assert.IsTrue(fmt2.IsBold);
            Assert.IsTrue(fmt2.IsItalic);


            IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);

            Assert.AreEqual(1, cf2.NumberOfRules);
            CellRangeAddress[] regions2 = cf2.GetFormattingRanges();
            Assert.AreEqual(1, regions2.Length);
            Assert.AreEqual("B9", regions2[0].FormatAsString());

            IConditionalFormattingRule rule3 = cf2.GetRule(0);

            Assert.AreEqual(ConditionType.Formula, rule3.ConditionType);
            Assert.AreEqual(ComparisonOperator.NoComparison, rule3.ComparisonOperation);
            Assert.AreEqual("$A$8>5", rule3.Formula1);
            Assert.IsNull(rule3.Formula2);

            IFontFormatting fmt3 = rule3.GetFontFormatting();

            //        Assert.AreEqual(HSSFColor.Red.index, fmt3.FontColorIndex);
            Assert.IsTrue(fmt3.IsBold);
            Assert.IsTrue(fmt3.IsItalic);

            IPatternFormatting fmt4 = rule3.GetPatternFormatting();

            //        Assert.AreEqual(HSSFColor.LIGHT_CORNFLOWER_BLUE.index, fmt4.FillBackgroundColor);
            //        Assert.AreEqual(HSSFColor.Automatic.index, fmt4.FillForegroundColor);
            Assert.AreEqual((short)FillPattern.NoFill, fmt4.FillPattern);
            // borders are not Set
            Assert.IsNull(rule3.GetBorderFormatting());

            IConditionalFormatting cf3 = sheetCF.GetConditionalFormattingAt(2);

            CellRangeAddress[] regions3 = cf3.GetFormattingRanges();
            Assert.AreEqual(1, regions3.Length);
            Assert.AreEqual("B1:B7", regions3[0].FormatAsString());
            Assert.AreEqual(2, cf3.NumberOfRules);

            IConditionalFormattingRule rule4 = cf3.GetRule(0);

            Assert.AreEqual(ConditionType.CellValueIs, rule4.ConditionType);
            Assert.AreEqual(ComparisonOperator.LessThanOrEqual, rule4.ComparisonOperation);
            Assert.AreEqual("\"AAA\"", rule4.Formula1);
            Assert.IsNull(rule4.Formula2);

            IConditionalFormattingRule rule5 = cf3.GetRule(1);

            Assert.AreEqual(ConditionType.CellValueIs, rule5.ConditionType);
            Assert.AreEqual(ComparisonOperator.Between, rule5.ComparisonOperation);
            Assert.AreEqual("\"A\"", rule5.Formula1);
            Assert.AreEqual("\"AAA\"", rule5.Formula2);
        }
Esempio n. 16
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);
        }
Esempio n. 17
0
 /// <summary>
 /// 判断单元格是否存在条件格式
 /// </summary>
 /// <param name="cell">单元格</param>
 /// <param name="cf">条件格式</param>
 private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
 {
     return(cf.GetFormattingRanges().Any(cra =>
                                         cell.RowIndex >= cra.FirstRow && cell.RowIndex <= cra.LastRow && cell.ColumnIndex >= cra.FirstColumn &&
                                         cell.ColumnIndex <= cra.LastColumn));
 }
        public void TestCreateFontFormatting()
        {
            IWorkbook workbook = _testDataProvider.CreateWorkbook();
            ISheet    sheet    = workbook.CreateSheet();

            ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;

            IConditionalFormattingRule rule1   = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
            IFontFormatting            fontFmt = rule1.CreateFontFormatting();

            Assert.IsFalse(fontFmt.IsItalic);
            Assert.IsFalse(fontFmt.IsBold);
            fontFmt.SetFontStyle(true, true);
            Assert.IsTrue(fontFmt.IsItalic);
            Assert.IsTrue(fontFmt.IsBold);

            Assert.AreEqual(-1, fontFmt.FontHeight); // not modified
            fontFmt.FontHeight = (/*setter*/ 200);
            Assert.AreEqual(200, fontFmt.FontHeight);
            fontFmt.FontHeight = (/*setter*/ 100);
            Assert.AreEqual(100, fontFmt.FontHeight);

            Assert.AreEqual(FontFormatting.SS_NONE, (short)fontFmt.EscapementType);
            fontFmt.EscapementType = (/*setter*/ FontSuperScript.SUB);
            Assert.AreEqual(FontFormatting.SS_SUB, (short)fontFmt.EscapementType);
            fontFmt.EscapementType = (/*setter*/ FontSuperScript.NONE);
            Assert.AreEqual(FontFormatting.SS_NONE, (short)fontFmt.EscapementType);
            fontFmt.EscapementType = (/*setter*/ FontSuperScript.SUPER);
            Assert.AreEqual(FontFormatting.SS_SUPER, (short)fontFmt.EscapementType);

            Assert.AreEqual(FontFormatting.U_NONE, (byte)fontFmt.UnderlineType);
            fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.SINGLE);
            Assert.AreEqual(FontFormatting.U_SINGLE, (byte)fontFmt.UnderlineType);
            fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.NONE);
            Assert.AreEqual(FontFormatting.U_NONE, (byte)fontFmt.UnderlineType);
            fontFmt.UnderlineType = (/*setter*/ FontUnderlineType.DOUBLE);
            Assert.AreEqual(FontFormatting.U_DOUBLE, (byte)fontFmt.UnderlineType);

            Assert.AreEqual(-1, fontFmt.FontColorIndex);
            fontFmt.FontColorIndex = (/*setter*/ IndexedColors.RED.Index);
            Assert.AreEqual(IndexedColors.RED.Index, fontFmt.FontColorIndex);
            fontFmt.FontColorIndex = (/*setter*/ IndexedColors.AUTOMATIC.Index);
            Assert.AreEqual(IndexedColors.AUTOMATIC.Index, fontFmt.FontColorIndex);
            fontFmt.FontColorIndex = (/*setter*/ IndexedColors.BLUE.Index);
            Assert.AreEqual(IndexedColors.BLUE.Index, fontFmt.FontColorIndex);

            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);

            IFontFormatting r1fp = cf.GetRule(0).GetFontFormatting();

            Assert.IsNotNull(r1fp);

            Assert.IsTrue(r1fp.IsItalic);
            Assert.IsTrue(r1fp.IsBold);
            Assert.AreEqual(FontFormatting.SS_SUPER, (short)r1fp.EscapementType);
            Assert.AreEqual(FontFormatting.U_DOUBLE, (short)r1fp.UnderlineType);
            Assert.AreEqual(IndexedColors.BLUE.Index, r1fp.FontColorIndex);
        }
Esempio n. 19
0
 public int AddConditionalFormatting(IConditionalFormatting cf)
 {
     return(AddConditionalFormatting((HSSFConditionalFormatting)cf));
 }
 /**
  * Adds a copy of HSSFConditionalFormatting object to the sheet
  * <p>This method could be used to copy HSSFConditionalFormatting object
  * from one sheet to another. For example:
  * <pre>
  * HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
  * newSheet.AddConditionalFormatting(cf);
  * </pre>
  *
  * @param cf HSSFConditionalFormatting object
  * @return index of the new Conditional Formatting object
  */
 public int AddConditionalFormatting(IConditionalFormatting cf)
 {
     XSSFConditionalFormatting xcf = (XSSFConditionalFormatting)cf;
     CT_Worksheet sh = _sheet.GetCTWorksheet();
     sh.AddNewConditionalFormatting().Set(xcf.GetCTConditionalFormatting());//this is already copied in Set -> .Copy()); ommitted
     return sh.SizeOfConditionalFormattingArray() - 1;
 }
        /// <summary>
        /// Adds a copy of HSSFConditionalFormatting object to the sheet
        /// This method could be used to copy HSSFConditionalFormatting object
        /// from one sheet to another.
        /// </summary>
        /// <param name="cf">HSSFConditionalFormatting object</param>
        /// <returns>index of the new Conditional Formatting object</returns>
        /// <example>
        /// HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
        /// newSheet.AddConditionalFormatting(cf);
        /// </example>
        public int AddConditionalFormatting(IConditionalFormatting cf)
        {
            CFRecordsAggregate cfraClone = ((HSSFConditionalFormatting)cf).CFRecordsAggregate.CloneCFAggregate();

            return _conditionalFormattingTable.Add(cfraClone);
        }
Esempio n. 22
0
        public void test52122()
        {
            IWorkbook workbook = new HSSFWorkbook();
            ISheet    sheet    = workbook.CreateSheet("Conditional Formatting Test");

            sheet.SetColumnWidth(0, 256 * 10);
            sheet.SetColumnWidth(1, 256 * 10);
            sheet.SetColumnWidth(2, 256 * 10);
            // Create some content.
            // row 0
            IRow  row   = sheet.CreateRow(0);
            ICell cell0 = row.CreateCell(0);

            cell0.SetCellType(CellType.Numeric);
            cell0.SetCellValue(100);
            ICell cell1 = row.CreateCell(1);

            cell1.SetCellType(CellType.Numeric);
            cell1.SetCellValue(120);
            ICell cell2 = row.CreateCell(2);

            cell2.SetCellType(CellType.Numeric);
            cell2.SetCellValue(130);
            // row 1
            row   = sheet.CreateRow(1);
            cell0 = row.CreateCell(0);
            cell0.SetCellType(CellType.Numeric);
            cell0.SetCellValue(200);
            cell1 = row.CreateCell(1);
            cell1.SetCellType(CellType.Numeric);
            cell1.SetCellValue(220);
            cell2 = row.CreateCell(2);
            cell2.SetCellType(CellType.Numeric);
            cell2.SetCellValue(230);
            // row 2
            row   = sheet.CreateRow(2);
            cell0 = row.CreateCell(0);
            cell0.SetCellType(CellType.Numeric);
            cell0.SetCellValue(300);
            cell1 = row.CreateCell(1);
            cell1.SetCellType(CellType.Numeric);
            cell1.SetCellValue(320);
            cell2 = row.CreateCell(2);
            cell2.SetCellType(CellType.Numeric);
            cell2.SetCellValue(330);
            // Create conditional formatting, CELL1 should be yellow if CELL0 is not blank.
            ISheetConditionalFormatting formatting = sheet.SheetConditionalFormatting;
            IConditionalFormattingRule  rule       = formatting.CreateConditionalFormattingRule("$A$1>75");
            IPatternFormatting          pattern    = rule.CreatePatternFormatting();

            pattern.FillBackgroundColor = IndexedColors.Blue.Index;
            pattern.FillPattern         = FillPattern.SolidForeground;
            CellRangeAddress[] range  = { CellRangeAddress.ValueOf("B2:C2") };
            CellRangeAddress[] range2 = { CellRangeAddress.ValueOf("B1:C1") };
            formatting.AddConditionalFormatting(range, rule);
            formatting.AddConditionalFormatting(range2, rule);
            // Write file.

            /*FileOutputStream fos = new FileOutputStream("c:\\temp\\52122_conditional-sheet.xls");
             * try {
             *  workbook.write(fos);
             * } finally {
             *  fos.Close();
             * }*/
            IWorkbook wbBack    = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)workbook);
            ISheet    sheetBack = wbBack.GetSheetAt(0);
            ISheetConditionalFormatting sheetConditionalFormattingBack = sheetBack.SheetConditionalFormatting;

            Assert.IsNotNull(sheetConditionalFormattingBack);
            IConditionalFormatting formattingBack = sheetConditionalFormattingBack.GetConditionalFormattingAt(0);

            Assert.IsNotNull(formattingBack);
            IConditionalFormattingRule ruleBack = formattingBack.GetRule(0);

            Assert.IsNotNull(ruleBack);
            IPatternFormatting patternFormattingBack1 = ruleBack.PatternFormatting;

            Assert.IsNotNull(patternFormattingBack1);
            Assert.AreEqual(IndexedColors.Blue.Index, patternFormattingBack1.FillBackgroundColor);
            Assert.AreEqual(FillPattern.SolidForeground, patternFormattingBack1.FillPattern);
        }
Esempio n. 23
0
        /// <summary>
        /// Adds a copy of HSSFConditionalFormatting object to the sheet
        /// This method could be used to copy HSSFConditionalFormatting object
        /// from one sheet to another.
        /// </summary>
        /// <param name="cf">HSSFConditionalFormatting object</param>
        /// <returns>index of the new Conditional Formatting object</returns>
        /// <example>
        /// HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
        /// newSheet.AddConditionalFormatting(cf);
        /// </example>
        public int AddConditionalFormatting(IConditionalFormatting cf)
        {
            CFRecordsAggregate cfraClone = ((HSSFConditionalFormatting)cf).CFRecordsAggregate.CloneCFAggregate();

            return(_conditionalFormattingTable.Add(cfraClone));
        }
Esempio n. 24
0
        public void TestSetSheetOrderHSSF()
        {
            IWorkbook wb = new HSSFWorkbook();
            ISheet    s1 = wb.CreateSheet("first sheet");
            ISheet    s2 = wb.CreateSheet("other sheet");

            IName name1 = wb.CreateName();

            name1.NameName        = (/*setter*/ "name1");
            name1.RefersToFormula = (/*setter*/ "'first sheet'!D1");

            IName name2 = wb.CreateName();

            name2.NameName        = (/*setter*/ "name2");
            name2.RefersToFormula = (/*setter*/ "'other sheet'!C1");


            IRow  s1r1 = s1.CreateRow(2);
            ICell c1   = s1r1.CreateCell(3);

            c1.SetCellValue(30);
            ICell c2 = s1r1.CreateCell(2);

            c2.CellFormula = (/*setter*/ "SUM('other sheet'!C1,'first sheet'!C1)");

            IRow  s2r1 = s2.CreateRow(0);
            ICell c3   = s2r1.CreateCell(1);

            c3.CellFormula = (/*setter*/ "'first sheet'!D3");
            ICell c4 = s2r1.CreateCell(2);

            c4.CellFormula = (/*setter*/ "'other sheet'!D3");

            // conditional formatting
            ISheetConditionalFormatting sheetCF = s1.SheetConditionalFormatting;

            IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.BETWEEN, "'first sheet'!D1", "'other sheet'!D1");

            IConditionalFormattingRule[] cfRules = { rule1 };

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

            wb.SetSheetOrder("other sheet", 0);

            // names
            Assert.AreEqual("'first sheet'!D1", wb.GetName("name1").RefersToFormula);
            Assert.AreEqual("'other sheet'!C1", wb.GetName("name2").RefersToFormula);

            // cells
            Assert.AreEqual("SUM('other sheet'!C1,'first sheet'!C1)", c2.CellFormula);
            Assert.AreEqual("'first sheet'!D3", c3.CellFormula);
            Assert.AreEqual("'other sheet'!D3", c4.CellFormula);

            // conditional formatting
            IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0);

            Assert.AreEqual("'first sheet'!D1", cf.GetRule(0).Formula1);
            Assert.AreEqual("'other sheet'!D1", cf.GetRule(0).Formula2);
        }
        public void TestRead()
        {
            IWorkbook wb = _testDataProvider.OpenSampleWorkbook("WithConditionalFormatting.xls");
            ISheet    sh = wb.GetSheet("CF");
            ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;

            Assert.AreEqual(3, sheetCF.NumConditionalFormattings);

            IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);

            Assert.AreEqual(2, cf1.NumberOfRules);

            CellRangeAddress[] regions1 = cf1.GetFormattingRanges();
            Assert.AreEqual(1, regions1.Length);
            Assert.AreEqual("A1:A8", regions1[0].FormatAsString());

            // CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
            IConditionalFormattingRule rule1 = cf1.GetRule(0);

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule1.ConditionType);
            Assert.AreEqual(ComparisonOperator.GT, rule1.ComparisonOperation);
            Assert.AreEqual("3", rule1.Formula1);
            Assert.IsNull(rule1.Formula2);
            // Fills and borders are not Set
            Assert.IsNull(rule1.GetPatternFormatting());
            Assert.IsNull(rule1.GetBorderFormatting());

            IFontFormatting fmt1 = rule1.GetFontFormatting();

            //        Assert.AreEqual(IndexedColors.GREEN.index, fmt1.FontColorIndex);
            Assert.IsTrue(fmt1.IsBold);
            Assert.IsFalse(fmt1.IsItalic);

            IConditionalFormattingRule rule2 = cf1.GetRule(1);

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule2.ConditionType);
            Assert.AreEqual(ComparisonOperator.LT, rule2.ComparisonOperation);
            Assert.AreEqual("-3", rule2.Formula1);
            Assert.IsNull(rule2.Formula2);
            Assert.IsNull(rule2.GetPatternFormatting());
            Assert.IsNull(rule2.GetBorderFormatting());

            IFontFormatting fmt2 = rule2.GetFontFormatting();

            //        Assert.AreEqual(IndexedColors.RED.index, fmt2.FontColorIndex);
            Assert.IsTrue(fmt2.IsBold);
            Assert.IsTrue(fmt2.IsItalic);


            IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);

            Assert.AreEqual(1, cf2.NumberOfRules);
            CellRangeAddress[] regions2 = cf2.GetFormattingRanges();
            Assert.AreEqual(1, regions2.Length);
            Assert.AreEqual("B9", regions2[0].FormatAsString());

            IConditionalFormattingRule rule3 = cf2.GetRule(0);

            Assert.AreEqual(ConditionType.FORMULA, rule3.ConditionType);
            Assert.AreEqual(ComparisonOperator.NO_COMPARISON, rule3.ComparisonOperation);
            Assert.AreEqual("$A$8>5", rule3.Formula1);
            Assert.IsNull(rule3.Formula2);

            IFontFormatting fmt3 = rule3.GetFontFormatting();

            //        Assert.AreEqual(IndexedColors.RED.index, fmt3.FontColorIndex);
            Assert.IsTrue(fmt3.IsBold);
            Assert.IsTrue(fmt3.IsItalic);

            IPatternFormatting fmt4 = rule3.GetPatternFormatting();

            //        Assert.AreEqual(IndexedColors.LIGHT_CORNFLOWER_BLUE.index, fmt4.FillBackgroundColor);
            //        Assert.AreEqual(IndexedColors.AUTOMATIC.index, fmt4.FillForegroundColor);
            Assert.AreEqual(PatternFormatting.NO_Fill, fmt4.FillPattern);
            // borders are not Set
            Assert.IsNull(rule3.GetBorderFormatting());

            IConditionalFormatting cf3 = sheetCF.GetConditionalFormattingAt(2);

            CellRangeAddress[] regions3 = cf3.GetFormattingRanges();
            Assert.AreEqual(1, regions3.Length);
            Assert.AreEqual("B1:B7", regions3[0].FormatAsString());
            Assert.AreEqual(2, cf3.NumberOfRules);

            IConditionalFormattingRule rule4 = cf3.GetRule(0);

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule4.ConditionType);
            Assert.AreEqual(ComparisonOperator.LE, rule4.ComparisonOperation);
            Assert.AreEqual("\"AAA\"", rule4.Formula1);
            Assert.IsNull(rule4.Formula2);

            IConditionalFormattingRule rule5 = cf3.GetRule(1);

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule5.ConditionType);
            Assert.AreEqual(ComparisonOperator.BETWEEN, rule5.ComparisonOperation);
            Assert.AreEqual("\"A\"", rule5.Formula1);
            Assert.AreEqual("\"AAA\"", rule5.Formula2);
        }