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

Example 46 with CellReference

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

the class BaseTestSheetUpdateArrayFormulas method testRemoveArrayFormula.

/**
     * create and remove array formulas
     */
@Test
public final void testRemoveArrayFormula() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2);
    assertEquals("C4:C6", range.formatAsString());
    CellRange<?> cr = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range);
    assertEquals(3, cr.size());
    // remove the formula cells in C4:C6
    CellRange<?> dcells = sheet.removeArrayFormula(cr.getTopLeftCell());
    // removeArrayFormula should return the same cells as setArrayFormula
    assertArrayEquals(cr.getFlattenedCells(), dcells.getFlattenedCells());
    for (Cell acell : cr) {
        assertFalse(acell.isPartOfArrayFormulaGroup());
        assertEquals(CellType.BLANK, acell.getCellTypeEnum());
    }
    // invocation of sheet.removeArrayFormula on any of them throws IllegalArgumentException
    for (Cell acell : cr) {
        try {
            sheet.removeArrayFormula(acell);
            fail("expected exception");
        } catch (IllegalArgumentException e) {
            String ref = new CellReference(acell).formatAsString();
            assertEquals("Cell " + ref + " is not part of an array formula.", e.getMessage());
        }
    }
    workbook.close();
}

Example 47 with CellReference

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

the class TestCellReference method testConvertNumToColString.

@Test
public void testConvertNumToColString() {
    short col = 702;
    String collRef = new CellReference(0, col).formatAsString();
    assertEquals("AAA1", collRef);
    short col2 = 0;
    String collRef2 = new CellReference(0, col2).formatAsString();
    assertEquals("A1", collRef2);
    short col3 = 27;
    String collRef3 = new CellReference(0, col3).formatAsString();
    assertEquals("AB1", collRef3);
    short col4 = 2080;
    String collRef4 = new CellReference(0, col4).formatAsString();
    assertEquals("CBA1", collRef4);
}

Example 48 with CellReference

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

the class TestCellReference method testFormatAsString.

@Test
public void testFormatAsString() {
    CellReference cellReference;
    cellReference = new CellReference(null, 0, 0, false, false);
    assertEquals("A1", cellReference.formatAsString());
    //absolute references
    cellReference = new CellReference(null, 0, 0, true, false);
    assertEquals("A$1", cellReference.formatAsString());
    //sheet name with no spaces
    cellReference = new CellReference("Sheet1", 0, 0, true, false);
    assertEquals("Sheet1!A$1", cellReference.formatAsString());
    //sheet name with spaces
    cellReference = new CellReference("Sheet 1", 0, 0, true, false);
    assertEquals("'Sheet 1'!A$1", cellReference.formatAsString());
}

Example 49 with CellReference

use of org.apache.poi.ss.util.CellReference 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;
}

Example 50 with CellReference

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

the class XSSFSheet method shiftRows.

/**
     * Shifts rows between startRow and endRow n number of rows.
     * If you use a negative number, it will shift rows up.
     * Code ensures that rows don't wrap around
     *
     * <p>
     * Additionally shifts merged regions that are completely defined in these
     * rows (ie. merged 2 cells on a row to be shifted). All merged regions that are
     * completely overlaid by shifting will be deleted.
     * <p>
     * @param startRow the row to start shifting
     * @param endRow the row to end shifting
     * @param n the number of rows to shift
     * @param copyRowHeight whether to copy the row height during the shift
     * @param resetOriginalRowHeight whether to set the original row's height to the default
     */
