Java使用POI实现多个excel合并成一个excel。

（1）使用技术

• 编程语言：Java
• 依赖jar包：poi

poi组件下载地址：https://archive.apache.org/dist/poi/release/bin/

（2）代码实现

实现思路：

• excel操作主要有如下几个步骤：
  • 创建新的excel工作簿
  • 合并单元格
  • 创建行
  • 创建列
  • 设置单元格样式
  • 设置单元格值

实现代码：

package com.gitee.zhuyb.excel.util;

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

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
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;

/**
 * @类名称: #ExcelUtil.java
 * @类描述: #Excel工具类
 * @创建人员: zhuyb
 * @创建时间: 2021-07-31 8:38:58
 * ------------------------------------------------------------
 * @version 1.0.0
 * @Copyright (C) zhuyb
 */
public abstract class ExcelUtil {

	/**
	 * #合并多个excel文件
	 * @param fileLists excel文件路径 
	 * @param path 目标文件保存目录
	 * @param fileName 目标文件名称
	 */
	public static void mergeExcel(List<String> fileLists, String path, String fileName) {
		// 创建新的excel工作簿
		XSSFWorkbook newExcelWorkBook = new XSSFWorkbook();
		// 遍历需要合并的excel文件
		for (String excelName : fileLists) {
			try (InputStream in = new FileInputStream(excelName)) {
				// 创建工作簿
				XSSFWorkbook tmpWorkBook = new XSSFWorkbook(in);
				// 获取工作簿中的Sheet个数
				int len = tmpWorkBook.getNumberOfSheets();
				if (len <= 1) {
					XSSFSheet tmpSheet = tmpWorkBook.getSheetAt(0);
					XSSFSheet newExcelSheet = newExcelWorkBook.createSheet(tmpSheet.getSheetName());
					// 复制sheet内容
					copyExcelSheet(newExcelWorkBook, tmpSheet, newExcelSheet);
				} else {
					for (int i = 0; i < len; i++) {
						XSSFSheet tmpSheet = tmpWorkBook.getSheetAt(i);
						XSSFSheet newExcelSheet = newExcelWorkBook.createSheet(tmpSheet.getSheetName());
						// 复制sheet内容
						copyExcelSheet(newExcelWorkBook, tmpSheet, newExcelSheet);
					}
				}
				// 关闭tmpWorkBook工作簿
				tmpWorkBook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		// 新生成的excel文件
		if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")) {
			fileName += ".xlsx";
		}
		String excelFileName = path + File.separator + fileName;
		// 判断文件是否存在
		File excelFile = new File(excelFileName);
		if (excelFile.exists()) {
			// 存在则删除
			excelFile.delete();
		}
		// 使用输出流写出
		try (FileOutputStream fos = new FileOutputStream(excelFileName)) {
			newExcelWorkBook.write(fos);
			fos.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				newExcelWorkBook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		System.out.println("excel文件合并成功，合并后文件路径：" + excelFileName);
	}
	
	/**
	 * #复制sheet到新的excel文件中
	 * @param workbook excel工作簿
	 * @param tmpSheet 来源sheet
	 * @param newExcelSheet 新生成的sheet
	 */
	public static void copyExcelSheet(XSSFWorkbook workbook, XSSFSheet tmpSheet, XSSFSheet newExcelSheet) {
		// 合并单元格
		mergeSheetAllRegion(tmpSheet, newExcelSheet);
		// 设置单元格列宽度
		// 获取最后一个单元格位置
		int len = tmpSheet.getRow(tmpSheet.getFirstRowNum()).getLastCellNum();
		for (int i = 0; i < len; i++) {
			newExcelSheet.setColumnWidth(i, tmpSheet.getColumnWidth(i));
		}
		// 复制每行内容
		Iterator<Row> it = tmpSheet.iterator();
		while (it.hasNext()) {
			XSSFRow tmpRow = (XSSFRow) it.next();
			// 创建新行
			XSSFRow newExcelRow = newExcelSheet.createRow(tmpRow.getRowNum());
			// 复制行
			copyExcelRow(workbook, tmpRow, newExcelRow);
		}
	}

	/**
	 * #合并单元格
	 * @param tmpSheet 来源sheet
	 * @param newExcelSheet 目标sheet
	 */
	private static void mergeSheetAllRegion(XSSFSheet tmpSheet, XSSFSheet newExcelSheet) {
		int num = tmpSheet.getNumMergedRegions();
		CellRangeAddress cellRange = null;
		for (int i = 0; i < num; i++) {
			cellRange = tmpSheet.getMergedRegion(i);
			newExcelSheet.addMergedRegion(cellRange);
		}
	}
	
	/**
	 * #复制excel中的行到新的sheet中
	 * @param workbook 目标工作簿
	 * @param tmpRow 来源excel行
	 * @param newExcelRow 目标excel行
	 */
	public static void copyExcelRow(XSSFWorkbook workbook, XSSFRow tmpRow, XSSFRow newExcelRow) {
		// 设置行高
		newExcelRow.setHeight(tmpRow.getHeight());
		// 获取所有列
		Iterator<Cell> it = tmpRow.cellIterator();
		while (it.hasNext()) {
			XSSFCell tmpCell = (XSSFCell) it.next();
			// 创建单元格
			XSSFCell newExcelCell = newExcelRow.createCell(tmpCell.getColumnIndex());
			// 复制单元格
			copyExcelCell(workbook, tmpCell, newExcelCell);
		}
	}
	
	/**
	 * #复制单元格
	 * @param workbook 目标工作簿
	 * @param tmpCell 来源excel单元格
	 * @param newExcelCell 目标excel单元格
	 */
	public static void copyExcelCell(XSSFWorkbook workbook, XSSFCell tmpCell, XSSFCell newExcelCell) {
		XSSFCellStyle newExcelStyle = workbook.createCellStyle();
        // 复制单元格样式
		newExcelStyle.cloneStyleFrom(tmpCell.getCellStyle());
        // 单元格样式
        newExcelCell.setCellStyle(newExcelStyle);
        if (tmpCell.getCellComment() != null) {
            newExcelCell.setCellComment(tmpCell.getCellComment());
        }
        // 不同数据类型处理
        CellType tmpCellType = tmpCell.getCellType();
        newExcelCell.setCellType(tmpCellType);
        if (tmpCellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(tmpCell)) {
                newExcelCell.setCellValue(tmpCell.getDateCellValue());
            } else {
                newExcelCell.setCellValue(tmpCell.getNumericCellValue());
            }
        } else if (tmpCellType == CellType.STRING) {
            newExcelCell.setCellValue(tmpCell.getRichStringCellValue());
        } else if (tmpCellType == CellType.BLANK) {
        } else if (tmpCellType == CellType.BOOLEAN) {
            newExcelCell.setCellValue(tmpCell.getBooleanCellValue());
        } else if (tmpCellType == CellType.ERROR) {
            newExcelCell.setCellErrorValue(tmpCell.getErrorCellValue());
        } else if (tmpCellType == CellType.FORMULA) {
            newExcelCell.setCellFormula(tmpCell.getCellFormula());
        } else {
        }
	}
	
}

代码测试：

package com.gitee.zhuyb.excel.app;

import java.util.Arrays;
import java.util.List;

import com.gitee.zhuyb.excel.util.ExcelUtil;

/**
 * @类名称: #ExcelApplication.java
 * @类描述: #TODO
 * @创建人员: zhuyb
 * @创建时间: 2021-07-31 9:29:05
 * ------------------------------------------------------------
 * @version 1.0.0
 * @Copyright (C) zhuyb
 */
public class ExcelApplication {

