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

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

Introduction

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

Prototype

void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);

Source Link

Document

Shifts rows between startRow and endRow n number of rows.

Usage

From source file:cn.afterturn.easypoi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * Sheet ?,?? ,??/*from  w ww  . j  a v  a2  s .co m*/
 *
 * @param sheet
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook)
        throws Exception {

    // ??
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);
    // 
    Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // ??
    List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null, null);
    // ??
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    int shiftRows = getShiftRows(dataSet, excelParams);
    //?,?
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(),
            shiftRows, true, true);
    mergedRegionHelper.shiftRows(sheet, teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
            shiftRows);
    templateSumHandler.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
            shiftRows);
    PoiExcelTempUtil.reset(sheet, teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(),
            sheet.getLastRowNum());
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];
    }
    // ??
    mergeCells(sheet, excelParams, titleHeight);
}

From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * Sheet ?,?? ,??/*from   w  w  w  .  j av  a2  s.c o  m*/
 *
 * @param sheet
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook)
        throws Exception {

    if (workbook instanceof XSSFWorkbook) {
        super.type = ExcelType.XSSF;
    }
    // ??
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = sheet.createDrawingPatriarch();
    // 
    Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
    ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // ??
    List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
    // ??
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    //?,?
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(),
            getShiftRows(dataSet, excelParams), true, true);
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
    }
    // ??
    mergeCells(sheet, excelParams, titleHeight);
}

From source file:com.qihang.winter.poi.excel.export.template.ExcelExportOfTemplateUtil.java

License:Apache License

/**
 * Sheet ?,?? ,??/*  www  .  j a  va  2  s .  c om*/
 * 
 * @param teplateParams
 * @param pojoClass
 * @param dataSet
 * @param workbook
 */
private void addDataToSheet(Class<?> pojoClass, Collection<?> dataSet, Sheet sheet, Workbook workbook)
        throws Exception {

    if (workbook instanceof XSSFWorkbook) {
        super.type = com.qihang.winter.poi.excel.entity.enmus.ExcelType.XSSF;
    }
    // ??
    Map<String, Integer> titlemap = getTitleMap(sheet);
    Drawing patriarch = sheet.createDrawingPatriarch();
    // 
    Field[] fileds = com.qihang.winter.poi.util.PoiPublicUtil.getClassFields(pojoClass);
    com.qihang.winter.poi.excel.annotation.ExcelTarget etarget = pojoClass
            .getAnnotation(com.qihang.winter.poi.excel.annotation.ExcelTarget.class);
    String targetId = null;
    if (etarget != null) {
        targetId = etarget.value();
    }
    // ??
    List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams = new ArrayList<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity>();
    getAllExcelField(null, targetId, fileds, excelParams, pojoClass, null);
    // ??
    sortAndFilterExportField(excelParams, titlemap);
    short rowHeight = getRowHeight(excelParams);
    int index = teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), titleHeight = index;
    //?,?
    sheet.shiftRows(teplateParams.getHeadingRows() + teplateParams.getHeadingStartRow(), sheet.getLastRowNum(),
            getShiftRows(dataSet, excelParams), true, true);
    if (excelParams.size() == 0) {
        return;
    }
    Iterator<?> its = dataSet.iterator();
    while (its.hasNext()) {
        Object t = its.next();
        index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight);
    }
    // ??
    mergeCells(sheet, excelParams, titleHeight);
}

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

License:Open Source License

