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

Example 51 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testCreatePatternFormatting.

@Test
public void testCreatePatternFormatting() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
    PatternFormatting patternFmt = rule1.createPatternFormatting();
    assertEquals(0, patternFmt.getFillBackgroundColor());
    patternFmt.setFillBackgroundColor(IndexedColors.RED.index);
    assertEquals(IndexedColors.RED.index, patternFmt.getFillBackgroundColor());
    assertEquals(0, patternFmt.getFillForegroundColor());
    patternFmt.setFillForegroundColor(IndexedColors.BLUE.index);
    assertEquals(IndexedColors.BLUE.index, patternFmt.getFillForegroundColor());
    assertEquals(PatternFormatting.NO_FILL, patternFmt.getFillPattern());
    patternFmt.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    assertEquals(PatternFormatting.SOLID_FOREGROUND, patternFmt.getFillPattern());
    patternFmt.setFillPattern(PatternFormatting.NO_FILL);
    assertEquals(PatternFormatting.NO_FILL, patternFmt.getFillPattern());
    patternFmt.setFillPattern(PatternFormatting.BRICKS);
    assertEquals(PatternFormatting.BRICKS, patternFmt.getFillPattern());
    ConditionalFormattingRule[] cfRules = { rule1 };
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A5") };
    sheetCF.addConditionalFormatting(regions, cfRules);
    // Verification
    ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
    assertNotNull(cf);
    assertEquals(1, cf.getNumberOfRules());
    PatternFormatting r1fp = cf.getRule(0).getPatternFormatting();
    assertNotNull(r1fp);
    assertEquals(IndexedColors.RED.index, r1fp.getFillBackgroundColor());
    assertEquals(IndexedColors.BLUE.index, r1fp.getFillForegroundColor());
    assertEquals(PatternFormatting.BRICKS, r1fp.getFillPattern());
    workbook.close();
}

Example 52 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testCreateBorderFormatting.

@Test
public void testCreateBorderFormatting() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
    BorderFormatting borderFmt = rule1.createBorderFormatting();
    assertEquals(BorderStyle.NONE, borderFmt.getBorderBottomEnum());
    borderFmt.setBorderBottom(BorderStyle.DOTTED);
    assertEquals(BorderStyle.DOTTED, borderFmt.getBorderBottomEnum());
    borderFmt.setBorderBottom(BorderStyle.NONE);
    assertEquals(BorderStyle.NONE, borderFmt.getBorderBottomEnum());
    borderFmt.setBorderBottom(BorderStyle.THICK);
    assertEquals(BorderStyle.THICK, borderFmt.getBorderBottomEnum());
    assertEquals(BorderStyle.NONE, borderFmt.getBorderTopEnum());
    borderFmt.setBorderTop(BorderStyle.DOTTED);
    assertEquals(BorderStyle.DOTTED, borderFmt.getBorderTopEnum());
    borderFmt.setBorderTop(BorderStyle.NONE);
    assertEquals(BorderStyle.NONE, borderFmt.getBorderTopEnum());
    borderFmt.setBorderTop(BorderStyle.THICK);
    assertEquals(BorderStyle.THICK, borderFmt.getBorderTopEnum());
    assertEquals(BorderStyle.NONE, borderFmt.getBorderLeftEnum());
    borderFmt.setBorderLeft(BorderStyle.DOTTED);
    assertEquals(BorderStyle.DOTTED, borderFmt.getBorderLeftEnum());
    borderFmt.setBorderLeft(BorderStyle.NONE);
    assertEquals(BorderStyle.NONE, borderFmt.getBorderLeftEnum());
    borderFmt.setBorderLeft(BorderStyle.THIN);
    assertEquals(BorderStyle.THIN, borderFmt.getBorderLeftEnum());
    assertEquals(BorderStyle.NONE, borderFmt.getBorderRightEnum());
    borderFmt.setBorderRight(BorderStyle.DOTTED);
    assertEquals(BorderStyle.DOTTED, borderFmt.getBorderRightEnum());
    borderFmt.setBorderRight(BorderStyle.NONE);
    assertEquals(BorderStyle.NONE, borderFmt.getBorderRightEnum());
    borderFmt.setBorderRight(BorderStyle.HAIR);
    assertEquals(BorderStyle.HAIR, borderFmt.getBorderRightEnum());
    ConditionalFormattingRule[] cfRules = { rule1 };
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A5") };
    sheetCF.addConditionalFormatting(regions, cfRules);
    // Verification
    ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
    assertNotNull(cf);
    assertEquals(1, cf.getNumberOfRules());
    BorderFormatting r1fp = cf.getRule(0).getBorderFormatting();
    assertNotNull(r1fp);
    assertEquals(BorderStyle.THICK, r1fp.getBorderBottomEnum());
    assertEquals(BorderStyle.THICK, r1fp.getBorderTopEnum());
    assertEquals(BorderStyle.THIN, r1fp.getBorderLeftEnum());
    assertEquals(BorderStyle.HAIR, r1fp.getBorderRightEnum());
    workbook.close();
}

