开发中经常会遇到excel的处理，导入导出解析等等。正好最近在项目里面需要用到EasyPOI，非常

容易，简单上手，一行代码实现导入导出。

1、导入maven依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.0.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.0.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.0.0</version>
</dependency>

在springboot项目里面也可以使用如下坐标

<dependency>

       <groupId>cn.afterturn</groupId>

       <artifactId>easypoi-spring-boot-starter</artifactId>

       <version>4.0.0</version>

   </dependency>

2、导出功能

前端页面：

<form class="layui-form" action="/load" onsubmit="return false" id="form">

    <div class="layui-form-item">

        <label class="layui-form-label">经办人</label>

        <div class="layui-input-inline">

            <input type="text" name="transactor" id="transactor" placeholder="请输入经办人" autocomplete="off" class="layui-input">

        </div>

    </div>

    <div class="layui-form-item">

        <label class="layui-form-label">摘要</label>

        <div class="layui-input-inline">

            <input type="text" name="summary" id="summary" placeholder="请输入摘要" autocomplete="off" class="layui-input">

        </div>

    </div>

    <div class="layui-form-item">

        <label class="layui-form-label">选择时间</label>

        <div class="layui-input-inline">

            <input type="text" name="opetime" id="opetime" placeholder="请选择时间范围" autocomplete="off"

                   class="layui-input">

        </div>

    </div>

    <div class="layui-form-item">

        <div class="layui-input-block">

            <button class="layui-btn" lay-submit id="loadBt">导出</button>

         </button>-->

        </div>

    </div>

</form>


<script>

    layui.use(['layer', 'laydate'], function () {

        var layer = layui.layer;

        var laydate = layui.laydate;

        laydate.render({

            elem: '#opetime',

            type:'date',

            range:true,//开启左右面板,可以进行日期范围的选择，很好用

            //trigger:'mouseover',//定义鼠标悬停时弹出控件

            theme:'#393D49',//主题颜色

            calendar:true//是否显示公历节日

        });

    });



    layui.use('form', function () {

        var form = layui.form;

        var $ = layui.$;

        $("#loadBt").click(function () {

            transactor = $("#transactor").val(),

            summary = $("#summary").val(),

            opetime=$("#opetime").val();

            $("#form").attr("onsubmit", "return true");

            $("#form").submit();

            $("#form").attr("onsubmit", "return false");

        });

     });

</script>

后台Controller层代码

/**

 * @Author Steel.D

 * @Description

 * @Date  2019-7-30 16:45

 * @Param  excel的模型集合，请求，响应

 * @return

 **/



@RequestMapping("/load")

public  void loadExcel(ModelMap map, HttpServletRequest request,

                       HttpServletResponse response){

    //将接收的参数进行处理

    String transactor = request.getParameter("transactor");

    String summary = request.getParameter("summary");

    String opetime = request.getParameter("opetime");

    //传过来的数据格式为2019-09-12 - 2019-09-30 需要进行处理

    opetime = opetime.replaceAll(" ", "");

    Map<String,Object> params1 = new HashMap<>();

    if ( StringUtils.isNotEmpty(opetime)){

        String substring = opetime.substring(0, 10);

        String substring1 = opetime.substring(11);

        Date date = DateUtils.strToDate(substring);

        Date date1 = DateUtils.strToDate(substring1);

        params1.put("opetime",date);

        params1.put("endtime",date1);

    }

    params1.put("transactor",transactor);

    params1.put("summary",summary);

    List<Finance> bills = financeDao.all(params1);

    ExportParams params = new ExportParams("流水详情", "概览", ExcelType.XSSF);

    params.setFreezeCol(2);

    map.put(NormalExcelConstants.DATA_LIST, bills);

    map.put(NormalExcelConstants.CLASS, Finance.class);

    map.put(NormalExcelConstants.PARAMS, params);

    map.put(NormalExcelConstants.FILE_NAME, "编易教育账单流水");

    //封装数据进行数据导出 PoiBaseView.render(map,request,response,NormalExcelConstants.EASYPOI_EXCEL_VIEW);
}

3、导入Excel功能

前端代码：

<form class="layui-form" action="/upload" enctype="multipart/form-data" method="post">

    <input type="file" name="file"><br>

    <input type="submit" value="提交" id="submit">

</form>

后台Controller，一行代码实现Excel解析

/**

 * @Author Steel.D

 * @Description 

 * @Date  2019-7-31 17:32

 * @Param 

 * @return

 **/



@PostMapping("/upload")

public ResponseInfo upload(@RequestParam("file") MultipartFile file){
//上传的Excel进行数据模型解析封装 ，四个参数分别为，上传的文件，excel表标题行数，头行数，实体类class

    List<TStudent> tStudents = EasyPoiUtil.importExcel(file, 1, 1, TStudent.class);

    studentService.sava(tStudents);

    return new ResponseInfo("1","成功");

}

EasyPOI非常实用的一个工具类：

