POI通过模板导出excel（包含表头合并处理）1、POI通过模板导出2、POI表头合并处理

103 阅读 0 评论 68 点赞

我是靠谱客的博主炙热大树，这篇文章主要介绍POI通过模板导出excel（包含表头合并处理）1、POI通过模板导出2、POI表头合并处理，现在分享给大家，希望可以做个参考。

最近接触到比较多poi相关的需求，总结一下通过模板导出以及表头合并的一些复杂情况处理。
简单使用的话可以参考下我之前写的 POI实现导入导出excel

1、POI通过模板导出

其实通过模板导出的原理，无非就是去获取到指定的模板文件，然后再去转换成文件流，最后填充自己的数据再转出excel文件。

先看下模板：（模板包含了一些样式，需要手动创建比较麻烦，所以采用模板转文件流的方式）
在这里插入图片描述

看下代码：

复制代码

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
    public static void exportMain(String templatePath){

        //获取模板
        File file = new File(templatePath);

        InputStream is = null;

        XSSFWorkbook wb = null;

        XSSFSheet sheet = null;

        InputStream exportInput = null;

        try {

            is = new FileInputStream(file);// 将excel文件转为输入流

            wb = new XSSFWorkbook(is);// 创建个workbook，

            // 获取第一个sheet
            sheet = wb.getSheetAt(0);

            //例子是第四行开始表头
            XSSFRow row3 = sheet.getRow(3);

            //获取总列数
            int cellNum = row3.getLastCellNum();

            //获取第四行的列
            for (int i=0;i<cellNum;i++){
                XSSFCell cell = row3.getCell(i);
                System.out.println("第4行第"+(i+1)+"列："+cell.toString());
            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        exportMain("C:/Users/53065/Desktop/模板表.xlsx");
    }

打印结果：

复制代码

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
第4行第1列：序号
第4行第2列：项目名称
第4行第3列：负责单位
第4行第4列：目前项目状态（完工、在建、前期）

第4行第5列：计划完工开工时间
第4行第6列：建设内容
第4行第7列：进展情况
第4行第8列：2023年工作内容
第4行第9列：总投资
第4行第10列：截止2022年12月31日已完成投资
第4行第11列：
第4行第12列：
第4行第13列：
第4行第14列：2023年计划投资
第4行第15列：
第4行第16列：
第4行第17列：
第4行第18列：
第4行第19列：
第4行第20列：
第4行第21列：
第4行第22列：资金具体来源
第4行第23列：
第4行第24列：备注

从输出结果可以看出，模板的数据我们已经拿到，以上例子只是为了简单获取模板中的数据，通过模板再可以进行下一步的操作，这部分可以参考 POI实现导入导出excel

注意：XSSFSheet是获取xlsx文件格式，HSSFSheet是获取xls格式
请根据具体情况修改！这边只是比较简单的例子。

2、POI表头合并处理

在这里插入图片描述
我们从这部分表头可以发现，有合并多个单元格的表头。
在很多需求中，导出需要数据与表头对应，如果通过列的序列去指定这种方式，其实是很不稳妥的，在模板列顺序改变后就会出现问题，所以如果出现这种比较复杂的表头时，我们采用的做法是合并表头的拼接作为表头唯一标识，对应数据（例如 2023年计划投资-计划投资额-总额）

那么需要怎么做呢？先来看个方法：（以上表头来看，我们需要处理的是第四行到第六行的表头）

复制代码

1
2
3
4
5
6
7
8
9
10
//获取单元格合并情况数
sheet.getNumMergedRegions();

//单元格合并情况
CellRangeAddress region = sheet.getMergedRegion(下标);
int firstRow = region.getFirstRow();//合并开始行
int firstColumn = region.getFirstColumn();//合并开始列
int lastColumn = region.getLastColumn();//合并结束列
int lastRow = region.getLastRow();//合并结束行

有了以上方法，我们的思路就是获取列合并的表头，只是行合并的表头过滤掉不处理，然后用map来存储，key为列的下标即可。

复制代码

public static void exportMain(String templatePath){

//获取模板
        File file = new File(templatePath);

InputStream is = null;

XSSFWorkbook wb = null;

XSSFSheet sheet = null;

InputStream exportInput = null;

try {

is = new FileInputStream(file);// 将excel文件转为输入流

wb = new XSSFWorkbook(is);// 创建个workbook，

// 获取第一个sheet
            sheet = wb.getSheetAt(0);

//存储表头合并部分
            Map<String,String> newBtMap = new HashMap<>();
            //获取合并部分
            for (int i=0;i<sheet.getNumMergedRegions();i++){
                CellRangeAddress region = sheet.getMergedRegion(i);
                int firstRow = region.getFirstRow();
                int firstColumn = region.getFirstColumn();
                int lastColumn = region.getLastColumn();
                int lastRow = region.getLastRow();
                //锁定表头且是列合并的，只是列合并的不管
                if (firstRow <3 || firstRow>5 || (lastColumn-firstColumn==0 && lastRow-firstRow>0)){
                    continue;
                }

Row row = sheet.getRow(firstRow);
                Cell cell = row.getCell(firstColumn);
                String newBtName = cell.toString().trim();
                System.out.println("开始列："+ firstColumn +"，结束列："+ lastColumn +"，开始行："+firstRow+"，结束列："+ lastRow+"，值："+newBtName);
                if (StringUtils.isEmpty(newBtName)){
                    continue;
                }
                for (int j=firstColumn;j<=lastColumn;j++){
                    String oldBtName = newBtMap.get(String.valueOf(j));
                    String newScBtName = "";
                    if (!StringUtils.isEmpty(oldBtName)){
                        newScBtName = oldBtName.trim() + "-";
                    }
                    newScBtName += newBtName ;
                    newBtMap.put(String.valueOf(j),newScBtName.replaceAll("\s*|r|n|t",""));
                }
            }

System.out.println("合并表头："+ JSONObject.toJSONString(newBtMap));

//            //例子是第四行开始表头
//            XSSFRow row3 = sheet.getRow(3);
//
//            //获取总列数
//            int cellNum = row3.getLastCellNum();
//
//            //获取第四行的列
//            for (int i=0;i<cellNum;i++){
//                XSSFCell cell = row3.getCell(i);
//                System.out.println("第4行第"+(i+1)+"列："+cell.toString());
//            }

}catch (Exception e){
            e.printStackTrace();
        }
    }

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
    public static void exportMain(String templatePath){

        //获取模板
        File file = new File(templatePath);

        InputStream is = null;

        XSSFWorkbook wb = null;

        XSSFSheet sheet = null;

        InputStream exportInput = null;

        try {

            is = new FileInputStream(file);// 将excel文件转为输入流

            wb = new XSSFWorkbook(is);// 创建个workbook，

            // 获取第一个sheet
            sheet = wb.getSheetAt(0);

            //存储表头合并部分
            Map<String,String> newBtMap = new HashMap<>();
            //获取合并部分
            for (int i=0;i<sheet.getNumMergedRegions();i++){
                CellRangeAddress region = sheet.getMergedRegion(i);
                int firstRow = region.getFirstRow();
                int firstColumn = region.getFirstColumn();
                int lastColumn = region.getLastColumn();
                int lastRow = region.getLastRow();
                //锁定表头且是列合并的，只是列合并的不管
                if (firstRow <3 || firstRow>5 || (lastColumn-firstColumn==0 && lastRow-firstRow>0)){
                    continue;
                }

                Row row = sheet.getRow(firstRow);
                Cell cell = row.getCell(firstColumn);
                String newBtName = cell.toString().trim();
                System.out.println("开始列："+ firstColumn +"，结束列："+ lastColumn +"，开始行："+firstRow+"，结束列："+ lastRow+"，值："+newBtName);
                if (StringUtils.isEmpty(newBtName)){
                    continue;
                }
                for (int j=firstColumn;j<=lastColumn;j++){
                    String oldBtName = newBtMap.get(String.valueOf(j));
                    String newScBtName = "";
                    if (!StringUtils.isEmpty(oldBtName)){
                        newScBtName = oldBtName.trim() + "-";
                    }
                    newScBtName += newBtName ;
                    newBtMap.put(String.valueOf(j),newScBtName.replaceAll("\s*|r|n|t",""));
                }
            }

            System.out.println("合并表头："+ JSONObject.toJSONString(newBtMap));

//            //例子是第四行开始表头
//            XSSFRow row3 = sheet.getRow(3);
//
//            //获取总列数
//            int cellNum = row3.getLastCellNum();
//
//            //获取第四行的列
//            for (int i=0;i<cellNum;i++){
//                XSSFCell cell = row3.getCell(i);
//                System.out.println("第4行第"+(i+1)+"列："+cell.toString());
//            }

        }catch (Exception e){
            e.printStackTrace();
        }
    }

打印结果：

复制代码

1
2
3
4
5
6
开始列：9，结束列：12，开始行：3，结束列：3，值：截止2022年12月31日已完成投资
开始列：13，结束列：20，开始行：3，结束列：3，值：2023年计划投资
开始列：21，结束列：22，开始行：3，结束列：3，值：资金具体来源
开始列：13，结束列：17，开始行：4，结束列：4，值：计划投资额
合并表头：{"11":"截止2022年12月31日已完成投资","22":"资金具体来源","12":"截止2022年12月31日已完成投资","13":"2023年计划投资-计划投资额","14":"2023年计划投资-计划投资额","15":"2023年计划投资-计划投资额","16":"2023年计划投资-计划投资额","17":"2023年计划投资-计划投资额","18":"2023年计划投资","19":"2023年计划投资","9":"截止2022年12月31日已完成投资","20":"2023年计划投资","10":"截止2022年12月31日已完成投资","21":"资金具体来源"}

这时会发现，这部分表头只包含了合并了列单元格的表头，那么还需要处理无单元格合并的情况。并将两种情况合并。

复制代码

public static void exportMain(String templatePath){

//获取模板
        File file = new File(templatePath);

InputStream is = null;

XSSFWorkbook wb = null;

XSSFSheet sheet = null;

InputStream exportInput = null;

try {

is = new FileInputStream(file);// 将excel文件转为输入流

wb = new XSSFWorkbook(is);// 创建个workbook，

// 获取第一个sheet
            sheet = wb.getSheetAt(0);

//获取表头
            Row btRow = sheet.getRow(3);
            //获取总列数
            int totalCellNum = btRow.getLastCellNum();

System.out.println("合并表头："+ JSONObject.toJSONString(newBtMap));

//取出无合并情况的表头（4-6行）
            Map<String,String> btMap = new HashMap<>();
            for (int j=3;j<6;j++){
                Row row = sheet.getRow(j);
                for (int i=0;i<totalCellNum;i++){
                    Cell cell = row.getCell(i);
                    if (StringUtils.isEmpty(cell.toString())){
                        continue;
                    }
                    String cellValue = cell.toString().trim().replaceAll("\s*|r|n|t","");
                    System.out.println("第"+(j+1)+"行第"+(i+1)+"列数据："+ cellValue);
                    btMap.put(String.valueOf(i),cellValue);
                }
            }

System.out.println("无合并情况表头："+JSONObject.toJSONString(btMap));

//合并所有表头
            //遍历拼接表头去拼接全部数据
            Set<String> newBtKeys = newBtMap.keySet();
            for (String key : newBtKeys){
                //最后一行表头
                String lastBtName = btMap.get(key);
                //除最后一行外的表头
                String cLastBtName = newBtMap.get(key);
                //最全的表头
                String allBtName = cLastBtName;
                if (!StringUtils.isEmpty(lastBtName) && !cLastBtName.equals(lastBtName)){
                    allBtName += "-"+lastBtName;
                }
                btMap.put(key,allBtName);
            }

System.out.println("导出模板拼接表头："+JSONObject.toJSONString(btMap));

}catch (Exception e){
            e.printStackTrace();
        }
    }

public static void main(String[] args) {
        exportMain("C:/Users/53065/Desktop/模板表.xlsx");
    }

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
    public static void exportMain(String templatePath){

        //获取模板
        File file = new File(templatePath);

        InputStream is = null;

        XSSFWorkbook wb = null;

        XSSFSheet sheet = null;

        InputStream exportInput = null;

        try {

            is = new FileInputStream(file);// 将excel文件转为输入流

            wb = new XSSFWorkbook(is);// 创建个workbook，

            // 获取第一个sheet
            sheet = wb.getSheetAt(0);

            //获取表头
            Row btRow = sheet.getRow(3);
            //获取总列数
            int totalCellNum = btRow.getLastCellNum();

            //存储表头合并部分
            Map<String,String> newBtMap = new HashMap<>();
            //获取合并部分
            for (int i=0;i<sheet.getNumMergedRegions();i++){
                CellRangeAddress region = sheet.getMergedRegion(i);
                int firstRow = region.getFirstRow();
                int firstColumn = region.getFirstColumn();
                int lastColumn = region.getLastColumn();
                int lastRow = region.getLastRow();
                //锁定表头且是列合并的，只是列合并的不管
                if (firstRow <3 || firstRow>5 || (lastColumn-firstColumn==0 && lastRow-firstRow>0)){
                    continue;
                }

                Row row = sheet.getRow(firstRow);
                Cell cell = row.getCell(firstColumn);
                String newBtName = cell.toString().trim();
                System.out.println("开始列："+ firstColumn +"，结束列："+ lastColumn +"，开始行："+firstRow+"，结束列："+ lastRow+"，值："+newBtName);
                if (StringUtils.isEmpty(newBtName)){
                    continue;
                }
                for (int j=firstColumn;j<=lastColumn;j++){
                    String oldBtName = newBtMap.get(String.valueOf(j));
                    String newScBtName = "";
                    if (!StringUtils.isEmpty(oldBtName)){
                        newScBtName = oldBtName.trim() + "-";
                    }
                    newScBtName += newBtName ;
                    newBtMap.put(String.valueOf(j),newScBtName.replaceAll("\s*|r|n|t",""));
                }
            }

            System.out.println("合并表头："+ JSONObject.toJSONString(newBtMap));


            //取出无合并情况的表头（4-6行）
            Map<String,String> btMap = new HashMap<>();
            for (int j=3;j<6;j++){
                Row row = sheet.getRow(j);
                for (int i=0;i<totalCellNum;i++){
                    Cell cell = row.getCell(i);
                    if (StringUtils.isEmpty(cell.toString())){
                        continue;
                    }
                    String cellValue = cell.toString().trim().replaceAll("\s*|r|n|t","");
                    System.out.println("第"+(j+1)+"行第"+(i+1)+"列数据："+ cellValue);
                    btMap.put(String.valueOf(i),cellValue);
                }
            }

            System.out.println("无合并情况表头："+JSONObject.toJSONString(btMap));

            //合并所有表头
            //遍历拼接表头去拼接全部数据
            Set<String> newBtKeys = newBtMap.keySet();
            for (String key : newBtKeys){
                //最后一行表头
                String lastBtName = btMap.get(key);
                //除最后一行外的表头
                String cLastBtName = newBtMap.get(key);
                //最全的表头
                String allBtName = cLastBtName;
                if (!StringUtils.isEmpty(lastBtName) && !cLastBtName.equals(lastBtName)){
                    allBtName += "-"+lastBtName;
                }
                btMap.put(key,allBtName);
            }

            System.out.println("导出模板拼接表头："+JSONObject.toJSONString(btMap));

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        exportMain("C:/Users/53065/Desktop/模板表.xlsx");
    }

打印结果：

复制代码

开始列：9，结束列：12，开始行：3，结束列：3，值：截止2022年12月31日已完成投资
开始列：13，结束列：20，开始行：3，结束列：3，值：2023年计划投资
开始列：21，结束列：22，开始行：3，结束列：3，值：资金具体来源
开始列：13，结束列：17，开始行：4，结束列：4，值：计划投资额
合并表头：{"11":"截止2022年12月31日已完成投资","22":"资金具体来源","12":"截止2022年12月31日已完成投资","13":"2023年计划投资-计划投资额","14":"2023年计划投资-计划投资额","15":"2023年计划投资-计划投资额","16":"2023年计划投资-计划投资额","17":"2023年计划投资-计划投资额","18":"2023年计划投资","19":"2023年计划投资","9":"截止2022年12月31日已完成投资","20":"2023年计划投资","10":"截止2022年12月31日已完成投资","21":"资金具体来源"}
第4行第1列数据：序号
第4行第2列数据：项目名称
第4行第3列数据：负责单位
第4行第4列数据：目前项目状态（完工、在建、前期）
第4行第5列数据：计划完工开工时间
第4行第6列数据：建设内容
第4行第7列数据：进展情况
第4行第8列数据：2023年工作内容
第4行第9列数据：总投资
第4行第10列数据：截止2022年12月31日已完成投资
第4行第14列数据：2023年计划投资
第4行第22列数据：资金具体来源
第4行第24列数据：备注
第5行第10列数据：已投资总额
第5行第11列数据：1.财政安排
第5行第12列数据：2.融资
第5行第13列数据：3.自筹
第5行第14列数据：计划投资额
第5行第19列数据：1.财政安排
第5行第20列数据：2.自筹
第5行第21列数据：3.融资
第5行第22列数据：1.自筹
第5行第23列数据：2.融资
第6行第14列数据：总额
第6行第15列数据：一季度
第6行第16列数据：二季度
第6行第17列数据：三季度
第6行第18列数据：四季度
无合并情况表头：{"11":"2.融资","22":"2.融资","23":"备注","12":"3.自筹","13":"总额","14":"一季度","15":"二季度","16":"三季度","17":"四季度","18":"1.财政安排","19":"2.自筹","0":"序号","1":"项目名称","2":"负责单位","3":"目前项目状态（完工、在建、前期）","4":"计划完工\开工时间","5":"建设内容","6":"进展情况","7":"2023年工作内容","8":"总投资","9":"已投资总额","20":"3.融资","21":"1.自筹","10":"1.财政安排"}
导出模板拼接表头：{"11":"截止2022年12月31日已完成投资-2.融资","22":"资金具体来源-2.融资","23":"备注","12":"截止2022年12月31日已完成投资-3.自筹","13":"2023年计划投资-计划投资额-总额","14":"2023年计划投资-计划投资额-一季度","15":"2023年计划投资-计划投资额-二季度","16":"2023年计划投资-计划投资额-三季度","17":"2023年计划投资-计划投资额-四季度","18":"2023年计划投资-1.财政安排","19":"2023年计划投资-2.自筹","0":"序号","1":"项目名称","2":"负责单位","3":"目前项目状态（完工、在建、前期）","4":"计划完工\开工时间","5":"建设内容","6":"进展情况","7":"2023年工作内容","8":"总投资","9":"截止2022年12月31日已完成投资-已投资总额","20":"2023年计划投资-3.融资","21":"资金具体来源-1.自筹","10":"截止2022年12月31日已完成投资-1.财政安排"}

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
开始列：9，结束列：12，开始行：3，结束列：3，值：截止2022年12月31日已完成投资
开始列：13，结束列：20，开始行：3，结束列：3，值：2023年计划投资
开始列：21，结束列：22，开始行：3，结束列：3，值：资金具体来源
开始列：13，结束列：17，开始行：4，结束列：4，值：计划投资额
合并表头：{"11":"截止2022年12月31日已完成投资","22":"资金具体来源","12":"截止2022年12月31日已完成投资","13":"2023年计划投资-计划投资额","14":"2023年计划投资-计划投资额","15":"2023年计划投资-计划投资额","16":"2023年计划投资-计划投资额","17":"2023年计划投资-计划投资额","18":"2023年计划投资","19":"2023年计划投资","9":"截止2022年12月31日已完成投资","20":"2023年计划投资","10":"截止2022年12月31日已完成投资","21":"资金具体来源"}
第4行第1列数据：序号
第4行第2列数据：项目名称
第4行第3列数据：负责单位
第4行第4列数据：目前项目状态（完工、在建、前期）
第4行第5列数据：计划完工开工时间
第4行第6列数据：建设内容
第4行第7列数据：进展情况
第4行第8列数据：2023年工作内容
第4行第9列数据：总投资
第4行第10列数据：截止2022年12月31日已完成投资
第4行第14列数据：2023年计划投资
第4行第22列数据：资金具体来源
第4行第24列数据：备注
第5行第10列数据：已投资总额
第5行第11列数据：1.财政安排
第5行第12列数据：2.融资
第5行第13列数据：3.自筹
第5行第14列数据：计划投资额
第5行第19列数据：1.财政安排
第5行第20列数据：2.自筹
第5行第21列数据：3.融资
第5行第22列数据：1.自筹
第5行第23列数据：2.融资
第6行第14列数据：总额
第6行第15列数据：一季度
第6行第16列数据：二季度
第6行第17列数据：三季度
第6行第18列数据：四季度
无合并情况表头：{"11":"2.融资","22":"2.融资","23":"备注","12":"3.自筹","13":"总额","14":"一季度","15":"二季度","16":"三季度","17":"四季度","18":"1.财政安排","19":"2.自筹","0":"序号","1":"项目名称","2":"负责单位","3":"目前项目状态（完工、在建、前期）","4":"计划完工\开工时间","5":"建设内容","6":"进展情况","7":"2023年工作内容","8":"总投资","9":"已投资总额","20":"3.融资","21":"1.自筹","10":"1.财政安排"}
导出模板拼接表头：{"11":"截止2022年12月31日已完成投资-2.融资","22":"资金具体来源-2.融资","23":"备注","12":"截止2022年12月31日已完成投资-3.自筹","13":"2023年计划投资-计划投资额-总额","14":"2023年计划投资-计划投资额-一季度","15":"2023年计划投资-计划投资额-二季度","16":"2023年计划投资-计划投资额-三季度","17":"2023年计划投资-计划投资额-四季度","18":"2023年计划投资-1.财政安排","19":"2023年计划投资-2.自筹","0":"序号","1":"项目名称","2":"负责单位","3":"目前项目状态（完工、在建、前期）","4":"计划完工\开工时间","5":"建设内容","6":"进展情况","7":"2023年工作内容","8":"总投资","9":"截止2022年12月31日已完成投资-已投资总额","20":"2023年计划投资-3.融资","21":"资金具体来源-1.自筹","10":"截止2022年12月31日已完成投资-1.财政安排"}