POI复制指定行到指定位置

最新推荐文章于 2023-12-05 08:55:56 发布

原创最新推荐文章于 2023-12-05 08:55:56 发布 · 5k 阅读

CC 4.0 BY-SA版权

本文介绍了一段Java代码，该代码使用Apache POI库来读取、操作和复制Excel文件中的数据。具体包括从Excel文件中读取特定单元格的数据、复制指定范围内的行到新位置，并将更改写回到原始文件。此代码适用于需要批量处理Excel数据的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

网上找的一部分代码，然后修改了一下

package test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExcelCopy {
	public static void main(String[] args) throws Exception {
		File newFile = new File("F://****.xls");  

		// 新文件写入数据，并下载
		InputStream is = null;  
		HSSFWorkbook workbook = null;  
		HSSFSheet sheet = null;  
		POIFSFileSystem ps = null;
		try {  
			is = new FileInputStream(newFile);// 将excel文件转为输入流  
			ps = new POIFSFileSystem(is);
			workbook = new HSSFWorkbook(ps);// 创建个workbook，  
			// 获取第一个sheet  
			sheet = workbook.getSheetAt(0);  
		} catch (Exception e1) {  
			e1.printStackTrace();  
		}  
		HSSFRow startRow = sheet.getRow(1);
		startRow.getCell(0).getStringCellValue();
		System.out.println("-------------------"+startRow.getCell(0).getStringCellValue());
		HSSFRow endRow = sheet.getRow(41);
		copyRows(sheet, 1, 41, 42);
		 // 写数据  
        FileOutputStream fos = new FileOutputStream(newFile);  
		workbook.write(fos); 
        fos.flush();  
        fos.close();  
        workbook.close();
	}
	/** 
	 * 复制行 
	 *  
	 * @param startColIndex 
	 *            起始列 
	 * @param endColIndex 
	 *            结束列
	 * @param pPosition 
	 *           目标起始行位置 
	 */

	public static HSSFSheet copycols(HSSFSheet currentSheet,int startCol, int endCol, int pPosition) {
		int pStartCol= startCol - 1;
		int pEndCol = endCol - 1;
		int targetColFrom;
		int targetColTo;
		int RowCount;
		CellRangeAddress region = null;
		int i;
		int j;
		if (pStartCol == -1 || pEndCol == -1) {
			return null;
		}

		System.out.println(currentSheet.getNumMergedRegions());
		for (i = 0; i < currentSheet.getNumMergedRegions(); i++) {

			region = currentSheet.getMergedRegion(i);

			if ((region.getFirstColumn() >= pStartCol)  
					&& (region.getLastColumn() <= pEndCol)) {

				targetColFrom = region.getFirstColumn() - pStartCol + pPosition;

				targetColTo = region.getLastColumn() - pStartCol + pPosition;

				CellRangeAddress newRegion = region.copy();

				newRegion.setFirstRow(region.getFirstRow());

				newRegion.setFirstColumn(targetColFrom);

				newRegion.setLastRow(region.getLastRow());

				newRegion.setLastColumn(targetColTo);

				currentSheet.addMergedRegion(newRegion);



			}


		}

		for (i = 0; i <= 50; i++) {

			HSSFRow sourceRow = currentSheet.getRow(i);

			if (sourceRow != null) {

				HSSFRow newRow = currentSheet.getRow(i);
				for (j = 0; j < pEndCol; j++) {

					HSSFCell templateCell = sourceRow.getCell(j);

					if(i == 0){
						currentSheet.setColumnWidth(pPosition+j, currentSheet.getColumnWidth(j));

					}
					if (templateCell != null) {

						HSSFCell newCell = newRow.createCell(pPosition+j);

						copyCell(templateCell, newCell);


					}


				}


			}


		}
		return currentSheet;

	}

	public static void copyRows(HSSFSheet 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;
		
		if (pStartRow == -1 || pEndRow == -1) {

			return;


		}

		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++) {

			HSSFRow sourceRow = currentSheet.getRow(i);

			columnCount = sourceRow.getLastCellNum();

			if (sourceRow != null) {

				HSSFRow newRow = currentSheet.createRow(pPosition - pStartRow  
						+ i);

				newRow.setHeight(sourceRow.getHeight());

				for (j = 0; j < columnCount; j++) {

					HSSFCell templateCell = sourceRow.getCell(j);

					if (templateCell != null) {

						HSSFCell newCell = newRow.createCell(j);

						copyCell(templateCell, newCell);


					}


				}


			}


		}


	}


	private static void copyCell(HSSFCell srcCell, HSSFCell distCell) {

		distCell.setCellStyle(srcCell.getCellStyle());
		
		if (srcCell.getCellComment() != null) {

			distCell.setCellComment(srcCell.getCellComment());


		}

		int srcCellType = srcCell.getCellType();

		distCell.setCellType(srcCellType);
		
		if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
			
			if (HSSFDateUtil.isCellDateFormatted(srcCell)) {

				distCell.setCellValue(srcCell.getDateCellValue());
				

			} else {

				distCell.setCellValue(srcCell.getNumericCellValue());


			}
			System.out.println("srcCell------------"+srcCell.getNumericCellValue());
			System.out.println("distCell------------"+distCell.getNumericCellValue());

		} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {

			distCell.setCellValue(srcCell.getRichStringCellValue());


		} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {

			// nothing21  

		} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {

			distCell.setCellValue(srcCell.getBooleanCellValue());


		} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {

			distCell.setCellErrorValue(srcCell.getErrorCellValue());


		} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {

			distCell.setCellFormula(srcCell.getCellFormula());


		} else {
			// nothing29  


		}

	}
}