Example usage for org.apache.poi.ss.usermodel Row getLastCellNum

List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getLastCellNum

Introduction

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

Prototype

short getLastCellNum();

Source Link

Document

Gets the index of the last cell contained in this row PLUS ONE.

Usage

From source file:net.lizhaoweb.maker.code.java.model.excel.read.ExcelFileReader.java

License:Open Source License

private Set<FieldInformation> analysisSheet(Configuration configuration, Sheet sheet) {
    if (configuration == null) {
        throw new IllegalArgumentException("The configuration is null");
    }/* w w  w . ja va 2  s.c om*/
    if (sheet == null) {
        throw new IllegalArgumentException("The sheet is null");
    }

    // Sheet 
    int rowSize = sheet.getLastRowNum();
    if (rowSize < 1) {
        throw new IllegalArgumentException("The sheet rows number less than 1");
    }

    // Sheet 
    Row titleRow = sheet.getRow(0);
    if (titleRow == null) {
        throw new IllegalArgumentException("The sheet title row is not found");
    }

    // Sheet 
    int columnSize = titleRow.getLastCellNum();
    if (columnSize < 1) {
        throw new IllegalArgumentException("The sheet columns number less than 1");
    }

    // ?
    Map<Integer, FieldInformation> fieldInformationMap = new HashMap<Integer, FieldInformation>();

    // ??
    ExecutorService fieldNameExecutorService = Executors.newCachedThreadPool();
    List<Future<Map<Integer, String>>> fieldNameFutureList = new ArrayList<Future<Map<Integer, String>>>();

    // ? Sheet 
    for (int columnIndex = 0; columnIndex < columnSize; columnIndex++) {
        FieldInformation fieldInformation = new FieldInformation();
        Cell cell = titleRow.getCell(columnIndex);
        String titleContent = cell.getStringCellValue();
        fieldInformation.setTitle(titleContent);
        fieldInformation.setDescribe(titleContent);
        fieldInformation.setType("String");
        fieldInformationMap.put(columnIndex, fieldInformation);

        // ???
        Future<Map<Integer, String>> classNameFuture = fieldNameExecutorService
                .submit(new TranslateCallable(this.getTranslator(), configuration, columnIndex, titleContent));
        fieldNameFutureList.add(classNameFuture);
    }

    // ? Sheet ?
    for (int rowIndex = 1; rowIndex <= rowSize; rowIndex++) {
        Row dataRow = sheet.getRow(rowIndex);
        if (dataRow == null) {// 
            continue;
        }
        if (dataRow.getLastCellNum() < columnSize) {// ?
            continue;
        }
        for (int columnIndex = 0; columnIndex < columnSize; columnIndex++) {
            FieldInformation fieldInformation = fieldInformationMap.get(columnIndex);
            Cell cell = dataRow.getCell(columnIndex);
            CellType cellType = cell.getCellTypeEnum();
            if (CellType.BOOLEAN == cellType) {
                fieldInformation.setType("java.lang.Boolean");
            } else if (CellType.NUMERIC == cellType) {
                try {
                    Date dateValue = cell.getDateCellValue();
                    if (dateValue == null) {
                        fieldInformation.setType("java.lang.Double");
                    } else {
                        fieldInformation.setType("java.util.Date");
                    }
                } catch (Exception e) {
                    fieldInformation.setType("java.lang.Double");
                }
            }
        }
    }

    // ??
    for (Future<Map<Integer, String>> future : fieldNameFutureList) {
        try {
            Map<Integer, String> fieldNameMap = future.get();
            for (Map.Entry<Integer, String> fieldNameMapEntry : fieldNameMap.entrySet()) {
                Integer key = fieldNameMapEntry.getKey();
                String value = fieldNameMapEntry.getValue();
                fieldInformationMap.get(key).setName(StringUtil.uncapitalize(value));
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
    }
    fieldNameExecutorService.shutdown();

    Set<FieldInformation> result = new HashSet<FieldInformation>(fieldInformationMap.values());
    return result;
}

From source file:net.morphbank.loadexcel.SheetReader.java

License:Open Source License

public Cell[] getRowCells(String sheetName, int rowNum) {
    Sheet sheet = getSheet(sheetName);/*from   w w w.j  av a 2 s  .  com*/
    Row row = sheet.getRow(rowNum);
    Cell allCellsAtRow[] = new Cell[row.getLastCellNum()];
    for (Cell cell : row) {
        allCellsAtRow[cell.getColumnIndex()] = cell;
    }
    return allCellsAtRow;
}

From source file:net.pcal.sqlsheet.XlsResultSetMetaData.java

License:Apache License

public XlsResultSetMetaData(Sheet sheet, XlsResultSet resultset, int firstSheetRowOffset) throws SQLException {

    if (sheet == null)
        throw new IllegalArgumentException();
    this.resultset = resultset;
    Row row = sheet.getRow(firstSheetRowOffset - 1);
    if (row == null) {
        throw new SQLException("No header row in sheet");
    }//from ww w.j a  v a  2s . com
    formatter = new DataFormatter();
    columnNames = new ArrayList<String>();
    for (short c = 0; c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c);
        String columnName = formatter.formatCellValue(cell);

        // Is it unique in the column name set
        int suffix;
        while (columnNames.contains(columnName)) {
            suffix = 1;
            columnName += "_" + suffix;
        }

        columnNames.add(columnName);
    }

    // Data Type profiling on the whole excel file
    int currentRowNumber = resultset.getRow();

    // A double map to back the relation between the column Id and the count of type
    Map<Integer, Map<Integer, Integer>> columnTypeScan = new HashMap<Integer, Map<Integer, Integer>>();
    while (resultset.next()) {
        int typeCode;
        for (int columnId = 1; columnId <= getColumnCount(); columnId++) {

            Cell cell = resultset.getCell(columnId);
            if (cell != null) {

                int excelCellType = cell.getCellType();
                switch (excelCellType) {
                case Cell.CELL_TYPE_BOOLEAN:
                    typeCode = Types.VARCHAR;
                    break;
                case Cell.CELL_TYPE_STRING:
                    typeCode = Types.VARCHAR;
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        typeCode = Types.DATE;
                    } else {
                        typeCode = Types.DOUBLE;
                    }
                    break;
                case Cell.CELL_TYPE_BLANK:
                    typeCode = Types.NULL;
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    try {
                        cell.getStringCellValue();
                        typeCode = Types.VARCHAR;
                    } catch (Exception e) {
                        cell.getNumericCellValue();
                        typeCode = Types.DOUBLE;
                    }
                    break;
                case Cell.CELL_TYPE_ERROR:
                    throw new RuntimeException("The ExcelType ( ERROR ) is not supported - Cell ("
                            + resultset.getRow() + "," + columnId + ")");

                default:
                    throw new RuntimeException("The ExcelType (" + excelCellType + ") is not supported - Cell ("
                            + resultset.getRow() + "," + columnId + ")");
                }
            } else {
                typeCode = Types.NULL;
            }
            Map<Integer, Integer> columnIdTypeMap = columnTypeScan.get(columnId);
            if (columnIdTypeMap == null) {
                columnIdTypeMap = new HashMap<Integer, Integer>();
                columnIdTypeMap.put(typeCode, 1);
                columnTypeScan.put(columnId, columnIdTypeMap);
            } else {
                Integer columnIdType = columnIdTypeMap.get(typeCode);
                if (columnIdType == null) {
                    columnIdTypeMap.put(typeCode, 1);
                } else {
                    int count = columnIdTypeMap.get(typeCode) + 1;
                    columnIdTypeMap.put(typeCode, count);
                }
            }

        }
        // Retrieve only one type
        for (Integer columnId : columnTypeScan.keySet()) {

            Integer numberOfVarchar = 0;
            Integer numberOfDouble = 0;
            Integer numberOfDate = 0;

            for (Map.Entry<Integer, Integer> columnIdTypeMap : columnTypeScan.get(columnId).entrySet()) {
                if (columnIdTypeMap.getKey() == Types.VARCHAR) {
                    numberOfVarchar = columnIdTypeMap.getValue();
                } else if (columnIdTypeMap.getKey() == Types.DOUBLE) {
                    numberOfDouble = columnIdTypeMap.getValue();
                } else if (columnIdTypeMap.getKey() == Types.DATE) {
                    numberOfDate = columnIdTypeMap.getValue();
                }
            }
            Integer finalColumnType = null;
            if (numberOfVarchar != 0) {
                finalColumnType = Types.VARCHAR;
            } else {
                if (numberOfDouble != 0 && numberOfDate == 0) {
                    finalColumnType = Types.DOUBLE;
                }
                if (numberOfDouble == 0 && numberOfDate != 0) {
                    finalColumnType = Types.DATE;
                }
            }
            if (finalColumnType == null) {
                finalColumnType = Types.VARCHAR;
            }
            columnTypeMap.put(columnId, finalColumnType);
        }

    }

    // Go back to the current row
    resultset.absolute(currentRowNumber);

}

