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

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

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

Introduction

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

Prototype

void removeMergedRegion(int index);

Source Link

Document

Removes a merged region of cells (hence letting them free)

Usage

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  . ja v a 2 s  .c o  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.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 w w  w .  j  a v a2s  .  co m
    }
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

private void deleteMergedRegion(int index) {
    final Sheet sheet = getActiveSheet();
    sheet.removeMergedRegion(index);
    MergedRegion mergedRegion = getState().mergedRegions.remove(index);
    // update the style for the region cells, effects region + 1 row&col
    for (int r = mergedRegion.row1; r <= (mergedRegion.row2 + 1); r++) {
        Row row = sheet.getRow(r - 1);/*from ww w  .j a  v  a  2  s . co m*/
        if (row != null) {
            for (int c = mergedRegion.col1; c <= (mergedRegion.col2 + 1); c++) {
                Cell cell = row.getCell(c - 1);
                if (cell != null) {
                    styler.cellStyleUpdated(cell, false);
                    valueManager.markCellForUpdate(cell);
                } else {
                    styler.clearCellStyle(r, c);
                }
            }
        }
    }
    styler.loadCustomBorderStylesToState();
}

From source file:net.sf.excelutils.tags.ForeachTag.java

License:Apache License

public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell)
        throws ExcelException {
    int forstart = curRow.getRowNum();
    int forend = -1;
    int forCount = 0;
    String foreach = "";
    boolean bFind = false;
    LOG.debug("ForeachTag: start=" + forstart);
    for (int rownum = forstart; rownum <= sheet.getLastRowNum(); rownum++) {
        Row row = sheet.getRow(rownum);/*  w w w  . jav a 2s .co m*/
        if (null == row)
            continue;
        for (short colnum = row.getFirstCellNum(); colnum <= row.getLastCellNum(); colnum++) {
            Cell cell = row.getCell(colnum, Row.RETURN_NULL_AND_BLANK);
            if (null == cell)
                continue;
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                String cellstr = cell.getStringCellValue();

                // get the tag instance for the cellstr
                ITag tag = ExcelParser.getTagClass(cellstr);

                if (null != tag) {
                    if (tag.hasEndTag()) {
                        if (0 == forCount) {
                            forstart = rownum;
                            foreach = cellstr;
                        }
                        forCount++;
                        break;
                    }
                }
                if (cellstr.startsWith(KEY_END)) {
                    forend = rownum;
                    forCount--;
                    if (forstart >= 0 && forend >= 0 && forend > forstart && forCount == 0) {
                        bFind = true;
                    }
                    break;
                }
            }
        }
        if (bFind)
            break;
    }

    if (!bFind)
        return new int[] { 0, 0, 1 };

    String properties = "";
    String property = "";
    // parse the collection an object
    StringTokenizer st = new StringTokenizer(foreach, " ");
    int pos = 0;
    while (st.hasMoreTokens()) {
        String str = st.nextToken();
        if (pos == 1) {
            property = str;
        }
        if (pos == 3) {
            properties = str;
        }
        pos++;
    }
    // get collection
    Object collection = ExcelParser.parseStr(context, properties);
    if (null == collection) {
        return new int[] { 0, 0, 1 };
    }
    // get the iterator of collection
    Iterator iterator = ExcelParser.getIterator(collection);

    // iterator
    int shiftNum = forend - forstart - 1;
    // set the start row number
    ExcelUtils.addValue(context, property + "StartRowNo", new Integer(forstart + 1));

    int old_forend = forend;
    int propertyId = 0;
    int shift = 0;
    if (null != iterator) {
        while (iterator.hasNext()) {
            Object obj = iterator.next();

            ExcelUtils.addValue(context, property, obj);
            // Iterator ID
            ExcelUtils.addValue(context, property + "Id", new Integer(propertyId));
            // Index start with 1
            ExcelUtils.addValue(context, property + "Index", new Integer(propertyId + 1));

            // shift the #foreach #end block
            sheet.shiftRows(forstart, sheet.getLastRowNum(), shiftNum, true, true);
            // copy the body fo #foreach #end block
            WorkbookUtils.copyRow(sheet, forstart + shiftNum + 1, forstart, shiftNum);
            // parse
            shift = ExcelParser.parse(context, wb, sheet, forstart, forstart + shiftNum - 1);

            forstart += shiftNum + shift;
            forend += shiftNum + shift;
            propertyId++;
        }
        ExcelUtils.addValue(context, property + "Size", new Integer(propertyId));
    }
    // set the end row number
    ExcelUtils.addValue(context, property + "EndRowNo", new Integer(forstart));
    // delete #foreach #end block
    for (int rownum = forstart; rownum <= forend; rownum++) {
        sheet.removeRow(WorkbookUtils.getRow(rownum, sheet));
    }

    // remove merged region in forstart & forend
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() >= forstart && r.getLastRow() <= forend) {
            sheet.removeMergedRegion(i);
            // we have to back up now since we removed one
            i = i - 1;
        }
    }

    if (forend + 1 <= sheet.getLastRowNum()) {
        sheet.shiftRows(forend + 1, sheet.getLastRowNum(), -(forend - forstart + 1), true, true);
    }
    return new int[] { ExcelParser.getSkipNum(forstart, forend), ExcelParser.getShiftNum(old_forend, forstart),
            1 };
}

