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