/// <summary>
 /// 添加条件格式规则
 /// </summary>
 /// <param name="cell">单元格</param>
 /// <param name="cfrs">条件格式规则</param>
 public static void AddConditionalFormattingRules(this ICell cell, IConditionalFormattingRule[] cfrs)
 {
     CellRangeAddress[] regions =
     {
         new CellRangeAddress(cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex)
     };
     cell.Sheet.SheetConditionalFormatting.AddConditionalFormatting(regions, cfrs);
 }
示例#2
0
        public void TestBooleanFormulaConditions()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();
            ISheet    sh = wb.CreateSheet();
            ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;

            IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("SUM(A1:A5)>10");

            Assert.AreEqual(ConditionType.Formula, rule1.ConditionType);
            Assert.AreEqual("SUM(A1:A5)>10", rule1.Formula1);
            int formatIndex1 = sheetCF.AddConditionalFormatting(
                new CellRangeAddress[] {
                CellRangeAddress.ValueOf("B1"),
                CellRangeAddress.ValueOf("C3"),
            }, rule1);

            Assert.AreEqual(0, formatIndex1);
            Assert.AreEqual(1, sheetCF.NumConditionalFormattings);
            CellRangeAddress[] ranges1 = sheetCF.GetConditionalFormattingAt(formatIndex1).GetFormattingRanges();
            Assert.AreEqual(2, ranges1.Length);
            Assert.AreEqual("B1", ranges1[0].FormatAsString());
            Assert.AreEqual("C3", ranges1[1].FormatAsString());

            // adjacent Address are merged
            int formatIndex2 = sheetCF.AddConditionalFormatting(
                new CellRangeAddress[] {
                CellRangeAddress.ValueOf("B1"),
                CellRangeAddress.ValueOf("B2"),
                CellRangeAddress.ValueOf("B3"),
            }, rule1);

            Assert.AreEqual(1, formatIndex2);
            Assert.AreEqual(2, sheetCF.NumConditionalFormattings);
            CellRangeAddress[] ranges2 = sheetCF.GetConditionalFormattingAt(formatIndex2).GetFormattingRanges();
            Assert.AreEqual(1, ranges2.Length);
            Assert.AreEqual("B1:B3", ranges2[0].FormatAsString());
        }
        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);
        }
示例#4
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);
        }
 /**
  * Replaces an existing Conditional Formatting rule at position idx.
  * Excel allows to create up to 3 Conditional Formatting rules.
  * This method can be useful to modify existing  Conditional Formatting rules.
  *
  * @param idx position of the rule. Should be between 0 and 2.
  * @param cfRule - Conditional Formatting rule
  */
 public void SetRule(int idx, IConditionalFormattingRule cfRule)
 {
     XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)cfRule;
     _cf.GetCfRuleArray(idx).Set(xRule.GetCTCfRule());
 }
 public void SetRule(int idx, IConditionalFormattingRule cfRule)
 {
     SetRule(idx, (HSSFConditionalFormattingRule)cfRule);
 }
        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);
        }
