void AddExpressionFormatCondition() { FormatCondition condition = new FormatCondition(); condition.FieldName = "Total"; condition.Expression = "[Total] > 3000"; condition.PredefinedFormatName = "BoldText"; condition.ApplyToRow = true; formats.Add("Bold Totals > 3000", condition); }
//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 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; }
void AddExpressionFormatCondition() { FormatCondition condition = new FormatCondition(); condition.FieldName = "Total"; condition.Expression = "[Total] > 3000"; condition.PredefinedFormatName = "BoldText"; condition.ApplyToRow = true; formats.Add("Bold Totals > 3000", condition); }
private void setFormatting(FormatCondition fc, System.Drawing.Color fontColor, System.Drawing.Color backgroundColor, System.Drawing.Color borderColor) { fc.Interior.Color = System.Drawing.ColorTranslator.ToOle(backgroundColor); fc.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor); fc.Font.Bold = (int)Microsoft.Office.Core.MsoTriState.msoTrue; fc.Borders[XlBordersIndex.xlEdgeBottom].Color = borderColor; fc.Borders[XlBordersIndex.xlEdgeLeft].Color = borderColor; fc.Borders[XlBordersIndex.xlEdgeRight].Color = borderColor; fc.Borders[XlBordersIndex.xlEdgeTop].Color = borderColor; }
//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"; }
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 }
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 }
//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 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 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 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 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 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 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 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 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 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 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"); }
//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 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 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 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 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 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 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 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 Gray attribute. private void AddIconSet5() { FormatConditionCollection conds = GetFormatCondition("M7:O8", Color.Aquamarine); int idx = conds.AddCondition(FormatConditionType.IconSet); FormatCondition cond = conds[idx]; cond.IconSet.Type = IconSetType.ArrowsGray3; Cell c = _sheet.Cells["M7"]; c.PutValue("ArrowsGray3"); }
//This method implements the IconSet conditional formatting type with 5 Arrows Colored attribute. private void AddIconSet4() { FormatConditionCollection conds = GetFormatCondition("M5:O6", Color.Aqua); int idx = conds.AddCondition(FormatConditionType.IconSet); FormatCondition cond = conds[idx]; cond.IconSet.Type = IconSetType.Arrows5; Cell c = _sheet.Cells["M5"]; c.PutValue("Arrows5"); }
public static object FormatCondition(FormatCondition condition, string formatString) { string[] formats = formatString.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries); foreach (string format in formats) { string[] pair = format.Trim().Split(new[] {':'}, StringSplitOptions.RemoveEmptyEntries); switch (pair[0]) { case "bgColor": string[] bgColor = pair[1].Split(new[] {','}); condition.Interior.Color = Color.FromArgb(Convert.ToInt32(bgColor[0]), Convert.ToInt32(bgColor[1]), Convert.ToInt32(bgColor[2])); break; case "font": string[] param = pair[1].Split(new[] {'|'}, StringSplitOptions.RemoveEmptyEntries); foreach (var p in param) { switch (p) { case "B": condition.Font.Bold = true; break; case "U": condition.Font.Underline = true; break; case "I": condition.Font.Italic = true; break; case "b": condition.Font.Bold = false; break; case "u": condition.Font.Underline = false; break; case "i": condition.Font.Italic = false; break; default: string[] rgb = p.Split(new[] {','}); condition.Font.Color = Color.FromArgb(Convert.ToInt32(rgb[0]), Convert.ToInt32(rgb[1]), Convert.ToInt32(rgb[2])); break; } } break; } } return null; }