/**
 * Shifts rows between startRow and endRow n number of rows. If you use a
 * negative number for n, the rows will be shifted upwards. This method
 * ensures that rows can't wrap around./*from w  w  w .  ja  va 2 s . c o m*/
 * <p>
 * If you are adding / deleting rows, you might want to change the number of
 * visible rows rendered {@link #getRows()} with {@link #setMaxRows(int)}.
 * <p>
 * See {@link Sheet#shiftRows(int, int, int, boolean, boolean)}.
 * 
 * @param startRow
 *            The first row to shift, 0-based
 * @param endRow
 *            The last row to shift, 0-based
 * @param n
 *            Number of rows to shift, positive numbers shift down, negative
 *            numbers shift up.
 * @param copyRowHeight
 *            True to copy the row height during the shift
 * @param resetOriginalRowHeight
 *            True to set the original row's height to the default
 */
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) {
    Sheet sheet = getActiveSheet();
    int lastNonBlankRow = getLastNonBlankRow(sheet);
    sheet.shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight);
    // need to re-send the cell values to client
    // remove all cached cell data that is now empty
    getFormulaEvaluator().clearAllCachedResultValues();
    int start = n < 0 ? Math.max(lastNonBlankRow, startRow) : startRow;
    int end = n < 0 ? endRow : startRow + n - 1;
    valueManager.updateDeletedRowsInClientCache(start + 1, end + 1);
    int firstAffectedRow = n < 0 ? startRow + n : startRow;
    int lastAffectedRow = n < 0 ? endRow : endRow + n;
    if (copyRowHeight || resetOriginalRowHeight) {
        // might need to increase the size of the row heights array
        int oldLength = getState(false).rowH.length;
        int neededLength = endRow + n + 1;
        if (n > 0 && oldLength < neededLength) {
            getState().rowH = Arrays.copyOf(getState().rowH, neededLength);
        }
        for (int i = firstAffectedRow; i <= lastAffectedRow; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                if (row.getZeroHeight()) {
                    getState().rowH[i] = 0f;
                } else {
                    getState().rowH[i] = row.getHeightInPoints();
                }
            } else {
                getState().rowH[i] = sheet.getDefaultRowHeightInPoints();
            }
        }
    }

    if (hasSheetOverlays()) {
        reloadImageSizesFromPOI = true;
    }
    // need to shift the cell styles, clear and update
    // need to go -1 and +1 because of shifted borders..
    final ArrayList<Cell> cellsToUpdate = new ArrayList<Cell>();
    for (int r = (firstAffectedRow - 1); r <= (lastAffectedRow + 1); r++) {
        if (r < 0) {
            r = 0;
        }
        Row row = sheet.getRow(r);
        final Integer rowIndex = new Integer(r + 1);
        if (row == null) {
            valueManager.updateDeletedRowsInClientCache(rowIndex, rowIndex);
            if (getState(false).hiddenRowIndexes.contains(rowIndex)) {
                getState().hiddenRowIndexes.remove(rowIndex);
            }
            for (int c = 0; c < getState().cols; c++) {
                styler.clearCellStyle(r, c);
            }
        } else {
            if (row.getZeroHeight()) {
                getState().hiddenRowIndexes.add(rowIndex);
            } else if (getState(false).hiddenRowIndexes.contains(rowIndex)) {
                getState().hiddenRowIndexes.remove(rowIndex);
            }
            for (int c = 0; c < getState().cols; c++) {
                Cell cell = row.getCell(c);
                if (cell == null) {
                    styler.clearCellStyle(r, c);
                    if (r <= lastNonBlankRow + n) {
                        // There might be a pre-shift value for this cell in
                        // client-side and should be overwritten
                        cell = row.createCell(c);
                        cellsToUpdate.add(cell);
                    }
                } else {
                    cellsToUpdate.add(cell);
                }
            }
        }
    }
    rowsMoved(firstAffectedRow, lastAffectedRow, n);

    for (Cell cell : cellsToUpdate) {
        styler.cellStyleUpdated(cell, false);
        markCellAsUpdated(cell, false);
    }
    styler.loadCustomBorderStylesToState();

    updateMarkedCells(); // deleted and formula cells and style selectors
    updateRowAndColumnRangeCellData(firstRow, firstColumn, lastRow, lastColumn); // shifted area values
    updateMergedRegions();

    CellReference selectedCellReference = selectionManager.getSelectedCellReference();
    if (selectedCellReference != null) {
        if (selectedCellReference.getRow() >= firstAffectedRow
                && selectedCellReference.getRow() <= lastAffectedRow) {
            selectionManager.onSheetAddressChanged(selectedCellReference.formatAsString(), false);
        }
    }
}

From source file:generate.CopyRow.java

/**
 * Copies a row from a row index on the given workbook and sheet to another row index. If the destination row is
 * already occupied, shift all rows down to make room.
 *
 * @param workbook//from  www  .  j a v  a2 s  .  com
 * @param worksheet
 * @param from
 * @param to
 */
public static void copyRow(Workbook workbook, Sheet worksheet, Integer from, Integer to) {
    Row sourceRow = worksheet.getRow(from);
    Row newRow = worksheet.getRow(to);

    if (alreadyExists(newRow))
        worksheet.shiftRows(to, worksheet.getLastRowNum(), 1, true, true);
    else {
        newRow = worksheet.createRow(to);
    }
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);
        if (oldCell != null) {
            copyCellStyle(workbook, oldCell, newCell);
            copyCellComment(oldCell, newCell);
            copyCellHyperlink(oldCell, newCell);
            copyCellDataTypeAndValue(oldCell, newCell);
        }
    }
    copyAnyMergedRegions(worksheet, sourceRow, newRow);
}

From source file:invoiceapplication.CopyRowOriginal.java

public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) {
    // Get the source / new row
    Row newRow = worksheet.getRow(destRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exists in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true);
    } else {/*from  w  w w .j av  a  2  s.  c  om*/
        newRow = worksheet.createRow(destRowNum);
    }
    copyAnyMergedRegions(worksheet, sourceRow, newRow);
    // Loops through source column to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        //Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // if the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Use old cell style
        newCell.setCellStyle(oldCell.getCellStyle());

        // If there is a cell comment, copy
        if (newCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellValue(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        }
    }
}

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);/*from   www .  j  a v  a 2  s. c  o  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 ww  .j a  va 2  s  . 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.PageTag.java

License:Apache License

public int[] parseTag(Object context, Workbook wb, Sheet sheet, Row curRow, Cell curCell)
        throws ExcelException {
    int rowNum = curRow.getRowNum();
    LOG.debug("#page at rownum = " + rowNum);
    sheet.setRowBreak(rowNum - 1);/*from  w w  w  . j a v a  2s .c  om*/
    sheet.removeRow(curRow);
    if (rowNum + 1 <= sheet.getLastRowNum()) {
        sheet.shiftRows(rowNum + 1, sheet.getLastRowNum(), -1, true, true);
    }
    return new int[] { 0, -1, 0 };
}

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

License:Apache License

/**
 * Parse #sheet detail in list by sheetName
 *///from www.  j  a v  a  2 s  . co  m
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 };
}