示例#8
0
 /// <summary>
 /// Adds the conditional formatting.
 /// </summary>
 /// <param name="regions">The regions.</param>
 /// <param name="rule1">The rule1.</param>
 /// <returns></returns>
 public int AddConditionalFormatting(CellRangeAddress[] regions,
                                     IConditionalFormattingRule rule1)
 {
     return(AddConditionalFormatting(regions, (HSSFConditionalFormattingRule)rule1));
 }
        /// <summary>
        /// Adds the conditional formatting.
        /// </summary>
        /// <param name="regions">The regions.</param>
        /// <param name="rule1">The rule1.</param>
        /// <param name="rule2">The rule2.</param>
        /// <returns></returns>
        public int AddConditionalFormatting(CellRangeAddress[] regions,
                IConditionalFormattingRule rule1,
                IConditionalFormattingRule rule2)
        {
            return AddConditionalFormatting(regions,
                    new HSSFConditionalFormattingRule[]
				{
						(HSSFConditionalFormattingRule)rule1, (HSSFConditionalFormattingRule)rule2
				});
        }
        public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule[] cfRules)
        {
            if (regions == null)
            {
                throw new ArgumentException("regions must not be null");
            }
            foreach (CellRangeAddress range in regions) range.Validate(SpreadsheetVersion.EXCEL2007);

            if (cfRules == null)
            {
                throw new ArgumentException("cfRules must not be null");
            }
            if (cfRules.Length == 0)
            {
                throw new ArgumentException("cfRules must not be empty");
            }
            if (cfRules.Length > 3)
            {
                throw new ArgumentException("Number of rules must not exceed 3");
            }
            XSSFConditionalFormattingRule[] hfRules;
            if (cfRules is XSSFConditionalFormattingRule[])
                hfRules = (XSSFConditionalFormattingRule[])cfRules;
            else
            {
                hfRules = new XSSFConditionalFormattingRule[cfRules.Length];
                Array.Copy(cfRules, 0, hfRules, 0, hfRules.Length);
            }

            CellRangeAddress[] mergeCellRanges = CellRangeUtil.MergeCellRanges(regions);
            CT_ConditionalFormatting cf = _sheet.GetCTWorksheet().AddNewConditionalFormatting();
            List<String> refs = new List<String>();
            foreach (CellRangeAddress a in mergeCellRanges) refs.Add(a.FormatAsString());
            cf.sqref = (refs);


            int priority = 1;
            foreach (CT_ConditionalFormatting c in _sheet.GetCTWorksheet().conditionalFormatting)
            {
                priority += c.sizeOfCfRuleArray();
            }

            foreach (IConditionalFormattingRule rule in cfRules)
            {
                XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)rule;
                xRule.GetCTCfRule().priority = (priority++);
                cf.AddNewCfRule().Set(xRule.GetCTCfRule());
            }
            return _sheet.GetCTWorksheet().SizeOfConditionalFormattingArray() - 1;
        }
        /// <summary>
        /// Allows to Add a new Conditional Formatting Set to the sheet.
        /// </summary>
        /// <param name="regions">list of rectangular regions to apply conditional formatting rules</param>
        /// <param name="cfRules">Set of up to three conditional formatting rules</param>
        /// <returns>index of the newly Created Conditional Formatting object</returns>
        public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule[] cfRules)
        {
            if (regions == null)
            {
                throw new ArgumentException("regions must not be null");
            }
            if (cfRules == null)
            {
                throw new ArgumentException("cfRules must not be null");
            }
            if (cfRules.Length == 0)
            {
                throw new ArgumentException("cfRules must not be empty");
            }
            if (cfRules.Length > 3)
            {
                throw new ArgumentException("Number of rules must not exceed 3");
            }

            CFRuleRecord[] rules = new CFRuleRecord[cfRules.Length];
            for (int i = 0; i != cfRules.Length; i++)
            {
                rules[i] = ((HSSFConditionalFormattingRule)cfRules[i]).CfRuleRecord;
            }
            CFRecordsAggregate cfra = new CFRecordsAggregate(regions, rules);
            return _conditionalFormattingTable.Add(cfra);
        }
 /// <summary>
 /// Adds the conditional formatting.
 /// </summary>
 /// <param name="regions">The regions.</param>
 /// <param name="rule1">The rule1.</param>
 /// <returns></returns>
 public int AddConditionalFormatting(CellRangeAddress[] regions,
         IConditionalFormattingRule rule1)
 {
     return AddConditionalFormatting(regions, (HSSFConditionalFormattingRule)rule1);
 }
        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;
        }
 public int AddConditionalFormatting(CellRangeAddress[] regions, IConditionalFormattingRule rule1, IConditionalFormattingRule rule2)
 {
     CellRangeAddress[] regions1 = regions;
     XSSFConditionalFormattingRule[] conditionalFormattingRuleArray;
     if (rule1 != null)
     {
         conditionalFormattingRuleArray = new XSSFConditionalFormattingRule[2]
         {
             (XSSFConditionalFormattingRule)rule1,
             (XSSFConditionalFormattingRule)rule2
         }
     }
     ;
     else
     {
         conditionalFormattingRuleArray = (XSSFConditionalFormattingRule[])null;
     }
     return(this.AddConditionalFormatting(regions1, (IConditionalFormattingRule[])conditionalFormattingRuleArray));
 }
