Java Code Examples for org.apache.poi.ss.util.CellRangeAddress#getLastRow()

/**
 * Also creates cells if they don't exist
 */
private CellRange<HSSFCell> getCellRange(CellRangeAddress range) {
    int firstRow = range.getFirstRow();
    int firstColumn = range.getFirstColumn();
    int lastRow = range.getLastRow();
    int lastColumn = range.getLastColumn();
    int height = lastRow - firstRow + 1;
    int width = lastColumn - firstColumn + 1;
    List<HSSFCell> temp = new ArrayList<HSSFCell>(height * width);
    for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) {
        for (int colIn = firstColumn; colIn <= lastColumn; colIn++) {
            HSSFRow row = getRow(rowIn);
            if (row == null) {
                row = createRow(rowIn);
            }
            HSSFCell cell = row.getCell(colIn);
            if (cell == null) {
                cell = row.createCell(colIn);
            }
            temp.add(cell);
        }
    }
    return SSCellRange.create(firstRow, firstColumn, height, width, temp, HSSFCell.class);
}
 

/**
 * 判断合并了行
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public static boolean isMergedRow(Sheet sheet,int row ,int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstColumn = range.getFirstColumn();
        int lastColumn = range.getLastColumn();
        int firstRow = range.getFirstRow();
        int lastRow = range.getLastRow();
        if(row == firstRow && row == lastRow){
            if(column >= firstColumn && column <= lastColumn){
                return true;
            }
        }
    }
    return false;
}
 

protected void validateCellRange(Matcher matcher, Cell cell) {
	int desiredR1 = Integer.parseInt( matcher.group(1) );
	int desiredC1 = Integer.parseInt( matcher.group(2) );
	int desiredR2 = Integer.parseInt( matcher.group(3) );
	int desiredC2 = Integer.parseInt( matcher.group(4) );
	
	int actualR1 = cell.getRowIndex() + 1;
	int actualC1 = cell.getColumnIndex() + 1;
	int actualR2 = actualR1;
	int actualC2 = actualC1;
	
	for( int i = 0; i < cell.getSheet().getNumMergedRegions(); ++i) {
		CellRangeAddress cra = cell.getSheet().getMergedRegion(i);
		if( ( cra.getFirstRow() == cell.getRowIndex() ) && ( cra.getFirstColumn() == cell.getColumnIndex() ) ) {
			assertEquals( actualR1, actualR2 );
			assertEquals( actualC1, actualC2 );
			actualR2 = cra.getLastRow() + 1;
			actualC2 = cra.getLastColumn() + 1;
		}
	}
	assertEquals( desiredR1, actualR1 );
	assertEquals( desiredC1, actualC1 );
	assertEquals( desiredR2, actualR2 );
	assertEquals( desiredC2, actualC2 );
}
 

public static CellRangeAddress getMergedRegion(final HSSFSheet sheet, final CellLocation location) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        final CellRangeAddress region = sheet.getMergedRegion(i);

        final int rowFrom = region.getFirstRow();
        final int rowTo = region.getLastRow();

        if (rowFrom == location.r && rowTo == location.r) {
            final int colFrom = region.getFirstColumn();

            if (colFrom == location.c) {
                return region;
            }
        }
    }

    return null;
}
 

/**
 * 获取合并单元格的值
 * @param sheet
 * @param row
 * @param column
 * @return
 */
public static String getMergedRegionValue(Sheet sheet ,int row , int column){
    int sheetMergeCount = sheet.getNumMergedRegions();

    for(int i = 0 ; i < sheetMergeCount ; i++){
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        int firstRow = ca.getFirstRow();
        int lastRow = ca.getLastRow();

        if(row >= firstRow && row <= lastRow){

            if(column >= firstColumn && column <= lastColumn){
                Row fRow = sheet.getRow(firstRow);
                Cell fCell = fRow.getCell(firstColumn);
                return getCellValue(fCell) ;
            }
        }
    }

    return null ;
}
 

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

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

@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
}
 

