Example usage for org.apache.poi.ss.usermodel Sheet getMergedRegion

List of usage examples for org.apache.poi.ss.usermodel Sheet getMergedRegion

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getMergedRegion

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getMergedRegion.

Prototype

public CellRangeAddress getMergedRegion(int index);

Source Link

Document

Returns the merged region at the specified index

Usage

From source file:ExampleClass.java

public static int getNbOfMergedRegions(Sheet sheet, int row) {
    int count = 0;
    for (int i = 0; i < sheet.getNumMergedRegions(); ++i) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        if (range.getFirstRow() <= row && range.getLastRow() >= row) {
            ++count;//from   www.  ja  v a2 s . c  om
        }
    }
    return count;
}

From source file:bad.robot.excel.row.CopyRow.java

License:Apache License

private static void copyAnyMergedRegions(Sheet worksheet, Row sourceRow, Row newRow) {
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++)
        copyMergeRegion(worksheet, sourceRow, newRow, worksheet.getMergedRegion(i));
}

From source file:com.compassplus.gui.MainForm.java

private void removeRow(Sheet sheet, int rowIndex, Workbook wb, boolean debug) {
    ArrayList<CellRangeAddress> cras = new ArrayList<CellRangeAddress>();
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        cras.add(sheet.getMergedRegion(i));
    }//from w w  w .j a  va2s . co m
    while (sheet.getNumMergedRegions() > 0) {
        sheet.removeMergedRegion(0);
    }
    int lastRowNum = sheet.getLastRowNum();

    if (debug) {
        //System.out.println("lastRowNum = " + lastRowNum);
        //System.out.println("rowIndex = " + rowIndex);
    }

    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        Row removingRow = sheet.getRow(rowIndex);

        sheet.removeRow(removingRow);
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }
    if (rowIndex == lastRowNum) {
        Row removingRow = sheet.getRow(rowIndex);

        if (removingRow != null) {
            sheet.removeRow(removingRow);
        }
    }
    for (CellRangeAddress cra : cras) {
        if (rowIndex >= cra.getFirstRow() && rowIndex <= cra.getLastRow()
                && cra.getFirstRow() != cra.getLastRow()) {
            cra.setLastRow(cra.getLastRow() - 1);
            sheet.addMergedRegion(cra);
        } else if (rowIndex < cra.getFirstRow()) {
            cra.setFirstRow(cra.getFirstRow() - 1);
            cra.setLastRow(cra.getLastRow() - 1);
            sheet.addMergedRegion(cra);
        } else if (rowIndex > cra.getLastRow()) {
            sheet.addMergedRegion(cra);
        }
    }

}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, DataTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*from   www. j  a  v  a2 s.co  m*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {
                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellValue(value);
                                cell.setCellStyle(facetStyle);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                        sheetRowIndex + (rowSpan - 1), //last row  (0-based)
                                        i, //first column (0-based)
                                        i //last column  (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);

                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellValue(value);
                            cell.setCellStyle(facetStyle);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                    sheetRowIndex, //last row  (0-based)
                                    i, //first column (0-based)
                                    i + (colSpan - 1) //last column  (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);
                    }

                    i++;
                }
            }

        }

    }
}

From source file:com.crm.webapp.util.ExcelCustomExporter.java

License:Apache License

protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }/*from   w  ww . j  av a  2  s. co m*/
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {

                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(value);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                        sheetRowIndex + rowSpan - 1, //last row  (0-based)
                                        i, //first column (0-based)
                                        i //last column  (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                    sheetRowIndex, //last row  (0-based)
                                    i, //first column (0-based)
                                    i + colSpan - 1 //last column  (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);

                    }
                    i++;
                }
            }

        }
    }

}

From source file:com.dituiba.excel.ExcelUtility.java

License:Apache License

