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

Example 56 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testClone.

@Test
public void testClone() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet();
    String formula = "7";
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(formula);
    FontFormatting fontFmt = rule1.createFontFormatting();
    fontFmt.setFontStyle(true, false);
    PatternFormatting patternFmt = rule1.createPatternFormatting();
    patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "1", "2");
    ConditionalFormattingRule[] cfRules = { rule1, rule2 };
    short col = 1;
    CellRangeAddress[] regions = { new CellRangeAddress(0, 65535, col, col) };
    sheetCF.addConditionalFormatting(regions, cfRules);
    try {
        wb.cloneSheet(0);
        assertEquals(2, wb.getNumberOfSheets());
    } catch (RuntimeException e) {
        if (e.getMessage().indexOf("needs to define a clone method") > 0) {
            fail("Identified bug 45682");
        }
        throw e;
    } finally {
        wb.close();
    }
}

Example 57 with CellRangeAddress

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

the class BaseTestBugzillaIssues method bug22720.

/**
     * Merged regions were being removed from the parent in cloned sheets
     */
@Test
public void bug22720() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    wb.createSheet("TEST");
    Sheet template = wb.getSheetAt(0);
    template.addMergedRegion(new CellRangeAddress(0, 1, 0, 2));
    template.addMergedRegion(new CellRangeAddress(2, 3, 0, 2));
    Sheet clone = wb.cloneSheet(0);
    int originalMerged = template.getNumMergedRegions();
    assertEquals("2 merged regions", 2, originalMerged);
    //remove merged regions from clone
    for (int i = template.getNumMergedRegions() - 1; i >= 0; i--) {
        clone.removeMergedRegion(i);
    }
    assertEquals("Original Sheet's Merged Regions were removed", originalMerged, template.getNumMergedRegions());
    //check if template's merged regions are OK
    if (template.getNumMergedRegions() > 0) {
        // fetch the first merged region...EXCEPTION OCCURS HERE
        template.getMergedRegion(0);
    }
    wb.close();
}

Example 58 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testCreateColorScaleFormatting.

@Test
public void testCreateColorScaleFormatting() throws IOException {
    Workbook wb1 = _testDataProvider.createWorkbook();
    Sheet sheet = wb1.createSheet();
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting clrFmt = rule1.getColorScaleFormatting();
    assertEquals(3, clrFmt.getNumControlPoints());
    assertEquals(3, clrFmt.getColors().length);
    assertEquals(3, clrFmt.getThresholds().length);
    clrFmt.getThresholds()[0].setRangeType(RangeType.MIN);
    clrFmt.getThresholds()[1].setRangeType(RangeType.NUMBER);
    clrFmt.getThresholds()[1].setValue(10d);
    clrFmt.getThresholds()[2].setRangeType(RangeType.MAX);
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A5") };
    sheetCF.addConditionalFormatting(regions, rule1);
    // Save, re-load and re-check
    Workbook wb2 = _testDataProvider.writeOutAndReadBack(wb1);
    wb1.close();
    sheet = wb2.getSheetAt(0);
    sheetCF = sheet.getSheetConditionalFormatting();
    assertEquals(1, sheetCF.getNumConditionalFormattings());
    ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
    assertEquals(1, cf.getNumberOfRules());
    rule1 = cf.getRule(0);
    clrFmt = rule1.getColorScaleFormatting();
    assertEquals(ConditionType.COLOR_SCALE, rule1.getConditionType());
    assertEquals(3, clrFmt.getNumControlPoints());
    assertEquals(3, clrFmt.getColors().length);
    assertEquals(3, clrFmt.getThresholds().length);
    assertEquals(RangeType.MIN, clrFmt.getThresholds()[0].getRangeType());
    assertEquals(RangeType.NUMBER, clrFmt.getThresholds()[1].getRangeType());
    assertEquals(RangeType.MAX, clrFmt.getThresholds()[2].getRangeType());
    assertEquals(null, clrFmt.getThresholds()[0].getValue());
    assertEquals(10d, clrFmt.getThresholds()[1].getValue(), 0);
    assertEquals(null, clrFmt.getThresholds()[2].getValue());
    wb2.close();
}