/**
 * 获取合并单元格的值
 *
 * @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;// 跳过已合并的行
		}
		
	}
}
 

private Span getSpan(XSSFSheet sheet,int row ,int column){
	int sheetMergeCount = sheet.getNumMergedRegions(); 
	for (int i = 0; i < sheetMergeCount; i++) {
		CellRangeAddress range = sheet.getMergedRegion(i);
		int firstColumn = range.getFirstColumn();
		int lastColumn = range.getLastColumn();
		int firstRow = range.getFirstRow();
		if(row == firstRow && column==firstColumn){  
			int lastRow = range.getLastRow();
			int rowSpan=lastRow-firstRow;
			if(rowSpan>0){
				rowSpan++;
			}
			int colSpan=lastColumn-firstColumn;
			if(colSpan>0){
				colSpan++;
			}
			return new Span(rowSpan,colSpan);
		}
	}
	return new Span(0,0);
}
 

/**
 *
 * @param rule
 * @return unmodifiable List of all cells in the rule's region matching the rule's condition
 */
public List<Cell> getMatchingCells(EvaluationConditionalFormatRule rule) {
    final List<Cell> cells = new ArrayList<Cell>();
    final Sheet sheet = rule.getSheet();
    
    for (CellRangeAddress region : rule.getRegions()) {
        for (int r = region.getFirstRow(); r <= region.getLastRow(); r++) {
            final Row row = sheet.getRow(r);
            if (row == null) {
                continue; // no cells to check
            }
            for (int c = region.getFirstColumn(); c <= region.getLastColumn(); c++) {
                final Cell cell = row.getCell(c);
                if (cell == null) {
                    continue;
                }
                
                List<EvaluationConditionalFormatRule> cellRules = getConditionalFormattingForCell(cell);
                if (cellRules.contains(rule)) {
                    cells.add(cell);
                }
            }
        }
    }
    return Collections.unmodifiableList(cells);
}
 

/**
 * @param source
 *            the sheet to copy.
 * @param destSheet
 *            the sheet to create.
 * @param srcRow
 *            the row to copy.
 * @param destRow
 *            the row to create.
 * @param styleMap
 *
 */
private static void copyRow(HSSFSheet source, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List<CellStyle> styleMap) {

	Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
	short dh = source.getDefaultRowHeight();
	if (srcRow.getHeight() != dh) {
		destRow.setHeight(srcRow.getHeight());
	}
	int j = srcRow.getFirstCellNum();
	if (j < 0) {
		j = 0;
	}
	for (; j <= srcRow.getLastCellNum(); j++) {
		HSSFCell oldCell = srcRow.getCell(j);
		XSSFCell newCell = destRow.getCell(j);
		if (oldCell != null) {
			if (newCell == null) {
				newCell = destRow.createCell(j);
			}
			copyCell(oldCell, newCell, styleMap);
			CellRangeAddress mergedRegion = getMergedRegion(source, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

			if (mergedRegion != null) {

				CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(),
						mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());

				CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
				if (isNewMergedRegion(wrapper, mergedRegions)) {
					mergedRegions.add(wrapper);
					destSheet.addMergedRegion(wrapper.range);
				}
			}
		}
	}

}
 

/**
 * 結合を考慮したセルのコメントを取得する。
 * @param cell 元となるセル。
 * @return コメント。コメントが設定されていなければ、nullを返す。
 */
private Comment getMergedCellComment(final Cell cell) {
    Comment comment = cell.getCellComment();
    if(comment != null) {
        return comment;
    }
    
    final Sheet sheet = cell.getSheet();
    final int size = sheet.getNumMergedRegions();
    
    for(int i=0; i < size; i++) {
        final CellRangeAddress range = sheet.getMergedRegion(i);
        if(!range.isInRange(cell)) {
            continue;
        }
        
        // nullでないセルを取得する。
        for(int rowIdx=range.getFirstRow(); rowIdx <= range.getLastRow(); rowIdx++) {
            final Row row = sheet.getRow(rowIdx);
            if(row == null) {
                continue;
            }

            for(int colIdx=range.getFirstColumn(); colIdx <= range.getLastColumn(); colIdx++) {
                final Cell valueCell = row.getCell(colIdx);
                if(valueCell == null) {
                    continue;
                }

                comment = valueCell.getCellComment();
                if(comment != null) {
                    return comment;
                }
            }
        }
    }
    
    return null;
    
}
 

/**
 * ネストしたレコードの親のセルを結合する
 * @param sheet シート
 * @param mergedSize 結合するセルのサイズ
 * @param valueCellPositions 結合する開始位置のセルのアドレス
 */
