循环Excel指定行

傷城~ 2023-10-17 15:10 105阅读 0赞

表达能力有限，只作为学习笔记使用。  
部分伪代码。

import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.HashMap;
    import java.util.Map;
    
    import org.apache.commons.lang.StringUtils;
    import org.apache.log4j.Logger;
    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;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    
    private XSSFWorkbook xwb;
    private String reportFile ="D://exceltemp/a.xlsx";
    
    xwb = new XSSFWorkbook(new FileInputStream(reportFile));
    XSSFSheet xSheet = xwb.getSheetAt(0);
    
    //假设循环十次  循环的单元格的行数是7行 首行列头不复制
    int beginPosition = 8;
    for(int i = 1; i <= 10; i++){
    	copyRows(xSheet, 2, 8, beginPosition);
    	beginPosition = beginPosition+7;
    }
    
    /**
     * 复制单元格
     * 
     * @param currentSheet
     *            sheet页
     * @param startRow
     *            开始行
     * @param endRow
     *            结束行
     * @param pPosition
     *            目标位置
     */
    public static void copyRows(XSSFSheet currentSheet, int startRow, int endRow, int pPosition) {
    
    	int pStartRow = startRow - 1;
    	int pEndRow = endRow - 1;
    	int targetRowFrom;
    	int targetRowTo;
    	int columnCount;
    	CellRangeAddress region = null;
    	int i;
    	int j;
    
    	for (i = 0; i < currentSheet.getNumMergedRegions(); i++) {
    		region = currentSheet.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());
    			currentSheet.addMergedRegion(newRegion);
    		}
    	}
    
    	for (i = pStartRow; i <= pEndRow; i++) {
    		XSSFRow sourceRow = (XSSFRow) currentSheet.getRow(i);
    		columnCount = sourceRow.getLastCellNum();
    		if (sourceRow != null) {
    			XSSFRow newRow = (XSSFRow) currentSheet.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);
    				}
    			}
    		}
    	}
    }
    //POI(3.15前)
    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
    
    	}
    }
    //POI(3.15后)
    public static void copyCell(XSSFCell srcCell, XSSFCell distCell) {
    	    distCell.setCellStyle(srcCell.getCellStyle());
    	    if (srcCell.getCellComment() != null) {
    	        distCell.setCellComment(srcCell.getCellComment());
    	    }
    	    CellType srcCellType = srcCell.getCellTypeEnum();
    	    distCell.setCellType(srcCellType);
    	    if(srcCellType == CellType.NUMERIC) {
    	    	if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
    	            distCell.setCellValue(srcCell.getDateCellValue());
    	        } else {
    	            distCell.setCellValue(srcCell.getNumericCellValue());
    	        }
    	    } else if (srcCellType == CellType.STRING) {
    	        distCell.setCellValue(srcCell.getRichStringCellValue());
    	    } else if (srcCellType ==CellType.BLANK) {
    	        // nothing21
    	    } else if (srcCellType == CellType.BOOLEAN) {
    	        distCell.setCellValue(srcCell.getBooleanCellValue());
    	    } else if (srcCellType == CellType.ERROR) {
    	        distCell.setCellErrorValue(srcCell.getErrorCellValue());
    	    } else if (srcCellType == CellType.FORMULA) {
    	        distCell.setCellFormula(srcCell.getCellFormula());
    	    } else { // nothing29
    
    	    }
    	}

Excel是这样的，循环生成2-8行  
![这里写图片描述][SouthEast]

[SouthEast]: https://img-blog.csdn.net/20170817160951477?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaml1d2VpaHU1MjE=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast