Apache POI复制行和复制单元格

电玩女神 2022-01-27 11:11 1244阅读 0赞

package test;
    
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    
    /**
     * POI工具类
     * @author suyunlong
     *
     */
    public class POIUtil {
    	/**
    	 * 复制行
    	 * @param startRow 开始行
    	 * @param endRow 结束行
    	 * @param pPosition 目标行
    	 * @param sheet 工作表对象
    	 */
    	public static void copyRows(int startRow,int endRow,int pPosition,XSSFSheet sheet){
    		int pStartRow=startRow;
    		int pEndRow=endRow;
    		int targetRowFrom;
    		int targetRowTo;
    		int columnCount;
    		CellRangeAddress region=null;
    		int i;
    		int j;
    		if(pStartRow == -1 || pEndRow == -1) {
    			return;
    		}
    		// 拷贝合并的单元格
    		for(i=0;i<sheet.getNumMergedRegions();i++){
    			region=sheet.getMergedRegion(i);
    			if((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) {
    				targetRowFrom=region.getFirstRow()-pStartRow+pPosition;
    				targetRowTo=region.getLastRow()-pStartRow+pPosition;
    				CellRangeAddress newRegion=region.copy();
    				newRegion.setFirstRow(targetRowFrom);
    				newRegion.setFirstColumn(region.getFirstColumn());
    				newRegion.setLastRow(targetRowTo);
    				newRegion.setLastColumn(region.getLastColumn());
    				sheet.addMergedRegion(newRegion);
    			}
    		}
    		// 设置列宽
    		for(i=pStartRow;i<=pEndRow;i++){
    			XSSFRow sourceRow=sheet.getRow(i);
    			columnCount=sourceRow.getLastCellNum();
    			if(sourceRow != null){
    				XSSFRow newRow=sheet.createRow(pPosition - pStartRow + i);
    				newRow.setHeight(sourceRow.getHeight());
    				for(j=0;j<columnCount;j++){
    					XSSFCell templateCell=sourceRow.getCell(j);
    					if(templateCell != null){
    						XSSFCell newCell=newRow.createCell(j);
    						copyCell(templateCell,newCell);
    					}
    				}
    			}
    		}
    	}
    	/**
    	 * 复制单元格
    	 * @param srcCell 原始单元格
    	 * @param distCell 目标单元格
    	 */
    	public static void copyCell(XSSFCell srcCell,XSSFCell distCell){
    		distCell.setCellStyle(srcCell.getCellStyle());
    		if(srcCell.getCellComment() != null){
    			distCell.setCellComment(srcCell.getCellComment());
    		}
    		int srcCellType=srcCell.getCellType();
    		distCell.setCellType(srcCellType);
    		if(srcCellType==XSSFCell.CELL_TYPE_NUMERIC){
    			if(HSSFDateUtil.isCellDateFormatted(srcCell)){
    				distCell.setCellValue(srcCell.getDateCellValue());
    			}
    			else{
    				distCell.setCellValue(srcCell.getNumericCellValue());
    			}
    		}
    		else if(srcCellType==XSSFCell.CELL_TYPE_STRING){
    			distCell.setCellValue(srcCell.getRichStringCellValue());
    		}
    		else if(srcCellType==XSSFCell.CELL_TYPE_BLANK){
    			// nothing21
    		}
    		else if(srcCellType==XSSFCell.CELL_TYPE_BOOLEAN){
    			distCell.setCellValue(srcCell.getBooleanCellValue());
    		}
    		else if(srcCellType==XSSFCell.CELL_TYPE_ERROR){
    			distCell.setCellErrorValue(srcCell.getErrorCellValue());
    		}
    		else if(srcCellType==XSSFCell.CELL_TYPE_FORMULA){
    			distCell.setCellFormula(srcCell.getCellFormula());
    		}
    		else{ // nothing29
    
    		}
    	}
    	/**
    	 * 表格中指定位置插入行
    	 * @param sheet 工作表对象
    	 * @param rowIndex 指定的行数
    	 * @return 当前行对象
    	 */
    	public static XSSFRow insertRow(XSSFSheet sheet,int rowIndex) {
    		XSSFRow row=null;
    		if(sheet.getRow(rowIndex) != null) {
    			int lastRowNo=sheet.getLastRowNum();
    			sheet.shiftRows(rowIndex,lastRowNo,1);
    		}
    		row=sheet.createRow(rowIndex);
    		return row;
    	}
    }