private void processSavingNestedMergedRecord(final Sheet sheet, final int mergedSize,
        final List<CellPosition> valueCellPositions) {

    if(mergedSize <= 1) {
        return;
    }

    // ネストした場合、上のセルのスタイルをコピーして、結合する
    for(CellPosition position : valueCellPositions) {
        Cell valueCell = POIUtils.getCell(sheet, position);
        if(valueCell == null) {
            continue;
        }

        final CellStyle style = valueCell.getCellStyle();

        // 結合するセルに対して、上のセルのスタイルをコピーする。
        // 行を挿入するときなどに必要になるため、スタイルを設定する。
        for(int i=1; i < mergedSize; i++) {
            Cell mergedCell = POIUtils.getCell(sheet, position.getColumn(), position.getRow() + i);
            mergedCell.setCellStyle(style);
            mergedCell.setCellType(CellType.BLANK);
        }

        final CellRangeAddress range = new CellRangeAddress(position.getRow(), position.getRow()+ mergedSize-1,
                position.getColumn(), position.getColumn());

        // 既に結合済みのセルがある場合、外す。
        for(int rowIdx=range.getFirstRow(); rowIdx <= range.getLastRow(); rowIdx++) {
            CellRangeAddress r = POIUtils.getMergedRegion(sheet, rowIdx, position.getColumn());
            if(r != null) {
                POIUtils.removeMergedRange(sheet, r);
            }
        }

        sheet.addMergedRegion(range);
    }

}
 

/**
 * @param range either the range of the entire block to be read, or just the
 *              top row of the cells, in which case the method will stop when
 *              the first empty cell is reached in the first column
 * @param columnTypes An array of data types expected at each column.
 *                 If this array is shorter than the number of column, then the last
 *                 data type is used until the end. So if only one value is given,
 *                 then that is used for the entire block.
 */
public Object[][] readBlock(final String range, final Class<?>... columnTypes) {

    if (columnTypes == null || columnTypes.length == 0) {
        throw new ExcelException("columnTypes cannot be null / empty");
    }

    final CellRangeAddress cra = CellRangeAddress.valueOf(range);
    final AreaReference ar = new AreaReference(range, null);
    final Sheet sheet = workbook.getSheet(ar.getFirstCell().getSheetName());

    final int firstColumn = cra.getFirstColumn();
    final int firstRow = cra.getFirstRow();
    final int lastRow = cra.getLastRow();
    final int height = lastRow - firstRow + 1;
    final int width = cra.getLastColumn() - firstColumn + 1;

    List<Object> result;
    if (height == 1) {
        result = new LinkedList<>();
    } else {
        result = new ArrayList<>(height);
    }

    for (int rowNum = 0; moreDataToRead(sheet, firstColumn, firstRow, lastRow, rowNum); rowNum++) {
        final Row row = sheet.getRow(firstRow + rowNum);
        final Object[] resultRow = new Object[width];
        result.add(resultRow);
        for (int colNum = 0; colNum < width; colNum++) {

            Class<?> colType;
            if (colNum < columnTypes.length - 1) {
                colType = columnTypes[colNum];
            } else {
                colType = columnTypes[columnTypes.length - 1];
            }

            final Cell cell = row.getCell(firstColumn + colNum);
            resultRow[colNum] = readCell(cell, colType);
        }

    }

    return result.toArray(new Object[][] {});
}
 

/**
 * Shifts, grows, or shrinks the merged regions due to a row shift.
 * Merged regions that are completely overlaid by shifting will be deleted.
 *
 * @param startRow the row to start shifting
 * @param endRow   the row to end shifting
 * @param n        the number of rows to shift
 * @return an array of affected merged regions, doesn't contain deleted ones
 */
public List<CellRangeAddress> shiftMergedRegions(int startRow, int endRow, int n) {
    List<CellRangeAddress> shiftedRegions = new ArrayList<CellRangeAddress>();
    Set<Integer> removedIndices = new HashSet<Integer>();
    //move merged regions completely if they fall within the new region boundaries when they are shifted
    int size = sheet.getNumMergedRegions();
    for (int i = 0; i < size; i++) {
        CellRangeAddress merged = sheet.getMergedRegion(i);

        // remove merged region that are replaced by the shifting,
        // i.e. where the area includes something in the overwritten area
        if(removalNeeded(merged, startRow, endRow, n)) {
            removedIndices.add(i);
            continue;
        }

        boolean inStart = (merged.getFirstRow() >= startRow || merged.getLastRow() >= startRow);
        boolean inEnd = (merged.getFirstRow() <= endRow || merged.getLastRow() <= endRow);

        //don't check if it's not within the shifted area
        if (!inStart || !inEnd) {
            continue;
        }

        //only shift if the region outside the shifted rows is not merged too
        if (!merged.containsRow(startRow - 1) && !merged.containsRow(endRow + 1)) {
            merged.setFirstRow(merged.getFirstRow() + n);
            merged.setLastRow(merged.getLastRow() + n);
            //have to remove/add it back
            shiftedRegions.add(merged);
            removedIndices.add(i);
        }
    }
    
    if(!removedIndices.isEmpty()) {
        sheet.removeMergedRegions(removedIndices);
    }

    //read so it doesn't get shifted again
    for (CellRangeAddress region : shiftedRegions) {
        sheet.addMergedRegion(region);
    }
    return shiftedRegions;
}
 

/**
 * 上部のセルと同じ値の場合マージする
 * @param currentCell
 * @param sheet
 * @param mergedRanges
 * @return
 */
private boolean processSavingMergedCell(final Cell currentCell, final Sheet sheet,
        final List<CellRangeAddress> mergedRanges, final Configuration config) {

    final int row = currentCell.getRowIndex();
    final int column = currentCell.getColumnIndex();

    if(column <= 0) {
        return false;
    }

    // 上のセルと比較する
    final String value = POIUtils.getCellContents(currentCell, config.getCellFormatter());
    String upperValue = POIUtils.getCellContents(POIUtils.getCell(sheet, column-1, row), config.getCellFormatter());

    // 結合されている場合、結合の先頭セルを取得する
    int startColumn = column - 1;
    CellRangeAddress currentMergedRange = null;
    for(CellRangeAddress range : mergedRanges) {
        // 列が範囲外の場合
        if((range.getFirstColumn() > startColumn) || (startColumn > range.getLastColumn())) {
            continue;
        }

        // 行が範囲外の場合
        if((range.getFirstRow() > row) || (row > range.getLastRow())) {
            continue;
        }

        upperValue = POIUtils.getCellContents(POIUtils.getCell(sheet, range.getFirstColumn(), row), config.getCellFormatter());
        currentMergedRange = range;
        break;
    }

    if(!value.equals(upperValue)) {
        // 値が異なる場合は結合しない
        return false;
    }

    // 既に結合済みの場合は一端解除する
    if(currentMergedRange != null) {
        startColumn = currentMergedRange.getFirstColumn();
        POIUtils.removeMergedRange(sheet, currentMergedRange);
    }

    final CellRangeAddress newRange = POIUtils.mergeCells(sheet, startColumn, row, column, row);
    mergedRanges.add(newRange);
    return true;

}
 

/**
 * 上部のセルと同じ値の場合マージする
 * @param currentCell
 * @param sheet
 * @param mergedRanges
 * @param config
 * @return
 */
private boolean processSavingMergedCell(final Cell currentCell, final Sheet sheet,
        final List<CellRangeAddress> mergedRanges, final Configuration config) {

    final int row = currentCell.getRowIndex();
    final int column = currentCell.getColumnIndex();

    if(row <= 0) {
        return false;
    }

    // 上のセルと比較する
    final String value = POIUtils.getCellContents(currentCell, config.getCellFormatter());
    String upperValue = POIUtils.getCellContents(POIUtils.getCell(sheet, column, row-1), config.getCellFormatter());

    // 結合されている場合、結合の先頭セルを取得する
    int startRow = row - 1;
    CellRangeAddress currentMergedRange = null;
    for(CellRangeAddress range : mergedRanges) {
        // 列が範囲外の場合
        if((range.getFirstColumn() > column) || (column > range.getLastColumn())) {
            continue;
        }

        // 行が範囲外の場合
        if((range.getFirstRow() > startRow) || (startRow > range.getLastRow())) {
            continue;
        }

        upperValue = POIUtils.getCellContents(POIUtils.getCell(sheet, column, range.getFirstRow()), config.getCellFormatter());
        currentMergedRange = range;
        break;
    }

    if(!value.equals(upperValue)) {
        // 値が異なる場合は結合しない
        return false;
    }

    // 既に結合済みの場合は一端解除する
    if(currentMergedRange != null) {
        startRow = currentMergedRange.getFirstRow();
        mergedRanges.remove(currentMergedRange);
        POIUtils.removeMergedRange(sheet, currentMergedRange);
    }

    final CellRangeAddress newRange = POIUtils.mergeCells(sheet, column, startRow, column, row);
    mergedRanges.add(newRange);
    return true;

}
 

