/// <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); }
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); }
// 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); }
/// <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)); }
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."); }
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); }
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 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); }
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()); }
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); }
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 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()); }
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 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); }