Apply Advanced Conditional Formatting

Microsoft Excel 2007 and later versions (2010/2013/2016) provides some advanced features for conditional formatting. For example, it lets you apply cell shading, borders, colored icons, arrows, flags and font formatting, etc. which has become quite sophisticated.

Apply Advanced Conditional Formatting to Microsoft Excel Files

Conditional formatting can:

• Add shaded data bars to graphically enhance the underlying numbers by embedding a simple bar chart in the cells.
• Automatically shade cells with color scales based on their relation to values in other cells in the range. The default settings shades the lowest value in red moving up to the highest value in green.
• Use icon sets in a similar way to color scales, but rather than shading the cells it adds small icons, such as arrows and traffic lights to the cells.

Aspose.Cells fully supports the conditional formatting provided by Microsoft Excel 2007 and later versions in XLSX format on cells at runtime. This example demonstrates an exercise for advanced conditional formatting types including IconSets, DataBars, Color Scales, TimePeriods, Top/Bottom and other rules with different sets of attributes.

• Adding Color Scale Conditional Formattings
• Adding Above Average Conditional Formattings
• Adding DataBars Conditional Formattings
• Adding IonSets Conditional Formattings
• Adding Text Conditional Formattings
• Adding TimePeriods Conditional Formattings
• Adding Top10 Conditional Formattings

Compute the Color Chosen by Microsoft Excel for ColorScale Conditional Formatting

Aspose.Cells lets you calculate the color selected by Microsoft Excel when ColorScale conditional formatting is used in a template file. See the sample code below to learn how to compute the color selected by Microsoft Excel.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
    
// Instantiate a workbook object
// Open the template file
Workbook workbook = new Workbook(dataDir + "Book1.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
// Get the A1 cell
Cell a1 = worksheet.Cells["A1"];

// Get the conditional formatting resultant object
ConditionalFormattingResult cfr1 = a1.GetConditionalFormattingResult();
// Get the ColorScale resultant color object
Color c = cfr1.ColorScaleResult;


// Read the color
Console.WriteLine(c.ToArgb().ToString());
Console.WriteLine(c.Name);