From source file:net.sf.ahtutils.report.util.DataUtil.java

public static void debugRow(Sheet sheet, Integer rowIndex) {
    // Using a StringBuffer to create one line with all column titles
    StringBuffer sb = new StringBuffer();
    sb.append("Debugging Row " + rowIndex + " ... ");

    // Selecting first row since this should be the place where the column titles should be placed 
    Row firstRow = sheet.getRow(rowIndex);

    // Iterating through all cells in first row
    for (short i = firstRow.getFirstCellNum(); i < firstRow.getLastCellNum(); i++) {
        Cell cell = firstRow.getCell(i);
        // Get the Cell Value as Object
        Object object = DataUtil.getCellValue(cell);

        // Get a String representation of the value
        String cellValue = getStringValue(object);

        // Add the content of the cell to StringBuffer
        sb.append("Column " + i + ": '" + cellValue + "' ");
    }/*ww w. j a  v a 2  s.c  o m*/

    // Show the StringBuffer content in logging
    logger.info(sb.toString());
}

From source file:net.sf.dvstar.swirl.desktopdbf.data.ExcelTableModel.java

License:Open Source License

/**
 * Called to convert a row of cells into a line of data that can later be
 * output to the CSV file./*from w ww.ja v  a2  s  . c o m*/
 *
 * @param row An instance of either the HSSFRow or XSSFRow classes that
 *            encapsulates information about a row of cells recovered from
 *            an Excel workbook.
 */
