合并单元格

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.xml.crypto.Data;
import java.io.*;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


/**

 * @version 1.0
 * @date 2021/6/3 15:41 周四
 */
public class DmeoTest {
    private static final int BUFFER_SIZE = 2 * 1024;

    public static void main(String[] args) {
        try {
//            ZipUtils zipUtils = new ZipUtils();
//            zipUtils.unzip("C:\\test\\批量新增人员信息模板及说明.zip", "C:\\test");
//            String excelFile = getExcelFile("C:\\test");
//            List<String> list = Arrays.asList(
//                new File(excelFile).toString(),
//                    new File("C:\\test\\b.xlsx").toString());
            String ExeclPath = "C:\\test\\批量新增人员信息模板及说明";//指定生成excel的地址
            String ExeclPath1 = "C:\\testyyy\\批量新增人员信息模板及说明";//指定生成excel的地址
//            mergexcel(list,"上传人员模板.xlsx",ExeclPath);
//            createFile(ExeclPath);
            // 指定文件夹路径
//            copyFile(ExeclPath, ExeclPath);
            /** 测试压缩方法1  */
//            FileOutputStream fos1 = new FileOutputStream(new File("C:\\test\\批量新增人员信息模板及说明.zip"));
//            toZip(ExeclPath, fos1,true);
            List<PersonTable> data = new ArrayList<>();
            exportZi(ExeclPath);
        } catch (Exception exception) {

        }
    }

    // 导出文本
    public static void exportZi(String filePath) {
        try {
            // 以新的实体类格式导出
//            List<ExcelPersonTableZi> excelPersonTables = new ArrayList<>();
            ExportParams deptExportParams = new ExportParams();
            ExcelPersonTableZi excelPersonTable = new ExcelPersonTableZi();
            excelPersonTable.setPersonId(1);
            excelPersonTable.setPersonName("13213");
            excelPersonTable.setGender(1);
            excelPersonTable.setIdentityNo("23123");
            excelPersonTable.setBirthday("1");
            excelPersonTable.setResidentialAddress("131");
            excelPersonTable.setEthnicity(" 312312");
            excelPersonTable.setTelephone("31232131");
            excelPersonTable.setIdenticationInfo("231312");
            excelPersonTable.setStudentLevel("3123213");
            excelPersonTable.setGrade("319831");
            excelPersonTable.setDepartments("13123");
            excelPersonTable.setIdentityTypeCode("312313");
            excelPersonTable.setDescription("1739132");
            excelPersonTable.setDataSource("123917312");
            excelPersonTable.setParkname("niahsda");
            // 设置sheet得名称
            deptExportParams.setSheetName("人员信息表");
            Map<String, Object> deptExportMap = new HashMap<>();
            // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
            deptExportMap.put("title", deptExportParams);
            // 模版导出对应得实体类型
            deptExportMap.put("entity", ExcelPersonTableZi.class);
            // sheet中要填充得数据
            deptExportMap.put("data", excelPersonTable);

            List<Map<String, Object>> sheetsList = new ArrayList<>();
            sheetsList.add(deptExportMap);
            Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
            FileOutputStream fos;
            String path = filePath + "/人员基础信息.xls";
//            String path = filePath + "/" + name;
            fos = new FileOutputStream(path);
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * @param src    源文件的路径
     * @param target 目标文件的路径
     * @Description: 通过字节流复制一个文件
     * @author L.Eric
     * create: 2013-4-16
     */
    public static void copyFile(String src, String target) {
        InputStream is = null;
        OutputStream os = null;

        try {
            is = new FileInputStream(src);
            os = new FileOutputStream(target);
            byte[] buff = new byte[1024];
            int len = 0;
            while ((len = is.read(buff, 0, buff.length)) != -1) {
                os.write(buff, 0, len);
            }
            System.out.println("文件拷贝成功！");
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } finally {
                    if (is != null) {
                        try {
                            is.close();
                        } catch (IOException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                        }
                    }
                }
            }
        }

    }