Example 59 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testShiftRows.

@Test
public void testShiftRows() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet();
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "SUM(A10:A15)", "1+SUM(B16:B30)");
    FontFormatting fontFmt = rule1.createFontFormatting();
    fontFmt.setFontStyle(true, false);
    PatternFormatting patternFmt = rule1.createPatternFormatting();
    patternFmt.setFillBackgroundColor(IndexedColors.YELLOW.index);
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.BETWEEN, "SUM(A10:A15)", "1+SUM(B16:B30)");
    BorderFormatting borderFmt = rule2.createBorderFormatting();
    borderFmt.setBorderDiagonal(BorderStyle.MEDIUM);
    CellRangeAddress[] regions = { // A3:A5
    new CellRangeAddress(2, 4, 0, 0) };
    sheetCF.addConditionalFormatting(regions, rule1);
    sheetCF.addConditionalFormatting(regions, rule2);
    // This row-shift should destroy the CF region
    sheet.shiftRows(10, 20, -9);
    assertEquals(0, sheetCF.getNumConditionalFormattings());
    // re-add the CF
    sheetCF.addConditionalFormatting(regions, rule1);
    sheetCF.addConditionalFormatting(regions, rule2);
    // This row shift should only affect the formulas
    sheet.shiftRows(14, 17, 8);
    ConditionalFormatting cf1 = sheetCF.getConditionalFormattingAt(0);
    assertEquals("SUM(A10:A23)", cf1.getRule(0).getFormula1());
    assertEquals("1+SUM(B24:B30)", cf1.getRule(0).getFormula2());
    ConditionalFormatting cf2 = sheetCF.getConditionalFormattingAt(1);
    assertEquals("SUM(A10:A23)", cf2.getRule(0).getFormula1());
    assertEquals("1+SUM(B24:B30)", cf2.getRule(0).getFormula2());
    sheet.shiftRows(0, 8, 21);
    cf1 = sheetCF.getConditionalFormattingAt(0);
    assertEquals("SUM(A10:A21)", cf1.getRule(0).getFormula1());
    assertEquals("1+SUM(#REF!)", cf1.getRule(0).getFormula2());
    cf2 = sheetCF.getConditionalFormattingAt(1);
    assertEquals("SUM(A10:A21)", cf2.getRule(0).getFormula1());
    assertEquals("1+SUM(#REF!)", cf2.getRule(0).getFormula2());
    wb.close();
}

Example 60 with CellRangeAddress

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

the class BaseTestConditionalFormatting method testSetCellRangeAddresswithSingleRange.

@Test
public void testSetCellRangeAddresswithSingleRange() throws Exception {
    Workbook wb = _testDataProvider.createWorkbook();
    final Sheet sheet = wb.createSheet("S1");
    final SheetConditionalFormatting cf = sheet.getSheetConditionalFormatting();
    assertEquals(0, cf.getNumConditionalFormattings());
    ConditionalFormattingRule rule1 = cf.createConditionalFormattingRule("$A$1>0");
    cf.addConditionalFormatting(new CellRangeAddress[] { CellRangeAddress.valueOf("A1:A5") }, rule1);
    assertEquals(1, cf.getNumConditionalFormattings());
    ConditionalFormatting readCf = cf.getConditionalFormattingAt(0);
    CellRangeAddress[] formattingRanges = readCf.getFormattingRanges();
    assertEquals(1, formattingRanges.length);
    CellRangeAddress formattingRange = formattingRanges[0];
    assertEquals("A1:A5", formattingRange.formatAsString());
    readCf.setFormattingRanges(new CellRangeAddress[] { CellRangeAddress.valueOf("A1:A6") });
    readCf = cf.getConditionalFormattingAt(0);
    formattingRanges = readCf.getFormattingRanges();
    assertEquals(1, formattingRanges.length);
    formattingRange = formattingRanges[0];
    assertEquals("A1:A6", formattingRange.formatAsString());
}