示例#15
0
        public void Write(string path, IProgress <string> progressHandler)
        {
            progressHandler.Report("Starting writing *.XLSX file...");
            progressHandler.Report("Path: " + path);

            using (var stream = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook     = new XSSFWorkbook();
                ISheet    entriesSheet = workbook.CreateSheet("Entries");
                ISheet    metaSheet    = workbook.CreateSheet("MetaData");

                int metaRowIndex = 0;
                foreach (var meta in MetaData)
                {
                    IRow  metaRow  = metaSheet.CreateRow(metaRowIndex);
                    ICell metaCell = metaRow.CreateCell(0);

                    metaCell.SetCellValue(meta);

                    metaRowIndex++;
                }

                metaSheet.SetColumnWidth(0, 100 * 256);

                IFont titleFont = workbook.CreateFont();
                titleFont.IsBold             = true;
                titleFont.FontHeightInPoints = 12;

                ICellStyle titleStyle = workbook.CreateCellStyle();
                titleStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
                titleStyle.FillPattern         = FillPattern.SolidForeground;
                titleStyle.BorderTop           = titleStyle.BorderLeft = titleStyle.BorderRight = titleStyle.BorderBottom = BorderStyle.Thin;
                titleStyle.SetFont(titleFont);

                ICellStyle valuesStyle = workbook.CreateCellStyle();
                valuesStyle.BorderTop         = valuesStyle.BorderLeft = valuesStyle.BorderRight = valuesStyle.BorderBottom = BorderStyle.Thin;
                valuesStyle.VerticalAlignment = VerticalAlignment.Center;
                valuesStyle.WrapText          = true;

                entriesSheet.DefaultColumnWidth       = 30;
                entriesSheet.DefaultRowHeightInPoints = 11 * 3;

                entriesSheet.SetColumnWidth(0, 50 * 256);
                entriesSheet.SetColumnWidth(1, 70 * 256);
                entriesSheet.SetColumnWidth(2, 70 * 256);

                entriesSheet.CreateFreezePane(0, 1);

                IRow titlesRow = entriesSheet.CreateRow(0);
                titlesRow.HeightInPoints = 20;

                ICell keyTitleCell                = titlesRow.CreateCell(0);
                ICell nativeTitleCell             = titlesRow.CreateCell(1);
                ICell translationTitleCell        = titlesRow.CreateCell(2);
                ICell translatorCommentsTitleCell = titlesRow.CreateCell(3);
                ICell flagsTitleCell              = titlesRow.CreateCell(4);
                ICell commentsTitleCell           = titlesRow.CreateCell(5);
                ICell referencesTitleCell         = titlesRow.CreateCell(6);

                foreach (var cell in titlesRow.Cells)
                {
                    cell.CellStyle = titleStyle;
                }

                keyTitleCell.SetCellValue("Key");
                nativeTitleCell.SetCellValue("Orginal");
                translationTitleCell.SetCellValue("Translation");
                translatorCommentsTitleCell.SetCellValue("Comments");
                flagsTitleCell.SetCellValue("Flags");
                commentsTitleCell.SetCellValue("Comments (generated)");
                referencesTitleCell.SetCellValue("References");

                int rowIndex = 1;
                foreach (var entry in Entries)
                {
                    IRow entryRow = entriesSheet.CreateRow(rowIndex);

                    ICell keyCell                = entryRow.CreateCell(0);
                    ICell nativeCell             = entryRow.CreateCell(1);
                    ICell translationCell        = entryRow.CreateCell(2);
                    ICell translatorCommentsCell = entryRow.CreateCell(3);
                    ICell flagsCell              = entryRow.CreateCell(4);
                    ICell commentsCell           = entryRow.CreateCell(5);
                    ICell referencesCell         = entryRow.CreateCell(6);

                    keyCell.SetCellValue(entry.msgctxt);
                    nativeCell.SetCellValue(entry.msgid);
                    translationCell.SetCellValue(entry.msgstr);
                    translatorCommentsCell.SetCellValue(GetMergedStringList(entry.translatorComments));
                    flagsCell.SetCellValue(GetMergedStringList(entry.flags));
                    commentsCell.SetCellValue(GetMergedStringList(entry.comments));
                    referencesCell.SetCellValue(GetMergedStringList(entry.references));


                    foreach (var cell in entryRow.Cells)
                    {
                        cell.CellStyle = valuesStyle;
                    }

                    rowIndex++;
                }

                IConditionalFormattingRule rule = entriesSheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Equal, "\"\"");
                IPatternFormatting         fill = rule.CreatePatternFormatting();
                fill.FillBackgroundColor = IndexedColors.Red.Index;
                fill.FillPattern         = FillPattern.SolidForeground;

                CellRangeAddress[] regions =
                {
                    new CellRangeAddress(1, Entries.Count, 2, 2)
                };

                entriesSheet.SheetConditionalFormatting.AddConditionalFormatting(regions, rule);

                workbook.Write(stream);
            }

            progressHandler.Report("File succesfuly created.");
        }