@Override
public void shiftRows(int startRow, int endRow, final int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
    XSSFVMLDrawing vml = getVMLDrawing(false);
    // first remove all rows which will be overwritten
    for (Iterator<Row> it = rowIterator(); it.hasNext(); ) {
        XSSFRow row = (XSSFRow) it.next();
        int rownum = row.getRowNum();
        // check if we should remove this row as it will be overwritten by the data later
        if (shouldRemoveRow(startRow, endRow, n, rownum)) {
            // remove row from worksheet.getSheetData row array
            // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
            // NOSONAR
            final Integer rownumI = new Integer(row.getRowNum());
            int idx = _rows.headMap(rownumI).size();
            worksheet.getSheetData().removeRow(idx);
            // remove row from _rows
            it.remove();
            // also remove any comments associated with this row
            if (sheetComments != null) {
                CTCommentList lst = sheetComments.getCTComments().getCommentList();
                for (CTComment comment : lst.getCommentArray()) {
                    String strRef = comment.getRef();
                    CellAddress ref = new CellAddress(strRef);
                    // is this comment part of the current row?
                    if (ref.getRow() == rownum) {
                        sheetComments.removeComment(ref);
                        vml.removeCommentShape(ref.getRow(), ref.getColumn());
                    }
                }
            }
            // also remove any hyperlinks associated with this row
            if (hyperlinks != null) {
                for (XSSFHyperlink link : new ArrayList<XSSFHyperlink>(hyperlinks)) {
                    CellReference ref = new CellReference(link.getCellRef());
                    if (ref.getRow() == rownum) {
                        hyperlinks.remove(link);
                    }
                }
            }
        }
    }
    // then do the actual moving and also adjust comments/rowHeight
    // we need to sort it in a way so the shifting does not mess up the structures, 
    // i.e. when shifting down, start from down and go up, when shifting up, vice-versa
    SortedMap<XSSFComment, Integer> commentsToShift = new TreeMap<XSSFComment, Integer>(new Comparator<XSSFComment>() {

        @Override
        public int compare(XSSFComment o1, XSSFComment o2) {
            int row1 = o1.getRow();
            int row2 = o2.getRow();
            if (row1 == row2) {
                // get multiple comments per row into the map
                return o1.hashCode() - o2.hashCode();
            }
            // when shifting down, sort higher row-values first
            if (n > 0) {
                return row1 < row2 ? 1 : -1;
            } else {
                // sort lower-row values first when shifting up
                return row1 > row2 ? 1 : -1;
            }
        }
    });
    for (Iterator<Row> it = rowIterator(); it.hasNext(); ) {
        XSSFRow row = (XSSFRow) it.next();
        int rownum = row.getRowNum();
        if (sheetComments != null) {
            // calculate the new rownum
            int newrownum = shiftedRowNum(startRow, endRow, n, rownum);
            // is there a change necessary for the current row?
            if (newrownum != rownum) {
                CTCommentList lst = sheetComments.getCTComments().getCommentList();
                for (CTComment comment : lst.getCommentArray()) {
                    String oldRef = comment.getRef();
                    CellReference ref = new CellReference(oldRef);
                    // is this comment part of the current row?
                    if (ref.getRow() == rownum) {
                        XSSFComment xssfComment = new XSSFComment(sheetComments, comment, vml == null ? null : vml.findCommentShape(rownum, ref.getCol()));
                        // we should not perform the shifting right here as we would then find
                        // already shifted comments and would shift them again...
                        commentsToShift.put(xssfComment, newrownum);
                    }
                }
            }
        }
        if (rownum < startRow || rownum > endRow) {
            continue;
        }
        if (!copyRowHeight) {
            row.setHeight((short) -1);
        }
        row.shift(n);
    }
    // i.e. from down to up if shifting down, vice-versa otherwise
    for (Map.Entry<XSSFComment, Integer> entry : commentsToShift.entrySet()) {
        entry.getKey().setRow(entry.getValue());
    }
    XSSFRowShifter rowShifter = new XSSFRowShifter(this);
    int sheetIndex = getWorkbook().getSheetIndex(this);
    String sheetName = getWorkbook().getSheetName(sheetIndex);
    FormulaShifter shifter = FormulaShifter.createForRowShift(sheetIndex, sheetName, startRow, endRow, n, SpreadsheetVersion.EXCEL2007);
    rowShifter.updateNamedRanges(shifter);
    rowShifter.updateFormulas(shifter);
    rowShifter.shiftMergedRegions(startRow, endRow, n);
    rowShifter.updateConditionalFormatting(shifter);
    rowShifter.updateHyperlinks(shifter);
    //rebuild the _rows map
    Map<Integer, XSSFRow> map = new HashMap<Integer, XSSFRow>();
    for (XSSFRow r : _rows.values()) {
        // Performance optimization: explicit boxing is slightly faster than auto-unboxing, though may use more memory
        // NOSONAR
        final Integer rownumI = new Integer(r.getRowNum());
        map.put(rownumI, r);
    }
    _rows.clear();
    _rows.putAll(map);
}