Java format Excel conditions

In Excel, the application of conditional formatting can greatly improve the design and readability of tables. Users can specify a single or multiple cell range to apply one or more conditional formats. This article will introduce the setting method of conditional format through Java program examples. When setting conditional format, due to different setting needs, this article will introduce the following key points of examples:
Example 1:

  1. Apply conditional formatting to highlight duplicate, unique values
  2. Apply conditional format to highlight peak (highest, lowest)
  3. Apply conditional formatting to highlight values below or above average
    Example 2:
  4. Apply conditional formatting for cell value types
  5. Apply conditional formatting for formula types
  6. Apply conditional formatting for data bar types
    Example 3:
  7. Delete conditional formatting

Using tool: free flame.xls for Java (free version)
Jar access and import: Official Website Download jar package , and unzip the jar under the lib folder into the Java program (or through the maven download and import To maven project program). The import effect is as follows:

Java example 1 - apply conditional formatting to highlight duplicate, unique, peak, above or below average

import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import com.spire.xls.core.spreadsheet.conditionalformatting.TimePeriodType;

import java.awt.*;

public class AddConditionalFormat {
    public static void main(String[] args) {
        //Create instance, load test document
        Workbook wb = new Workbook();
        wb.loadFromFile("test.xlsx");

        //Get first sheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Add conditional format 1 and specify data range
        XlsConditionalFormats format1 = sheet.getConditionalFormats().add();
        format1.addRange(sheet.getCellRange("A2:A12"));
        //Highlight cells below average
        IConditionalFormat cf1 = format1.addAverageCondition(AverageType.Below);
        cf1.setBackColor(new Color(230,230,250));
        //Highlight cells above average
        IConditionalFormat cf2 = format1.addAverageCondition(AverageType.Above);
        cf2.setBackColor(new Color(224,255,255));

        //Add conditional format 2 and specify data range
        XlsConditionalFormats format2 = sheet.getConditionalFormats().add();
        format2.addRange(sheet.getCellRange("B2:B12"));
        //Highlight maximum
        IConditionalFormat cf3 = format2.addTopBottomCondition(TopBottomType.Top, 1);
        cf3.setBackColor(new Color(144,238,144));
        //Highlight lowest cell
        IConditionalFormat cf4 = format2.addTopBottomCondition(TopBottomType.Bottom, 1);
        cf4.setBackColor(new Color(221,160,221));

        //Add conditional format 3 and specify data range
        XlsConditionalFormats format3 = sheet.getConditionalFormats().add();
        format3.addRange(sheet.getCellRange("C2:C12"));
        //Highlight cells with unique values
        IConditionalFormat cf5 = format3.addDuplicateValuesCondition();
        cf5.setFormatType(ConditionalFormatType.UniqueValues);
        cf5.setBackColor(new Color(0,255,255));

        //Add conditional format 4 and specify data range
        XlsConditionalFormats format4 = sheet.getConditionalFormats().add();
        format4.addRange(sheet.getCellRange("D2:D12"));
        //Highlight cells with duplicate values
        IConditionalFormat cf6 = format4.addDuplicateValuesCondition();
        cf6.setFormatType(ConditionalFormatType.DuplicateValues);
        cf6.setBackColor(new Color(255,228,196));

        //Add conditional format 5 and specify data range
        XlsConditionalFormats format5 = sheet.getConditionalFormats().add();
        format5.addRange(sheet.getCellRange("E2:E12"));
        //Cell highlighting day of week
        IConditionalFormat cf7 = format5.addTimePeriodCondition(TimePeriodType.ThisWeek);
        cf7.setBackColor(new Color(255,165,0));

        //Save document
        wb.saveToFile("AddConditionalFormat.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Conditional format application effect:

Java example 2 -- conditional format of applying cell value, formula and data bar type

import com.spire.xls.*;

import java.awt.*;

public class AddConditionalFormat {
    public static void main(String[] args) {
        //Create instance, load test document
        Workbook wb = new Workbook();
        wb.loadFromFile("sample.xlsx");

        //Get first sheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Get data range of application condition format
        CellRange range = sheet.getCellRange("A2:H27");

        //Add conditional format 1
        ConditionalFormatWrapper format1 = range.getConditionalFormats().addCondition();
        //Conditional format type 1 based on cell value
        format1.setFormatType(ConditionalFormatType.CellValue);
        //Bold the cells with values between 60 and 90 and set the font color to orange
        format1.setFirstFormula("90");
        format1.setSecondFormula("100");
        format1.setOperator(ComparisonOperatorType.Between);
        format1.setFontColor(new Color(30,144,255));
        //format1.setBackColor(Color.orange);

        //Add conditional format 2
        ConditionalFormatWrapper format2 = range.getConditionalFormats().addCondition();
        format2.setFormatType(ConditionalFormatType.CellValue);
        format2.setFirstFormula("60");
        format2.setOperator(ComparisonOperatorType.Less);
        format2.setFontColor(Color.red);
        //format2.setBackColor(Color.red);
        format2.isBold();
        //Add border format (border color, border type) to conditional format 2
        format2.setLeftBorderColor(Color.red);
        format2.setRightBorderColor(new Color(0,0,139));
        format2.setTopBorderColor(new Color(123,104,238));
        format2.setBottomBorderColor(new Color(50,205,50));
        format2.setLeftBorderStyle(LineStyleType.Medium);
        format2.setRightBorderStyle(LineStyleType.Thick);
        format2.setTopBorderStyle(LineStyleType.Double);
        format2.setBottomBorderStyle(LineStyleType.Double);

        //The type of condition format 3 is formula
        ConditionalFormatWrapper format3 = range.getConditionalFormats().addCondition();
        format3.setFormatType(ConditionalFormatType.Formula);

        //Custom formula fills the background color with the row of cells below 60
        format3.setFirstFormula("=OR($C2<60,$D2<60,$E2<60,$F2<60,$G2<60,$H2<60)");
        format3.setBackColor(Color.lightGray);

        //Get the second sheet
        Worksheet sheet2 = wb.getWorksheets().get(1);

        //Get data range of application condition format
        CellRange range2 = sheet2.getCellRange("B2:D7");

        //Add condition type 4 as data bars
        ConditionalFormatWrapper format4 = range2.getConditionalFormats().addCondition();
        format4.setFormatType(ConditionalFormatType.DataBar);
        format4.getDataBar().setBarColor(new Color(152,251,152));

        //Save document
        wb.saveToFile("AddConditionalFormat2.xlsx", ExcelVersion.Version2013);
        wb.dispose();
    }
}

Conditional format application effect:


Java example 3 -- deleting conditional formats
(the test document here takes the document generated in example 1 as an example)

import com.spire.xls.*;

public class RemoveConditionalFormat {
    public static void main(String[] args) {
        Workbook wb = new Workbook();
        wb.loadFromFile("AddConditionalFormat.xlsx");

        //Get first sheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Remove conditional formatting from the specified cell range
        sheet.getCellRange("A5:H5").getConditionalFormats().removeAt(3);

        //Save and open document
        wb.saveToFile("RemoveConditionalFormat.xlsx", ExcelVersion.Version2010);
        wb.dispose();
    }
}

Conditional format delete effect:

(end)

Tags: Java Maven Excel less

Posted on Thu, 19 Mar 2020 11:46:42 -0400 by darkninja_com