//This method implements the TimePeriod conditional formatting type with Yesterday attribute.
private void AddTimePeriod_10()
{
FormatConditionCollection conds = GetFormatCondition("I19:K20", Color.MediumSeaGreen);
int idx = conds.AddCondition(FormatConditionType.TimePeriod);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
cond.TimePeriod = TimePeriodType.Yesterday;
Cell c = _sheet.Cells["I19"];
Style style = c.GetStyle();
style.Number = 30;
c.SetStyle(style);
c.PutValue(DateTime.Parse("2008/07/30"));
c = _sheet.Cells["K20"];
c.PutValue(DateTime.Parse("2008/08/03"));
style = c.GetStyle();
style.Number = 30;
c.SetStyle(style);
c = _sheet.Cells["I20"];
c.PutValue("Yesterday");
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
//Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 5;
ca.StartColumn = 0;
ca.EndColumn = 3;
fcs.AddArea(ca);
//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "50", "100");
FormatCondition fc = fcs[conditionIndex];
fc.Style.Pattern = BackgroundType.ReverseDiagonalStripe;
fc.Style.ForegroundColor = Color.FromArgb(255, 255, 0);
fc.Style.BackgroundColor = Color.FromArgb(0, 255, 255);
workbook.Save(dataDir + "output.xlsx");
//ExEnd:1
}
public static void AddConditionalFormatting(ref Worksheet ws, int endRow, int TotalColumn)
{
int index = ws.ConditionalFormattings.Add();
FormatConditionCollection conds = ws.ConditionalFormattings[index];
int[] isCondForm = Program.isCondForm;
for (int i = 0; i < isCondForm.Length; i++)
{
if (isCondForm[i] == 1)
{
CellArea area = new CellArea();
area.StartRow = 3;
area.EndRow = endRow;
area.StartColumn = TotalColumn + 1 + i;
area.EndColumn = TotalColumn + 1 + i;
conds.AddArea(area);
}
}
int idx = conds.AddCondition(FormatConditionType.ContainsText);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.LightGreen;
cond.Style.Pattern = BackgroundType.Solid;
cond.Text = "x";
}
//This method implements the AboveAverage conditional formatting type.
private void AddAboveAverage()
{
FormatConditionCollection conds = GetFormatCondition("A11:C12", Color.Tomato);
int idx = conds.AddCondition(FormatConditionType.AboveAverage);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
}
//This method implements a simple Top10 conditional formatting type.
private void AddTop10_1()
{
FormatConditionCollection conds = GetFormatCondition("A17:C20", Color.Gray);
int idx = conds.AddCondition(FormatConditionType.Top10);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Yellow;
cond.Style.Pattern = BackgroundType.Solid;
}
//This method implements the NotContainsText conditional formatting type.
private void AddNotContainsText()
{
FormatConditionCollection conds = GetFormatCondition("E7:G8", Color.LightCoral);
int idx = conds.AddCondition(FormatConditionType.NotContainsText);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
cond.Text = "3";
}
//This method implements the ContainsText conditional formatting type.
private void AddContainsText()
{
FormatConditionCollection conds = GetFormatCondition("E5:G6", Color.LightBlue);
int idx = conds.AddCondition(FormatConditionType.ContainsText);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Yellow;
cond.Style.Pattern = BackgroundType.Solid;
cond.Text = "1";
}
//This method implements the ColorScale conditional formatting type with some color scale attributes.
private void Add3ColorScale()
{
FormatConditionCollection conds = GetFormatCondition("A7:C8", Color.Green);
int idx = conds.AddCondition(FormatConditionType.ColorScale);
FormatCondition cond = conds[idx];
cond.ColorScale.MinCfvo.Type = FormatConditionValueType.Number;
cond.ColorScale.MinCfvo.Value = 9;
cond.ColorScale.MinColor = Color.Purple;
}
//This method implements the ColorScale conditional formatting type with some color scale attributes.
private void Add2ColorScale()
{
FormatConditionCollection conds = GetFormatCondition("A9:C10", Color.White);
int idx = conds.AddCondition(FormatConditionType.ColorScale);
FormatCondition cond = conds[idx];
//cond.ColorScale.MidCfvo = null;
cond.ColorScale.MinColor = Color.Gold;
cond.ColorScale.MaxColor = Color.SkyBlue;
}
public static void Main(string[] args)
{
//ExStart:1
// The path to the documents directory.
string dataDir = Aspose.Cells.Examples.Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
{
System.IO.Directory.CreateDirectory(dataDir);
}
//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
//Sets the conditional format range.
CellArea ca = new CellArea();
ca = new CellArea();
ca.StartRow = 2;
ca.EndRow = 2;
ca.StartColumn = 1;
ca.EndColumn = 1;
fcs.AddArea(ca);
//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.Expression);
//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Formula1 = "=IF(SUM(B1:B2)>100,TRUE,FALSE)";
fc.Style.BackgroundColor = Color.Red;
sheet.Cells["B3"].Formula = "=SUM(B1:B2)";
sheet.Cells["C4"].PutValue("If Sum of B1:B2 is greater than 100, B3 will have RED background");
//Saving the Excel file
workbook.Save(dataDir + "output.out.xls", SaveFormat.Auto);
//ExEnd:1
}
public static void Run()
{
// ExStart:AddColorScales
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create workbook
Workbook workbook = new Workbook();
// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Add some data in cells
worksheet.Cells["A1"].PutValue("2-Color Scale");
worksheet.Cells["D1"].PutValue("3-Color Scale");
for (int i = 2; i <= 15; i++)
{
worksheet.Cells["A" + i].PutValue(i);
worksheet.Cells["D" + i].PutValue(i);
}
// Adding 2-Color Scale Conditional Formatting
CellArea ca = CellArea.CreateCellArea("A2", "A15");
int idx = worksheet.ConditionalFormattings.Add();
FormatConditionCollection fcc = worksheet.ConditionalFormattings[idx];
fcc.AddCondition(FormatConditionType.ColorScale);
fcc.AddArea(ca);
FormatCondition fc = worksheet.ConditionalFormattings[idx][0];
fc.ColorScale.Is3ColorScale = false;
fc.ColorScale.MaxColor = Color.LightBlue;
fc.ColorScale.MinColor = Color.LightGreen;
// Adding 3-Color Scale Conditional Formatting
ca = CellArea.CreateCellArea("D2", "D15");
idx = worksheet.ConditionalFormattings.Add();
fcc = worksheet.ConditionalFormattings[idx];
fcc.AddCondition(FormatConditionType.ColorScale);
fcc.AddArea(ca);
fc = worksheet.ConditionalFormattings[idx][0];
fc.ColorScale.Is3ColorScale = true;
fc.ColorScale.MaxColor = Color.LightBlue;
fc.ColorScale.MidColor = Color.Yellow;
fc.ColorScale.MinColor = Color.LightGreen;
// Save the workbook
workbook.Save(dataDir + "output_out_.xlsx");
// ExEnd:AddColorScales
}
//This method implements the IconSet conditional formatting type with 4 Arrows Gray attribute.
private void AddIconSet6()
{
FormatConditionCollection conds = GetFormatCondition("M9:O10", Color.Azure);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.ArrowsGray4;
Cell c = _sheet.Cells["M9"];
c.PutValue("ArrowsGray4");
}
//This method implements another Top10 conditional formatting type.
private void AddTop10_2()
{
FormatConditionCollection conds = GetFormatCondition("A21:C24", Color.Green);
int idx = conds.AddCondition(FormatConditionType.Top10);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
cond.Top10.IsBottom = true;
}
//This method implements the IconSet conditional formatting type with 5 Arrows Gray attribute.
private void AddIconSet7()
{
FormatConditionCollection conds = GetFormatCondition("M11:O12", Color.Beige);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.ArrowsGray5;
Cell c = _sheet.Cells["M11"];
c.PutValue("ArrowsGray5");
}
//This method implements an AboveAverage conditional formatting type with some custom attributes.
private void AddAboveAverage2()
{
FormatConditionCollection conds = GetFormatCondition("A13:C14", Color.Empty);
int idx = conds.AddCondition(FormatConditionType.AboveAverage);
FormatCondition cond = conds[idx];
cond.AboveAverage.IsAboveAverage = false;
cond.AboveAverage.IsEqualAverage = true;
cond.Style.BackgroundColor = Color.Pink;
cond.Style.Pattern = BackgroundType.Solid;
}
//This method implements the IconSet conditional formatting type with another 3 Symbols attribute.
private void AddIconSet15()
{
FormatConditionCollection conds = GetFormatCondition("M27:O28", Color.Chartreuse);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.Symbols32;
Cell c = _sheet.Cells["M27"];
c.PutValue("Symbols32");
}
//This method implements the IconSet conditional formatting type with 3 Flags attribute.
private void AddIconSet8()
{
FormatConditionCollection conds = GetFormatCondition("M13:O14", Color.Bisque);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.Flags3;
Cell c = _sheet.Cells["M13"];
c.PutValue("Flags3");
}
//This method implements the IconSet conditional formatting type with 4 Ratings attribute.
private void AddIconSet10()
{
FormatConditionCollection conds = GetFormatCondition("M17:O18", Color.Blue);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.Rating4;
Cell c = _sheet.Cells["M17"];
c.PutValue("Rating4");
}
//This method implements the IconSet conditional formatting type with 3 Arrows Colored attribute.
private void AddIconSet2()
{
FormatConditionCollection conds = GetFormatCondition("M1:O2", Color.AliceBlue);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.Arrows3;
Cell c = _sheet.Cells["M1"];
c.PutValue("Arrows3");
}
//This method implements the IconSet conditional formatting type with 4 Red To Black attribute.
private void AddIconSet12()
{
FormatConditionCollection conds = GetFormatCondition("M21:O22", Color.Brown);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.RedToBlack4;
Cell c = _sheet.Cells["M21"];
c.PutValue("RedToBlack4");
}
//This method implements the IconSet conditional formatting type with 5 Ratings attribute.
private void AddIconSet11()
{
FormatConditionCollection conds = GetFormatCondition("M19:O20", Color.BlueViolet);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.Rating5;
Cell c = _sheet.Cells["M19"];
c.PutValue("Rating5");
}
//This method implements the IconSet conditional formatting type with 3 Signs attribute.
private void AddIconSet13()
{
FormatConditionCollection conds = GetFormatCondition("M23:O24", Color.BurlyWood);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.Signs3;
Cell c = _sheet.Cells["M23"];
c.PutValue("Signs3");
}
//This method implements the IconSet conditional formatting type with another 3 Traffic Lights attribute.
private void AddIconSet17()
{
FormatConditionCollection conds = GetFormatCondition("M31:O32", Color.Coral);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.TrafficLights32;
Cell c = _sheet.Cells["M31"];
c.PutValue("TrafficLights32");
}
//This method implements the IconSet conditional formatting type with 3 Traffic Lights attribute.
private void AddIconSet16()
{
FormatConditionCollection conds = GetFormatCondition("M29:O30", Color.Chocolate);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.TrafficLights31;
Cell c = _sheet.Cells["M29"];
c.PutValue("TrafficLights31");
}
//This method implements another Top10 conditional formatting type with some custom attributes.
private void AddTop10_3()
{
FormatConditionCollection conds = GetFormatCondition("A25:C28", Color.Orange);
int idx = conds.AddCondition(FormatConditionType.Top10);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Blue;
cond.Style.Pattern = BackgroundType.Solid;
cond.Top10.IsPercent = true;
}
//This method implements the DataBars conditional formatting type with Percentile attribute.
private void AddDataBar2()
{
FormatConditionCollection conds = GetFormatCondition("E3:G4", Color.LightGreen);
int idx = conds.AddCondition(FormatConditionType.DataBar);
FormatCondition cond = conds[idx];
cond.DataBar.Color = Color.Orange;
cond.DataBar.MinCfvo.Type = FormatConditionValueType.Percentile;
cond.DataBar.MinCfvo.Value = 30.78;
cond.DataBar.ShowValue = false;
}
//This method implements another Top10 conditional formatting type with some custom attributes.
private void AddTop10_4()
{
FormatConditionCollection conds = GetFormatCondition("A29:C32", Color.Gold);
int idx = conds.AddCondition(FormatConditionType.Top10);
FormatCondition cond = conds[idx];
cond.Style.BackgroundColor = Color.Green;
cond.Style.Pattern = BackgroundType.Solid;
cond.Top10.Rank = 3;
}
//This method implements the IconSet conditional formatting type with 5 Quarters attribute.
private void AddIconSet9()
{
FormatConditionCollection conds = GetFormatCondition("M15:O16", Color.BlanchedAlmond);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.Quarters5;
Cell c = _sheet.Cells["M15"];
c.PutValue("Quarters5");
}
//This method implements the IconSet conditional formatting type with 4 Traffic Lights attribute.
private void AddIconSet18()
{
FormatConditionCollection conds = GetFormatCondition("M33:O35", Color.CornflowerBlue);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.TrafficLights4;
Cell c = _sheet.Cells["M33"];
c.PutValue("TrafficLights4");
}
//This method implements the IconSet conditional formatting type with 3 Symbols attribute.
private void AddIconSet14()
{
FormatConditionCollection conds = GetFormatCondition("M25:O26", Color.CadetBlue);
int idx = conds.AddCondition(FormatConditionType.IconSet);
FormatCondition cond = conds[idx];
cond.IconSet.Type = IconSetType.Symbols3;
Cell c = _sheet.Cells["M25"];
c.PutValue("Symbols3");
}