    /**
     * @param pathName 需要复制的目标目录
     * @param target   生成的目标文件目录
     * @Description: 复制一个目录下的所有目录文件（只复制目录结构）
     * @author L.Eric
     * create: 2013-4-16
     */
    public static void parseDir(String pathName, String target) {
        //创建一个新的目录
        File targetFile = new File(target);
        if (!targetFile.exists()) {
            targetFile.mkdirs();
        }

        //创建一个抽象路径
        File file = new File(pathName);
        if (file.isDirectory()) {
            File[] files = file.listFiles();
            for (File f : files) {
                if (f.isDirectory()) {
                    parseDir(f.getPath(), target + "//" + f.getName());
                }
            }
        }
    }

    /**
     * 压缩成ZIP 方法1
     *
     * @param srcDir           压缩文件夹路径
     * @param out              压缩文件输出流
     * @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
     *                         false:所有文件跑到压缩包根目录下(注意：不保留目录结构可能会出现同名文件,会压缩失败)
     * @throws RuntimeException 压缩失败会抛出运行时异常
     */
    public static void toZip(String srcDir, OutputStream out, boolean KeepDirStructure) throws RuntimeException {
        long start = System.currentTimeMillis();
        ZipOutputStream zos = null;
        try {
            zos = new ZipOutputStream(out);
            File sourceFile = new File(srcDir);
            compress(sourceFile, zos, sourceFile.getName(), KeepDirStructure);
            long end = System.currentTimeMillis();
            System.out.println("压缩完成，耗时：" + (end - start) + " ms");
        } catch (Exception e) {
            throw new RuntimeException("zip error from ZipUtils", e);
        } finally {
            if (zos != null) {
                try {
                    zos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 递归压缩方法
     *
     * @param sourceFile       源文件
     * @param zos              zip输出流
     * @param name             压缩后的名称
     * @param KeepDirStructure 是否保留原来的目录结构,true:保留目录结构;
     *                         false:所有文件跑到压缩包根目录下(注意：不保留目录结构可能会出现同名文件,会压缩失败)
     * @throws Exception
     */
    private static void compress(File sourceFile, ZipOutputStream zos, String name, boolean KeepDirStructure) throws Exception {
        byte[] buf = new byte[BUFFER_SIZE];
        if (sourceFile.isFile()) {
            // 向zip输出流中添加一个zip实体，构造器中name为zip实体的文件的名字
            zos.putNextEntry(new ZipEntry(name));
            // copy文件到zip输出流中
            int len;
            FileInputStream in = new FileInputStream(sourceFile);
            while ((len = in.read(buf)) != -1) {
                zos.write(buf, 0, len);
            }
            // Complete the entry
            zos.closeEntry();
            in.close();
        } else {
            File[] listFiles = sourceFile.listFiles();
            if (listFiles == null || listFiles.length == 0) {
                // 需要保留原来的文件结构时,需要对空文件夹进行处理
                if (KeepDirStructure) {
                    // 空文件夹的处理
                    zos.putNextEntry(new ZipEntry(name + "/"));
                    // 没有文件，不需要文件的copy
                    zos.closeEntry();
                }
            } else {
                for (File file : listFiles) {
                    // 判断是否需要保留原来的文件结构
                    if (KeepDirStructure) {
                        // 注意：file.getName()前面需要带上父文件夹的名字加一斜杠,
                        // 不然最后压缩包中就不能保留原来的文件结构,即：所有文件都跑到压缩包根目录下了
                        compress(file, zos, name + "/" + file.getName(), KeepDirStructure);
                    } else {
                        compress(file, zos, file.getName(), KeepDirStructure);
                    }
                }
            }
        }
    }

    // 创建一个临时的文件夹存放execl
    public static String createFile(String path) {
        File file = new File(path);
        //判断文件是否存在;
        if (!file.exists()) {
            //创建文件;
            boolean bol = file.mkdirs();
            if (bol) {
                System.out.println(path + " 路径创建成功!");
            } else {
                System.out.println(path + " 路径创建失败!");
            }
        } else {
            System.out.println(path + " 文件已经存在!");
        }
        return path;
    }


    /**
     * 生成.zip文件;
     */
    public static void craeteZipPath(String path, String fileMulu) throws IOException {
        ZipOutputStream zipOutputStream = null;
        File file = new File(path + ".zip");
        zipOutputStream = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(file)));
        File[] files = new File(path).listFiles();
        FileInputStream fileInputStream = null;
        byte[] buf = new byte[1024];
        int len = 0;
        if (files != null && files.length > 0) {
            for (File excelFile : files) {
                String fileName = excelFile.getName();
                fileInputStream = new FileInputStream(excelFile);
                //放入压缩zip包中;
                zipOutputStream.putNextEntry(new ZipEntry(fileMulu + "/" + fileName));
                //读取文件;
                while ((len = fileInputStream.read(buf)) > 0) {
                    zipOutputStream.write(buf, 0, len);
                }
                //关闭;
                zipOutputStream.closeEntry();
                if (fileInputStream != null) {
                    fileInputStream.close();
                }
            }
        }
        if (zipOutputStream != null) {
            zipOutputStream.close();
        }
    }

    //获得以.xls为后缀的Excel文件
    public static String getExcelFile(String path) {
        String absolutePath = null;
        File file = new File(path);
        if (!file.exists()) {//判断当前目录是否存在
            System.err.println("The dir are not exists!");
            return null;
        }
        String[] content = file.list();//取得当前目录下所有文件和文件夹
        for (String name : content) {
            File temp = new File(path, name);
            if (temp.isDirectory()) {//判断是否是目录
                String[] con = temp.list();
                for (String name1 : con) {
                    if (name1.endsWith(".xls") || name1.endsWith(".xlsx")) {
                        File excelFile = new File(temp, name1);
                        absolutePath = excelFile.getAbsolutePath();
                    }
                }
            }
        }
        return absolutePath;
    }

    /**
     * * 合并多个ExcelSheet
     *
     * @param files     文件字符串(file.toString)集合,按顺序进行合并，合并的Excel中Sheet名称不可重复
     * @param excelName 合并后Excel名称(包含后缀.xslx)
     * @param dirPath   存储目录
     * @return
     * @Date: 2020/9/18 15:31
     */
    public static void mergexcel(List<String> files, String excelName, String dirPath) {
        XSSFWorkbook newExcelCreat = new XSSFWorkbook();
        // 遍历每个源excel文件，TmpList为源文件的名称集合
        for (String fromExcelName : files) {
            try (InputStream in = new FileInputStream(fromExcelName)) {
                XSSFWorkbook fromExcel = null;
                try {
                    fromExcel = new XSSFWorkbook(in);
                } catch (IOException e) {
                    System.out.println(e.getMessage());
                }
                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);
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        // 定义新生成的xlxs表格文件
        String allFileName = dirPath + File.separator + excelName;
        try (FileOutputStream fileOut = new FileOutputStream(allFileName)) {
            newExcelCreat.write(fileOut);
            fileOut.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                newExcelCreat.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 合并单元格
     *
     * @param fromSheet
     * @param toSheet
     */
    private 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
     */
    private static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        // 复制单元格样式
        newstyle.cloneStyleFrom(fromCell.getCellStyle());
        // 样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        CellType fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == CellType.STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == CellType.BLANK) {
            // nothing21
        } else if (fromCellType == CellType.BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == CellType.ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == CellType.FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else {
            // nothing29
        }
    }

    /**
     * 行复制功能
     *
     * @param wb
     * @param oldRow
     * @param toRow
     */
    private 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
     */
    private 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);
        }
    }

}

  <!--使用POI实现导入导出-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>