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

Example 81 with CellRangeAddress

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

the class BaseTestSheet method shiftMerged.

@Test
public void shiftMerged() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    CreationHelper factory = wb.getCreationHelper();
    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue(factory.createRichTextString("first row, first cell"));
    row = sheet.createRow(1);
    cell = row.createCell(1);
    cell.setCellValue(factory.createRichTextString("second row, second cell"));
    CellRangeAddress region = CellRangeAddress.valueOf("A2:B2");
    sheet.addMergedRegion(region);
    sheet.shiftRows(1, 1, 1);
    region = sheet.getMergedRegion(0);
    CellRangeAddress expectedRegion = CellRangeAddress.valueOf("A3:B3");
    assertEquals("Merged region should shift down a row", expectedRegion, region);
    wb.close();
}

Example 82 with CellRangeAddress

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

the class BaseTestSheet method addOverlappingMergedRegions.

/**
     * Disallow creating wholly or partially overlapping merged regions
     * as this results in a corrupted workbook
     */
@Test
public void addOverlappingMergedRegions() throws IOException {
    final Workbook wb = _testDataProvider.createWorkbook();
    final Sheet sheet = wb.createSheet();
    //A1:B2
    final CellRangeAddress baseRegion = new CellRangeAddress(0, 1, 0, 1);
    sheet.addMergedRegion(baseRegion);
    try {
        //A1:B2
        final CellRangeAddress duplicateRegion = new CellRangeAddress(0, 1, 0, 1);
        sheet.addMergedRegion(duplicateRegion);
        fail("Should not be able to add a merged region (" + duplicateRegion.formatAsString() + ") " + "if sheet already contains the same merged region (" + baseRegion.formatAsString() + ")");
    } catch (final IllegalStateException e) {
    // expected here
    }
    try {
        //B2:C3
        final CellRangeAddress partiallyOverlappingRegion = new CellRangeAddress(1, 2, 1, 2);
        sheet.addMergedRegion(partiallyOverlappingRegion);
        fail("Should not be able to add a merged region (" + partiallyOverlappingRegion.formatAsString() + ") " + "if it partially overlaps with an existing merged region (" + baseRegion.formatAsString() + ")");
    } catch (final IllegalStateException e) {
    // expected here
    }
    try {
        //A1:A2
        final CellRangeAddress subsetRegion = new CellRangeAddress(0, 1, 0, 0);
        sheet.addMergedRegion(subsetRegion);
        fail("Should not be able to add a merged region (" + subsetRegion.formatAsString() + ") " + "if it is a formal subset of an existing merged region (" + baseRegion.formatAsString() + ")");
    } catch (final IllegalStateException e) {
    // expected here
    }
    try {
        //A1:C3
        final CellRangeAddress supersetRegion = new CellRangeAddress(0, 2, 0, 2);
        sheet.addMergedRegion(supersetRegion);
        fail("Should not be able to add a merged region (" + supersetRegion.formatAsString() + ") " + "if it is a formal superset of an existing merged region (" + baseRegion.formatAsString() + ")");
    } catch (final IllegalStateException e) {
    // expected here
    }
    final CellRangeAddress disjointRegion = new CellRangeAddress(10, 11, 10, 11);
    sheet.addMergedRegion(disjointRegion);
    wb.close();
}

Example 83 with CellRangeAddress

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

the class BaseTestSheetShiftRows method shiftMergedRowsToMergedRowsDown.

@Test
public void shiftMergedRowsToMergedRowsDown() throws IOException {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet = wb.createSheet("test");
    // populate sheet cells
    populateSheetCells(sheet);
    CellRangeAddress A1_E1 = new CellRangeAddress(0, 0, 0, 4);
    CellRangeAddress A2_C2 = new CellRangeAddress(1, 1, 0, 2);
    sheet.addMergedRegion(A1_E1);
    sheet.addMergedRegion(A2_C2);
    // A1:E1 should be moved to A2:E2
    // A2:C2 will be removed
    sheet.shiftRows(0, 0, 1);
    assertEquals(1, sheet.getNumMergedRegions());
    assertEquals(CellRangeAddress.valueOf("A2:E2"), sheet.getMergedRegion(0));
    wb.close();
}

Example 84 with CellRangeAddress

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

the class XSSFSheet method removeArrayFormula.

@Override
public CellRange<XSSFCell> removeArrayFormula(Cell cell) {
    if (cell.getSheet() != this) {
        throw new IllegalArgumentException("Specified cell does not belong to this sheet.");
    }
    for (CellRangeAddress range : arrayFormulas) {
        if (range.isInRange(cell)) {
            arrayFormulas.remove(range);
            CellRange<XSSFCell> cr = getCellRange(range);
            for (XSSFCell c : cr) {
                c.setCellType(CellType.BLANK);
            }
            return cr;
        }
    }
    String ref = ((XSSFCell) cell).getCTCell().getR();
    throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
}

Example 85 with CellRangeAddress

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

the class XSSFSheet method getDataValidations.

@Override
public List<XSSFDataValidation> getDataValidations() {
    List<XSSFDataValidation> xssfValidations = new ArrayList<XSSFDataValidation>();
    CTDataValidations dataValidations = this.worksheet.getDataValidations();
    if (dataValidations != null && dataValidations.getCount() > 0) {
        for (CTDataValidation ctDataValidation : dataValidations.getDataValidationArray()) {
            CellRangeAddressList addressList = new CellRangeAddressList();
            @SuppressWarnings("unchecked") List<String> sqref = ctDataValidation.getSqref();
            for (String stRef : sqref) {
                String[] regions = stRef.split(" ");
                for (String region : regions) {
                    String[] parts = region.split(":");
                    CellReference begin = new CellReference(parts[0]);
                    CellReference end = parts.length > 1 ? new CellReference(parts[1]) : begin;
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(begin.getRow(), end.getRow(), begin.getCol(), end.getCol());
                    addressList.addCellRangeAddress(cellRangeAddress);
                }
            }
            XSSFDataValidation xssfDataValidation = new XSSFDataValidation(addressList, ctDataValidation);
            xssfValidations.add(xssfDataValidation);
        }
    }
    return xssfValidations;
}