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

Example 86 with CellRangeAddress

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

the class XSSFSheet method setAutoFilter.

@SuppressWarnings("resource")
@Override
public XSSFAutoFilter setAutoFilter(CellRangeAddress range) {
    CTAutoFilter af = worksheet.getAutoFilter();
    if (af == null) {
        af = worksheet.addNewAutoFilter();
    }
    CellRangeAddress norm = new CellRangeAddress(range.getFirstRow(), range.getLastRow(), range.getFirstColumn(), range.getLastColumn());
    String ref = norm.formatAsString();
    af.setRef(ref);
    XSSFWorkbook wb = getWorkbook();
    int sheetIndex = getWorkbook().getSheetIndex(this);
    XSSFName name = wb.getBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex);
    if (name == null) {
        name = wb.createBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex);
    }
    name.getCTName().setHidden(true);
    CellReference r1 = new CellReference(getSheetName(), range.getFirstRow(), range.getFirstColumn(), true, true);
    CellReference r2 = new CellReference(null, range.getLastRow(), range.getLastColumn(), true, true);
    String fmla = r1.formatAsString() + ":" + r2.formatAsString();
    name.setRefersToFormula(fmla);
    return new XSSFAutoFilter(this);
}

Example 87 with CellRangeAddress

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

the class XSSFSheet method write.

protected void write(OutputStream out) throws IOException {
    boolean setToNull = false;
    if (worksheet.sizeOfColsArray() == 1) {
        CTCols col = worksheet.getColsArray(0);
        if (col.sizeOfColArray() == 0) {
            setToNull = true;
            // this is necessary so that we do not write an empty <cols/> item into the sheet-xml in the xlsx-file
            // Excel complains about a corrupted file if this shows up there!
            worksheet.setColsArray(null);
        } else {
            setColWidthAttribute(col);
        }
    }
    // Now re-generate our CTHyperlinks, if needed
    if (hyperlinks.size() > 0) {
        if (worksheet.getHyperlinks() == null) {
            worksheet.addNewHyperlinks();
        }
        CTHyperlink[] ctHls = new CTHyperlink[hyperlinks.size()];
        for (int i = 0; i < ctHls.length; i++) {
            // If our sheet has hyperlinks, have them add
            //  any relationships that they might need
            XSSFHyperlink hyperlink = hyperlinks.get(i);
            hyperlink.generateRelationIfNeeded(getPackagePart());
            // Now grab their underling object
            ctHls[i] = hyperlink.getCTHyperlink();
        }
        worksheet.getHyperlinks().setHyperlinkArray(ctHls);
    } else {
        if (worksheet.getHyperlinks() != null) {
            final int count = worksheet.getHyperlinks().sizeOfHyperlinkArray();
            for (int i = count - 1; i >= 0; i--) {
                worksheet.getHyperlinks().removeHyperlink(i);
            }
            // For some reason, we have to remove the hyperlinks one by one from the CTHyperlinks array
            // before unsetting the hyperlink array.
            // Resetting the hyperlink array seems to break some XML nodes.
            //worksheet.getHyperlinks().setHyperlinkArray(new CTHyperlink[0]);
            worksheet.unsetHyperlinks();
        } else {
        // nothing to do
        }
    }
    int minCell = Integer.MAX_VALUE, maxCell = Integer.MIN_VALUE;
    for (XSSFRow row : _rows.values()) {
        // first perform the normal write actions for the row
        row.onDocumentWrite();
        // then calculate min/max cell-numbers for the worksheet-dimension
        if (row.getFirstCellNum() != -1) {
            minCell = Math.min(minCell, row.getFirstCellNum());
        }
        if (row.getLastCellNum() != -1) {
            maxCell = Math.max(maxCell, row.getLastCellNum());
        }
    }
    // finally, if we had at least one cell we can populate the optional dimension-field
    if (minCell != Integer.MAX_VALUE) {
        String ref = new CellRangeAddress(getFirstRowNum(), getLastRowNum(), minCell, maxCell).formatAsString();
        if (worksheet.isSetDimension()) {
            worksheet.getDimension().setRef(ref);
        } else {
            worksheet.addNewDimension().setRef(ref);
        }
    }
    XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
    xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorksheet.type.getName().getNamespaceURI(), "worksheet"));
    worksheet.save(out, xmlOptions);
    // Bug 52233: Ensure that we have a col-array even if write() removed it
    if (setToNull) {
        worksheet.addNewCols();
    }
}

Example 88 with CellRangeAddress

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

the class XSSFSheet method checkForIntersectingMergedRegions.

/**
     * Verify that no merged regions intersect another merged region in this sheet.
     *
     * @throws IllegalStateException if at least one region intersects with another merged region in this sheet
     */
private void checkForIntersectingMergedRegions() {
    final List<CellRangeAddress> regions = getMergedRegions();
    final int size = regions.size();
    for (int i = 0; i < size; i++) {
        final CellRangeAddress region = regions.get(i);
        for (final CellRangeAddress other : regions.subList(i + 1, regions.size())) {
            if (region.intersects(other)) {
                String msg = "The range " + region.formatAsString() + " intersects with another merged region " + other.formatAsString() + " in this sheet";
                throw new IllegalStateException(msg);
            }
        }
    }
}

Example 89 with CellRangeAddress

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

the class XSSFSheetConditionalFormatting method addConditionalFormatting.

public int addConditionalFormatting(CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) {
    if (regions == null) {
        throw new IllegalArgumentException("regions must not be null");
    }
    for (CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007);
    if (cfRules == null) {
        throw new IllegalArgumentException("cfRules must not be null");
    }
    if (cfRules.length == 0) {
        throw new IllegalArgumentException("cfRules must not be empty");
    }
    if (cfRules.length > 3) {
        throw new IllegalArgumentException("Number of rules must not exceed 3");
    }
    CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions);
    CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting();
    List<String> refs = new ArrayList<String>();
    for (CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());
    cf.setSqref(refs);
    int priority = 1;
    for (CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()) {
        priority += c.sizeOfCfRuleArray();
    }
    for (ConditionalFormattingRule rule : cfRules) {
        XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule) rule;
        xRule.getCTCfRule().setPriority(priority++);
        cf.addNewCfRule().set(xRule.getCTCfRule());
    }
    return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray() - 1;
}

Example 90 with CellRangeAddress

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

the class XSSFRowShifter method updateHyperlinks.

/**
     * Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
     * is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
     * do not track the content they point to.
     *
     * @param shifter
     */
public void updateHyperlinks(FormulaShifter shifter) {
    int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
    List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList();
    for (Hyperlink hyperlink : hyperlinkList) {
        XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink;
        String cellRef = xhyperlink.getCellRef();
        CellRangeAddress cra = CellRangeAddress.valueOf(cellRef);
        CellRangeAddress shiftedRange = shiftRange(shifter, cra, sheetIndex);
        if (shiftedRange != null && shiftedRange != cra) {
            // shiftedRange should not be null. If shiftedRange is null, that means
            // that a hyperlink wasn't deleted at the beginning of shiftRows when
            // identifying rows that should be removed because they will be overwritten
            xhyperlink.setCellReference(shiftedRange.formatAsString());
        }
    }
}