/** * Use Excel conditional formatting to highlight payments that are due in the next thirty days. * In this example, Due dates are entered in cells A2:A4. */ static void Expiry(ISheet sheet) { ICellStyle style = sheet.Workbook.CreateCellStyle(); style.DataFormat = (short)BuiltinFormats.GetBuiltinFormat("d-mmm"); sheet.CreateRow(0).CreateCell(0).SetCellValue("Date"); sheet.CreateRow(1).CreateCell(0).SetCellFormula("TODAY()+29"); sheet.CreateRow(2).CreateCell(0).SetCellFormula("A2+1"); sheet.CreateRow(3).CreateCell(0).SetCellFormula("A3+1"); for (int rownum = 1; rownum <= 3; rownum++) { sheet.GetRow(rownum).GetCell(0).CellStyle = style; } ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =A2=A1 (White Font) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)"); IFontFormatting font = rule1.CreateFontFormatting(); font.SetFontStyle(false, true); font.FontColorIndex = IndexedColors.Blue.Index; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A2:A4") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.GetRow(0).CreateCell(1).SetCellValue("Dates within the next 30 days are highlighted"); }
/** * Use Excel conditional formatting to hide the duplicate values, * and make the list easier to read. In this example, when the table is sorted by Region, * the second (and subsequent) occurences of each region name will have white font colour. */ static void HideDupplicates(ISheet sheet) { sheet.CreateRow(0).CreateCell(0).SetCellValue("City"); sheet.CreateRow(1).CreateCell(0).SetCellValue("Boston"); sheet.CreateRow(2).CreateCell(0).SetCellValue("Boston"); sheet.CreateRow(3).CreateCell(0).SetCellValue("Chicago"); sheet.CreateRow(4).CreateCell(0).SetCellValue("Chicago"); sheet.CreateRow(5).CreateCell(0).SetCellValue("New York"); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =A2=A1 (White Font) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("A2=A1"); IFontFormatting font = rule1.CreateFontFormatting(); font.FontColorIndex = IndexedColors.White.Index; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A2:A6") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.GetRow(1).CreateCell(1).SetCellValue("<== the second (and subsequent) " + "occurences of each region name will have white font colour. " + "Condition: Formula Is =A2=A1 (White Font)"); }
/** * Use Excel conditional formatting to highlight duplicate entries in a column. */ static void FormatDuplicates(ISheet sheet) { sheet.CreateRow(0).CreateCell(0).SetCellValue("Code"); sheet.CreateRow(1).CreateCell(0).SetCellValue(4); sheet.CreateRow(2).CreateCell(0).SetCellValue(3); sheet.CreateRow(3).CreateCell(0).SetCellValue(6); sheet.CreateRow(4).CreateCell(0).SetCellValue(3); sheet.CreateRow(5).CreateCell(0).SetCellValue(5); sheet.CreateRow(6).CreateCell(0).SetCellValue(8); sheet.CreateRow(7).CreateCell(0).SetCellValue(0); sheet.CreateRow(8).CreateCell(0).SetCellValue(2); sheet.CreateRow(9).CreateCell(0).SetCellValue(8); sheet.CreateRow(10).CreateCell(0).SetCellValue(6); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =A2=A1 (White Font) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1"); IFontFormatting font = rule1.CreateFontFormatting(); font.SetFontStyle(false, true); font.FontColorIndex = (IndexedColors.Blue.Index); CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A2:A11") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.GetRow(2).CreateCell(1).SetCellValue("<== Duplicates numbers in the column are highlighted. " + "Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)"); }
public XSSFDxfStyleProvider(CT_Dxf dxf, int stripeSize, IIndexedColorMap colorMap) { this.stripeSize = stripeSize; this.colorMap = colorMap; if (dxf == null) { border = null; font = null; number = null; fill = null; } else { border = dxf.IsSetBorder() ? new XSSFBorderFormatting(dxf.border) : null; font = dxf.IsSetFont() ? new XSSFFontFormatting(dxf.font) : null; if (dxf.IsSetNumFmt()) { CT_NumFmt numFmt = dxf.numFmt; number = new ExcelNumberFormat((int)numFmt.numFmtId, numFmt.formatCode); } else { number = null; } fill = dxf.IsSetFill() ? new XSSFPatternFormatting(dxf.fill) : null; } }
/** * Use Excel conditional formatting to check for errors, * and change the font colour to match the cell colour. * In this example, if formula result is #DIV/0! then it will have white font colour. */ static void Errors(ISheet sheet) { sheet.CreateRow(0).CreateCell(0).SetCellValue(84); sheet.CreateRow(1).CreateCell(0).SetCellValue(0); sheet.CreateRow(2).CreateCell(0).SetCellFormula("ROUND(A1/A2,0)"); sheet.CreateRow(3).CreateCell(0).SetCellValue(0); sheet.CreateRow(4).CreateCell(0).SetCellFormula("ROUND(A6/A4,0)"); sheet.CreateRow(5).CreateCell(0).SetCellValue(41); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =ISERROR(C2) (White Font) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("ISERROR(A1)"); IFontFormatting font = rule1.CreateFontFormatting(); font.FontColorIndex = (IndexedColors.White.Index); CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A6") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.GetRow(2).CreateCell(1).SetCellValue("<== The error in this cell is hidden. Condition: Formula is =ISERROR(C2) (White Font)"); sheet.GetRow(4).CreateCell(1).SetCellValue("<== The error in this cell is hidden. Condition: Formula is =ISERROR(C2) (White Font)"); }
public void TestShiftRows() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet = wb.CreateSheet(); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule( ComparisonOperator.Between, "SUM(A10:A15)", "1+SUM(B16:B30)"); IFontFormatting fontFmt = rule1.CreateFontFormatting(); fontFmt.SetFontStyle(true, false); IPatternFormatting patternFmt = rule1.CreatePatternFormatting(); patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index); IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule( ComparisonOperator.Between, "SUM(A10:A15)", "1+SUM(B16:B30)"); IBorderFormatting borderFmt = rule2.CreateBorderFormatting(); borderFmt.BorderDiagonal = BorderStyle.Medium; CellRangeAddress[] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5 }; sheetCF.AddConditionalFormatting(regions, rule1); sheetCF.AddConditionalFormatting(regions, rule2); // This row-shift should destroy the CF region sheet.ShiftRows(10, 20, -9); Assert.AreEqual(0, sheetCF.NumConditionalFormattings); // re-add the CF sheetCF.AddConditionalFormatting(regions, rule1); sheetCF.AddConditionalFormatting(regions, rule2); // This row shift should only affect the formulas sheet.ShiftRows(14, 17, 8); IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0); Assert.AreEqual("SUM(A10:A23)", cf1.GetRule(0).Formula1); Assert.AreEqual("1+SUM(B24:B30)", cf1.GetRule(0).Formula2); IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1); Assert.AreEqual("SUM(A10:A23)", cf2.GetRule(0).Formula1); Assert.AreEqual("1+SUM(B24:B30)", cf2.GetRule(0).Formula2); sheet.ShiftRows(0, 8, 21); cf1 = sheetCF.GetConditionalFormattingAt(0); Assert.AreEqual("SUM(A10:A21)", cf1.GetRule(0).Formula1); Assert.AreEqual("1+SUM(#REF!)", cf1.GetRule(0).Formula2); cf2 = sheetCF.GetConditionalFormattingAt(1); Assert.AreEqual("SUM(A10:A21)", cf2.GetRule(0).Formula1); Assert.AreEqual("1+SUM(#REF!)", cf2.GetRule(0).Formula2); }
public void TestClone() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet = wb.CreateSheet(); String formula = "7"; ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(formula); IFontFormatting fontFmt = rule1.CreateFontFormatting(); fontFmt.SetFontStyle(true, false); 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); try { wb.CloneSheet(0); } catch (Exception e) { if (e.Message.IndexOf("needs to define a clone method") > 0) { Assert.Fail("Indentified bug 45682"); } throw e; } Assert.AreEqual(2, wb.NumberOfSheets); }
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 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); }
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); }
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); }
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); }