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

List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddress
  6. CellRangeAddress

Introduction

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

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Creates new cell range.

Usage

From source file:com.plugin.excel.util.ExcelUtil.java

License:Apache License

/**
 * @param srcSheet/*  w w w .j  av  a 2s  .  c om*/
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *            -
 */
public static void copyRow(SXSSFSheet srcSheet, SXSSFSheet destSheet, Row srcRow, Row destRow,
        Map<Integer, CellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a
    // merged zone
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        Cell oldCell = srcRow.getCell(j); // ancienne cell
        Cell newCell = destRow.getCell(j); // new cell
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell
            copyCell(oldCell, newCell, styleMap);
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                // System.out.println("Selected merged region: " +
                // mergedRegion.toString());
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
                        mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                // System.out.println("New merged region: " +
                // newMergedRegion.toString());
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }

}

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsService.java

License:Open Source License

private int fillColumnWithStaffForWorkOnLine(final HSSFSheet sheet, int rowNum,
        final Entity assignmentToShiftReport, final List<DateTime> days, final List<Entity> productionLines,
        final Entity dictionaryItem) {
    if ((assignmentToShiftReport != null) && (days != null) && (productionLines != null)) {

        for (Entity productionLine : productionLines) {
            int rowNumFromLastSection = rowNum;
            int numberOfColumnsForWorkers = getNumberOfRowsForWorkers(assignmentToShiftReport, days,
                    productionLine, dictionaryItem);
            for (int i = 0; i < numberOfColumnsForWorkers; i++) {
                HSSFRow row = sheet.createRow(rowNum);
                rowNum++;//from   w ww.j  a  v  a2  s.co m
            }

            String productionLineValue = null;
            if (productionLine.getStringField(PLACE) == null) {
                productionLineValue = productionLine.getStringField(NUMBER);
            } else {
                productionLineValue = productionLine.getStringField(NUMBER) + "-"
                        + productionLine.getStringField(ProductionLineFields.PLACE);
            }
            HSSFRow firstRowInSection = null;
            if (sheet.getRow(rowNumFromLastSection) == null) {
                firstRowInSection = sheet.createRow(rowNumFromLastSection);
                rowNum++;
            } else {
                firstRowInSection = sheet.getRow(rowNumFromLastSection);

            }
            HSSFCell cell = firstRowInSection.createCell(0);
            cell.setCellValue(productionLineValue);
            sheet.addMergedRegion(new CellRangeAddress(rowNumFromLastSection, rowNum - 1, 0, 0));
            int columnNumber = 1;
            int maxLength = 0;

            for (DateTime day : days) {
                Entity assignmentToShift = assignmentToShiftXlsHelper
                        .getAssignmentToShift(assignmentToShiftReport.getBelongsToField(SHIFT), day.toDate());
                if (assignmentToShift == null) {
                    columnNumber += 3;
                    continue;
                }
                List<Entity> staffs = assignmentToShiftXlsHelper.getStaffsList(assignmentToShift,
                        dictionaryItem.getStringField(NAME), productionLine);
                if (staffs.isEmpty()) {
                    columnNumber += 3;
                    continue;
                }
                String staffsValue = assignmentToShiftXlsHelper.getListOfWorkers(staffs);

                List<String> workers = assignmentToShiftXlsHelper.getListOfWorker(staffs);

                int rowIndex = rowNumFromLastSection;

                for (String worker : workers) {
                    sheet.getRow(rowIndex).createCell(columnNumber).setCellValue(worker);
                    rowIndex++;
                }
                if (workers.isEmpty()) {
                    sheet.getRow(rowIndex).createCell(columnNumber).setCellValue(" ");

                }
                if (maxLength < staffsValue.length()) {
                    maxLength = staffsValue.length();
                }
                // row.setHeightInPoints(assignmentToShiftXlsStyleHelper.getHeightForRow(maxLength, 22, 14));
                columnNumber += 3;
            }
            for (int i = rowNumFromLastSection; i < rowNum; i++) {
                assignmentToShiftXlsStyleHelper.addMarginsAndStylesForSeries(sheet, i,
                        assignmentToShiftXlsHelper.getNumberOfDaysBetweenGivenDates(assignmentToShiftReport));

            }
        }

    }
    return rowNum;
}

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsService.java

License:Open Source License