示例#16
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);
        }
        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);
        }
示例#18
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);
        }
示例#19
0
 public void AddRule(IConditionalFormattingRule cfRule)
 {
     AddRule((HSSFConditionalFormattingRule)cfRule);
 }
 public int AddConditionalFormatting(CellRangeAddress[] regions,
         IConditionalFormattingRule rule1, IConditionalFormattingRule rule2)
 {
     return AddConditionalFormatting(regions,
             rule1 == null ? null : new XSSFConditionalFormattingRule[] {
             (XSSFConditionalFormattingRule)rule1,
             (XSSFConditionalFormattingRule)rule2
     });
 }
        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);
        }
示例#22
0
 public void SetRule(int idx, IConditionalFormattingRule cfRule)
 {
     SetRule(idx, (HSSFConditionalFormattingRule)cfRule);
 }
        void MakePercentageConditionalFormattingRules()
        {
            IConditionalFormattingRule rule1 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThanOrEqual, GreenBandString);
            IPatternFormatting fill1 = rule1.CreatePatternFormatting();
            fill1.FillBackgroundColor = IndexedColors.BrightGreen.Index;
            fill1.FillPattern = (short)FillPattern.SolidForeground;

            IConditionalFormattingRule rule2 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThanOrEqual, YellowBandString);
            IPatternFormatting fill2 = rule2.CreatePatternFormatting();
            fill2.FillBackgroundColor = IndexedColors.Yellow.Index;
            fill2.FillPattern = (short)FillPattern.SolidForeground;

            IConditionalFormattingRule rule3 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, YellowBandString);
            IPatternFormatting fill3 = rule3.CreatePatternFormatting();
            fill3.FillBackgroundColor = IndexedColors.Red.Index;
            fill3.FillPattern = (short)FillPattern.SolidForeground;

            PercentageFormattingRules = new IConditionalFormattingRule[] { rule1, rule2, rule3 };
        }
 public void AddRule(IConditionalFormattingRule cfRule)
 {
     this._cf.AddNewCfRule().Set(((XSSFConditionalFormattingRule)cfRule).GetCTCfRule());
 }
