Excel合并与在现有文件增加列

   <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-core</artifactId>
            <version>4.6.1</version>
        </dependency>

package com.example.demo.utils2;

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.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.*;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;

public class ExcelMergeUtils {
    private static final int XLS_MAX_ROWS = 65535;
    private static final int XLSX_MAX_ROWS = 1048575;


    public static void main(String[] args) throws IOException {

        mergeExcel(new File("E:\\file\\excelAll\\33.xls"), Arrays.asList(Objects.requireNonNull(new File("E:\\file\\testAddRowGJ").listFiles())), false);


        if (1 == 1) {
            return;
        }

        File deleteFile = new File("G:\\1\\23");
//        保存的目录
        String savePath = "E:\\file\\testAddRowJF2";

        /*addRow("E:\\file\\gj\\123.xls", savePath, false);

         */
        //取得这个目录下的所有子文件对象
        File[] deleteFiles = deleteFile.listFiles();
        for (File deleteFile1 : deleteFiles) {
            System.err.println(deleteFile1.getName());
            if (deleteFile1.isDirectory()) {
                File[] deleteFiles2 = deleteFile1.listFiles();
                for (File file : deleteFiles2) {
                    if (file.getName().toLowerCase().indexOf("xls") != -1 || file.getName().toLowerCase().indexOf("xlsx") != -1) {
                        addRow(file.getCanonicalPath(), savePath, false);
                    }
                }
            }
        }

    }


    private static int id = 1;

    /**
     * 给excel添加列
     *
     * @param filepath
     * @param isXlsx
     */
    public static void addRow(String filepath, String saveFilePathDir, boolean isXlsx) {
        int id2 = id++;
        OutputStream out = null;

        try {
            File file = new File(filepath);
            //获取excel
            Workbook workBook = isXlsx ? new XSSFWorkbook(new FileInputStream(file)) : new HSSFWorkbook(new FileInputStream(file));

//            Workbook workBook = new XSSFWorkbook(in);
//            workBook.removeSheetAt(0);
            Sheet sheet = workBook.getSheetAt(1);//获取第一个sheet页
            int i = 0;//用于显示空行
            int j = 0;//已经匹配到的数据行
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {//从表格第二行开始遍历
                i += 1;
                Row row = sheet.getRow(rowNum);//获取行
                Cell cell = row.getCell(0);//获取该行的第6列的单元格
                //当这行没数据时说明到底了
                if (null == cell || StringUtils.isEmpty(cell.getStringCellValue())) {
                    System.out.println("null====第" + i + "行");
                    break;
                }
//                cell.setCellType(CellType.STRING);//设置单元格格式，证件号多为纯数字

                Cell cell5 = row.createCell(4);//创建单元格
                Cell cell6 = row.createCell(5);//创建单元格
                Cell cell7 = row.createCell(6);//创建单元格

                if (rowNum == 0) {
                    cell5.setCellValue("小区");
                    cell6.setCellValue("图片目录");
                    cell7.setCellValue("id");
                } else {
                    cell5.setCellValue("解放");
                    cell6.setCellValue(file.getParentFile().getName());
                    cell7.setCellValue(id2);
                }
            }
            out = new FileOutputStream(saveFilePathDir + "//" + file.getName());
            workBook.write(out);//最后一顶要写入输出流
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 合并多个 Excel 文件
     *
     * @param mergedFile 合并后的文件
     * @param files      待合并的文件
     * @param isXlsx     合并文件类型是否是 xlsx
     * @throws IOException 合并异常
     */
    public static void mergeExcel(File mergedFile, List<File> files, boolean isXlsx) throws IOException {
        if (mergedFile == null || files == null) {
            return;
        }
        try (Workbook mergedWorkbook = isXlsx ? new SXSSFWorkbook() : new HSSFWorkbook();
             FileOutputStream out = new FileOutputStream(mergedFile)) {
            Sheet newSheet = mergedWorkbook.createSheet();
            int start = 0;
            for (File file : files) {
                if (file == null) {
                    continue;
                }
                try (Workbook oldWorkbook = isXlsx ? new XSSFWorkbook(new FileInputStream(file)) : new HSSFWorkbook(new FileInputStream(file))) {

                    int oldSheetSize = oldWorkbook.getNumberOfSheets();
                    for (int i = 0; i < 1; i++) {
                        Sheet oldSheet = oldWorkbook.getSheetAt(i);
                        int oldRowSize = oldSheet.getLastRowNum();
                        for (int j = 0; j <= oldRowSize; j++) {
                            if (start == (isXlsx ? XLSX_MAX_ROWS : XLS_MAX_ROWS)) {
                                newSheet = mergedWorkbook.createSheet();
                                start = newSheet.getLastRowNum();
                            }
                            Row oldRow = oldSheet.getRow(j);
                            Row newRow = newSheet.createRow(start);
                            copyRow(oldRow, newRow);
                            start++;
                        }
                    }
                }
            }
            mergedWorkbook.write(out);
        }
    }

    private static void copyRow(Row oldRow, Row newRow) {
        newRow.setHeight(oldRow.getHeight());
        for (int i = oldRow.getFirstCellNum(); i <= oldRow.getLastCellNum(); i++) {
            Cell oldCell = oldRow.getCell(i);
            if (null != oldCell) {
                copyCell(oldCell, newRow.createCell(i));
            }
        }
    }

    private static void copyCell(Cell oldCell, Cell newCell) {
        switch (oldCell.getCellType()) {
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(oldCell)) {
                    newCell.setCellValue(cn.hutool.core.date.DateUtil.formatDate(oldCell.getDateCellValue()));
                } else {
                    newCell.setCellValue(oldCell.getNumericCellValue());
                }
                break;
            case BLANK:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            default:
                break;
        }
    }

}