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 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 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 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);
}