private int fillColumnWithStaffForOtherTypes(final HSSFSheet sheet, int rowNum,
        final Entity assignmentToShiftReport, final List<DateTime> days, final Entity occupationType) {
    if ((assignmentToShiftReport != null) && (days != null) && (occupationType != null)) {

        int rowNumFromLastSection = rowNum;

        int numberOfColumnsForWorkers = getNumberOfRowsForWorkersForOtherTypes(assignmentToShiftReport, days,
                occupationType);// w  w w .j  av a 2s  . c o  m
        for (int i = 0; i < numberOfColumnsForWorkers; i++) {
            HSSFRow row = sheet.createRow(rowNum);
            rowNum++;
        }
        String occupationTypeValue = occupationType.getStringField(NAME);

        HSSFRow firstRowInSection = null;
        if (sheet.getRow(rowNumFromLastSection) == null) {
            firstRowInSection = sheet.createRow(rowNumFromLastSection);
            rowNum++;
        } else {
            firstRowInSection = sheet.getRow(rowNumFromLastSection);

        }
        HSSFCell cell = firstRowInSection.createCell(0);
        cell.setCellValue(occupationTypeValue);
        sheet.addMergedRegion(new CellRangeAddress(rowNumFromLastSection, rowNum - 1, 0, 0));

        int columnNumber = 1;
        int maxLength = 0;
        for (DateTime day : days) {
            Entity assignmentToShift = assignmentToShiftXlsHelper
                    .getAssignmentToShift(assignmentToShiftReport.getBelongsToField(SHIFT), day.toDate());
            if (assignmentToShift == null) {
                columnNumber += 3;
                continue;
            }
            List<Entity> staffs = assignmentToShiftXlsHelper.getStaffsList(assignmentToShift,
                    occupationType.getStringField(NAME), null);

            List<String> workers = Lists.newArrayList();
            if (OccupationType.OTHER_CASE.getStringValue()
                    .equals(occupationType.getStringField(TECHNICAL_CODE))) {
                workers = assignmentToShiftXlsHelper.getListOfWorkerWithOtherCases(staffs);
            } else {
                workers = assignmentToShiftXlsHelper.getListOfWorker(staffs);
            }

            int rowIndex = rowNumFromLastSection;

            for (String worker : workers) {
                sheet.getRow(rowIndex).createCell(columnNumber).setCellValue(worker);
                rowIndex++;
            }
            if (workers.isEmpty()) {
                sheet.getRow(rowIndex).createCell(columnNumber).setCellValue(" ");

            }
            columnNumber += 3;
        }

        for (int i = rowNumFromLastSection; i < rowNum; i++) {
            assignmentToShiftXlsStyleHelper.addMarginsAndStylesForSeries(sheet, i,
                    assignmentToShiftXlsHelper.getNumberOfDaysBetweenGivenDates(assignmentToShiftReport));
        }
    }

    return rowNum;
}

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java

License:Open Source License

public void addMarginsAndStylesForAuthor(final HSSFSheet sheet, final int rowNumber, final int numberOfDays) {
    int firstColumnNumber = 0;
    int lastColumnNumber;
    int margin = 3;

    if (numberOfDays < 3) {
        lastColumnNumber = 10;//from w ww  .  j a va2  s.  c  o  m
    } else {
        lastColumnNumber = (numberOfDays + 1) * margin;
    }

    for (int columnNumber = firstColumnNumber; columnNumber <= lastColumnNumber; columnNumber++) {
        if (sheet.getRow(rowNumber).getCell(columnNumber) == null) {
            sheet.getRow(rowNumber).createCell(columnNumber);
        }

        if (columnNumber == firstColumnNumber) {
            setGreyDataStyleBorderLeftAlignLeftBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
        } else if (columnNumber == lastColumnNumber) {
            setGreyDataStyleBorderRightAlignLeftBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
        } else {
            setGreyDataStyleAlignLeftBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
        }
    }

    sheet.addMergedRegion(
            new CellRangeAddress(rowNumber, rowNumber, firstColumnNumber, firstColumnNumber + margin - 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, firstColumnNumber + margin,
            firstColumnNumber + (margin * 2) - 1));
    sheet.addMergedRegion(
            new CellRangeAddress(rowNumber, rowNumber, firstColumnNumber + (margin * 2), lastColumnNumber));
}

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java

License:Open Source License

public void addMarginsAndStylesForAssignmentToShift(final HSSFSheet sheet, final int rowNumber,
        final int numberOfDays) {
    int margin = 3;
    int firstColumn = 0;
    int lastColumn = (numberOfDays + 1) * margin;

    for (int columnNumber = firstColumn; columnNumber <= lastColumn; columnNumber++) {
        if (sheet.getRow(rowNumber).getCell(columnNumber) == null) {
            sheet.getRow(rowNumber).createCell(columnNumber);
        }//from  ww  w.j  av  a2 s  .  c om

        setWhiteDataStyleBorderBoxAlignCenterBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
    }

    for (int columnNumber = 1; columnNumber <= lastColumn; columnNumber += margin) {
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, columnNumber, columnNumber + 2));
    }
}

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsStyleHelper.java

License:Open Source License

