protected List <EvaluationConditionalFormatRule> GetRules(ISheet sheet)
{
String sheetName = sheet.SheetName;
List <EvaluationConditionalFormatRule> rules = formats[sheetName];
if (rules == null)
{
if (formats.ContainsKey(sheetName))
{
return(new List <EvaluationConditionalFormatRule>());
}
ISheetConditionalFormatting scf = sheet.SheetConditionalFormatting;
int count = scf.NumConditionalFormattings;
rules = new List <EvaluationConditionalFormatRule>(count);
formats.Add(sheetName, rules);
for (int i = 0; i < count; i++)
{
IConditionalFormatting f = scf.GetConditionalFormattingAt(i);
//optimization, as this may be expensive for lots of ranges
CellRangeAddress[] regions = f.GetFormattingRanges();
for (int r = 0; r < f.NumberOfRules; r++)
{
IConditionalFormattingRule rule = f.GetRule(r);
rules.Add(new EvaluationConditionalFormatRule(workbookEvaluator, sheet, f, i, rule, r, regions));
}
}
// need them in formatting and priority order so logic works right
rules.Sort();
}
return(rules);
}
public int AddConditionalFormatting(IConditionalFormatting cf)
{
XSSFConditionalFormatting conditionalFormatting = (XSSFConditionalFormatting)cf;
CT_Worksheet ctWorksheet = this._sheet.GetCTWorksheet();
ctWorksheet.AddNewConditionalFormatting().Set(conditionalFormatting.GetCTConditionalFormatting());
return(ctWorksheet.SizeOfConditionalFormattingArray() - 1);
}
/**
* Adds a copy of HSSFConditionalFormatting object to the sheet
* <p>This method could be used to copy HSSFConditionalFormatting object
* from one sheet to another. For example:
* <pre>
* HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
* newSheet.AddConditionalFormatting(cf);
* </pre>
*
* @param cf HSSFConditionalFormatting object
* @return index of the new Conditional Formatting object
*/
public int AddConditionalFormatting(IConditionalFormatting cf)
{
XSSFConditionalFormatting xcf = (XSSFConditionalFormatting)cf;
CT_Worksheet sh = _sheet.GetCTWorksheet();
sh.AddNewConditionalFormatting().Set(xcf.GetCTConditionalFormatting());//this is already copied in Set -> .Copy()); ommitted
return(sh.SizeOfConditionalFormattingArray() - 1);
}
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 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);
}
}
/// <summary>
/// 单元格是否存在条件格式
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="cf">条件格式</param>
/// <returns></returns>
private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
{
var cfRangeAddrs = cf.GetFormattingRanges();
foreach (var cfRangeAddr in cfRangeAddrs)
{
if (cell.RowIndex >= cfRangeAddr.FirstRow && cell.RowIndex <= cfRangeAddr.LastRow
&& cell.ColumnIndex >= cfRangeAddr.FirstColumn && cell.ColumnIndex <= cfRangeAddr.LastColumn)
{
return true;
}
}
return false;
}
/// <summary>
/// 单元格是否存在条件格式
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="cf">条件格式</param>
/// <returns></returns>
private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
{
CellRangeAddress[] cfRangeAddrs = cf.GetFormattingRanges();
foreach (CellRangeAddress cfRangeAddr in cfRangeAddrs)
{
if (cell.RowIndex >= cfRangeAddr.FirstRow && cell.RowIndex <= cfRangeAddr.LastRow &&
cell.ColumnIndex >= cfRangeAddr.FirstColumn && cell.ColumnIndex <= cfRangeAddr.LastColumn)
{
return(true);
}
}
return(false);
}
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);
}
/// <summary>
/// 获取条件格式规则
/// </summary>
/// <param name="cell">单元格</param>
/// <returns></returns>
public static IConditionalFormattingRule[] GetConditionalFormattingRules(this ICell cell)
{
var cfrs = new List <IConditionalFormattingRule>();
ISheetConditionalFormatting scf = cell.Sheet.SheetConditionalFormatting;
for (var i = 0; i < scf.NumConditionalFormattings; i++)
{
IConditionalFormatting cf = scf.GetConditionalFormattingAt(i);
if (cell.ExistConditionalFormatting(cf))
{
for (var j = 0; j < cf.NumberOfRules; j++)
{
cfrs.Add(cf.GetRule(j));
}
}
}
return(cfrs.ToArray());
}
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 EvaluationConditionalFormatRule(WorkbookEvaluator workbookEvaluator, ISheet sheet, IConditionalFormatting formatting, int formattingIndex, IConditionalFormattingRule rule, int ruleIndex, CellRangeAddress[] regions)
{
this.workbookEvaluator = workbookEvaluator;
this.sheet = sheet;
this.formatting = formatting;
this.rule = rule;
this.formattingIndex = formattingIndex;
this.ruleIndex = ruleIndex;
this.priority = rule.Priority;
this.regions = regions;
formula1 = rule.Formula1;
formula2 = rule.Formula2;
text = rule.Text;
lowerText = text == null ? null : text.ToLowerInvariant();
numberFormat = rule.NumberFormat;
@operator = (OperatorEnum)rule.ComparisonOperation;
type = rule.ConditionType;
}
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 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 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);
}
/// <summary>
/// 判断单元格是否存在条件格式
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="cf">条件格式</param>
private static bool ExistConditionalFormatting(this ICell cell, IConditionalFormatting cf)
{
return(cf.GetFormattingRanges().Any(cra =>
cell.RowIndex >= cra.FirstRow && cell.RowIndex <= cra.LastRow && cell.ColumnIndex >= cra.FirstColumn &&
cell.ColumnIndex <= cra.LastColumn));
}
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 int AddConditionalFormatting(IConditionalFormatting cf)
{
return(AddConditionalFormatting((HSSFConditionalFormatting)cf));
}
/**
* Adds a copy of HSSFConditionalFormatting object to the sheet
* <p>This method could be used to copy HSSFConditionalFormatting object
* from one sheet to another. For example:
* <pre>
* HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
* newSheet.AddConditionalFormatting(cf);
* </pre>
*
* @param cf HSSFConditionalFormatting object
* @return index of the new Conditional Formatting object
*/
public int AddConditionalFormatting(IConditionalFormatting cf)
{
XSSFConditionalFormatting xcf = (XSSFConditionalFormatting)cf;
CT_Worksheet sh = _sheet.GetCTWorksheet();
sh.AddNewConditionalFormatting().Set(xcf.GetCTConditionalFormatting());//this is already copied in Set -> .Copy()); ommitted
return sh.SizeOfConditionalFormattingArray() - 1;
}
/// <summary>
/// Adds a copy of HSSFConditionalFormatting object to the sheet
/// This method could be used to copy HSSFConditionalFormatting object
/// from one sheet to another.
/// </summary>
/// <param name="cf">HSSFConditionalFormatting object</param>
/// <returns>index of the new Conditional Formatting object</returns>
/// <example>
/// HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
/// newSheet.AddConditionalFormatting(cf);
/// </example>
public int AddConditionalFormatting(IConditionalFormatting cf)
{
CFRecordsAggregate cfraClone = ((HSSFConditionalFormatting)cf).CFRecordsAggregate.CloneCFAggregate();
return _conditionalFormattingTable.Add(cfraClone);
}
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);
}
/// <summary>
/// Adds a copy of HSSFConditionalFormatting object to the sheet
/// This method could be used to copy HSSFConditionalFormatting object
/// from one sheet to another.
/// </summary>
/// <param name="cf">HSSFConditionalFormatting object</param>
/// <returns>index of the new Conditional Formatting object</returns>
/// <example>
/// HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
/// newSheet.AddConditionalFormatting(cf);
/// </example>
public int AddConditionalFormatting(IConditionalFormatting cf)
{
CFRecordsAggregate cfraClone = ((HSSFConditionalFormatting)cf).CFRecordsAggregate.CloneCFAggregate();
return(_conditionalFormattingTable.Add(cfraClone));
}
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 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);
}