Example 53 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testBooleanFormulaConditions.

/**
     * Test format conditions based on a boolean formula
     */
@Test
public void testBooleanFormulaConditions() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sh = wb.createSheet();
    SheetConditionalFormatting sheetCF = sh.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("SUM(A1:A5)>10");
    assertEquals(ConditionType.FORMULA, rule1.getConditionType());
    assertEquals("SUM(A1:A5)>10", rule1.getFormula1());
    int formatIndex1 = sheetCF.addConditionalFormatting(new CellRangeAddress[] { CellRangeAddress.valueOf("B1"), CellRangeAddress.valueOf("C3") }, rule1);
    assertEquals(0, formatIndex1);
    assertEquals(1, sheetCF.getNumConditionalFormattings());
    CellRangeAddress[] ranges1 = sheetCF.getConditionalFormattingAt(formatIndex1).getFormattingRanges();
    assertEquals(2, ranges1.length);
    assertEquals("B1", ranges1[0].formatAsString());
    assertEquals("C3", ranges1[1].formatAsString());
    // adjacent address are merged
    int formatIndex2 = sheetCF.addConditionalFormatting(new CellRangeAddress[] { CellRangeAddress.valueOf("B1"), CellRangeAddress.valueOf("B2"), CellRangeAddress.valueOf("B3") }, rule1);
    assertEquals(1, formatIndex2);
    assertEquals(2, sheetCF.getNumConditionalFormattings());
    CellRangeAddress[] ranges2 = sheetCF.getConditionalFormattingAt(formatIndex2).getFormattingRanges();
    assertEquals(1, ranges2.length);
    assertEquals("B1:B3", ranges2[0].formatAsString());
    wb.close();
}

Example 54 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testCreateFontFormatting.

@Test
public void testCreateFontFormatting() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "7");
    FontFormatting fontFmt = rule1.createFontFormatting();
    assertFalse(fontFmt.isItalic());
    assertFalse(fontFmt.isBold());
    fontFmt.setFontStyle(true, true);
    assertTrue(fontFmt.isItalic());
    assertTrue(fontFmt.isBold());
    // not modified
    assertEquals(-1, fontFmt.getFontHeight());
    fontFmt.setFontHeight(200);
    assertEquals(200, fontFmt.getFontHeight());
    fontFmt.setFontHeight(100);
    assertEquals(100, fontFmt.getFontHeight());
    assertEquals(FontFormatting.SS_NONE, fontFmt.getEscapementType());
    fontFmt.setEscapementType(FontFormatting.SS_SUB);
    assertEquals(FontFormatting.SS_SUB, fontFmt.getEscapementType());
    fontFmt.setEscapementType(FontFormatting.SS_NONE);
    assertEquals(FontFormatting.SS_NONE, fontFmt.getEscapementType());
    fontFmt.setEscapementType(FontFormatting.SS_SUPER);
    assertEquals(FontFormatting.SS_SUPER, fontFmt.getEscapementType());
    assertEquals(FontFormatting.U_NONE, fontFmt.getUnderlineType());
    fontFmt.setUnderlineType(FontFormatting.U_SINGLE);
    assertEquals(FontFormatting.U_SINGLE, fontFmt.getUnderlineType());
    fontFmt.setUnderlineType(FontFormatting.U_NONE);
    assertEquals(FontFormatting.U_NONE, fontFmt.getUnderlineType());
    fontFmt.setUnderlineType(FontFormatting.U_DOUBLE);
    assertEquals(FontFormatting.U_DOUBLE, fontFmt.getUnderlineType());
    assertEquals(-1, fontFmt.getFontColorIndex());
    fontFmt.setFontColorIndex(IndexedColors.RED.index);
    assertEquals(IndexedColors.RED.index, fontFmt.getFontColorIndex());
    fontFmt.setFontColorIndex(IndexedColors.AUTOMATIC.index);
    assertEquals(IndexedColors.AUTOMATIC.index, fontFmt.getFontColorIndex());
    fontFmt.setFontColorIndex(IndexedColors.BLUE.index);
    assertEquals(IndexedColors.BLUE.index, fontFmt.getFontColorIndex());
    ConditionalFormattingRule[] cfRules = { rule1 };
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A5") };
    sheetCF.addConditionalFormatting(regions, cfRules);
    // Verification
    ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
    assertNotNull(cf);
    assertEquals(1, cf.getNumberOfRules());
    FontFormatting r1fp = cf.getRule(0).getFontFormatting();
    assertNotNull(r1fp);
    assertTrue(r1fp.isItalic());
    assertTrue(r1fp.isBold());
    assertEquals(FontFormatting.SS_SUPER, r1fp.getEscapementType());
    assertEquals(FontFormatting.U_DOUBLE, r1fp.getUnderlineType());
    assertEquals(IndexedColors.BLUE.index, r1fp.getFontColorIndex());
    workbook.close();
}

