使用easy poi快速导入导出

简介
easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导入,导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法。

集成
pom 中引入依赖即可

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

整合工具类 EasyPoiUtil

package cn.common.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.common.exception.ZXException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author huangy
 * @date 2019/6/28 14:57
 */
public class EasyPoiUtil {
       /**
        * 导出Excel，包括文件名以及表名。是否创建表头
        *
        * @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 默认格式 默认有创建表头
     */
    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多sheet形式导出
     * @param list
     * @param fileName
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    /**
     * 常规默认导出方式
     * @param list
     * @param pojoClass
     * @param fileName
     * @param response
     * @param exportParams
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        ExcelExportUtil.closeExportBigExcel();
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 多sheet默认导出方式
     * @param list
     * @param fileName
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        ExcelExportUtil.closeExportBigExcel();
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载excel
     * @param fileName
     * @param response
     * @param workbook
     */
    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 ZXException(e.getMessage());
        }
    }

    /**
     * 导入 文件路径形式
     * @param filePath
     * @param titleRows
     * @param headerRows
     * @param pojoClass
     * @param <T>
     * @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 ZXException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new ZXException(e.getMessage());
        }
        return list;
    }

    /**
     * 导入 MultipartFile 形式
     * @param file
     * @param titleRows
     * @param headerRows
     * @param pojoClass
     * @param <T>
     * @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 ZXException("excel文件不能为空");
        } catch (Exception e) {
            throw new ZXException(e.getMessage());
        }
        return list;
    }

}

使用示例
实体类

public class BlackListExport {
    @Excel(name = "客户姓名", width = 15, orderNum = "2")
    private String name;
    @Excel(name = "备注", width = 10, orderNum = "1")
    private String remark;
    @Excel(name = "手机号", width = 15, orderNum = "0")
    private String phone;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getRemark() {
        return remark;
    }

    public void setRemark(String remark) {
        this.remark = remark;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public BlackListExport() {
    }

    public BlackListExport(String name, String remark, String phone) {
        this.name = name;
        this.remark = remark;
        this.phone = phone;
    }
}

接口

    @ApiOperation(value = "easyPoiUtil 导出测试")
    @GetMapping(value = "/poi/export1")
    public void export1(HttpServletResponse response){
        List<BlackListExport> list=new ArrayList<>();
        for(int i=0;i<10000;i++){
            list.add(new BlackListExport(i+"",i+"",i+""));
        }
        EasyPoiUtil.exportExcel(list,"zx","huangy",BlackListExport.class,"zx.xls",response);
    }

    /**
     * 如果填充不同sheet得data数据列表使用相同得list对象进行填充的话，
     * 会出现第一次填充得sheet有数据，后续其他使用相同list对象进行data填充得sheet没有数据展示。
     * @param response
     */
    @ApiOperation(value = "多sheet 导出测试")
    @GetMapping(value = "/poi/export2")
    public void export2(HttpServletResponse response){
        // 查询数据,此处省略
        List list = new ArrayList<>();
        list.add(new BlackListExport("姓名1","备注1","手机1")) ;
        list.add(new BlackListExport("姓名2","备注2","手机2")) ;
        list.add(new BlackListExport("姓名3","备注3","手机3")) ;
        List list2 = new ArrayList<>();
        list2.add(new BlackListExport("姓名-1","备注-1","手机-1")) ;
        list2.add(new BlackListExport("姓名-2","备注-2","手机-2")) ;
        list2.add(new BlackListExport("姓名-3","备注-3","手机-3")) ;
        List<Map<String,Object>> sheetsList = new ArrayList<>();
        for(int i=1;i<=4;i++){
        // 设置导出配置
        // 创建参数对象（用来设定excel得sheet得内容等信息）
        ExportParams params = new ExportParams() ;
        // 设置sheet得名称
        params.setSheetName("表格"+i);

        //创建sheet使用的map
        Map<String,Object> dataMap = new HashMap<>();
        // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
        dataMap.put("title",params) ;
        // 模版导出对应得实体类型
        dataMap.put("entity",BlackListExport.class) ;
        // sheet中要填充得数据
       if(i%2==0){
           dataMap.put("data",list) ;
       }else {
           dataMap.put("data",list2) ;
       }

        sheetsList.add(dataMap);
       }
        EasyPoiUtil.exportExcel(sheetsList,"hy.xls",response);
    }