Example usage for org.apache.poi.hssf.usermodel HSSFSheet getNumMergedRegions

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getNumMergedRegions

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getNumMergedRegions

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet getNumMergedRegions.

Prototype

@Override
public int getNumMergedRegions()

Source Link

Document

returns the number of merged regions

Usage

From source file:com.cms.utils.ExcelReader.java

public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress merged = (CellRangeAddress) sheet.getMergedRegion(i);
        if (merged.isInRange(rowNum, cellNum)) {
            return merged;
        }//  w ww  . j a  v a  2s  .co m
    }
    return null;
}

From source file:com.eryansky.core.excelTools.ExcelUtils.java

License:Apache License

public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) {
    if (src == null || dest == null)
        return;/*from  w ww .j  a  v  a2 s.co  m*/

    dest.setAlternativeExpression(src.getAlternateExpression());
    dest.setAlternativeFormula(src.getAlternateFormula());
    dest.setAutobreaks(src.getAutobreaks());
    dest.setDialog(src.getDialog());
    if (src.getColumnBreaks() != null) {
        for (int col : src.getColumnBreaks()) {
            dest.setColumnBreak(col);
        }
    }
    dest.setDefaultColumnWidth(src.getDefaultColumnWidth());
    dest.setDefaultRowHeight(src.getDefaultRowHeight());
    dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints());
    dest.setDisplayGuts(src.getDisplayGuts());
    dest.setFitToPage(src.getFitToPage());
    dest.setHorizontallyCenter(src.getHorizontallyCenter());
    dest.setDisplayFormulas(src.isDisplayFormulas());
    dest.setDisplayGridlines(src.isDisplayGridlines());
    dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings());
    dest.setGridsPrinted(src.isGridsPrinted());
    dest.setPrintGridlines(src.isPrintGridlines());

    for (int i = 0; i < src.getNumMergedRegions(); i++) {
        CellRangeAddress r = src.getMergedRegion(i);
        dest.addMergedRegion(r);
    }

    if (src.getRowBreaks() != null) {
        for (int row : src.getRowBreaks()) {
            dest.setRowBreak(row);
        }
    }
    dest.setRowSumsBelow(src.getRowSumsBelow());
    dest.setRowSumsRight(src.getRowSumsRight());

    int maxcol = 0;
    for (int i = 0; i <= src.getLastRowNum(); i++) {
        HSSFRow row = src.getRow(i);
        if (row != null) {
            if (maxcol < row.getLastCellNum())
                maxcol = row.getLastCellNum();
        }
    }
    for (int col = 0; col <= maxcol; col++) {
        if (src.getColumnWidth(col) != src.getDefaultColumnWidth())
            dest.setColumnWidth(col, src.getColumnWidth(col));
        dest.setColumnHidden(col, src.isColumnHidden(col));
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixLeftBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) {
    if (columnIndex > 1) {
        fixLeftCell(sheet, resultCell.getRowIndex(), columnIndex - 1, cellStyle);
        // fix merged left border
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
                int firstRow = mergedRegion.getFirstRow();
                int lastRow = mergedRegion.getLastRow();

                for (int leftIndex = firstRow; leftIndex <= lastRow; leftIndex++) {
                    fixLeftCell(sheet, leftIndex, columnIndex - 1, cellStyle);
                }//from w w w. j a  v a 2  s . c o  m
                break;
            }
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixRightBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, HSSFCell resultCell) {
    fixRightCell(sheet, resultCell.getRowIndex(), columnIndex + 1, cellStyle);
    // fix merged right border
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
            int firstRow = mergedRegion.getFirstRow();
            int lastRow = mergedRegion.getLastRow();
            int regionWidth = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1;

            for (int rightIndex = firstRow; rightIndex <= lastRow; rightIndex++) {
                fixRightCell(sheet, rightIndex, columnIndex + regionWidth, cellStyle);
            }//from   w ww  .ja va2 s  .  c  om
            break;
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixUpBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex,
        HSSFCell resultCell) {//from   w  w  w.j a  va 2s.  c o m
    if (rowIndex > 0) {
        // fix simple up border
        fixUpCell(sheet, rowIndex - 1, columnIndex, cellStyle);
        // fix merged up border
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
                int firstColumn = mergedRegion.getFirstColumn();
                int lastColumn = mergedRegion.getLastColumn();

                for (int upIndex = firstColumn; upIndex <= lastColumn; upIndex++) {
                    fixUpCell(sheet, rowIndex - 1, upIndex, cellStyle);
                }
                break;
            }
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

private void fixDownBorder(HSSFCellStyle cellStyle, HSSFSheet sheet, int columnIndex, int rowIndex,
        HSSFCell resultCell) {/*from   w w  w. j  a  v  a 2s .co  m*/
    // fix simple down border
    fixDownCell(sheet, rowIndex + 1, columnIndex, cellStyle);
    // fix merged down border
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {
            int firstColumn = mergedRegion.getFirstColumn();
            int lastColumn = mergedRegion.getLastColumn();
            int regionHeight = mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1;

            for (int downIndex = firstColumn; downIndex <= lastColumn; downIndex++) {
                fixDownCell(sheet, rowIndex + regionHeight, downIndex, cellStyle);
            }
            break;
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

protected void removeMergedRegions(HSSFSheet resultSheet) {
    for (int i = 0, size = resultSheet.getNumMergedRegions(); i < size; i++) {
        resultSheet.removeMergedRegion(0);//each time we remove region - they "move to left" so region 1 become region 0
    }/*from w w w .  j av  a2s . c  om*/
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Method creates mapping [rangeName : List&lt;CellRangeAddress&gt;].
 * List contains all merge regions for this named range.
 * Attention: if merged regions writes wrong - look on methods isMergeRegionInsideNamedRange or isNamedRangeInsideMergeRegion
 * todo: how to recognize if merge region must be copied with named range
 *
 * @param currentSheet Sheet which contains merge regions
 *//* w  w  w . j av a  2  s .c o m*/
protected void initMergeRegions(HSSFSheet currentSheet) {
    int rangeNumber = templateWorkbook.getNumberOfNames();
    for (int i = 0; i < rangeNumber; i++) {
        HSSFName aNamedRange = templateWorkbook.getNameAt(i);

        String refersToFormula = aNamedRange.getRefersToFormula();
        if (!AreaReference.isContiguous(refersToFormula)) {
            continue;
        }

        AreaReference aref = new AreaReference(refersToFormula);
        Integer rangeFirstRow = aref.getFirstCell().getRow();
        Integer rangeFirstColumn = (int) aref.getFirstCell().getCol();
        Integer rangeLastRow = aref.getLastCell().getRow();
        Integer rangeLastColumn = (int) aref.getLastCell().getCol();

        for (int j = 0; j < currentSheet.getNumMergedRegions(); j++) {
            CellRangeAddress mergedRegion = currentSheet.getMergedRegion(j);
            if (mergedRegion != null) {
                Integer regionFirstRow = mergedRegion.getFirstRow();
                Integer regionFirstColumn = mergedRegion.getFirstColumn();
                Integer regionLastRow = mergedRegion.getLastRow();
                Integer regionLastColumn = mergedRegion.getLastColumn();

                boolean mergedInsideNamed = isMergeRegionInsideNamedRange(rangeFirstRow, rangeFirstColumn,
                        rangeLastRow, rangeLastColumn, regionFirstRow, regionFirstColumn, regionLastRow,
                        regionLastColumn);

                boolean namedInsideMerged = isNamedRangeInsideMergeRegion(rangeFirstRow, rangeFirstColumn,
                        rangeLastRow, rangeLastColumn, regionFirstRow, regionFirstColumn, regionLastRow,
                        regionLastColumn);

                if (mergedInsideNamed || namedInsideMerged) {
                    String name = aNamedRange.getNameName();
                    SheetRange sheetRange = new SheetRange(mergedRegion, currentSheet.getSheetName());
                    if (mergeRegionsForRangeNames.get(name) == null) {
                        ArrayList<SheetRange> list = new ArrayList<SheetRange>();
                        list.add(sheetRange);
                        mergeRegionsForRangeNames.put(name, list);
                    } else {
                        mergeRegionsForRangeNames.get(name).add(sheetRange);
                    }
                }
            }
        }
    }
}

From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java

License:Apache License

/**
 * Create new merge regions in result sheet identically to range's merge regions from template.
 * Not support copy of frames and rules//w  w w  .  jav  a 2 s . co m
 *
 * @param resultSheet            - result sheet
 * @param rangeName              - range name
 * @param firstTargetRangeRow    - first column of target range
 * @param firstTargetRangeColumn - first column of target range
 */
protected void copyMergeRegions(HSSFSheet resultSheet, String rangeName, int firstTargetRangeRow,
        int firstTargetRangeColumn) {
    int rangeNameIdx = templateWorkbook.getNameIndex(rangeName);
    if (rangeNameIdx == -1)
        return;

    HSSFName aNamedRange = templateWorkbook.getNameAt(rangeNameIdx);
    AreaReference aref = new AreaReference(aNamedRange.getRefersToFormula());
    int column = aref.getFirstCell().getCol();
    int row = aref.getFirstCell().getRow();

    List<SheetRange> regionsList = mergeRegionsForRangeNames.get(rangeName);
    if (regionsList != null)
        for (SheetRange sheetRange : regionsList) {
            if (resultSheet.getSheetName().equals(sheetRange.getSheetName())) {
                CellRangeAddress cra = sheetRange.getCellRangeAddress();
                if (cra != null) {
                    int regionHeight = cra.getLastRow() - cra.getFirstRow() + 1;
                    int regionWidth = cra.getLastColumn() - cra.getFirstColumn() + 1;

                    int regionVOffset = cra.getFirstRow() - row;
                    int regionHOffset = cra.getFirstColumn() - column;

                    CellRangeAddress newRegion = cra.copy();
                    newRegion.setFirstColumn(regionHOffset + firstTargetRangeColumn);
                    newRegion.setLastColumn(regionHOffset + regionWidth - 1 + firstTargetRangeColumn);

                    newRegion.setFirstRow(regionVOffset + firstTargetRangeRow);
                    newRegion.setLastRow(regionVOffset + regionHeight - 1 + firstTargetRangeRow);

                    boolean skipRegion = false;

                    for (int mergedIndex = 0; mergedIndex < resultSheet.getNumMergedRegions(); mergedIndex++) {
                        CellRangeAddress mergedRegion = resultSheet.getMergedRegion(mergedIndex);

                        if (!intersects(newRegion, mergedRegion)) {
                            continue;
                        }

                        skipRegion = true;
                    }

                    if (!skipRegion) {
                        resultSheet.addMergedRegion(newRegion);
                    }
                }
            }
        }
}

From source file:com.jfinal.ext.render.excel.PoiKit.java

License:Apache License

public HSSFWorkbook export() {
    Preconditions.checkNotNull(headers, "headers can not be null");
    Preconditions.checkNotNull(columns, "columns can not be null");
    Preconditions.checkArgument(cellWidth >= 0, "cellWidth < 0");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);
    HSSFRow row = null;// ww w. jav  a  2  s  .c o  m
    HSSFCell cell = null;
    if (headers.length > 0) {
        row = sheet.createRow(0);
        if (headerRow <= 0) {
            headerRow = HEADER_ROW;
        }
        headerRow = Math.min(headerRow, MAX_ROWS);
        for (int h = 0, lenH = headers.length; h < lenH; h++) {
            @SuppressWarnings("deprecation")
            Region region = new Region(0, (short) h, (short) headerRow - 1, (short) h);// ?rowFromcolumnFrom
            sheet.addMergedRegion(region);// rowTocolumnTo
            // 
            sheet.getNumMergedRegions();
            if (cellWidth > 0) {
                sheet.setColumnWidth(h, cellWidth);
            }
            cell = row.createCell(h);
            cell.setCellValue(headers[h]);
        }
    }
    if (data.size() == 0) {
        return wb;
    }
    for (int i = 0, len = data.size(); i < len; i++) {
        row = sheet.createRow(i + headerRow);
        Object obj = data.get(i);
        if (obj == null) {
            continue;
        }
        if (obj instanceof Map) {
            processAsMap(columns, row, obj);
        } else if (obj instanceof Model) {
            processAsModel(columns, row, obj);
        } else if (obj instanceof Record) {
            processAsRecord(columns, row, obj);
        }
    }
    return wb;
}