C # applying Excel conditional Formatting (i)

The conditional formatting feature in Excel is a powerful and convenient feature that can greatly improve the design and readability of tables by using conditional formatting, which allows users to specify a single or multiple range of cells to apply one or more formats, which in this way significantly improves the operability of the table. The following describes how to set up and apply Excel conditional formatting in C # programming.
An overview of the sample essentials:

1. Apply conditional formatting based on cell values
2. Apply conditional formatting based on custom formulas
3. Apply data bar Condition type format
4. Delete conditional formatting
   4.1 Delete conditional formatting from the specified data range
   4.2 Remove all conditional formatting

Using tools

• Free Spire.xls for. NET 8.3 (freeware edition)
• Visual Studio

Sample code (for reference)

The test documentation is as follows:

"Example 1" Applies conditional formatting

Using spire.xls;using System.drawing;namespace conditionalformatting_xls{class Program {static void Main (ST            Ring[] (args) {//Instantiate Workbook object and load document Workbook WB = new Workbook (); Wb.            LoadFromFile ("sample.xlsx"); Gets the first worksheet Worksheet sheet = wb.            Worksheets[0]; Gets the data range cellrange Range = Sheet.            range["A2:h27"]; Add conditional formatting to the selected range 1 conditionalformatwrapper format1 = range.            Conditionalformats.addcondition (); Conditional format Type 1 is based on cell value FORMAT1.            Formattype = Conditionalformattype.cellvalue; Make the font bold for cells between 60 and 90, and set the font color to orange format1.            Firstformula = "60"; Format1.            Secondformula = "90"; Format1.            Operator = Comparisonoperatortype.between; Format1.            FontColor = Color.orange; Format1.            BackColor = Color.orange; Add conditional format 2 conditionalformatwrapper FORMAT2 = range.      Conditionalformats.addcondition ();      Format2.            Formattype = Conditionalformattype.cellvalue; Format2.            Firstformula = "60"; Format2.            Operator = comparisonoperatortype.less; Format2.            FontColor = color.red; Format2.            BackColor = color.red; Format2.            IsBold = true; Add border formatting (border color, border type) to conditional format 2 format2.            Leftbordercolor = color.red; Format2.            Rightbordercolor = Color.darkblue; Format2.            Topbordercolor = Color.deepskyblue; Format2.            Bottombordercolor = Color.deepskyblue; Format2.            Leftborderstyle = Linestyletype.medium; Format2.            Rightborderstyle = Linestyletype.thick; Format2.            Topborderstyle = linestyletype.double; Format2.            Bottomborderstyle = linestyletype.double; The type for conditional format 3 is a custom formula Conditionalformatwrapper FORMAT3 = range.            Conditionalformats.addcondition (); Format3.            Formattype = Conditionalformattype.formula; Custom formula fills the row of cells below 60 with the background color format3.FirstFormula = "=or ($C 2<60, $D 2<60, $E 2<60, $F 2<60, $G 2<60, $H 2<60)"; Format3.            BackColor = Color.gray; Save and open the document WB.            SaveToFile ("Result.xlsx", excelversion.version2013);    System.Diagnostics.Process.Start ("result.xlsx");} }}

Debug run the program, generate the document, as follows:

"Example 2" applies conditional formatting for a data bar type

using Spire.Xls;using System.Drawing;namespace ConditionalFormatting_XLS{    class Program    {        static void Main(string[] args)        {              //实例化workbook对象并加载文档            Workbook wb = new Workbook();            wb.LoadFromFile("sample.xlsx");            //获取第一个工作表            Worksheet sheet = wb.Worksheets[1];            //获取数据范围            CellRange range = sheet.Range["B2:D7"];            //添加条件类型4为data bars            ConditionalFormatWrapper format4 = sheet.AllocatedRange.ConditionalFormats.AddCondition();            format4.FormatType = ConditionalFormatType.DataBar;            format4.DataBar.BarColor = Color.ForestGreen;            //保存并打开文档            wb.SaveToFile("result1.xlsx", ExcelVersion.Version2013);            System.Diagnostics.Process.Start("result1.xlsx");          }    }}

Test results:

"Example 3" Delete conditional formatting

using Spire.Xls;namespace RemoveConditionalFormat_XLS{    class Program    {        static void Main(string[] args)        {            //实例化Workbook类对象，加载测试文档            Workbook workbook = new Workbook();            workbook.LoadFromFile("test.xlsx");            //获取第一个工作表            Worksheet sheet = workbook.Worksheets[0];            //删除指定区域的条件格式            //sheet.Range["A5:H5"].ConditionalFormats.Remove();            //删除表格中的所有条件格式            sheet.AllocatedRange.ConditionalFormats.Remove();            //保存并打开文档            workbook.SaveToFile("result1.xlsx", ExcelVersion.Version2010);            System.Diagnostics.Process.Start("result1.xlsx");        }    }}

Delete Effect

1. To delete a conditional format for a specified data range
   
2. Remove all conditional formatting
   
   This is an example of how to apply conditional formatting to excel in C #.
   If you want to reprint, please indicate the source.

C # applying Excel conditional Formatting (i)