From source file:net.sf.excelutils.tags.IfTag.java

License:Apache License

public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell)
        throws ExcelException {
    int ifstart = curRow.getRowNum();
    int ifend = -1;
    int ifCount = 0;
    String ifstr = "";
    boolean bFind = false;
    for (int rownum = ifstart; rownum <= sheet.getLastRowNum(); rownum++) {
        Row row = sheet.getRow(rownum);/*from w  w w . ja v  a  2s .com*/
        if (null == row)
            continue;
        for (short colnum = row.getFirstCellNum(); colnum <= row.getLastCellNum(); colnum++) {
            Cell cell = row.getCell(colnum, Row.RETURN_NULL_AND_BLANK);
            if (null == cell)
                continue;
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                String cellstr = cell.getStringCellValue();

                // get the tag instance for the cellstr
                ITag tag = ExcelParser.getTagClass(cellstr);

                if (null != tag) {
                    if (tag.hasEndTag()) {
                        if (0 == ifCount) {
                            ifstart = rownum;
                            ifstr = cellstr;
                        }
                        ifCount++;
                        break;
                    }
                }
                if (cellstr.startsWith(KEY_END)) {
                    ifend = rownum;
                    ifCount--;
                    if (ifstart >= 0 && ifend >= 0 && ifend > ifstart && ifCount == 0) {
                        bFind = true;
                    }
                    break;
                }
            }
        }
        if (bFind)
            break;
    }

    if (!bFind)
        return new int[] { 0, 0, 1 };

    // test if condition
    boolean bResult = false;
    // remove #if tag and get condition expression
    String expr = ifstr.trim().substring(KEY_IF.length()).trim();

    // parse the condition expression
    expr = (String) ExcelParser.parseStr(context, expr, true);

    // use beanshell to eval expression value

    try {
        Interpreter in = createInterpreter(context);
        LOG.debug("IfTag test expr=" + expr);
        Object v = in.eval(expr);
        bResult = ((Boolean) v).booleanValue();
    } catch (Exception e) {
        LOG.error("IfTag test expr error", e);
        bResult = false;
    }

    if (bResult) { // if condition is true
        // remove #if tag and #end tag only
        sheet.removeRow(WorkbookUtils.getRow(ifstart, sheet));
        sheet.removeRow(WorkbookUtils.getRow(ifend, sheet));
        // remove merged region in ifstart & ifend
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress r = sheet.getMergedRegion(i);
            if (r.getFirstRow() == ifstart && r.getLastRow() == ifstart
                    || r.getFirstRow() == ifend && r.getLastRow() == ifend) {
                sheet.removeMergedRegion(i);
                // we have to back up now since we removed one
                i = i - 1;
            }
        }
        if (ifend + 1 <= sheet.getLastRowNum()) {
            sheet.shiftRows(ifend + 1, sheet.getLastRowNum(), -1, true, true);
        }
        if (ifstart + 1 <= sheet.getLastRowNum()) {
            sheet.shiftRows(ifstart + 1, sheet.getLastRowNum(), -1, true, true);
        }
        return new int[] { 1, -2, 1 };
    } else { // if condition is false
        // remove #if #end block
        for (int rownum = ifstart; rownum <= ifend; rownum++) {
            sheet.removeRow(WorkbookUtils.getRow(rownum, sheet));
        }
        // remove merged region in ifstart & ifend
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress r = sheet.getMergedRegion(i);
            if (r.getFirstRow() >= ifstart && r.getLastRow() <= ifend) {
                sheet.removeMergedRegion(i);
                // we have to back up now since we removed one
                i = i - 1;
            }
        }
        if (ifend + 1 <= sheet.getLastRowNum()) {
            sheet.shiftRows(ifend + 1, sheet.getLastRowNum(), -(ifend - ifstart + 1), true, true);
        }
        return new int[] { ExcelParser.getSkipNum(ifstart, ifend), ExcelParser.getShiftNum(ifend, ifstart), 1 };
    }
}