private ArrayList rowToCSV(Row row) {
    Cell cell = null;
    int lastCellNum = 0;
    ArrayList<String> csvLine = new ArrayList<String>();

    // Check to ensure that a row was recovered from the sheet as it is
    // possible that one or more rows between other populated rows could be
    // missing - blank. If the row does contain cells then...
    if (row != null) {

        // Get the index for the right most cell on the row and then
        // step along the row from left to right recovering the contents
        // of each cell, converting that into a formatted String and
        // then storing the String into the csvLine ArrayList.
        lastCellNum = row.getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            cell = row.getCell(i);
            if (cell == null) {
                csvLine.add("");
            } else {
                if (cell.getCellType() != Cell.CELL_TYPE_FORMULA) {
                    String cellValue = this.formatter.formatCellValue(cell);
                    if (i + 1 > getMaxColumnWidths().size()) {
                        getMaxColumnWidths().add(new Integer(0));
                    }
                    //                        int newW = Math.max(cellValue.length() * CHAR_W0, curW * CHAR_W0);
                    int curW = getMaxColumnWidths().get(i);
                    int newW = Math.max(cellValue.length(), curW);
                    getMaxColumnWidths().set(i, newW);
                    csvLine.add(cellValue);
                } else {
                    csvLine.add(this.formatter.formatCellValue(cell, this.evaluator));
                }
            }
        }
        // Make a note of the index number of the right most cell. This value
        // will later be used to ensure that the matrix of data in the CSV file
        // is square.
        if (lastCellNum > this.getMaxRowWidth()) {
            this.maxRowWidth = lastCellNum;
        }
    }
    //this.csvData.add(csvLine);
    return csvLine;
}

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

License:Apache License

/**
 * parse the Excel template//from w w  w . j  ava  2 s  .c  o m
 * 
 * @param context data object
 * @param sheet Excel sheet
 * @param fromRow the start
 * @param toRow the end
 * @return int skip number
 */