Example 55 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testRead.

protected void testRead(String filename) throws IOException {
    Workbook wb = _testDataProvider.openSampleWorkbook(filename);
    Sheet sh = wb.getSheet("CF");
    SheetConditionalFormatting sheetCF = sh.getSheetConditionalFormatting();
    assertEquals(3, sheetCF.getNumConditionalFormattings());
    ConditionalFormatting cf1 = sheetCF.getConditionalFormattingAt(0);
    assertEquals(2, cf1.getNumberOfRules());
    CellRangeAddress[] regions1 = cf1.getFormattingRanges();
    assertEquals(1, regions1.length);
    assertEquals("A1:A8", regions1[0].formatAsString());
    // CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
    ConditionalFormattingRule rule1 = cf1.getRule(0);
    assertEquals(ConditionType.CELL_VALUE_IS, rule1.getConditionType());
    assertEquals(ComparisonOperator.GT, rule1.getComparisonOperation());
    assertEquals("3", rule1.getFormula1());
    assertNull(rule1.getFormula2());
    // fills and borders are not set
    assertNull(rule1.getPatternFormatting());
    assertNull(rule1.getBorderFormatting());
    FontFormatting fmt1 = rule1.getFontFormatting();
    //        assertEquals(IndexedColors.GREEN.index, fmt1.getFontColorIndex());
    assertTrue(fmt1.isBold());
    assertFalse(fmt1.isItalic());
    ConditionalFormattingRule rule2 = cf1.getRule(1);
    assertEquals(ConditionType.CELL_VALUE_IS, rule2.getConditionType());
    assertEquals(ComparisonOperator.LT, rule2.getComparisonOperation());
    assertEquals("-3", rule2.getFormula1());
    assertNull(rule2.getFormula2());
    assertNull(rule2.getPatternFormatting());
    assertNull(rule2.getBorderFormatting());
    FontFormatting fmt2 = rule2.getFontFormatting();
    //        assertEquals(IndexedColors.RED.index, fmt2.getFontColorIndex());
    assertTrue(fmt2.isBold());
    assertTrue(fmt2.isItalic());
    ConditionalFormatting cf2 = sheetCF.getConditionalFormattingAt(1);
    assertEquals(1, cf2.getNumberOfRules());
    CellRangeAddress[] regions2 = cf2.getFormattingRanges();
    assertEquals(1, regions2.length);
    assertEquals("B9", regions2[0].formatAsString());
    ConditionalFormattingRule rule3 = cf2.getRule(0);
    assertEquals(ConditionType.FORMULA, rule3.getConditionType());
    assertEquals(ComparisonOperator.NO_COMPARISON, rule3.getComparisonOperation());
    assertEquals("$A$8>5", rule3.getFormula1());
    assertNull(rule3.getFormula2());
    FontFormatting fmt3 = rule3.getFontFormatting();
    //        assertEquals(IndexedColors.RED.index, fmt3.getFontColorIndex());
    assertTrue(fmt3.isBold());
    assertTrue(fmt3.isItalic());
    PatternFormatting fmt4 = rule3.getPatternFormatting();
    //        assertEquals(IndexedColors.LIGHT_CORNFLOWER_BLUE.index, fmt4.getFillBackgroundColor());
    //        assertEquals(IndexedColors.AUTOMATIC.index, fmt4.getFillForegroundColor());
    assertEquals(PatternFormatting.NO_FILL, fmt4.getFillPattern());
    // borders are not set
    assertNull(rule3.getBorderFormatting());
    ConditionalFormatting cf3 = sheetCF.getConditionalFormattingAt(2);
    CellRangeAddress[] regions3 = cf3.getFormattingRanges();
    assertEquals(1, regions3.length);
    assertEquals("B1:B7", regions3[0].formatAsString());
    assertEquals(2, cf3.getNumberOfRules());
    ConditionalFormattingRule rule4 = cf3.getRule(0);
    assertEquals(ConditionType.CELL_VALUE_IS, rule4.getConditionType());
    assertEquals(ComparisonOperator.LE, rule4.getComparisonOperation());
    assertEquals("\"AAA\"", rule4.getFormula1());
    assertNull(rule4.getFormula2());
    ConditionalFormattingRule rule5 = cf3.getRule(1);
    assertEquals(ConditionType.CELL_VALUE_IS, rule5.getConditionType());
    assertEquals(ComparisonOperator.BETWEEN, rule5.getComparisonOperation());
    assertEquals("\"A\"", rule5.getFormula1());
    assertEquals("\"AAA\"", rule5.getFormula2());
    wb.close();
}