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 single or multiple ranges of cells to apply one or more formats, which in so doing can significantly improve 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 the 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

usingSpire.xls;usingSystem.Drawing;namespaceconditionalformatting_xls{classProgram {Static voidMain (string[] args) {            //instantiating a Workbook object and loading a documentWorkbook WB =NewWorkbook (); Wb. LoadFromFile ("sample.xlsx"); //Get first worksheetWorksheet sheet = wb. worksheets[0]; //Get data rangeCellRange range = Sheet. range["a2:h27"]; //add a conditional format to the selected range 1Conditionalformatwrapper FORMAT1 =range.            Conditionalformats.addcondition (); //conditional format Type 1 based on cell valuesFormat1. Formattype =Conditionalformattype.cellvalue; //make the font bold and set the font color to orange in cells between 60 and 90Format1. Firstformula =" -"; Format1. Secondformula=" -"; Format1. Operator=Comparisonoperatortype.between; Format1. FontColor=Color.orange; //FORMAT1.            BackColor = Color.orange; //Adding conditional formatting 2Conditionalformatwrapper FORMAT2 =range.            Conditionalformats.addcondition (); Format2. Formattype=Conditionalformattype.cellvalue; Format2. Firstformula=" -"; 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 2Format2. 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; //type of conditional Format 3 is a custom formulaConditionalformatwrapper FORMAT3 =range.            Conditionalformats.addcondition (); Format3. Formattype=Conditionalformattype.formula; //Custom formulas Fill the row of cells below 60 with the background colorFormat3. 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 a documentWb. 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

usingSpire.xls;usingSystem.Drawing;namespaceconditionalformatting_xls{classProgram {Static voidMain (string[] args) {            //instantiating a Workbook object and loading a documentWorkbook WB =NewWorkbook (); Wb. LoadFromFile ("sample.xlsx"); //get a 2nd worksheetWorksheet sheet = wb. worksheets[1]; //Get data rangeCellRange range = Sheet. range["B2:d7"]; //Add condition type 4 to data barsConditionalformatwrapper FORMAT4 =sheet.            AllocatedRange.ConditionalFormats.AddCondition (); Format4. Formattype=Conditionalformattype.databar; Format4. Databar.barcolor=Color.forestgreen; //Save and open a documentWb. SaveToFile ("result1.xlsx", excelversion.version2013); System.Diagnostics.Process.Start ("result1.xlsx"); }    }}

Test results:

"Example 3" Delete conditional formatting

usingSpire.xls;namespaceremoveconditionalformat_xls{classProgram {Static voidMain (string[] args) {            //instantiate the workbook class, load the test documentWorkbook Workbook =NewWorkbook (); Workbook. LoadFromFile ("test.xlsx"); //Get first worksheetWorksheet sheet = workbook. worksheets[0]; //Delete conditional formatting for a specified range//sheet. range["A5:h5"].            Conditionalformats.remove (); //Delete all conditional formatting from a tablesheet.            AllocatedRange.ConditionalFormats.Remove (); //Save and open a documentWorkbook. SaveToFile ("result1.xlsx", excelversion.version2010); System.Diagnostics.Process.Start ("result1.xlsx"); }    }}

Delete Effect

1. Delete conditional formatting for the specified data range

2. Delete all conditional formats

This is an example of how to apply conditional formatting to excel in C #.

If necessary, please indicate the source.