Example usage for org.apache.poi.ss.util CellRangeAddressList CellRangeAddressList

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddressList CellRangeAddressList.

Prototype

public CellRangeAddressList() 

Source Link

Usage

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void addCommentStatus(Sheet p_sheet, Set<Integer> rowsWithCommentSet, int last_row) {
    DataValidationHelper dvHelper = p_sheet.getDataValidationHelper();
    DataValidationConstraint dvConstraintAll = null;
    DataValidationConstraint dvConstraintOne = null;
    CellRangeAddressList addressListOne = new CellRangeAddressList();
    CellRangeAddressList addressListAll = new CellRangeAddressList();
    CellRangeAddress cellAddress = null;

    List<String> status = new ArrayList<String>();
    status.addAll(IssueOptions.getAllStatus());
    String[] allStatus = new String[status.size()];
    status.toArray(allStatus);//w  ww  .  ja v  a  2s  . c  om
    dvConstraintAll = dvHelper.createExplicitListConstraint(allStatus);

    String[] oneStatus = { Issue.STATUS_QUERY };
    dvConstraintOne = dvHelper.createExplicitListConstraint(oneStatus);

    if (rowsWithCommentSet.size() == 0) {
        cellAddress = new CellRangeAddress(SEGMENT_START_ROW, last_row - 1, COMMENT_STATUS_COLUMN,
                COMMENT_STATUS_COLUMN);
        addressListOne.addCellRangeAddress(cellAddress);
        addCommentStatusValidation(p_sheet, dvHelper, dvConstraintOne, addressListOne);
    } else {
        boolean hasComment = false;
        int startRow = SEGMENT_START_ROW;
        int endRow = -1;
        for (int row = SEGMENT_START_ROW; row < last_row; row++) {
            if (rowsWithCommentSet.contains(row)) {
                if (!hasComment && row != SEGMENT_START_ROW) {
                    endRow = row - 1;
                    cellAddress = new CellRangeAddress(startRow, endRow, COMMENT_STATUS_COLUMN,
                            COMMENT_STATUS_COLUMN);
                    addressListOne.addCellRangeAddress(cellAddress);
                    startRow = row;
                }
                hasComment = true;
            } else {
                if (hasComment) {
                    endRow = row - 1;
                    cellAddress = new CellRangeAddress(startRow, endRow, COMMENT_STATUS_COLUMN,
                            COMMENT_STATUS_COLUMN);
                    addressListAll.addCellRangeAddress(cellAddress);
                    startRow = row;
                }
                hasComment = false;
            }

            if (row == last_row - 1) {
                cellAddress = new CellRangeAddress(startRow, last_row - 1, COMMENT_STATUS_COLUMN,
                        COMMENT_STATUS_COLUMN);
                if (hasComment) {
                    addressListAll.addCellRangeAddress(cellAddress);
                } else {
                    addressListOne.addCellRangeAddress(cellAddress);
                }
            }
        }

        addCommentStatusValidation(p_sheet, dvHelper, dvConstraintAll, addressListAll);
        addCommentStatusValidation(p_sheet, dvHelper, dvConstraintOne, addressListOne);
    }
}

---

From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java

License:Apache License

private CellRangeAddressList createNewAppendingCellRangeAddressList(
        CellRangeAddressList originalAddressRangeList, int newLastRowIndex) {
    CellRangeAddressList extendedCellRangeAddressList = new CellRangeAddressList();
    for (CellRangeAddress ca : originalAddressRangeList.getCellRangeAddresses()) {
        extendedCellRangeAddressList.addCellRangeAddress(createAppendingCellRangeAddress(ca, newLastRowIndex));
    }//from w  w  w. j ava 2s . c o  m
    return extendedCellRangeAddressList;
}

---

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("rawtypes")
private static void writeDataValidations(Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) {
    int templateRowStartIndex = sheetProcessor.getTemplateStartRowIndex();
    int templateRowEndIndex = sheetProcessor.getTemplateEndRowIndex();
    int step = templateRowEndIndex - templateRowStartIndex + 1;
    int rowStartIndex = sheetProcessor.getStartRowIndex();

    Set<Integer> configColIndexSet = new HashSet<Integer>();
    for (Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> fieldIndexMapping : sheetProcessor
            .getFieldMapping().export().entrySet()) {
        if (fieldIndexMapping == null || fieldIndexMapping.getValue() == null) {
            continue;
        }/*from w  ww  .ja v a 2s.c om*/
        for (Entry<Integer, ExcelWriteFieldMappingAttribute> indexProcessorMapping : fieldIndexMapping
                .getValue().entrySet()) {
            if (indexProcessorMapping == null || indexProcessorMapping.getKey() == null) {
                continue;
            }
            configColIndexSet.add(indexProcessorMapping.getKey());
        }
    }

    List<? extends DataValidation> dataValidations = sheet.getDataValidations();
    if (dataValidations != null) {
        for (DataValidation dataValidation : dataValidations) {
            if (dataValidation == null) {
                continue;
            }
            CellRangeAddressList cellRangeAddressList = dataValidation.getRegions();
            if (cellRangeAddressList == null) {
                continue;
            }

            CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses();
            if (cellRangeAddresses == null || cellRangeAddresses.length == 0) {
                continue;
            }

            CellRangeAddressList newCellRangeAddressList = new CellRangeAddressList();
            boolean validationContains = false;
            for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
                if (cellRangeAddress == null) {
                    continue;
                }
                if (templateRowEndIndex < cellRangeAddress.getFirstRow()
                        || templateRowStartIndex > cellRangeAddress.getLastRow()) {// specify row
                    continue;
                }
                for (Integer configColIndex : configColIndexSet) {
                    if (configColIndex < cellRangeAddress.getFirstColumn()
                            || configColIndex > cellRangeAddress.getLastColumn()) {// specify column
                        continue;
                    }
                    if (templateRowStartIndex == templateRowEndIndex) {
                        newCellRangeAddressList.addCellRangeAddress(rowStartIndex, configColIndex,
                                sheet.getLastRowNum(), configColIndex);
                        validationContains = true;
                    } else {
                        int start = cellRangeAddress.getFirstRow() > templateRowStartIndex
                                ? cellRangeAddress.getFirstRow()
                                : templateRowStartIndex;
                        int end = cellRangeAddress.getLastRow() < templateRowEndIndex
                                ? cellRangeAddress.getLastRow()
                                : templateRowEndIndex;
                        long lastRow = sheet.getLastRowNum();
                        if (lastRow > end) {
                            long count = (lastRow - templateRowEndIndex) / step;
                            int i = templateRowEndIndex;
                            for (; i < count; i++) {
                                newCellRangeAddressList.addCellRangeAddress(start + i * step, configColIndex,
                                        end + i * step, configColIndex);
                                validationContains = true;
                            }
                            long _start = start + i * step;
                            if (_start <= lastRow) {
                                long _end = end + i * step;
                                _end = _end < lastRow ? _end : lastRow;
                                newCellRangeAddressList.addCellRangeAddress((int) _start, configColIndex,
                                        (int) _end, configColIndex);
                                validationContains = true;
                            }
                        }
                    }
                }
            }
            if (validationContains) {
                DataValidation newDataValidation = sheet.getDataValidationHelper()
                        .createValidation(dataValidation.getValidationConstraint(), newCellRangeAddressList);
                sheet.addValidationData(newDataValidation);
            }
        }
    }
}

---