From source file:net.sf.excelutils.tags.SheetTag.java

License:Apache License

/**
 * Parse #sheet detail in list by sheetName
 *///  www  .  j  av a2 s. c  om
public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell)
        throws ExcelException {
    String sheetExpr = curCell.getStringCellValue();
    StringTokenizer st = new StringTokenizer(sheetExpr, " ");

    String properties = "";
    String property = "";
    String sheetName = "";
    // parse the collection an object
    int pos = 0;
    while (st.hasMoreTokens()) {
        String str = st.nextToken();
        if (pos == 1) {
            property = str;
        }
        if (pos == 3) {
            properties = str;
        }
        if (pos == 5) {
            sheetName = str;
        }
        pos++;
    }

    // get collection
    Object collection = ExcelParser.parseStr(context, properties);
    if (null == collection) {
        return new int[] { 0, 0, 1 };
    }

    // remove #sheet tag
    sheet.removeRow(curRow);

    // remove merged region in forstart & forend
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() >= curRow.getRowNum() && r.getLastRow() <= curRow.getRowNum()) {
            sheet.removeMergedRegion(i);
            i = i - 1;
        }
    }
    sheet.shiftRows(curRow.getRowNum() + 1, sheet.getLastRowNum(), -1, true, true);

    // get the iterator of collection
    Iterator iterator = ExcelParser.getIterator(collection);
    if (null != iterator) {
        // first obj, use parse method
        Object firstObj = null;
        if (iterator.hasNext()) {
            firstObj = iterator.next();
        }

        // next obj, clone sheet and use parseSheet method
        while (iterator.hasNext()) {
            Object obj = iterator.next();
            ExcelUtils.addValue(context, property, obj);
            try {
                int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet);

                // clone sheet
                Sheet cloneSheet = wb.cloneSheet(sheetIndex);

                // set cloneSheet name
                int cloneSheetIndex = WorkbookUtils.getSheetIndex(wb, cloneSheet);
                setSheetName(obj, wb, cloneSheetIndex, sheetName);

                // parse cloneSheet
                ExcelUtils.parseSheet(context, wb, cloneSheet);
            } catch (Exception e) {
                if (LOG.isErrorEnabled()) {
                    LOG.error("parse sheet error", e);
                }
            }
        }

        if (null != firstObj) {
            ExcelUtils.addValue(context, property, firstObj);
            // set sheet name
            int sheetIndex = WorkbookUtils.getSheetIndex(wb, sheet);
            setSheetName(firstObj, wb, sheetIndex, sheetName);
        }
    }

    return new int[] { 0, -1, 0 };
}

From source file:net.sf.excelutils.WorkbookUtils.java

License:Apache License