	public static void main(String[] args) {
		List<String> fileLists = Arrays.asList(new String[] {
				"C:\\Users\\zhuyb\\Desktop\\excel\\demo.xlsx",
				"C:\\Users\\zhuyb\\Desktop\\excel\\demo02.xlsx"
		});
		String path = "C:\\Users\\zhuyb\\Desktop\\excel";
		String fileName = "合并单元格的excel";
		ExcelUtil.mergeExcel(fileLists, path, fileName);
	}
	
}

在指定目录下面，创建两个excel用作测试文件。

然后运行测试类，查看生成的excel文件，所有sheet多合并。

java可以通过使用jacob.jar包中的方法实现将多个excel合并为一个，代码如下。
提示：该方法可以合成2010版本的excel，2013版本合成的时候可能会出现问题。

// 首先将所有需要合成的excel放到combineFilePath路径上
String combineFilePath = "D:\test\combine\";
// excelPatn是合成excel的文件路径
String excelPath = "D:\test2\combineFile.xlsx";

JacobExcelTool jacobexcel = new JacobEReportTool();
// 判断一下excel版本
 String version = jacobexcel.getExcelApplicationVersion();
 // 如果是excel2010则可以合成
if (version.equals("14.0")){
	jacobexcel.openExcelFile(excelPath);
	jacobexcel.copyExcelList(combineFilePath,jacobexcel.workbooks, jacobexcel.sheet);
	jacobexcel.closeExcelFile(false);
}				

共通JacobExcelTool 定义如下：

public class JacobExcelTool {
	