示例#25
0
        public void TestSingleFormulaConditions()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();
            ISheet    sh = wb.CreateSheet();
            ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;

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

            Assert.AreEqual(ConditionType.CellValueIs, rule1.ConditionType);
            Assert.AreEqual("SUM(A1:A5)+10", rule1.Formula1);
            Assert.AreEqual(ComparisonOperator.Equal, rule1.ComparisonOperation);

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

            Assert.AreEqual(ConditionType.CellValueIs, rule2.ConditionType);
            Assert.AreEqual("15", rule2.Formula1);
            Assert.AreEqual(ComparisonOperator.NotEqual, rule2.ComparisonOperation);

            IConditionalFormattingRule rule3 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.NotEqual, "15");

            Assert.AreEqual(ConditionType.CellValueIs, rule3.ConditionType);
            Assert.AreEqual("15", rule3.Formula1);
            Assert.AreEqual(ComparisonOperator.NotEqual, rule3.ComparisonOperation);

            IConditionalFormattingRule rule4 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.GreaterThan, "0");

            Assert.AreEqual(ConditionType.CellValueIs, rule4.ConditionType);
            Assert.AreEqual("0", rule4.Formula1);
            Assert.AreEqual(ComparisonOperator.GreaterThan, rule4.ComparisonOperation);

            IConditionalFormattingRule rule5 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.LessThan, "0");

            Assert.AreEqual(ConditionType.CellValueIs, rule5.ConditionType);
            Assert.AreEqual("0", rule5.Formula1);
            Assert.AreEqual(ComparisonOperator.LessThan, rule5.ComparisonOperation);

            IConditionalFormattingRule rule6 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.GreaterThanOrEqual, "0");

            Assert.AreEqual(ConditionType.CellValueIs, rule6.ConditionType);
            Assert.AreEqual("0", rule6.Formula1);
            Assert.AreEqual(ComparisonOperator.GreaterThanOrEqual, rule6.ComparisonOperation);

            IConditionalFormattingRule rule7 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.LessThanOrEqual, "0");

            Assert.AreEqual(ConditionType.CellValueIs, rule7.ConditionType);
            Assert.AreEqual("0", rule7.Formula1);
            Assert.AreEqual(ComparisonOperator.LessThanOrEqual, rule7.ComparisonOperation);

            IConditionalFormattingRule rule8 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.Between, "0", "5");

            Assert.AreEqual(ConditionType.CellValueIs, rule8.ConditionType);
            Assert.AreEqual("0", rule8.Formula1);
            Assert.AreEqual("5", rule8.Formula2);
            Assert.AreEqual(ComparisonOperator.Between, rule8.ComparisonOperation);

            IConditionalFormattingRule rule9 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.NotBetween, "0", "5");

            Assert.AreEqual(ConditionType.CellValueIs, rule9.ConditionType);
            Assert.AreEqual("0", rule9.Formula1);
            Assert.AreEqual("5", rule9.Formula2);
            Assert.AreEqual(ComparisonOperator.NotBetween, rule9.ComparisonOperation);
        }
示例#26
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);
        }
示例#27
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);
        }
 /**
  * Add a Conditional Formatting rule.
  * Excel allows to create up to 3 Conditional Formatting rules.
  *
  * @param cfRule - Conditional Formatting rule
  */
 public void AddRule(IConditionalFormattingRule cfRule)
 {
     XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule)cfRule;
     _cf.AddNewCfRule().Set(xRule.GetCTCfRule());
 }