public static void copyRows(Sheet st, int startRow, int endRow, int pPosition) {
    int pStartRow = startRow;
    int pEndRow = endRow;
    int targetRowFrom;
    int targetRowTo;
    int columnCount;
    CellRangeAddress region = null;/*from   ww  w. j  a  v  a 2s.  com*/
    int i;
    int j;
    for (i = 0; i < st.getNumMergedRegions(); i++) {
        region = st.getMergedRegion(i);
        if ((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) {
            targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
            targetRowTo = region.getLastRow() - pStartRow + pPosition;

            CellRangeAddress newRegion = region.copy();

            newRegion.setFirstRow(targetRowFrom);
            newRegion.setFirstColumn(region.getFirstColumn());
            newRegion.setLastRow(targetRowTo);
            newRegion.setLastColumn(region.getLastColumn());
            st.addMergedRegion(newRegion);
        }
    }
    //set the column height and value
    for (i = pStartRow; i <= pEndRow; i++) {
        Row sourceRow = st.getRow(i);
        columnCount = sourceRow.getLastCellNum();
        if (sourceRow != null) {
            Row newRow = st.createRow(pPosition + i);
            newRow.setHeight(sourceRow.getHeight());
            for (j = 0; j < columnCount; j++) {
                Cell templateCell = sourceRow.getCell(j);
                if (templateCell != null) {
                    Cell newCell = newRow.createCell(j);
                    copyCell(templateCell, newCell);
                }
            }
        }
    }
}

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

License:Apache License

@Override
public void apply() {
    for (DataObject dataObject : data) {
        HSSFCell templateCell = dataObject.templateCell;
        HSSFCell resultCell = dataObject.resultCell;
        BandData bandData = dataObject.bandData;

        HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook();
        HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook();

        String templateCellValue = templateCell.getStringCellValue();

        Matcher matcher = pattern.matcher(templateCellValue);
        if (matcher.find()) {
            String paramName = matcher.group(1);
            String styleName = (String) bandData.getParameterValue(paramName);
            if (styleName == null)
                continue;

            HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName);
            if (cellStyle == null)
                continue;

            HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle);

            if (resultStyle == null) {
                HSSFCellStyle newStyle = resultWorkbook.createCellStyle();
                // color
                newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor());
                newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor());
                newStyle.setFillPattern(cellStyle.getFillPattern());

                // borders
                newStyle.setBorderLeft(cellStyle.getBorderLeft());
                newStyle.setBorderRight(cellStyle.getBorderRight());
                newStyle.setBorderTop(cellStyle.getBorderTop());
                newStyle.setBorderBottom(cellStyle.getBorderBottom());

                // border colors
                newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor());
                newStyle.setRightBorderColor(cellStyle.getRightBorderColor());
                newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor());
                newStyle.setTopBorderColor(cellStyle.getTopBorderColor());

                // alignment
                newStyle.setAlignment(cellStyle.getAlignment());
                newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment());
                // misc
                DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat();
                newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString()));
                newStyle.setHidden(cellStyle.getHidden());
                newStyle.setLocked(cellStyle.getLocked());
                newStyle.setIndention(cellStyle.getIndention());
                newStyle.setRotation(cellStyle.getRotation());
                newStyle.setWrapText(cellStyle.getWrapText());
                // font
                HSSFFont cellFont = cellStyle.getFont(templateWorkbook);
                HSSFFont newFont = fontCache.getFontByTemplate(cellFont);

                if (newFont == null) {
                    newFont = resultWorkbook.createFont();

                    newFont.setFontName(cellFont.getFontName());
                    newFont.setItalic(cellFont.getItalic());
                    newFont.setStrikeout(cellFont.getStrikeout());
                    newFont.setTypeOffset(cellFont.getTypeOffset());
                    newFont.setBoldweight(cellFont.getBoldweight());
                    newFont.setCharSet(cellFont.getCharSet());
                    newFont.setColor(cellFont.getColor());
                    newFont.setUnderline(cellFont.getUnderline());
                    newFont.setFontHeight(cellFont.getFontHeight());
                    newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints());
                    fontCache.addCachedFont(cellFont, newFont);
                }//from  www . ja va2  s .  co m
                newStyle.setFont(newFont);

                resultStyle = newStyle;
                styleCache.addCachedNamedStyle(cellStyle, resultStyle);
            }

            fixNeighbourCellBorders(cellStyle, resultCell);

            resultCell.setCellStyle(resultStyle);

            Sheet sheet = resultCell.getSheet();
            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 firstCol = mergedRegion.getFirstColumn();
                    int lastCol = mergedRegion.getLastColumn();

                    for (int row = firstRow; row <= lastRow; row++)
                        for (int col = firstCol; col <= lastCol; col++)
                            sheet.getRow(row).getCell(col).setCellStyle(resultStyle);

                    // cell includes only in one merged region
                    break;
                }
            }
        }
    }
}

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

public void unmergeCells(int sheetIndex, String reference) {
    Sheet sheet = getSheet(sheetIndex);
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress cra = sheet.getMergedRegion(i);
        if (cra.formatAsString().equals(reference)) {
            sheet.removeMergedRegion(i);
            break;
        }/*from  www . j a  v  a  2  s  .  co  m*/
    }
}

From source file:com.ncc.excel.test.ExcelUtil.java

License:Apache License

/** 
 * ??? /*from www.  j  a va2 s.co m*/
 *  
 * @param sheet 
 * @param row 
 * @param column 
 * @return 
 */
public void setMergedRegion(Sheet sheet) {
    int sheetMergeCount = sheet.getNumMergedRegions();

    for (int i = 0; i < sheetMergeCount; i++) {
        // ????  
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstRow = ca.getFirstRow();
        if (startReadPos - 1 > firstRow) {// ??????  
            continue;
        }
        int lastRow = ca.getLastRow();
        int mergeRows = lastRow - firstRow;// ?  
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        // ???????  
        for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) {
            // ??  
            sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn));
            j = j + mergeRows;// ?  
        }

    }
}

From source file:com.vaadin.addon.spreadsheet.action.UnMergeCellsAction.java

License:Open Source License

@Override
public void executeActionOnSelection(Spreadsheet spreadsheet, SelectionChangeEvent event) {
    CellRangeAddress selectedCellReference = event.getSelectedCellMergedRegion();
    Sheet sheet = spreadsheet.getActiveSheet();
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        if (selectedCellReference.getFirstColumn() == mergedRegion.getFirstColumn()
                && selectedCellReference.getFirstRow() == mergedRegion.getFirstRow()) {
            spreadsheet.removeMergedRegion(i);
        }//from w ww.  j  a  v  a 2s  .c  om
    }
}