Examples with CellRangeAddress org.apache.poi.ss.util.CellRangeAddress used on opensource projects

Example 16 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class XSSFDataValidation method prettyPrint.

public String prettyPrint() {
    StringBuilder builder = new StringBuilder();
    for (CellRangeAddress address : regions.getCellRangeAddresses()) {
        builder.append(address.formatAsString());
    }
    builder.append(" => ");
    builder.append(this.validationConstraint.prettyPrint());
    return builder.toString();
}

Example 17 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class ConditionalFormats method expiry.

/**
     *  Use Excel conditional formatting to highlight payments that are due in the next thirty days.
     *  In this example, Due dates are entered in cells A2:A4.
     */
static void expiry(Sheet sheet) {
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));
    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");
    for (int rownum = 1; rownum <= 3; rownum++) {
        sheet.getRow(rownum).getCell(0).setCellStyle(style);
    }
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

Example 18 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class ConditionalFormats method iconSets.

/**
     * Icon Sets / Multi-States allow you to have icons shown which vary
     *  based on the values, eg Red traffic light / Yellow traffic light /
     *  Green traffic light
     */
static void iconSets(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Icon Sets");
    Row r = sheet.createRow(1);
    r.createCell(0).setCellValue("Reds");
    r.createCell(1).setCellValue(0);
    r.createCell(2).setCellValue(0);
    r.createCell(3).setCellValue(0);
    r = sheet.createRow(2);
    r.createCell(0).setCellValue("Yellows");
    r.createCell(1).setCellValue(5);
    r.createCell(2).setCellValue(5);
    r.createCell(3).setCellValue(5);
    r = sheet.createRow(3);
    r.createCell(0).setCellValue("Greens");
    r.createCell(1).setCellValue(10);
    r.createCell(2).setCellValue(10);
    r.createCell(3).setCellValue(10);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("B1:B4") };
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_TRAFFIC_LIGHTS);
    IconMultiStateFormatting im1 = rule1.getMultiStateFormatting();
    im1.getThresholds()[0].setRangeType(RangeType.MIN);
    im1.getThresholds()[1].setRangeType(RangeType.PERCENT);
    im1.getThresholds()[1].setValue(33d);
    im1.getThresholds()[2].setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule1);
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C1:C4") };
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_FLAGS);
    IconMultiStateFormatting im2 = rule1.getMultiStateFormatting();
    im2.getThresholds()[0].setRangeType(RangeType.PERCENT);
    im2.getThresholds()[0].setValue(0d);
    im2.getThresholds()[1].setRangeType(RangeType.PERCENT);
    im2.getThresholds()[1].setValue(33d);
    im2.getThresholds()[2].setRangeType(RangeType.PERCENT);
    im2.getThresholds()[2].setValue(67d);
    sheetCF.addConditionalFormatting(regions, rule2);
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D1:D4") };
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(IconSet.GYR_3_SYMBOLS_CIRCLE);
    IconMultiStateFormatting im3 = rule1.getMultiStateFormatting();
    im3.setIconOnly(true);
    im3.getThresholds()[0].setRangeType(RangeType.MIN);
    im3.getThresholds()[1].setRangeType(RangeType.NUMBER);
    im3.getThresholds()[1].setValue(3d);
    im3.getThresholds()[2].setRangeType(RangeType.NUMBER);
    im3.getThresholds()[2].setValue(7d);
    sheetCF.addConditionalFormatting(regions, rule3);
}

Example 19 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class ConditionalFormats method errors.

/**
     *  Use Excel conditional formatting to check for errors,
     *  and change the font colour to match the cell colour.
     *  In this example, if formula result is  #DIV/0! then it will have white font colour.
     */
static void errors(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue(84);
    sheet.createRow(1).createCell(0).setCellValue(0);
    sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)");
    sheet.createRow(3).createCell(0).setCellValue(0);
    sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)");
    sheet.createRow(5).createCell(0).setCellValue(41);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =ISERROR(C2)   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontColorIndex(IndexedColors.WHITE.index);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(2).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
    sheet.getRow(4).createCell(1).setCellValue("<== The error in this cell is hidden. Condition: Formula Is   =ISERROR(C2)   (White Font)");
}

Example 20 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class ConditionalFormats method multiCell.

/**
     * Highlight multiple cells based on a formula
     */
static void multiCell(Sheet sheet) {
    // header row
    Row row0 = sheet.createRow(0);
    row0.createCell(0).setCellValue("Units");
    row0.createCell(1).setCellValue("Cost");
    row0.createCell(2).setCellValue("Total");
    Row row1 = sheet.createRow(1);
    row1.createCell(0).setCellValue(71);
    row1.createCell(1).setCellValue(29);
    row1.createCell(2).setCellValue(2059);
    Row row2 = sheet.createRow(2);
    row2.createCell(0).setCellValue(85);
    row2.createCell(1).setCellValue(29);
    row2.createCell(2).setCellValue(2059);
    Row row3 = sheet.createRow(3);
    row3.createCell(0).setCellValue(71);
    row3.createCell(1).setCellValue(29);
    row3.createCell(2).setCellValue(2059);
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    // Condition 1: Formula Is   =$B2>75   (Blue Fill)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75");
    PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.BLUE.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:C4") };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75   (Blue Fill)");
}