/**
 * @方法名 copySheet
 * @功能 复制sheet
 * @参数 @param sheetFrom
 * @参数 @param sheetTo
 * @参数 @return
 * @返回 HSSFSheet
 * @author Administrator
 * @throws
 */
@SuppressWarnings("deprecation")
private static HSSFSheet copySheet(HSSFSheet sheetFrom, HSSFSheet sheetTo) {
	
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
	
}
 

/**
 * 見出し付きのセルの情報を取得する。
 * @param anno マッピング情報が設定されているアノテーション
 * @param processCase 処理ケース
 * @return ラベルや値が設定されているセルの開始情報
 */
public Optional<LabelInfo> handle(final Annotation anno, final ProcessCase processCase) {

    final AnnotationProxy annoProxy = new AnnotationProxy(anno);

    // ラベルの位置を取得する
    final Optional<CellPosition> labelPosition = getLabelPosition(annoProxy);
    if(!labelPosition.isPresent()) {
        return Optional.empty();
    }

    final int column = labelPosition.get().getColumn();
    final int row = labelPosition.get().getRow();

    /*
     * 見出しか結合している場合を考慮する場合
     * ・結合サイズ分で補正する。
     * ・考慮しない場合は、mergedXXXSizeの値は0のまま。
     */
    int mergedRowSize = 0;
    int mergedColumnSize = 0;
    if(annoProxy.labelMerged()) {
        CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, row, column);
        if(mergedRegion != null) {
            mergedRowSize = mergedRegion.getLastRow() - mergedRegion.getFirstRow();
            mergedColumnSize = mergedRegion.getLastColumn() - mergedRegion.getFirstColumn();
        }
    }

    int range = annoProxy.range();
    if(range < 1){
        range = 1;
    }

    // 値が設定されているセルを検索する。
    Point targetPosition = new Point();
    Cell targetCell = null;
    for(int i=0; i < range; i++){
        final int index = annoProxy.skip() + i +1;
        if(annoProxy.type() == LabelledCellType.Left) {
            targetPosition.x = column - index;
            targetPosition.y = row;
            targetCell = POIUtils.getCell(sheet, targetPosition);

        } else if(annoProxy.type() == LabelledCellType.Right) {
            targetPosition.x = column + index + mergedColumnSize;
            targetPosition.y = row;
            targetCell = POIUtils.getCell(sheet, targetPosition);

        } else if(annoProxy.type() == LabelledCellType.Bottom) {
            targetPosition.x = column;
            targetPosition.y = row + index + mergedRowSize;
            targetCell = POIUtils.getCell(sheet, targetPosition);

        }

        if(POIUtils.getCellContents(targetCell, config.getCellFormatter()).length() > 0){
            break;
        }

        if(processCase.equals(ProcessCase.Save)) {
            /*
             * 書き込み時は、属性rangeの範囲を考慮しない。
             * テンプレートファイルの場合、値は空を設定しているため。
             */
            break;
        }
    }

    final LabelInfo info = new LabelInfo();
    info.valueCell = targetCell;
    info.valueAddress = CellPosition.of(targetPosition);
    info.label = POIUtils.getCellContents(POIUtils.getCell(sheet, column, row), config.getCellFormatter());

    return Optional.of(info);
}
 

/**
 * 領域の行サイズ（行セル数）を計算します。
 *
 * @since 2.0
 * @param region 領域
 * @return 行サイズ（行セル数）。引数がnullの時は、0を返します。
 */
public static int getRowSize(final CellRangeAddress region) {
    if(region == null) {
        return 0;
    }
    return region.getLastRow() - region.getFirstRow() + 1;
}