public void addMarginsAndStylesForSeries(final HSSFSheet sheet, final int rowNumber, final int numberOfDays) {
    int margin = 3;
    int firstColumn = 0;
    int lastColumn = (numberOfDays + 1) * margin;

    for (int columnNumber = firstColumn; columnNumber <= lastColumn; columnNumber++) {
        if (sheet.getRow(rowNumber).getCell(columnNumber) == null) {
            sheet.getRow(rowNumber).createCell(columnNumber);
        }/* www.j av a 2  s .  com*/

        if (columnNumber == firstColumn) {
            setWhiteDataStyleBorderBoxAlignCenterBold(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
        } else {
            setWhiteDataStyleBorderBoxAlignLeft(sheet, sheet.getRow(rowNumber).getCell(columnNumber));
        }
    }

    for (int columnNumber = 1; columnNumber <= lastColumn; columnNumber += margin) {
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, columnNumber, columnNumber + 2));
    }
}

From source file:com.qihang.winter.poi.excel.export.base.ExcelExportBase.java

License:Apache License

/**
 *  ? Cells/*w w w .  jav a  2 s  . co  m*/
 * 
 * @param styles
 * @param rowHeight
 * @throws Exception
 */
public int createCells(Drawing patriarch, int index, Object t,
        List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams, Sheet sheet,
        Workbook workbook, short rowHeight) throws Exception {
    com.qihang.winter.poi.excel.entity.params.ExcelExportEntity entity;
    Row row = sheet.createRow(index);
    row.setHeight(rowHeight);
    int maxHeight = 1, cellNum = 0;
    int indexKey = createIndexCell(row, index, excelParams.get(0));
    cellNum += indexKey;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            Collection<?> list = getListCellValue(entity, t);
            int listC = 0;
            for (Object obj : list) {
                createListCells(patriarch, index + listC, cellNum, obj, entity.getList(), sheet, workbook);
                listC++;
            }
            cellNum += entity.getList().size();
            if (list != null && list.size() > maxHeight) {
                maxHeight = list.size();
            }
        } else {
            Object value = getCellValue(entity, t);
            if (entity.getType() == 1) {
                createStringCell(row, cellNum++, value == null ? "" : value.toString(),
                        index % 2 == 0 ? getStyles(false, entity) : getStyles(true, entity), entity);
            } else {
                createImageCell(patriarch, entity, row, cellNum++, value == null ? "" : value.toString(), t);
            }
        }
    }
    // ????
    cellNum = 0;
    for (int k = indexKey, paramSize = excelParams.size(); k < paramSize; k++) {
        entity = excelParams.get(k);
        if (entity.getList() != null) {
            cellNum += entity.getList().size();
        } else if (entity.isNeedMerge()) {
            for (int i = index + 1; i < index + maxHeight; i++) {
                sheet.getRow(i).createCell(cellNum);
                sheet.getRow(i).getCell(cellNum).setCellStyle(getStyles(false, entity));
            }
            sheet.addMergedRegion(new CellRangeAddress(index, index + maxHeight - 1, cellNum, cellNum));
            cellNum++;
        }
    }
    return maxHeight;

}

From source file:com.qpark.eip.core.poi.impl.AbstractSheetProvider.java

License:Open Source License

/**
 * Get the merging {@link CellRangeAddress} for a or a list of
 * {@link Section}s.//from   w  ww .  ja  v  a  2  s. co  m
 *
 * @param row
 *            the row number the {@link CellRangeAddress} is merging.
 * @param start
 *            the starting column.
 * @param sections
 *            the {@link Section} or list of {@link Section}s.
 * @return the {@link CellRangeAddress}.
 */
public static CellRangeAddress getMergedHeader(final int row, final int start, final Section<?>... sections) {
    AtomicInteger stop = new AtomicInteger(start);
    if (Objects.nonNull(sections)) {
        Arrays.asList(sections).stream().filter(s -> Objects.nonNull(s))
                .forEach(s -> stop.set(stop.get() + s.size()));
    }
    CellRangeAddress value = new CellRangeAddress(row, row, start, stop.get() - 1);
    return value;
}

From source file:com.qpark.eip.core.poi.impl.AbstractSheetProvider.java

License:Open Source License

/**
 * @see com.qpark.eip.core.poi.Sheet#finish()
 *//*from www  . j a  va  2s . co  m*/
@Override
public void finish() {
    AtomicInteger ai = new AtomicInteger(0);
    this.getColumnDefinition().stream().forEach(cd -> {
        if (cd.isAutoSize()) {
            this.sheet.autoSizeColumn(ai.get());
        }
        ai.incrementAndGet();
    });
    this.sheet.createFreezePane(this.getFreezedColumns(), this.superHeaderRowSize + 1,
            this.getFreezedColumns() + 1, this.superHeaderRowSize + 1);
    if (Objects.nonNull(this.sheet.getRow(this.superHeaderRowSize))) {
        this.sheet.setAutoFilter(new CellRangeAddress(this.superHeaderRowSize,
                this.sheet.getRow(this.sheet.getLastRowNum()).getRowNum(), 0,
                this.sheet.getRow(this.superHeaderRowSize).getLastCellNum() - 1));
    }
}

From source file:com.sccl.attech.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ???
 */
public void createAddMergedRegion() {
    sheet.addMergedRegion(new CellRangeAddress(1, 23, 6, 11));
}