Poi合并excel

package com.lyy.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
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;

import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * excel工具类
 * 将所有的excel的sheet 提出来组成一个新的excel
 * @author liyy
 * @since 2024-01-02 14:27:51
 **/
@Slf4j
public class ExcelMegerUtil {

	public static void main(String[] args) throws Exception {
//		test1Merge(); // 测试源码

		test2Merge(); // 根据源码改造成适合业务的代码
	}

	private static void test1Merge() throws Exception {
		// 指定要合并的 Excel 文件路径
		String[] filePaths = {
				"C:\\Users\\1400096\\Desktop\\test111.xlsx",
				"C:\\Users\\1400096\\Desktop\\test112.xlsx"
				// ...
		};

		// 创建合并后的 Excel 工作簿对象
		XSSFWorkbook workbook = new XSSFWorkbook();

		// 遍历每个 Excel 文件
		for (String filePath : filePaths) {
			// 读取 Excel 文件
			FileInputStream inputStream = new FileInputStream(new File(filePath));
			XSSFWorkbook workbookSource = new XSSFWorkbook(inputStream);
			inputStream.close();

			// 遍历每个 sheet
			for (int i = 0; i < workbookSource.getNumberOfSheets(); i++) {
				XSSFSheet sheet = workbookSource.getSheetAt(i);
				XSSFSheet newSheet = workbook.createSheet(sheet.getSheetName());

				mergerRegion(newSheet, sheet);

				// 将 sheet 中的行和单元格复制到新的 sheet 中
				for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
					XSSFRow row = sheet.getRow(rowNum);
					if (row != null) {
						// 设置行高
						XSSFRow newRow = newSheet.createRow(rowNum);
						newRow.setHeight(row.getHeight());
						// 获取所有列
						for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
							// 列宽
							newSheet.setColumnWidth(cellNum, sheet.getColumnWidth(cellNum));
							// 复制单元格
							XSSFCell cell = row.getCell(cellNum);
							if (cell != null) {
								XSSFCell newCell = newRow.createCell(cellNum);
//								newCell.setCellValue(cell.getStringCellValue());

								// 赋值样式
								XSSFCellStyle newExcelStyle = workbook.createCellStyle();
								newExcelStyle.cloneStyleFrom(cell.getCellStyle());
								newCell.setCellStyle(newExcelStyle);
								if(cell.getCellComment()!=null){
									newCell.setCellComment(cell.getCellComment());
								}

								//不同数据类型处理
								switch (cell.getCellType()){
									case Cell.CELL_TYPE_BLANK:
										break;
									case Cell.CELL_TYPE_BOOLEAN:
										newCell.setCellValue(cell.getBooleanCellValue());
										break;
									case Cell.CELL_TYPE_ERROR:
										newCell.setCellValue(cell.getErrorCellValue());
										break;
									case Cell.CELL_TYPE_FORMULA:
										newCell.setCellValue(cell.getCachedFormulaResultType());
										break;
									case Cell.CELL_TYPE_NUMERIC:
										newCell.setCellValue(cell.getNumericCellValue());
										break;
									case Cell.CELL_TYPE_STRING:
										newCell.setCellValue(cell.getStringCellValue());
										break;
								}
							}
						}
					}
				}
			}
		}

		// 将合并后的 Excel 写入输出流中
		FileOutputStream outputStream = new FileOutputStream("C:\\Users\\1400096\\Desktop\\mergeTest.xlsx");
		workbook.write(outputStream);
		outputStream.close();
	}

	private static void test2Merge() throws Exception {
		// 指定要合并的 Excel 文件路径
		String[] filePaths = {
				"C:\\Users\\1400096\\Desktop\\test111.xlsx",
				"C:\\Users\\1400096\\Desktop\\test112.xlsx"
				// ...
		};

		List<Map<String, Object>> sheetList = new ArrayList<>();
		// 遍历每个 Excel 文件
		int i = 1;
		for (String filePath : filePaths) {
			// 读取 Excel 文件
			FileInputStream inputStream = new FileInputStream(new File(filePath));
			byte[] bytes = FileUtils.getBytesByInput(inputStream);

			Map<String, Object> sheet = new HashMap<>();
			sheet.put("sheetName", "ss" + i);
			sheet.put("excelBytes", bytes);
			sheetList.add(sheet);
			++i;
		}

		byte[] mergeBytes = mergeExcelByByte(sheetList);
		// 将合并后的 Excel 写入输出流中
		FileOutputStream outputStream = new FileOutputStream("C:\\Users\\1400096\\Desktop\\testMMM.xlsx");
		BufferedOutputStream bos = new BufferedOutputStream(outputStream);
		bos.write(mergeBytes);

		bos.close();
		outputStream.close();
	}

	/**
	 * sheetList:[{"sheetName":"sheetName", "excelBytes":"4324325terggtert43"}]
	 */
	public static byte[] mergeExcelByByte(List<Map<String, Object>> sheetList) throws Exception {
		// 创建合并后的 Excel 工作簿对象
		XSSFWorkbook newWorkbook = new XSSFWorkbook();

		for (Map<String, Object> entry : sheetList) {
			String sheetName = (String) entry.get("sheetName");
			byte[] fileBytes = (byte[]) entry.get("excelBytes");

			InputStream inputStream = new ByteArrayInputStream(fileBytes);
			XSSFWorkbook workbookSource = new XSSFWorkbook(inputStream);
			inputStream.close();
			// 遍历每个 sheet
			for (int i = 0; i < workbookSource.getNumberOfSheets(); i++) {
				XSSFSheet sheet = workbookSource.getSheetAt(i);
				XSSFSheet newSheet = newWorkbook.createSheet(sheetName);

				// 复制 sheet
				copySheet(newSheet, sheet);
			}
		}

		OutputStream out = new ByteArrayOutputStream();
		newWorkbook.write(out);

		ByteArrayInputStream byteArrayInputStream = FileUtils.parseOutputToInput(out);
		byte[] byteRet = FileUtils.getBytesByInput(byteArrayInputStream);
		return byteRet;
	}

	/** 复制sheet */
	private static void copySheet(XSSFSheet newSheet, XSSFSheet sheet) {
		// 合并单元格
		mergerRegion(newSheet, sheet);

		// 将 sheet 中的行和单元格复制到新的 sheet 中
		for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
			XSSFRow row = sheet.getRow(rowNum);
			if (row != null) {
				XSSFRow newRow = newSheet.createRow(rowNum);
				// 复制行
				copyRow(newSheet, sheet, newRow, row);
			}
		}

		// 列宽
		for (int cellNum = 0; cellNum <= sheet.getRow(0).getLastCellNum(); cellNum++) {
			newSheet.setColumnWidth(cellNum, sheet.getColumnWidth(cellNum));
		}
	}

	/** 复制行 */
	private static void copyRow(XSSFSheet newSheet, XSSFSheet sheet, XSSFRow newRow, XSSFRow row) {
		if (row != null) {
			// 设置行高
			newRow.setHeight(row.getHeight());
			// 获取所有列
			for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
				// 复制单元格
				XSSFCell cell = row.getCell(cellNum);
				if (cell != null) {
					XSSFCell newCell = newRow.createCell(cellNum);
//								newCell.setCellValue(cell.getStringCellValue());
					// 复制 单元格
					copyCell(newCell, cell);
				}
			}
		}
	}

	/** 复制 单元格 */
	private static void copyCell(XSSFCell newCell, XSSFCell cell) {
		// 赋值样式
		XSSFCellStyle newExcelStyle = newCell.getSheet().getWorkbook().createCellStyle();
		newExcelStyle.cloneStyleFrom(cell.getCellStyle());
		// poi按照一个源单元格设置目标单元格格式，如果两个单元格不在同一个workbook，要用cloneStyleFrom()，不能用setCellStyle()
		newCell.setCellStyle(newExcelStyle);
		if(cell.getCellComment()!=null){
			newCell.setCellComment(cell.getCellComment());
		}

		//不同数据类型处理
		switch (cell.getCellType()){
			case Cell.CELL_TYPE_BLANK:
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				newCell.setCellValue(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_ERROR:
				newCell.setCellValue(cell.getErrorCellValue());
				break;
			case Cell.CELL_TYPE_FORMULA:
				newCell.setCellValue(cell.getCachedFormulaResultType());
				break;
			case Cell.CELL_TYPE_NUMERIC:
				newCell.setCellValue(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING:
				newCell.setCellValue(cell.getStringCellValue());
				break;
		}
	}

	/** 复制原有sheet的合并单元格到新创建的sheet */
	private static void mergerRegion(XSSFSheet newSheet, XSSFSheet sheet) {
		int rownum = sheet.getLastRowNum();
		List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>();
		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			oldRanges.add(sheet.getMergedRegion(i));
		}
		// 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式
		for (int k = 0; k < oldRanges.size(); k++) {
			CellRangeAddress oldRange = oldRanges.get(k);
			if (oldRange.getFirstRow() >= 0 && oldRange.getLastRow() <= rownum) {
				newSheet.addMergedRegion(oldRange);
			}
		}
	}
}