Examples with ExtendedColor org.apache.poi.ss.usermodel.ExtendedColor used on opensource projects

Example 1 with ExtendedColor

use of org.apache.poi.ss.usermodel.ExtendedColor in project poi by apache.

the class ConditionalFormats method dataBars.

/**
     * DataBars / Data-Bars allow you to have bars shown vary
     *  based on the values, from full to empty
     */
static void dataBars(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Data Bars");
    Row r = sheet.createRow(1);
    r.createCell(1).setCellValue("Green Positive");
    r.createCell(2).setCellValue("Blue Mix");
    r.createCell(3).setCellValue("Red Negative");
    r = sheet.createRow(2);
    r.createCell(1).setCellValue(0);
    r.createCell(2).setCellValue(0);
    r.createCell(3).setCellValue(0);
    r = sheet.createRow(3);
    r.createCell(1).setCellValue(5);
    r.createCell(2).setCellValue(-5);
    r.createCell(3).setCellValue(-5);
    r = sheet.createRow(4);
    r.createCell(1).setCellValue(10);
    r.createCell(2).setCellValue(10);
    r.createCell(3).setCellValue(-10);
    r = sheet.createRow(5);
    r.createCell(1).setCellValue(5);
    r.createCell(2).setCellValue(5);
    r.createCell(3).setCellValue(-5);
    r = sheet.createRow(6);
    r.createCell(1).setCellValue(20);
    r.createCell(2).setCellValue(-10);
    r.createCell(3).setCellValue(-20);
    sheet.setColumnWidth(0, 3000);
    sheet.setColumnWidth(1, 5000);
    sheet.setColumnWidth(2, 5000);
    sheet.setColumnWidth(3, 5000);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ExtendedColor color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FF63BE7B");
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:B7") };
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db1 = rule1.getDataBarFormatting();
    db1.getMinThreshold().setRangeType(RangeType.MIN);
    db1.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule1);
    color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FF5A8AC6");
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C2:C7") };
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db2 = rule2.getDataBarFormatting();
    db2.getMinThreshold().setRangeType(RangeType.MIN);
    db2.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule2);
    color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FFF8696B");
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D2:D7") };
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db3 = rule3.getDataBarFormatting();
    db3.getMinThreshold().setRangeType(RangeType.MIN);
    db3.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule3);
}

Example 2 with ExtendedColor

use of org.apache.poi.ss.usermodel.ExtendedColor in project poi by apache.

the class ConditionalFormats method colourScales.

/**
     * Color Scales / Colour Scales / Colour Gradients allow you shade the
     *  background colour of the cell based on the values, eg from Red to
     *  Yellow to Green.
     */
static void colourScales(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Colour Scales");
    Row r = sheet.createRow(1);
    r.createCell(0).setCellValue("Red-Yellow-Green");
    for (int i = 1; i <= 7; i++) {
        r.createCell(i).setCellValue((i - 1) * 5);
    }
    r = sheet.createRow(2);
    r.createCell(0).setCellValue("Red-White-Blue");
    for (int i = 1; i <= 9; i++) {
        r.createCell(i).setCellValue((i - 1) * 5);
    }
    r = sheet.createRow(3);
    r.createCell(0).setCellValue("Blue-Green");
    for (int i = 1; i <= 16; i++) {
        r.createCell(i).setCellValue((i - 1));
    }
    sheet.setColumnWidth(0, 5000);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:H2") };
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs1 = rule1.getColorScaleFormatting();
    cs1.getThresholds()[0].setRangeType(RangeType.MIN);
    cs1.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
    cs1.getThresholds()[1].setValue(50d);
    cs1.getThresholds()[2].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs1.getColors()[0]).setARGBHex("FFF8696B");
    ((ExtendedColor) cs1.getColors()[1]).setARGBHex("FFFFEB84");
    ((ExtendedColor) cs1.getColors()[2]).setARGBHex("FF63BE7B");
    sheetCF.addConditionalFormatting(regions, rule1);
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B3:J3") };
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs2 = rule2.getColorScaleFormatting();
    cs2.getThresholds()[0].setRangeType(RangeType.MIN);
    cs2.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
    cs2.getThresholds()[1].setValue(50d);
    cs2.getThresholds()[2].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs2.getColors()[0]).setARGBHex("FFF8696B");
    ((ExtendedColor) cs2.getColors()[1]).setARGBHex("FFFCFCFF");
    ((ExtendedColor) cs2.getColors()[2]).setARGBHex("FF5A8AC6");
    sheetCF.addConditionalFormatting(regions, rule2);
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B4:Q4") };
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs3 = rule3.getColorScaleFormatting();
    cs3.setNumControlPoints(2);
    cs3.getThresholds()[0].setRangeType(RangeType.MIN);
    cs3.getThresholds()[1].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs3.getColors()[0]).setARGBHex("FF5A8AC6");
    ((ExtendedColor) cs3.getColors()[1]).setARGBHex("FF63BE7B");
    sheetCF.addConditionalFormatting(regions, rule3);
}