public static int parse(Object context, Workbook wb, Sheet sheet, int fromRow, int toRow)
        throws ExcelException {
    int[] shift = new int[] { 0, 0, 0 }; // {SkipNum, ShiftNum, break flag}
    int shiftCount = 0;

    int rownum = fromRow;
    while (rownum <= toRow) {
        // shift
        rownum += shift[1] + shift[0];
        toRow += shift[1];
        if (rownum > toRow)
            break;

        shift[0] = 0;
        shift[1] = 0;
        shift[2] = 0;
        Row row = sheet.getRow(rownum);
        // set current row number
        ExcelUtils.addValue(context, "currentRowNo", new Integer(rownum + 1));
        if (null == row) {
            rownum++;
            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) {
                continue;
            }
            // if the cell is null then continue
            String cellstr = cell.getStringCellValue();
            if (null == cellstr || "".equals(cellstr)) {
                continue;
            }

            ITag tag = getTagClass(cellstr);
            if (null != tag) {
                shift = tag.parseTag(context, wb, sheet, row, cell);
            } else {
                parseCell(context, sheet, row, cell);
            }

            shiftCount += shift[1];
            if (shift[2] == 1)
                break;
        }
        rownum++;
    }
    return shiftCount;
}

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);
        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;
                        }//  ww w .j  ava2  s.  co m
                        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);
        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;
                        }// w ww  .j  a  v a 2s.  c o  m
                        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.WorkbookUtils.java

License:Apache License

/**
 * Set Print Area//from   w w w.j  a  v  a 2 s . c o m
 *
 * @param wb
 * @param sheetIndex
 */
public static void setPrintArea(Workbook wb, int sheetIndex) {
    // sheet
    Sheet sheet = wb.getSheetAt(sheetIndex);
    if (null != sheet) {
        // #endRow
        Row endRow = sheet.getRow(sheet.getLastRowNum());
        if (null != endRow) {
            Cell cell = WorkbookUtils.getCell(endRow, 0);
            String cellStr = cell.getStringCellValue();
            cellStr = cellStr == null ? "" : cellStr.trim();
            if (cellStr.startsWith(EndRowTag.KEY_ENDROW)) {
                // search #endColumn
                int endColumn = endRow.getLastCellNum();
                for (int i = endRow.getLastCellNum(); i >= endRow.getFirstCellNum(); i--) {
                    Cell endCell = WorkbookUtils.getCell(endRow, i);
                    String endCellStr = endCell.getStringCellValue();
                    endCellStr = endCellStr == null ? "" : endCellStr.trim();
                    if (endCellStr.startsWith(EndRowTag.KEY_ENDCOLUMN)) {
                        endColumn = i;
                        break;
                    }
                }
                wb.setPrintArea(sheetIndex, endRow.getFirstCellNum(), endColumn, sheet.getFirstRowNum(),
                        sheet.getLastRowNum() - 1);
                sheet.removeRow(endRow);
            }
        }
    }
}

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

License:Apache License

/**
 * copy row//from  w w  w .  j av  a  2s. c o m
 *
 * @param sheet
 * @param from begin of the row
 * @param to destination fo the row
 * @param count count of copy
 */
public static void copyRow(Sheet sheet, int from, int to, int count) {

    for (int rownum = from; rownum < from + count; rownum++) {
        Row fromRow = sheet.getRow(rownum);
        Row toRow = getRow(to + rownum - from, sheet);
        if (null == fromRow)
            return;
        toRow.setHeight(fromRow.getHeight());
        toRow.setHeightInPoints(fromRow.getHeightInPoints());
        int lastCellNum = fromRow.getLastCellNum();
        lastCellNum = lastCellNum > 255 ? 255 : lastCellNum;
        for (int i = fromRow.getFirstCellNum(); i <= lastCellNum && i >= 0; i++) {
            Cell fromCell = getCell(fromRow, i);
            Cell toCell = getCell(toRow, i);
            // toCell.setEncoding(fromCell.getEncoding());
            toCell.setCellStyle(fromCell.getCellStyle());
            toCell.setCellType(fromCell.getCellType());
            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;
            default:
            }
        }
    }

    // copy merged region
    List shiftedRegions = new ArrayList();
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress r = sheet.getMergedRegion(i);
        if (r.getFirstRow() >= from && r.getLastRow() < from + count) {
            CellRangeAddress n_r = new CellRangeAddress(r.getFirstRow() + to - from, r.getLastRow() + to - from,
                    r.getFirstColumn(), r.getLastColumn());
            shiftedRegions.add(n_r);
        }
    }

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