	public ActiveXComponent activexcomponent;

	public Dispatch workbooks = null;

	public Dispatch workbook = null;

	public Dispatch sheet = null;
	
	public Vector< String > oldSheetName = new Vector< String >();
	
	private HashMap<String,String> fileType=new HashMap<String,String>();
	{
		fileType.put("txt", "icons/txticon.exe");
		fileType.put("doc", "icons/wordicon.exe");
		fileType.put("xls", "icons/excelicon.exe");
		fileType.put("pdf", "icons/pdficon.ico");

	}
	
	private Vector<String> imageType=new Vector<String>();
	{
		imageType.add("bmp");
		imageType.add("jpg");
		imageType.add("jpeg");
		imageType.add("gif");
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		JacobExcelTool jacobexcel=new JacobExcelTool ();
	}
	
	public void saveAsExcelFile(String fileName) {
		try {
			Dispatch.call(sheet, "Select");
			File f = new File(fileName);
			f.delete();
			Dispatch.invoke(workbook, "SaveAs", Dispatch.Method,
					new Object[] { fileName }, new int[1]);
		} catch (Exception e) {
			System.out.println("另存为时出错．．．");
			e.printStackTrace();
		}
	}
	public void closeExcelFile(boolean close) {
		try {
			Dispatch.call((Dispatch) workbook, "Save");
			Dispatch.call((Dispatch) workbook, "Close", new Variant(close));
		} catch (Exception e) {
			System.out.println("保存关闭时出错．．．");
			e.printStackTrace();
		} finally {
			activexcomponent.invoke("Quit", new Variant[] {});
		}
	}
	public void openExcelFile(String filename,String sheetName){
		try {
			
			activexcomponent = new ActiveXComponent("Excel.Application");
			activexcomponent.setProperty("Visible", new Variant(false));
			workbooks = activexcomponent.getProperty("Workbooks").toDispatch();
			workbook = Dispatch.invoke(workbooks,"Open",Dispatch.Method,
					new Object[] { filename, new Variant(false), new Variant(false)},// 是否以只读方式打开
					new int[1]).toDispatch();
			Dispatch sheets = Dispatch.get((Dispatch) workbook,"Sheets").toDispatch();
			sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get,new Variant[] { new Variant(sheetName) },
					new int[1]).toDispatch();
			//激活sheet工作表
			Dispatch.call(sheet, "Activate");
		} catch (Exception e) {
			System.out.println("打开EXCEL时出错．．．");
			e.printStackTrace();
		}
	}
	public void openExcelFile(String filename){
		try {
			activexcomponent = new ActiveXComponent("Excel.Application");
			activexcomponent.setProperty("Visible", new Variant(false));
			workbooks = activexcomponent.getProperty("Workbooks").toDispatch();
			workbook = Dispatch.invoke(workbooks,"Open",Dispatch.Method,
					new Object[] { filename, new Variant(false), new Variant(false)},// 是否以只读方式打开
					new int[1]).toDispatch();
			Dispatch sheets = Dispatch.get((Dispatch) workbook,"Sheets").toDispatch();
			int Count = Dispatch.get(  sheets , "Count").toInt();
			for(int i = 0; i < Count; i  ++ )
			{
				int SheetIdx = i +1;
				Dispatch tmpsheet = Dispatch.invoke( sheets , "Item" , Dispatch.Get , new Object[]{SheetIdx} , new int[1] ).toDispatch();
				if( tmpsheet != null )
				{
					oldSheetName.add( new String( Dispatch.get( tmpsheet , "Name" ).toString() ));
				}
			}
			
			sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get,new Variant[] { new Variant(1) },
					new int[1]).toDispatch();
			//激活sheet工作表
			Dispatch.call(sheet, "Activate");
		} catch (Exception e) {
			System.out.println("打开EXCEL时出错．．．");
			e.printStackTrace();
		}
	}
	
	// 写入值
	private void setValue(String position, String type, String value) {
		Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { position },
				new int[1]).toDispatch();
		Dispatch.put(cell, type, value);
	}
	// 写入值
	public void insertValue(String position, String type, String value) {
		Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { position },
				new int[1]).toDispatch();
		String str=getValue(position);
		Dispatch.put(cell, type, str+value);
	}

	// 读取值
	public String getValue(String position) {
		Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { position },
				new int[1]).toDispatch();
		String value = Dispatch.get(cell, "Value").toString();

		return value;
	}
	// 读取值是否隐藏
	public String getisDIS(String position) {
		Dispatch cell = Dispatch.invoke(sheet, "Range", Dispatch.Get, new Object[] { position },
				new int[1]).toDispatch();
		String value = Dispatch.get(cell, "Row").toString();
		return "";
	}
	
	//拷贝行,例如:拷贝行A2,L2到行A5,L5(copyInsertRow("A2","L2","A5","L5"))
	public void copyInsertRow(Dispatch srcSheet,String a,String b,Dispatch toSheet,String c,String d){
		Dispatch copyRow = Dispatch.invoke(srcSheet, "Range", Dispatch.Get,
								new Variant[] { new Variant(a+":"+b)}, new int[1]).toDispatch();
		Dispatch.invoke(copyRow, "Copy", Dispatch.Method,
								new Variant[] {Variant.VT_MISSING }, new int[1]);
		Dispatch pastRow = Dispatch.invoke(toSheet, "Range", Dispatch.Get,
								new Variant[] { new Variant(c+":"+d)}, new int[1]).toDispatch();
		Dispatch.call(pastRow, "Insert");
	}
	//拷贝粘贴cell
	public void copyPastCell(String x,String y){
		Dispatch copyCell = Dispatch.invoke(sheet, "Range", Dispatch.Get,
								new Variant[] { new Variant(x)}, new int[1]).toDispatch();
		Dispatch.invoke(copyCell, "Copy", Dispatch.Method,
								new Variant[] {Variant.VT_MISSING }, new int[1]);
		Dispatch pastCell = Dispatch.invoke(sheet, "Range", Dispatch.Get,
								new Variant[] { new Variant(y)}, new int[1]).toDispatch();
		Dispatch.call(pastCell, "Select");
		Dispatch.call(sheet, "Paste");
	}
	
	//插入图片,例如：	insertImages("F21","c:/cc.bmp")
	public void insertImages(String picPosition, String picFilePath) {
		Dispatch.call(sheet, "Activate");
		Dispatch d = Dispatch.invoke(sheet, "Range", Dispatch.Get,new Variant[] { new Variant(picPosition) },
				new int[1]).toDispatch();
		Dispatch.call(d, "Select"); 
		Dispatch pictures = Dispatch.call(sheet, "Pictures").toDispatch();
		Dispatch.call(pictures, "Insert", picFilePath).toDispatch();
		Dispatch.call(sheet, "Activate");

	}
	
	
	public void copyFromAnotherExcel(String path,String sheetname,String form_index,String to_index){
		JacobEReportTool jacobexcel1=new JacobEReportTool();
		jacobexcel1.openExcelFile(path,sheetname);
		Dispatch copyCell = Dispatch.invoke(jacobexcel1.sheet, "Range", Dispatch.Get,
				new Variant[] { new Variant(form_index)}, new int[1]).toDispatch();
        Dispatch.invoke(copyCell, "Copy", Dispatch.Method,
				new Variant[] {Variant.VT_MISSING }, new int[1]);
		Dispatch.call(sheet, "Activate");
		Dispatch pastCell = Dispatch.invoke(sheet, "Range", Dispatch.Get,
				new Variant[] { new Variant(to_index)}, new int[1]).toDispatch();
        Dispatch.call(pastCell, "Select");
        Dispatch.call(sheet, "Paste");
        Dispatch.put((Dispatch) jacobexcel1.activexcomponent, "CutCopyMode", new Variant(false));
        jacobexcel1.closeExcelFile(false);
	}

	public void copySheetFromAnotherExcel(String path, String sheetname, Dispatch workbooks,
			Dispatch sheet_to) {

		Dispatch workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method,
				new Object[] { path, new Variant(false), new Variant(false) },// 是否以只读方式打开
				new int[1]).toDispatch();
		Dispatch sheets = Dispatch.get((Dispatch) workbook, "Sheets").toDispatch();
		Dispatch sheet = Dispatch.invoke(sheets, "Item", Dispatch.Get,
				new Variant[] { new Variant(sheetname) }, new int[1]).toDispatch();
		//将sheet复制到 sheet_to后面
		Dispatch.invoke(sheet, "Copy", Dispatch.Method, new Variant[] { Variant.VT_MISSING,
				new Variant(sheet_to) }, new int[1]);
		Dispatch.put((Dispatch) activexcomponent, "CutCopyMode", new Variant(false));
		try {
			Dispatch.call((Dispatch) workbook, "Close", new Variant(false));
		} catch (Exception e) {
			System.out.println("保存关闭时出错．．．");
			e.printStackTrace();
		}
	}
	
	public void copyAnotherExcel(String path, Dispatch workbooks,
			Dispatch sheet_to)
	{
		Dispatch workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method,
				new Object[] { path, new Variant(false), new Variant(false) },// 是否以只读方式打开
				new int[1]).toDispatch();
		Dispatch sheets = Dispatch.get((Dispatch) workbook, "Sheets").toDispatch();
		int Count = Dispatch.get(  sheets , "Count").toInt();
		for(int i = 0; i < Count; i  ++ )
		{
			int SheetIdx = i +1;
			Dispatch tmpsheet = Dispatch.invoke( sheets , "Item" , Dispatch.Get , new Object[]{SheetIdx} , new int[1] ).toDispatch();
			if( tmpsheet !=  null )
			{
				//将tmpsheet复制到sheet_to前面
				Dispatch.invoke(tmpsheet, "Copy", Dispatch.Method, new Variant[] { new Variant(sheet_to) ,
						Variant.VT_MISSING }, new int[1]);
				Dispatch.put((Dispatch) activexcomponent, "CutCopyMode", new Variant(false));
				
				String tmpsheetName = Dispatch.get( tmpsheet , "Name" ).toString();
			}
		}
		try {
			Dispatch.call((Dispatch) workbook, "Close", new Variant(false));
		} catch (Exception e) {
			System.out.println("保存关闭时出错．．．");
			e.printStackTrace();
		}
	}
	
	public void copyExcelList( String folderPath , Dispatch workbooks,
			Dispatch sheet_to )
	{
		
		File folder = new File( folderPath );
		if( folder.isDirectory() )
		{
			File[] listFile = folder.listFiles();
			
			for( int fileIdx = 0; fileIdx < listFile.length; fileIdx ++ )
			{
				if(listFile[fileIdx].getName().startsWith("~$")) continue; //add by xiaolei 20161207
				Dispatch workbook = Dispatch.invoke(workbooks, "Open", Dispatch.Method,
						new Object[] { listFile[fileIdx].getAbsolutePath(), new Variant(false), new Variant(false) },// 是否以只读方式打开
						new int[1]).toDispatch();
				Dispatch sheets = Dispatch.get((Dispatch) workbook, "Sheets").toDispatch();
				int Count = Dispatch.get(  sheets , "Count").toInt();
				for(int i = 0; i < Count; i  ++ )
				{
					int SheetIdx = i +1;
					Dispatch tmpsheet = Dispatch.invoke( sheets , "Item" , Dispatch.Get , new Object[]{SheetIdx} , new int[1] ).toDispatch();
					if( tmpsheet !=  null )
					{
						//将tmpsheet复制到sheet_to前面
						Dispatch.invoke(tmpsheet, "Copy", Dispatch.Method, new Variant[] { new Variant(sheet_to) ,
								Variant.VT_MISSING }, new int[1]);
						Dispatch.put((Dispatch) activexcomponent, "CutCopyMode", new Variant(false));
						
						String tmpsheetName = Dispatch.get( tmpsheet , "Name" ).toString();
					}
				}
				
				
				
				try {
//					Dispatch.call((Dispatch) workbook, "Save");
//					Dispatch.call((Dispatch) workbook, "Close", new Variant(false));
				} catch (Exception e) {
					System.out.println("保存关闭时出错．．．");
					e.printStackTrace();
				}/* finally {
					activexcomponent.invoke("Quit", new Variant[] {});
				}*/
			}
			for( int i = 0; i < oldSheetName.size() ; i ++ )
			{
				Dispatch tmpsheets = Dispatch.get((Dispatch) this.workbook, "Sheets").toDispatch();
				Dispatch tmpsheet = Dispatch.invoke(tmpsheets, "Item", Dispatch.Get,
						new Variant[] { new Variant(oldSheetName.get(i)) }, new int[1]).toDispatch();
				Dispatch.call((Dispatch) tmpsheet, "Delete");
			}
			
		}
	}
	
	public String getExcelApplicationVersion()
	{
		ActiveXComponent xl = new ActiveXComponent("Excel.Application");
		String version = new String( xl.getProperty("Version").toString() );
		System.out.println("version=" + xl.getProperty("Version")); 
		xl.safeRelease();
		return version;
	}

一、新建一个Excel（.xlsx）

二、选择要合并的 excel所在的文件夹

三、点击确定，正常情况下就完成了！ 看 八

四、出现报错

五、点击 “ 高级编辑器 ” →“ 转换示例文件 ”

六、将红框处连带 【逗号 “，”】 也删除掉，点击完成。

七、点击 “ 关闭并上载 ”

八、 完成 ！