/**

 * @Author Steel.D

 * @Description   easypoi导入导出通用工具类

 * @Date  2019-7-31 9:29

 * @Param

 * @return

 **/

public class EasyPoiUtil {

    /**

     * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头

     *

     * @author Steel.D

     * @Date 2019-7-31 9:30

     * @param list 导出的实体类

     * @param title 表头名称

     * @param sheetName sheet表名

     * @param pojoClass 映射的实体类

     * @param isCreateHeader 是否创建表头

     * @param fileName

     * @param response

     * @return

     */

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {

        ExportParams exportParams = new ExportParams(title, sheetName);

        exportParams.setCreateHeadRows(isCreateHeader);

        defaultExport(list, pojoClass, fileName, response, exportParams);

    }





    /**

     * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头

     *

     * @author Steel.D

     * @Date 2019-7-31 9:35

     * @param list 导出的实体类

     * @param title 表头名称

     * @param sheetName sheet表名

     * @param pojoClass 映射的实体类

     * @param fileName  文件名

     * @param response

     * @return

     */

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {

        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));

    }



    /**

     * 功能描述：Map 集合导出

     *

     * @author Steel.D

     * @Date 2019-7-31 9:45

     * @param list 实体集合

     * @param fileName 导出的文件名称

     * @param response

     * @return

     */

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {

        defaultExport(list, fileName, response);

    }



    /**

     * 功能描述：默认导出方法

     *

     * @author Steel.D

     * @Date 2019-7-31 9:50

     * @param list 导出的实体集合

     * @param fileName 导出的文件名

     * @param pojoClass pojo实体

     * @param exportParams ExportParams封装实体

     * @param response

     * @return

     */

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);

        if (workbook != null) {

            downLoadExcel(fileName, response, workbook);

        }

    }

    /**

     * 功能描述：Excel导出

     *

     * @author Steel.D

     * @Date 2019-7-31 10:35

     * @param fileName 文件名称

     * @param response

     * @param workbook Excel对象

     * @return

     */

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {

        try {

            response.setCharacterEncoding("UTF-8");

            response.setHeader("content-Type", "application/vnd.ms-excel");

            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            workbook.write(response.getOutputStream());

        } catch (IOException e) {

            throw new  RuntimeException(e);

        }

    }



    /**

     * 功能描述：默认导出方法

     *

     * @author SteeL.D

     * @Date 2019-7-31 10:45

     * @param list 导出的实体集合

     * @param fileName 导出的文件名

     * @param response

     * @return

     */

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {

        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);

        if (workbook != null) ;

        downLoadExcel(fileName, response, workbook);

    }





    /**

     * 功能描述：根据文件路径来导入Excel

     *

     * @author Steel.D

     * @Date 2019-7-31 11:05

     * @param filePath 文件路径

     * @param titleRows 表标题的行数

     * @param headerRows 表头行数

     * @param pojoClass Excel实体类

     * @return

     */

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {

        //判断文件是否存在

        if (StringUtils.isBlank(filePath)) {

            return null;

        }

        ImportParams params = new ImportParams();

        params.setTitleRows(titleRows);

        params.setHeadRows(headerRows);

        List<T> list = null;

        try {

            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);

        } catch (NoSuchElementException e) {

            throw new RuntimeException("模板不能为空");

        } catch (Exception e) {

            e.printStackTrace();



        }

        return list;

    }



    /**

     * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类

     *

     * @author Steel.D

     * @Date 2019-7-31 11:30

     * @param file 上传的文件

     * @param titleRows 表标题的行数

     * @param headerRows 表头行数

     * @param pojoClass Excel实体类

     * @return

     */

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {

        if (file == null) {

            return null;

        }

        ImportParams params = new ImportParams();

        params.setTitleRows(titleRows);

        params.setHeadRows(headerRows);

        List<T> list = null;

        try {

            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);

        } catch (NoSuchElementException e) {

            throw new RuntimeException("excel文件不能为空");

        } catch (Exception e) {

            throw new RuntimeException(e.getMessage());

        }

        return list;

    }



}

实体类导出的时候需要用到@Excel注解

@Excel(name="账单id")

private Integer id;

@Excel(name="经办人")

private String transactor;

@Excel(name = "摘要")

private String summary;

@Excel(name="详情",width = 50)

private String details;

@Excel(name="收入")

private Integer income;

@Excel(name = "支出")

private Integer payment;

@Excel(name = "累计")

private Integer count;

@Excel(name = "备注",width = 20)

private String comments;

@Excel(name="处理时间",exportFormat ="yyyy-MM-dd HH:mm:ss",width = 30)

Logo
华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

cover

HAS2024：华为云以系统性创新加速千行万业智能化升级

avatar 华为云开发者联盟
cover

数仓的两种轻量级数据交换格式：json与jsonb

avatar 华为云开发者联盟
cover

如何基于香橙派AIpro对视频/图像数据进行预处理？

avatar 华为云开发者联盟