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

ゝ一世哀愁。 2022-05-09 10:42 350阅读 0赞

## 代码: ##

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表单中

![70][]

![70 1][]

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

![70 2][]

[70]: /images/20220509/d5fe0ecefb7e4f9a9da368d465545f4a.png
[70 1]: /images/20220509/a0a01f3f23494bac88b7001b431e830f.png
[70 2]: /images/20220509/486302355cb64f3f846fb81488632330.png