示例#29
0
        public void TestBasic()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();
            ISheet    sh = wb.CreateSheet();
            ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;

            Assert.AreEqual(0, sheetCF.NumConditionalFormattings);
            try
            {
                Assert.IsNull(sheetCF.GetConditionalFormattingAt(0));
                Assert.Fail("expected exception");
            }
            catch (ArgumentException e)
            {
                Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range"));
            }

            try
            {
                sheetCF.RemoveConditionalFormatting(0);
                Assert.Fail("expected exception");
            }
            catch (ArgumentException e)
            {
                Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range"));
            }

            IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("1");
            IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule("2");
            IConditionalFormattingRule rule3 = sheetCF.CreateConditionalFormattingRule("3");
            IConditionalFormattingRule rule4 = sheetCF.CreateConditionalFormattingRule("4");

            try
            {
                sheetCF.AddConditionalFormatting(null, rule1);
                Assert.Fail("expected exception");
            }
            catch (ArgumentException e)
            {
                Assert.IsTrue(e.Message.StartsWith("regions must not be null"));
            }
            try
            {
                sheetCF.AddConditionalFormatting(
                    new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
                    (IConditionalFormattingRule)null);
                Assert.Fail("expected exception");
            }
            catch (ArgumentException e)
            {
                Assert.IsTrue(e.Message.StartsWith("cfRules must not be null"));
            }

            try
            {
                sheetCF.AddConditionalFormatting(
                    new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
                    new IConditionalFormattingRule[0]);
                Assert.Fail("expected exception");
            }
            catch (ArgumentException e)
            {
                Assert.IsTrue(e.Message.StartsWith("cfRules must not be empty"));
            }

            try
            {
                sheetCF.AddConditionalFormatting(
                    new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
                    new IConditionalFormattingRule[] { rule1, rule2, rule3, rule4 });
                Assert.Fail("expected exception");
            }
            catch (ArgumentException e)
            {
                Assert.IsTrue(e.Message.StartsWith("Number of rules must not exceed 3"));
            }
        }
 public void AddRule(IConditionalFormattingRule cfRule)
 {
     AddRule((HSSFConditionalFormattingRule)cfRule);
 }
        public void TestSingleFormulaConditions()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();
            ISheet    sh = wb.CreateSheet();
            ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;

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

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule1.ConditionType);
            Assert.AreEqual("SUM(A1:A5)+10", rule1.Formula1);
            Assert.AreEqual(ComparisonOperator.EQUAL, rule1.ComparisonOperation);

            IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.NOT_EQUAL, "15");

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule2.ConditionType);
            Assert.AreEqual("15", rule2.Formula1);
            Assert.AreEqual(ComparisonOperator.NOT_EQUAL, rule2.ComparisonOperation);

            IConditionalFormattingRule rule3 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.NOT_EQUAL, "15");

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule3.ConditionType);
            Assert.AreEqual("15", rule3.Formula1);
            Assert.AreEqual(ComparisonOperator.NOT_EQUAL, rule3.ComparisonOperation);

            IConditionalFormattingRule rule4 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.GT, "0");

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule4.ConditionType);
            Assert.AreEqual("0", rule4.Formula1);
            Assert.AreEqual(ComparisonOperator.GT, rule4.ComparisonOperation);

            IConditionalFormattingRule rule5 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.LT, "0");

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule5.ConditionType);
            Assert.AreEqual("0", rule5.Formula1);
            Assert.AreEqual(ComparisonOperator.LT, rule5.ComparisonOperation);

            IConditionalFormattingRule rule6 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.GE, "0");

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule6.ConditionType);
            Assert.AreEqual("0", rule6.Formula1);
            Assert.AreEqual(ComparisonOperator.GE, rule6.ComparisonOperation);

            IConditionalFormattingRule rule7 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.LE, "0");

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule7.ConditionType);
            Assert.AreEqual("0", rule7.Formula1);
            Assert.AreEqual(ComparisonOperator.LE, rule7.ComparisonOperation);

            IConditionalFormattingRule rule8 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.BETWEEN, "0", "5");

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule8.ConditionType);
            Assert.AreEqual("0", rule8.Formula1);
            Assert.AreEqual("5", rule8.Formula2);
            Assert.AreEqual(ComparisonOperator.BETWEEN, rule8.ComparisonOperation);

            IConditionalFormattingRule rule9 = sheetCF.CreateConditionalFormattingRule(
                ComparisonOperator.NOT_BETWEEN, "0", "5");

            Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule9.ConditionType);
            Assert.AreEqual("0", rule9.Formula1);
            Assert.AreEqual("5", rule9.Formula2);
            Assert.AreEqual(ComparisonOperator.NOT_BETWEEN, rule9.ComparisonOperation);
        }