引入maven

https://mvnrepository.com/search?q=poi

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>

读入Excel工作簿

FileInputStream in = new FileInputStream(file);
        Workbook source_workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).open(in);
//此方法可以避免一次将所有的行加入内存，导致比较大的Excel出现内存溢出

需要添加的maven依赖：

<dependency>
    <groupId>com.monitorjbl</groupId>
    <artifactId>xlsx-streamer</artifactId>
    <version>2.1.0</version>
</dependency>

遍历文件夹下的所有工作簿

private static File[] getAllFiles(String DirectoryName) throws InterruptedException {
        File file = new File(new File("").getAbsolutePath() + "/" + DirectoryName);
        System.out.println(file);
        System.out.println("==================正在加载文件==================");
        for (File listFile : file.listFiles()) {
            System.out.println(listFile);
        }
        System.out.println("==================" + file.listFiles().length + "个文件加载完毕==================");
        return file.listFiles();
    }

获取表头并将表头写入Excel

//得到表头
    private static List<String> getAllHead(File[] fileList) throws IOException {
        List<String> heads = new LinkedList<String>();
        Workbook workbook = WorkbookFactory.create(fileList[0]);
        Row firstRow = workbook.getSheetAt(sheet_num - 1).getRow(0);
        for (int i = 0; i < firstRow.getPhysicalNumberOfCells(); i++) {
            heads.add(firstRow.getCell(i).toString());
        }
        return heads;
    }

//将所有表头写入合成Excel
    private static void writeAllHeadToNewExcel(XSSFWorkbook new_workbook, List<String> heads) {
        XSSFRow new_rows = new_workbook.getSheetAt(0).createRow(0);
        for (int i = 0; i < heads.size(); i++) {
            new_rows.createCell(i).setCellValue(heads.get(i));
            new_rows.getCell(i).setCellType(CellType.STRING);
        }
    }

写入Excel工作簿

//将此文件第二行开始写入目标Excel
public static XSSFWorkbook new_workbook = new XSSFWorkbook();
public static XSSFSheet new_sheet;

private static void writeDataToNewExcel(File file) throws IOException {
        FileInputStream in = new FileInputStream(file);
        Workbook source_workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).open(in);
//        System.out.println(source_workbook);
        Sheet source_sheet = source_workbook.getSheetAt(sheet_num - 1);
        for (Row row : source_sheet) {
            if(row.getRowNum() != 0) {
                XSSFRow new_rows = new_sheet.createRow(new_sheet.getLastRowNum() + 1);
//                System.out.println("开始遍历第" + (row.getRowNum() + 1) + "行数据：");
                for (Cell cell : row) {
                    if (cell == null || cell.getCellType() == CellType.BLANK) {
                        break;
                    } else {
                        switch (cell.getCellType()) {
                            case STRING:
                                new_rows.createCell(cell.getColumnIndex()).setCellValue(cell.getStringCellValue());
                                new_rows.getCell(cell.getColumnIndex()).setCellType(CellType.STRING);
                                break;
                            case NUMERIC:
                                new_rows.createCell(cell.getColumnIndex()).setCellValue(cell.getNumericCellValue());
                                new_rows.getCell(cell.getColumnIndex()).setCellType(CellType.NUMERIC);
                                break;
                        }
                    }
                }
            } else {
                continue;
            }
        }
        in.close();
}

生成文件

//生成文件
    private static void generateFile(String fileName) throws IOException {
        File new_file = new File(new File("").getAbsolutePath() + "/" + fileName);
        FileOutputStream outputStream = new FileOutputStream(new_file);
        System.out.println("==================正在生成文件==================");
        new_workbook.write(outputStream);
        System.out.println("==================文件生成完毕==================");
        System.out.println("==================请查看文件夹==================");
        System.out.println("new_file");
    }

ps: 弹窗选择文件夹

public File[] getDirectoryFilesFromExplorer() {
    JFileChooser fileChooser = new JFileChooser();
    FileSystemView fsv = FileSystemView.getFileSystemView();
    System.out.println();
    System.out.println(fsv.getHomeDirectory());                //得到桌面路径
    fileChooser.setCurrentDirectory(fsv.getHomeDirectory());
    fileChooser.setDialogTitle("请选择要操作的文件...");
    fileChooser.setApproveButtonText("确定");
    fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);

    String tamp;
    int result = fileChooser.showOpenDialog(null);
    if (JFileChooser.APPROVE_OPTION == result) {
        String path = fileChooser.getSelectedFile().getPath();
        System.out.println("文件加载路径：\t" + path);
        File[] files = new File(path).listFiles();

        if (files.length == 0) {
            System.out.println("当前文件夹下没有文件，请重新选择");
            return getDirectoryFilesFromExplorer();
        } else {
            for (File file : files) {
                System.out.println("已加载文件：\t" + file);
            }
            return files;
        }

    } else {
        System.out.println("未选择文件夹，是否重新选择  Y:重新选择  N:退出程序");
        Scanner input = new Scanner(System.in);
        tamp = input.next();
        if ("Y".equals(tamp)) {
            return getDirectoryFilesFromExplorer();
        } else {
            System.exit(0);
            return null;
        }
    }
}

Java使用poi库操作Excel