/** * Highlight cells based on their values */ static void SameCell(ISheet sheet) { sheet.CreateRow(0).CreateCell(0).SetCellValue(84); sheet.CreateRow(1).CreateCell(0).SetCellValue(74); sheet.CreateRow(2).CreateCell(0).SetCellValue(50); sheet.CreateRow(3).CreateCell(0).SetCellValue(51); sheet.CreateRow(4).CreateCell(0).SetCellValue(49); sheet.CreateRow(5).CreateCell(0).SetCellValue(41); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Cell Value Is greater than 70 (Blue Fill) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "70"); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = (IndexedColors.Blue.Index); fill1.FillPattern = FillPattern.SolidForeground; // Condition 2: Cell Value Is less than 50 (Green Fill) IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "50"); IPatternFormatting fill2 = rule2.CreatePatternFormatting(); fill2.FillBackgroundColor = (IndexedColors.Green.Index); fill2.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A6") }; sheetCF.AddConditionalFormatting(regions, rule1, rule2); sheet.GetRow(0).CreateCell(2).SetCellValue("<== Condition 1: Cell Value is greater than 70 (Blue Fill)"); sheet.GetRow(4).CreateCell(2).SetCellValue("<== Condition 2: Cell Value is less than 50 (Green Fill)"); }
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); }
/// <summary> /// /// </summary> /// <param name="path"></param> /// <param name="maxRowIndex">Row index of max values</param> /// <param name="minRowIndex">Row index of min values</param> /// <param name="numFaiRows"></param> /// <param name="numFaiColumns"></param> /// <param name="numMiscRows">Count of rows other than fai value rows</param> public static void FormatFaiExcel(string path, int maxRowIndex, int minRowIndex, int numFaiRows, int numFaiColumns, int numMiscRows) { XSSFWorkbook wb; using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) { wb = new XSSFWorkbook(file); } // Delete unformatted file File.Delete(path); var ws = wb.GetSheetAt(0); for (int j = 0; j < numFaiColumns; j++) { var columnIndex = GetExcelColumnName(j + 1); var maxFormula = $"${columnIndex}${maxRowIndex + 1}"; var minFormula = $"${columnIndex}${minRowIndex + 1}"; ISheetConditionalFormatting sheetCF = ws.SheetConditionalFormatting; // Max rule IConditionalFormattingRule maxRule = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, maxFormula); IPatternFormatting fill1 = maxRule.CreatePatternFormatting(); fill1.FillBackgroundColor = (IndexedColors.Red.Index); fill1.FillPattern = FillPattern.SolidForeground; // min rule IConditionalFormattingRule minRule = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.LessThan, minFormula); IPatternFormatting fill2 = minRule.CreatePatternFormatting(); fill2.FillBackgroundColor = (IndexedColors.Blue.Index); fill2.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf($"{columnIndex}{numMiscRows+1}:{columnIndex}{numFaiRows+numMiscRows}") }; sheetCF.AddConditionalFormatting(regions, maxRule, minRule); } using (var fs = File.Create(path)) { wb.Write(fs); } }
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); }
/** * 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)"); }
/** * 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)"); }
/** * Use Excel conditional formatting to highlight items that are in a list on the worksheet. */ static void InList(ISheet sheet) { sheet.CreateRow(0).CreateCell(0).SetCellValue("Codes"); sheet.CreateRow(1).CreateCell(0).SetCellValue("AA"); sheet.CreateRow(2).CreateCell(0).SetCellValue("BB"); sheet.CreateRow(3).CreateCell(0).SetCellValue("GG"); sheet.CreateRow(4).CreateCell(0).SetCellValue("AA"); sheet.CreateRow(5).CreateCell(0).SetCellValue("FF"); sheet.CreateRow(6).CreateCell(0).SetCellValue("XX"); sheet.CreateRow(7).CreateCell(0).SetCellValue("CC"); sheet.GetRow(0).CreateCell(2).SetCellValue("Valid"); sheet.GetRow(1).CreateCell(2).SetCellValue("AA"); sheet.GetRow(2).CreateCell(2).SetCellValue("BB"); sheet.GetRow(3).CreateCell(2).SetCellValue("CC"); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =A2=A1 (White Font) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)"); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = (IndexedColors.LightBlue.Index); fill1.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A2:A8") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.GetRow(2).CreateCell(3).SetCellValue("<== Use Excel conditional formatting to highlight items that are in a list on the worksheet"); }
/** * 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"); }
public void TestCopy() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet1 = wb.CreateSheet(); ISheet sheet2 = wb.CreateSheet(); ISheetConditionalFormatting sheet1CF = sheet1.SheetConditionalFormatting; ISheetConditionalFormatting sheet2CF = sheet2.SheetConditionalFormatting; Assert.AreEqual(0, sheet1CF.NumConditionalFormattings); Assert.AreEqual(0, sheet2CF.NumConditionalFormattings); IConditionalFormattingRule rule1 = sheet1CF.CreateConditionalFormattingRule( ComparisonOperator.Equal, "SUM(A1:A5)+10"); IConditionalFormattingRule rule2 = sheet1CF.CreateConditionalFormattingRule( ComparisonOperator.NotEqual, "15"); // adjacent Address are merged int formatIndex = sheet1CF.AddConditionalFormatting( new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A5"), CellRangeAddress.ValueOf("C1:C5") }, rule1, rule2); Assert.AreEqual(0, formatIndex); Assert.AreEqual(1, sheet1CF.NumConditionalFormattings); Assert.AreEqual(0, sheet2CF.NumConditionalFormattings); sheet2CF.AddConditionalFormatting(sheet1CF.GetConditionalFormattingAt(formatIndex)); Assert.AreEqual(1, sheet2CF.NumConditionalFormattings); IConditionalFormatting sheet2cf = sheet2CF.GetConditionalFormattingAt(0); Assert.AreEqual(2, sheet2cf.NumberOfRules); Assert.AreEqual("SUM(A1:A5)+10", sheet2cf.GetRule(0).Formula1); Assert.AreEqual(ComparisonOperator.Equal, sheet2cf.GetRule(0).ComparisonOperation); Assert.AreEqual(ConditionType.CellValueIs, sheet2cf.GetRule(0).ConditionType); Assert.AreEqual("15", sheet2cf.GetRule(1).Formula1); Assert.AreEqual(ComparisonOperator.NotEqual, sheet2cf.GetRule(1).ComparisonOperation); Assert.AreEqual(ConditionType.CellValueIs, sheet2cf.GetRule(1).ConditionType); }
public void TestCreatePatternFormatting() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet = workbook.CreateSheet(); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "7"); IPatternFormatting patternFmt = rule1.CreatePatternFormatting(); Assert.AreEqual(0, patternFmt.FillBackgroundColor); patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Red.Index); Assert.AreEqual(HSSFColor.Red.Index, patternFmt.FillBackgroundColor); Assert.AreEqual(0, patternFmt.FillForegroundColor); patternFmt.FillForegroundColor = (/*setter*/ HSSFColor.Blue.Index); Assert.AreEqual(HSSFColor.Blue.Index, patternFmt.FillForegroundColor); Assert.AreEqual((short)FillPattern.NoFill, patternFmt.FillPattern); patternFmt.FillPattern = (short)FillPattern.SolidForeground; Assert.AreEqual((short)FillPattern.SolidForeground, patternFmt.FillPattern); patternFmt.FillPattern = (short)FillPattern.NoFill; Assert.AreEqual((short)FillPattern.NoFill, patternFmt.FillPattern); if (this._testDataProvider.GetSpreadsheetVersion() == SpreadsheetVersion.EXCEL97) { patternFmt.FillPattern = (short)FillPattern.Bricks; Assert.AreEqual((short)FillPattern.Bricks, patternFmt.FillPattern); } IConditionalFormattingRule[] cfRules = { rule1 }; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A5") }; sheetCF.AddConditionalFormatting(regions, cfRules); // Verification IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0); Assert.IsNotNull(cf); Assert.AreEqual(1, cf.NumberOfRules); IPatternFormatting r1fp = cf.GetRule(0).GetPatternFormatting(); Assert.IsNotNull(r1fp); Assert.AreEqual(HSSFColor.Red.Index, r1fp.FillBackgroundColor); Assert.AreEqual(HSSFColor.Blue.Index, r1fp.FillForegroundColor); if (this._testDataProvider.GetSpreadsheetVersion() == SpreadsheetVersion.EXCEL97) { Assert.AreEqual((short)FillPattern.Bricks, r1fp.FillPattern); } }
public void TestRemove() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet1 = wb.CreateSheet(); ISheetConditionalFormatting sheetCF = sheet1.SheetConditionalFormatting; Assert.AreEqual(0, sheetCF.NumConditionalFormattings); IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule( ComparisonOperator.Equal, "SUM(A1:A5)"); // adjacent Address are merged int formatIndex = sheetCF.AddConditionalFormatting( new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A5") }, rule1); Assert.AreEqual(0, formatIndex); Assert.AreEqual(1, sheetCF.NumConditionalFormattings); sheetCF.RemoveConditionalFormatting(0); 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")); } formatIndex = sheetCF.AddConditionalFormatting( new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A5") }, rule1); Assert.AreEqual(0, formatIndex); Assert.AreEqual(1, sheetCF.NumConditionalFormattings); sheetCF.RemoveConditionalFormatting(0); 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")); } }
public void TestCreatePatternFormatting() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet = workbook.CreateSheet(); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.EQUAL, "7"); IPatternFormatting patternFmt = rule1.CreatePatternFormatting(); Assert.AreEqual(0, patternFmt.FillBackgroundColor); patternFmt.FillBackgroundColor = (/*setter*/ IndexedColors.RED.Index); Assert.AreEqual(IndexedColors.RED.Index, patternFmt.FillBackgroundColor); Assert.AreEqual(0, patternFmt.FillForegroundColor); patternFmt.FillForegroundColor = (/*setter*/ IndexedColors.BLUE.Index); Assert.AreEqual(IndexedColors.BLUE.Index, patternFmt.FillForegroundColor); Assert.AreEqual(PatternFormatting.NO_Fill, patternFmt.FillPattern); patternFmt.FillPattern = (/*setter*/ PatternFormatting.SOLID_FOREGROUND); Assert.AreEqual(PatternFormatting.SOLID_FOREGROUND, patternFmt.FillPattern); patternFmt.FillPattern = (/*setter*/ PatternFormatting.NO_Fill); Assert.AreEqual(PatternFormatting.NO_Fill, patternFmt.FillPattern); patternFmt.FillPattern = (/*setter*/ PatternFormatting.BRICKS); Assert.AreEqual(PatternFormatting.BRICKS, patternFmt.FillPattern); IConditionalFormattingRule[] cfRules = { rule1 }; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A5") }; sheetCF.AddConditionalFormatting(regions, cfRules); // Verification IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0); Assert.IsNotNull(cf); Assert.AreEqual(1, cf.NumberOfRules); IPatternFormatting r1fp = cf.GetRule(0).GetPatternFormatting(); Assert.IsNotNull(r1fp); Assert.AreEqual(IndexedColors.RED.Index, r1fp.FillBackgroundColor); Assert.AreEqual(IndexedColors.BLUE.Index, r1fp.FillForegroundColor); Assert.AreEqual(PatternFormatting.BRICKS, r1fp.FillPattern); }
public static void ConditionFormat(ISheet sheet, int rowIndex) { ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule = sheetCF.CreateConditionalFormattingRule("$B3<>\"Transferred\""); IPatternFormatting fill = rule.CreatePatternFormatting(); fill.FillBackgroundColor = (IndexedColors.Red.Index); fill.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf($"B3:B{rowIndex}") }; sheetCF.AddConditionalFormatting(regions, rule); }
/** * Highlight multiple cells based on a formula */ static void MultiCell(ISheet sheet) { // header row IRow row0 = sheet.CreateRow(0); row0.CreateCell(0).SetCellValue("Units"); row0.CreateCell(1).SetCellValue("Cost"); row0.CreateCell(2).SetCellValue("Total"); IRow row1 = sheet.CreateRow(1); row1.CreateCell(0).SetCellValue(71); row1.CreateCell(1).SetCellValue(29); row1.CreateCell(2).SetCellValue(2059); IRow row2 = sheet.CreateRow(2); row2.CreateCell(0).SetCellValue(85); row2.CreateCell(1).SetCellValue(29); row2.CreateCell(2).SetCellValue(2059); IRow row3 = sheet.CreateRow(3); row3.CreateCell(0).SetCellValue(71); row3.CreateCell(1).SetCellValue(29); row3.CreateCell(2).SetCellValue(2059); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =$B2>75 (Blue Fill) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("$A2>75"); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = IndexedColors.Blue.Index; fill1.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A2:C4") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.GetRow(2).CreateCell(4).SetCellValue("<== Condition 1: Formula is =$B2>75 (Blue Fill)"); }
/** * You can use Excel conditional formatting to shade bands of rows on the worksheet. * In this example, 3 rows are shaded light grey, and 3 are left with no shading. * In the MOD function, the total number of rows in the set of banded rows (6) is entered. */ static void ShadeBands(ISheet sheet) { ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("MOD(ROW(),6)<3"); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = (IndexedColors.Grey25Percent.Index); fill1.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:Z100") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.CreateRow(0).CreateCell(1).SetCellValue("Shade Bands of Rows"); sheet.CreateRow(1).CreateCell(1).SetCellValue("Condition: Formula is =MOD(ROW(),6)<2 (Light Grey Fill)"); }
/** * Use Excel conditional formatting to shade alternating rows on the worksheet */ static void ShadeAlt(ISheet sheet) { ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =A2=A1 (White Font) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("MOD(ROW(),2)"); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = (IndexedColors.LightGreen.Index); fill1.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:Z100") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.CreateRow(0).CreateCell(1).SetCellValue("Shade Alternating Rows"); sheet.CreateRow(1).CreateCell(1).SetCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)"); }
static void Main(string[] args) { InitializeWorkbook(); HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1"); ISheetConditionalFormatting hscf = sheet1.SheetConditionalFormatting; // Define a Conditional Formatting rule, which triggers formatting // when cell's value is bigger than 55 and smaller than 500 // applies patternFormatting defined below. IConditionalFormattingRule rule = hscf.CreateConditionalFormattingRule( ComparisonOperator.Between, "55", // 1st formula "500" // 2nd formula ); // Create pattern with red background IPatternFormatting patternFmt = rule.CreatePatternFormatting(); patternFmt.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index; //// Define a region containing first column CellRangeAddress[] regions = { new CellRangeAddress(0, 65535, 0, 1) }; // Apply Conditional Formatting rule defined above to the regions hscf.AddConditionalFormatting(regions, rule); //fill cell with numeric values sheet1.CreateRow(0).CreateCell(0).SetCellValue(50); sheet1.CreateRow(0).CreateCell(1).SetCellValue(101); sheet1.CreateRow(1).CreateCell(1).SetCellValue(25); sheet1.CreateRow(1).CreateCell(0).SetCellValue(150); WriteToFile(); }
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 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 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); }
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 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 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 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 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 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 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); }