1、需求

有多个excel文件，每个文件都有多个表头，现在需要将这些excel文件合并成一个excel文件，并且筛选出指定表头的数据。

2、代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
@Value("${OriginalFile}")
private String originalDocument;
@Value("${finalDocument}")
private String finalDocument;


private static int findColumnIndex(Sheet sheet, String headerName) {
    Row headerRow = sheet.getRow(0); // 假设第一行是表头
    if (headerRow != null) {
        for (Cell cell : headerRow) {
            if (headerName.equals(cell.getStringCellValue())) {
                return cell.getColumnIndex();
            }
        }
    }
    return -1; // 如果未找到，返回-1
}

@Bean
CommandLineRunner commandLineRunner(){
    return args -> {

        String folderPath = originalDocument; // Excel文件夹的路径
        String outputPath = finalDocument; // 输出文件的路径

        try (Workbook newWorkbook = new XSSFWorkbook()) {
            Sheet newSheet = newWorkbook.createSheet("Merged Data");
            AtomicReference<Row> newRow = new AtomicReference<>(newSheet.createRow(0));
            newRow.get().createCell(0).setCellValue("题名");
            newRow.get().createCell(1).setCellValue("文种");
            newRow.get().createCell(2).setCellValue("内容");

            AtomicInteger newRowNum = new AtomicInteger(1);

            Files.walk(Paths.get(folderPath))
                    .filter(Files::isRegularFile)
                    .filter(path -> path.toString().endsWith(".xlsx"))
                    .forEach(path -> {
                        try (InputStream is = new FileInputStream(path.toFile());
                             Workbook workbook = WorkbookFactory.create(is)) {
                            Sheet sheet = workbook.getSheetAt(0); // 假设数据在第一个Sheet
                            int titleIndex = findColumnIndex(sheet, "题名");
                            int typeIndex = findColumnIndex(sheet, "文种");
                            int contentIndex = findColumnIndex(sheet, "内容");

                            // 跳过表头，从第二行开始读取数据
                            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                                Row row = sheet.getRow(i);
                                if (row != null) {
                                    newRow.set(newSheet.createRow(newRowNum.getAndIncrement()));
                                    if (titleIndex != -1) {
                                        newRow.get().createCell(0).setCellValue(row.getCell(titleIndex).getStringCellValue());
                                    }
                                    if (typeIndex != -1) {
                                        newRow.get().createCell(1).setCellValue(row.getCell(typeIndex).getStringCellValue());
                                    }
                                    if (contentIndex != -1) {
                                        newRow.get().createCell(2).setCellValue(row.getCell(contentIndex).getStringCellValue());
                                    }
                                }
                            }
                        } catch (Exception e) {
                            log.info(e);

                        }
                    });

            try (OutputStream fileOut = new FileOutputStream(outputPath)) {
                newWorkbook.write(fileOut);
            }
        }
        ;
    };

    }
}