Java POI组件实现多个Excel文件整合成一个多Sheet的Excel文件

POI操作更细，上代码：

package com.weichai;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {

public static void main(String[] args) throws Exception {
	// TODO Auto-generated method stub
	// 将所有类型的尽调excel文件合并成一个excel文件
	String Path = "D:\\ExcelTest";
	File file = new File(Path);
	File[] tempList = file.listFiles();
	String TmpList [] = new String [2];
	System.out.println("该目录下对象个数：" + tempList.length);
	for (int i = 0; i < tempList.length; i++) {
		if (tempList[i].isFile()) {
			TmpList[i] = tempList[i].toString();
			System.out.println("文件:"+TmpList[i]+" 待处理");
		}
	}
	XSSFWorkbook newExcelCreat = new XSSFWorkbook();
	for (String fromExcelName : TmpList) {    // 遍历每个源excel文件，TmpList为源文件的名称集合
		InputStream in = new FileInputStream(fromExcelName);
		XSSFWorkbook fromExcel = new XSSFWorkbook(in);
		int length = fromExcel.getNumberOfSheets();
		if(length<=1){       //长度为1时
			XSSFSheet oldSheet = fromExcel.getSheetAt(0);
			XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
		    copySheet(newExcelCreat, oldSheet, newSheet);
		}else{
			for (int i = 0; i < length; i++) {// 遍历每个sheet
				XSSFSheet oldSheet = fromExcel.getSheetAt(i);
				XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
				copySheet(newExcelCreat, oldSheet, newSheet);
			}
		}
	}
	String allFileName = Path+ "\\New.xlsx";    //定义新生成的xlx表格文件
	FileOutputStream fileOut = new FileOutputStream(allFileName);
	newExcelCreat.write(fileOut);
	fileOut.flush();
	fileOut.close();
	// 删除各个源文件
	//		for (String fromExcelName : TmpList) {// 遍历每个源excel文件
	//			File Existfile = new File(fromExcelName);
	//			if (Existfile.exists()) {
	//				Existfile.delete();
	//			}
	//		}
	System.out.println("运行结束!");
}

public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {

	toStyle.cloneStyleFrom(fromStyle);// 此一行代码搞定
	// 下面统统不用
	/*
	 * //对齐方式 toStyle.setAlignment(fromStyle.getAlignment()); //边框和边框颜色
	 * toStyle.setBorderBottom(fromStyle.getBorderBottom());
	 * toStyle.setBorderLeft(fromStyle.getBorderLeft());
	 * toStyle.setBorderRight(fromStyle.getBorderRight());
	 * toStyle.setBorderTop(fromStyle.getBorderTop());
	 * toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
	 * toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
	 * toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
	 * toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor()); //背景和前景
	 * //toStyle.setFillPattern(fromStyle.getFillPattern());
	 * //填充图案，不起作用，转为黑色
	 * toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
	 * //不起作用
	 * toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
	 * toStyle.setDataFormat(fromStyle.getDataFormat()); //数据格式
	 * //toStyle.setFont(fromStyle.getFont()); //不起作用
	 * toStyle.setHidden(fromStyle.getHidden());
	 * toStyle.setIndention(fromStyle.getIndention());//首行缩进
	 * toStyle.setLocked(fromStyle.getLocked());
	 * toStyle.setRotation(fromStyle.getRotation());//旋转
	 * toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
	 * //垂直对齐 toStyle.setWrapText(fromStyle.getWrapText()); //文本换行
	 */
}

/**
 * 合并单元格
 * @param fromSheet
 * @param toSheet
 */
public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
	int num = fromSheet.getNumMergedRegions();
	CellRangeAddress cellR = null;
	for (int i = 0; i < num; i++) {
		cellR = fromSheet.getMergedRegion(i);
		toSheet.addMergedRegion(cellR);
	}
}

/**
 * 复制单元格 
 * @param wb
 * @param fromCell
 * @param toCell
 */
public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
	XSSFCellStyle newstyle = wb.createCellStyle();
	copyCellStyle(fromCell.getCellStyle(), newstyle);
  //  toCell.setEncoding(fromCell.getStringCelllValue());
	// 样式
	toCell.setCellStyle(newstyle);
	if (fromCell.getCellComment() != null) {
		toCell.setCellComment(fromCell.getCellComment());
	}
	// 不同数据类型处理
	int fromCellType = fromCell.getCellType();
	toCell.setCellType(fromCellType);
	if (fromCellType == XSSFCell.CELL_TYPE_NUMERIC) {
		if (XSSFDateUtil.isCellDateFormatted(fromCell)) {
			toCell.setCellValue(fromCell.getDateCellValue());
		} else {
			toCell.setCellValue(fromCell.getNumericCellValue());
		}
	} else if (fromCellType == XSSFCell.CELL_TYPE_STRING) {
		toCell.setCellValue(fromCell.getRichStringCellValue());
	} else if (fromCellType == XSSFCell.CELL_TYPE_BLANK) {
		// nothing21
	} else if (fromCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
		toCell.setCellValue(fromCell.getBooleanCellValue());
	} else if (fromCellType == XSSFCell.CELL_TYPE_ERROR) {
		toCell.setCellErrorValue(fromCell.getErrorCellValue());
	} else if (fromCellType == XSSFCell.CELL_TYPE_FORMULA) {
		toCell.setCellFormula(fromCell.getCellFormula());
	} else { // nothing29
	}

}

/**
 * 行复制功能
 * @param wb
 * @param oldRow
 * @param toRow
 */
public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
	toRow.setHeight(oldRow.getHeight());
	for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) {
		XSSFCell tmpCell = (XSSFCell) cellIt.next();
		XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
		copyCell(wb, tmpCell, newCell);
	}
}

/**
 * Sheet复制 
 * @param wb
 * @param fromSheet
 * @param toSheet
 */
public static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
	mergeSheetAllRegion(fromSheet, toSheet);
	// 设置列宽
	int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
	for (int i = 0; i <= length; i++) {
		toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
	}
	for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
		XSSFRow oldRow = (XSSFRow) rowIt.next();
		XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
		copyRow(wb, oldRow, newRow);
	}
}

public class XSSFDateUtil extends DateUtil {

}

}
运行截图:
将Student1和Student2的数据整合到一个Excel表单中

生成的New.xlsx文件如下图所示:

运行截图:

将Student1和Student2的数据整合到一个Excel表单中

生成的New.xlsx文件如下图所示: