(9) How to Use Apache POI to Operate Excel files ----- ConditionalFormatting ),

(9) How to Use Apache POI to Operate Excel files ----- ConditionalFormatting ),

Sometimes, we may want to modify some data in an Excel worksheet based on a specific condition.

The comparison operator object ComparisonOperator supports seven operators except BWTWEEN and NOT_BEWTEEN.

package org.apache.poi.ss.usermodel;public final class ComparisonOperator{  public static final byte NO_COMPARISON = 0;  public static final byte BETWEEN = 1;  public static final byte NOT_BETWEEN = 2;  public static final byte EQUAL = 3;  public static final byte NOT_EQUAL = 4;  public static final byte GT = 5;  public static final byte LT = 6;  public static final byte GE = 7;  public static final byte LE = 8;    public ComparisonOperator() {}}

The Demo code is as follows:

Import java. io. fileOutputStream; import org. apache. poi. ss. usermodel. cell; import org. apache. poi. ss. usermodel. comparisonOperator; import org. apache. poi. ss. usermodel. conditionalFormattingRule; import org. apache. poi. ss. usermodel. fontFormatting; import org. apache. poi. ss. usermodel. indexedColors; import org. apache. poi. ss. usermodel. patternFormatting; import org. apache. poi. ss. usermodel. row; import org. apache. poi. ss. usermodel. sheetConditionalFormatting; import org. apache. poi. ss. util. cellRangeAddress; import org. apache. poi. xssf. usermodel. XSSFCellStyle; import org. apache. poi. xssf. usermodel. XSSFSheet; import org. apache. poi. xssf. usermodel. XSSFWorkbook; public class ConditionFormatDemo {public static void main (String [] args) throws Exception {// 1. generate 5 rows and 9 columns of Data XSSFWorkbook wb = new XSSFWorkbook (); XSSFSheet sheet = wb. createSheet ("Sheet1"); XSSFCellStyle style = wb. createCellStyle (); for (int I = 0; I <5; I ++) {Row row = sheet. createRow (short) I); for (int j = 0; j <9; j ++) {Cell cell Cell = row. createCell (short) j); cell. setCellValue (I + 1) * 10 + (j + 1); cell. setCellStyle (style) ;}// 2. set Condition Format SheetConditionalFormatting sheetCF = sheet. getSheetConditionalFormatting (); ConditionalFormattingRule rule1 = sheetCF. createConditionalFormattingRule (ComparisonOperator. GT, "50"); PatternFormatting fill1 = rule1.createPatternFormatting (); fill1.setFillBackgroundColor (IndexedColors. RED. index); fill1.setFillPattern (PatternFormatting. SOLID_FOREGROUND); FontFormatting fontFormatting = rule1.createFontFormatting (); fontFormatting. setFontStyle (true, true); CellRangeAddress [] regions = {CellRangeAddress. valueOf ("A1: I5")}; sheetCF. addConditionalFormatting (regions, rule1); // 3. output the Excel file FileOutputStream fileOut = new FileOutputStream ("conditionFormatTest.xlsx"); wb. write (fileOut); fileOut. close ();}}

The effect after running is as follows,