public static void shiftCell(Sheet sheet, Row row, Cell beginCell, int shift, int rowCount) {

    if (shift == 0)
        return;//from w w  w . j a  v  a 2s.  c  om

    // get the from & to row
    int fromRow = row.getRowNum();
    int toRow = row.getRowNum() + rowCount - 1;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() == row.getRowNum()) {
            if (r.getLastRow() > toRow) {
                toRow = r.getLastRow();
            }
            if (r.getFirstRow() < fromRow) {
                fromRow = r.getFirstRow();
            }
        }
    }

    for (int rownum = fromRow; rownum <= toRow; rownum++) {
        Row curRow = WorkbookUtils.getRow(rownum, sheet);
        int lastCellNum = curRow.getLastCellNum();
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);
            Cell toCell = WorkbookUtils.getCell(curRow, cellpos + shift);
            toCell.setCellType(fromCell.getCellType());
            toCell.setCellStyle(fromCell.getCellStyle());
            switch (fromCell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                toCell.setCellValue(fromCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                toCell.setCellFormula(fromCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                toCell.setCellValue(fromCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                toCell.setCellValue(fromCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                toCell.setCellErrorValue(fromCell.getErrorCellValue());
                break;
            }
            fromCell.setCellValue("");
            fromCell.setCellType(Cell.CELL_TYPE_BLANK);
            // Workbook wb = new Workbook();
            // CellStyle style = wb.createCellStyle();
            // fromCell.setCellStyle(style);
        }

        // process merged region
        for (int cellpos = lastCellNum; cellpos >= beginCell.getColumnIndex(); cellpos--) {
            Cell fromCell = WorkbookUtils.getCell(curRow, cellpos);

            List shiftedRegions = new ArrayList();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress r = sheet.getMergedRegion(i);
                if (r.getFirstRow() == curRow.getRowNum() && r.getFirstColumn() == fromCell.getColumnIndex()) {
                    r.setFirstColumn((short) (r.getFirstColumn() + shift));
                    r.setLastColumn((short) (r.getLastColumn() + shift));
                    // have to remove/add it back
                    shiftedRegions.add(r);
                    sheet.removeMergedRegion(i);
                    // we have to back up now since we removed one
                    i = i - 1;
                }
            }

            // readd so it doesn't get shifted again
            Iterator iterator = shiftedRegions.iterator();
            while (iterator.hasNext()) {
                CellRangeAddress region = (CellRangeAddress) iterator.next();
                sheet.addMergedRegion(region);
            }
        }
    }
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ????//  ww  w  .  j  av  a 2 s  . c o m
 * 
 * @param sheet 
 * @param rangeAddress 
 */
public static void clearMergedRegion(Sheet sheet, CellRangeAddress rangeAddress) {

    // ????
    Set<CellRangeAddress> clearMergedCellSet = getMergedAddress(sheet, rangeAddress);

    // ???
    SortedSet<Integer> deleteIndexs = new TreeSet<Integer>(Collections.reverseOrder());
    int fromSheetMargNums = sheet.getNumMergedRegions();
    for (int i = 0; i < fromSheetMargNums; i++) {

        CellRangeAddress mergedAddress = null;
        if (sheet instanceof XSSFSheet) {
            mergedAddress = ((XSSFSheet) sheet).getMergedRegion(i);
        } else if (sheet instanceof HSSFSheet) {
            mergedAddress = ((HSSFSheet) sheet).getMergedRegion(i);
        }

        for (CellRangeAddress address : clearMergedCellSet) {
            if (mergedAddress.formatAsString().equals(address.formatAsString())) {
                // 
                deleteIndexs.add(i);
                break;
            }
        }

    }
    for (Integer index : deleteIndexs) {
        sheet.removeMergedRegion(index);
    }
}

From source file:org.bbreak.excella.reports.listener.RemoveAdapter.java

License:Open Source License

/**
 * ??????????????//from   w  w w.  j a va  2  s  .co m
 * 
 * @param sheet
 * @param removeRowNum
 * @param removeColNum
 */
private void removeRegion(Sheet sheet, int removeRowNum, int removeColNum) {
    // ???????
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        // ??(Region)??
        CellRangeAddress region = sheet.getMergedRegion(i);

        if (removeRowNum != -1) {
            if (region.getFirstRow() > removeRowNum || removeRowNum > region.getLastRow()) {
                continue;
            }
        }

        if (removeColNum != -1) {
            if (region.getFirstColumn() > removeColNum || removeColNum > region.getLastColumn()) {
                continue;
            }
        }
        // ???INDEX?????
        sheet.removeMergedRegion(i);
        return;
    }
}

From source file:org.openpythia.utilities.SSUtilities.java

License:Apache License

public static void deleteRow(Sheet sheet, Row rowToDelete) {

    // if the row contains merged regions, delete them
    List<Integer> mergedRegionsToDelete = new ArrayList<>();
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == rowToDelete.getRowNum()
                && mergedRegion.getLastRow() == rowToDelete.getRowNum()) {
            // this region is within the row - so mark it for deletion
            mergedRegionsToDelete.add(i);
        }/*from  ww w .j av  a  2s .  c  o m*/
    }

    // now that we know all regions to delete just do it
    for (Integer indexToDelete : mergedRegionsToDelete) {
        sheet.removeMergedRegion(indexToDelete);
    }

    int rowIndex = rowToDelete.getRowNum();

    // this only removes the content of the row
    sheet.removeRow(rowToDelete);

    int lastRowNum = sheet.getLastRowNum();

    // shift the rest of the sheet one index down
    if (rowIndex >= 0 && rowIndex < lastRowNum) {
        sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
    }
}