public void TestClone()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
String formula = "7";
HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula);
HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();
fontFmt.SetFontStyle(true, false);
HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();
patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);
HSSFConditionalFormattingRule rule2 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");
HSSFConditionalFormattingRule[] 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)
{
throw new AssertionException("Indentified bug 45682");
}
throw e;
}
Assert.AreEqual(2, wb.NumberOfSheets);
}
public void TestShiftRows()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(
ComparisonOperator.Between, "sum(A10:A15)", "1+sum(B16:B30)");
HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();
fontFmt.SetFontStyle(true, false);
HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();
patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);
HSSFConditionalFormattingRule[] cfRules = { rule1, };
CellRangeAddress[] regions =
{
new CellRangeAddress(2, 4, 0, 0), // A3:A5
};
sheetCF.AddConditionalFormatting(regions, cfRules);
// 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, cfRules);
// This row shift should only affect the formulas
sheet.ShiftRows(14, 17, 8);
HSSFConditionalFormatting cf = (HSSFConditionalFormatting)sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual("SUM(A10:A23)", cf.GetRule(0).Formula1);
Assert.AreEqual("1+SUM(B24:B30)", cf.GetRule(0).Formula2);
sheet.ShiftRows(0, 8, 21);
cf = (HSSFConditionalFormatting)sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual("SUM(A10:A21)", cf.GetRule(0).Formula1);
Assert.AreEqual("1+SUM(#REF!)", cf.GetRule(0).Formula2);
}
static void Main(string[] args)
{
InitializeWorkbook();
HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1");
HSSFSheetConditionalFormatting 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.
HSSFConditionalFormattingRule rule = hscf.CreateConditionalFormattingRule(
ComparisonOperator.BETWEEN,
"55", // 1st formula
"500" // 2nd formula
);
// Create pattern with red background
HSSFPatternFormatting 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 TestCreateCF()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
String formula = "7";
HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula);
HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();
fontFmt.SetFontStyle(true, false);
HSSFBorderFormatting bordFmt = (HSSFBorderFormatting)rule1.CreateBorderFormatting();
bordFmt.BorderBottom = BorderStyle.Thin;
bordFmt.BorderTop = BorderStyle.Thick;
bordFmt.BorderLeft = BorderStyle.Dashed;
bordFmt.BorderRight = BorderStyle.Dotted;
HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();
patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);
HSSFConditionalFormattingRule rule2 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");
HSSFConditionalFormattingRule[] 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);
HSSFConditionalFormatting cf = (HSSFConditionalFormatting)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 = (HSSFConditionalFormattingRule)cf.GetRule(0);
Assert.AreEqual("7", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
HSSFFontFormatting r1fp = (HSSFFontFormatting)rule1.GetFontFormatting();
Assert.IsNotNull(r1fp);
Assert.IsTrue(r1fp.IsItalic);
Assert.IsFalse(r1fp.IsBold);
HSSFBorderFormatting r1bf = (HSSFBorderFormatting)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);
HSSFPatternFormatting r1pf = (HSSFPatternFormatting)rule1.GetPatternFormatting();
Assert.IsNotNull(r1pf);
Assert.AreEqual(HSSFColor.Yellow.Index, r1pf.FillBackgroundColor);
rule2 = (HSSFConditionalFormattingRule)cf.GetRule(1);
Assert.AreEqual("2", rule2.Formula2);
Assert.AreEqual("1", rule2.Formula1);
}
