# ExcelUtilPoi

**Repository Path**: weiloveqi/excel-util-poi

## Basic Information

- **Project Name**: ExcelUtilPoi
- **Description**: poi封装的一套导入导出千万级数据
1 本工具类借鉴与easypoi和阿里的easyExcel
2 本人测试这俩框架在sax解析时都有小问题，可能我没玩明白
2 更适用于目前项目只有poi jar 的项目,要不会带来版本冲突风险 
3 ExcelUtil 这个类已经就能满足大家日常开发需求了(除excel导入数据量太大会内存溢出，本人测试数据也在50万左右，看你数据大小)
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 3
- **Created**: 2022-03-28
- **Last Updated**: 2025-09-17

## Categories & Tags

**Categories**: Uncategorized

**Tags**: Excel导入导出

## README

#### 介绍
poi封装的一套导入导出千万级数据
1 本工具类借鉴与easypoi和阿里的easyExcel
2 本人测试这俩框架在sax解析时都有小问题，可能我没玩明白
2 更适用于目前项目只有poi jar 的项目,要不会带来版本冲突风险
3 ExcelUtil 这个类已经就能满足大家日常开发需求了(除excel导入数据量太大会内存溢出，本人测试数据也在50万左右，看你数据大小)

#### 1简单导出
![img.png](img/d1.png)

@GetMapping(value = "/downLoad")
public  void  downLoad(HttpServletResponse response) throws Exception {
List<ExcelTable> excelTables = excelTableMapper.selectAll();
ExcelExportParam excelExportParam = new ExcelExportParam();
excelExportParam.setFileName("测试");
excelExportParam.setDataList(excelTables);
excelExportParam.setSourceClass(ExcelTable.class);
//报表下载
ExcelUtil.exportExcel(response, excelExportParam);
}

 运行结果:
![img.png](img/dj1.png)


#### 1.1导出带标题并文本换行

    @GetMapping(value = "/downLoad2")
    public  void  downLoad2(HttpServletResponse response) throws Exception {
        List<ExcelTable> excelTables = excelTableMapper.selectAll();
        ExcelExportParam excelExportParam = new ExcelExportParam();
        excelExportParam.setTitleName("我是标题");
        excelExportParam.setFileName("测试");
        //长文本换行
        excelExportParam.setLineFeed(true);
        excelExportParam.setDataList(excelTables);
        excelExportParam.setSourceClass(ExcelTable.class);
        //报表下载
        ExcelUtil.exportExcel(response, excelExportParam);
    }

运行结果:
![img.png](img/dj1.1.png)


#### 1.2 导出带标题并文本换行并 合并主题单元格

![img.png](img/d1.2.png)

    @GetMapping(value = "/downLoad2")
    public  void  downLoad2(HttpServletResponse response) throws Exception {
        List<ExcelTable> excelTables = excelTableMapper.selectAll();
        ExcelExportParam excelExportParam = new ExcelExportParam();
        excelExportParam.setTitleName("我是标题");
        excelExportParam.setFileName("测试");
        //长文本换行
        excelExportParam.setLineFeed(true);
        excelExportParam.setDataList(excelTables);
        excelExportParam.setSourceClass(ExcelTable.class);
        //报表下载
        ExcelUtil.exportExcel(response, excelExportParam);
    }
运行结果:

![img.png](img/dj1.2.png)


#### 1.3 导出带占位符 ${}
![img.png](img/d1.3.png)

    @GetMapping(value = "/downLoad2")
    public  void  downLoad2(HttpServletResponse response) throws Exception {
        List<ExcelTable> excelTables = excelTableMapper.selectAll();
        ExcelExportParam excelExportParam = new ExcelExportParam();
        excelExportParam.setTitleName("我是标题");
        excelExportParam.setFileName("测试");
        //长文本换行
        excelExportParam.setLineFeed(true);
        excelExportParam.setDataList(excelTables);
        excelExportParam.setSourceClass(ExcelTable.class);
        HashMap<String, Object> hashMap = new HashMap<>();
        hashMap.put("name","名字");
        excelExportParam.setPlaceholderMap(hashMap);

        //报表下载
        ExcelUtil.exportExcel(response, excelExportParam);
    }


运行结果:

![img.png](img/dj1.3.png)


#### 1.4 导出多sheet


    @GetMapping(value = "/downLoad3")
    public  void  downLoad3(HttpServletResponse response) throws Exception {
       List<ExcelExportParam> list = new ArrayList<>();
        for (int i = 0; i < 2; i++) {
            List<ExcelTable> excelTables = excelTableMapper.selectAll();
            ExcelExportParam excelExportParam = new ExcelExportParam();
            excelExportParam.setTitleName("我是标题");
            excelExportParam.setFileName("测试");
            excelExportParam.setSheetName(i+"测试sheet");
            //长文本换行
            excelExportParam.setLineFeed(true);
            excelExportParam.setDataList(excelTables);
            excelExportParam.setSourceClass(ExcelTable.class);
            HashMap<String, Object> hashMap = new HashMap<>();
            hashMap.put("name","名字");
            excelExportParam.setPlaceholderMap(hashMap);
            list.add(excelExportParam);
        }
        //报表下载
        ExcelUtil.exportExcelMoreSheet(response, list);
    }

运行结果:

![img.png](img/dj1.4.1.png)
![img.png](img/dj1.4.2.png)


#### 1.5 导出组占位符

![img.png](img/d1.5.png)

    @GetMapping(value = "/downLoad3")
    public  void  downLoad3(HttpServletResponse response) throws Exception {
    List<ExcelExportParam> list = new ArrayList<>();
    for (int i = 0; i < 2; i++) {
    List<ExcelTable> excelTables = excelTableMapper.selectAll();
    ExcelExportParam excelExportParam = new ExcelExportParam();
    excelExportParam.setTitleName("我是标题");
    excelExportParam.setFileName("测试");
    excelExportParam.setSheetName(i+"测试sheet");
    //长文本换行
    excelExportParam.setLineFeed(true);
    excelExportParam.setDataList(excelTables);
    excelExportParam.setSourceClass(ExcelTable.class);
    HashMap<String, Object> hashMap = new HashMap<>();
    hashMap.put("name","名字");
    hashMap.put("peopleInfo","人员信息");
    excelExportParam.setPlaceholderMap(hashMap);
    list.add(excelExportParam);
    }
    //报表下载
    ExcelUtil.exportExcelMoreSheet(response, list);
    }


运行结果:

![img.png](img/dj1.5.png)

#### 导入行头必须唯一

#### 2.1 导入带主题和行头已经合并的单元格 （不支持行头重复），多 sheet 时，必须每一个都是一样的模版

![img.png](img/drExcel1.png)

![img.png](img/drExcel2.png)

    @RequestMapping(value = "/importExcel",method = RequestMethod.POST)
    public   List  importExcel(MultipartFile file) throws Exception {
        ExcelImportVo excelImportVo = new ExcelImportVo();
        excelImportVo.setPojoClass(ExcelTable.class);
        excelImportVo.setTitleRows(1);
        HashMap<String, Object> hashMap = new HashMap<>();
        hashMap.put("name","名字");
        excelImportVo.setPlaceholderMap(hashMap);
        return ExcelUtil.readExcel(file.getInputStream(),excelImportVo);
    }
运行结果:

![img.png](img/drApi1.png)