EasyPoi Excel简单导出导入

EasyPoi官方文档: http://easypoi.mydoc.io/.

一 导出

1 引入依赖

<dependency>
   <!-- easypoi导出 -->
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.3.0</version>
</dependency>

<dependency>
    <!-- Hutool是一个Java工具包 -->
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>${hutool.version}</version>
</dependency>

2 新建Excel导出工具类

此处使用sa-token示例项目下工具类EasyExcelUtil
sa-token官方文档: http://sa-token.dev33.cn/doc/index.html#/.

public class EasyExcelUtil {

    private static final String HSSF = ".xls";
    private static final String XSSF = ".xlsx";

    /**
     * 注解导出
     *
     * @param dataList 数据
     * @param aClass   类对象
     * @param params   excel参数
     * @param fileName 文件名称
     * @param modelMap
     * @param request
     * @param response
     */
    public static void normalExcel(List<?> dataList,
                                   Class<?> aClass,
                                   ExportParams params,
                                   String fileName,
                                   ModelMap modelMap,
                                   HttpServletRequest request,
                                   HttpServletResponse response) {
        modelMap.put(NormalExcelConstants.DATA_LIST, dataList);
        modelMap.put(NormalExcelConstants.CLASS, aClass);
        modelMap.put(NormalExcelConstants.PARAMS, params);
        modelMap.put(NormalExcelConstants.FILE_NAME, fileName);
        PoiBaseView.render(modelMap, request, response, NormalExcelConstants.EASYPOI_EXCEL_VIEW);
    }

    /**
     * 注解导出
     *
     * @param dataList 数据
     * @param aClass   类对象
     * @param params   excel参数
     * @param fileName 文件名称
     * @param response
     */
    @Deprecated
    public static void normalExcel(List<?> dataList,
                                   Class<?> aClass, ExportParams params,
                                   String fileName, HttpServletResponse response) {

        try {
            Workbook workbook = ExcelExportUtil.exportExcel(params, aClass, dataList);
            if (workbook instanceof HSSFWorkbook) {
                fileName += HSSF;
            } else {
                fileName += XSSF;
            }
            ServletOutputStream outputStream = response.getOutputStream();
            response.setHeader(HttpHeaders.CONTENT_DISPOSITION, StrUtil.format("attachment;filename={}", URLUtil.encode(fileName, CharsetUtil.UTF_8)));
            response.setContentType(CharsetUtil.UTF_8);
            workbook.write(outputStream);
            IoUtil.close(outputStream);
            IoUtil.close(workbook);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 模板导出
     *
     * @param map
     * @param params
     * @param fileName
     * @param modelMap
     * @param request
     * @param response
     */
    public static void templateExcel(Map<String, Object> map, TemplateExportParams params, String fileName, ModelMap modelMap, HttpServletRequest request,
                                     HttpServletResponse response) {
        modelMap.put(TemplateExcelConstants.FILE_NAME, fileName);
        modelMap.put(TemplateExcelConstants.PARAMS, params);
        modelMap.put(TemplateExcelConstants.MAP_DATA, map);
        PoiBaseView.render(modelMap, request, response,
                TemplateExcelConstants.EASYPOI_TEMPLATE_EXCEL_VIEW);
    }

    /**
     * map 导出
     *
     * @param list
     * @param entity
     * @param params
     * @param fileName
     * @param modelMap
     * @param request
     * @param response
     */
    public static void mapExcel(List<Map<String, Object>> list, List<ExcelExportEntity> entity, ExportParams params, String fileName, ModelMap modelMap, HttpServletRequest request,
                                HttpServletResponse response) {
        modelMap.put(MapExcelConstants.MAP_LIST, list);
        modelMap.put(MapExcelConstants.ENTITY_LIST, entity);
        modelMap.put(MapExcelConstants.PARAMS, params);
        modelMap.put(MapExcelConstants.FILE_NAME, fileName);
        PoiBaseView.render(modelMap, request, response, MapExcelConstants.EASYPOI_MAP_EXCEL_VIEW);
    }

    /**
     * 大数据导出
     * <p>http://doc.wupaas.com/docs/easypoi/easypoi-1c10lbsojh62f</p>
     *
     * @param aClass
     * @param params
     * @param dataParams
     * @param excelExportServer
     * @param modelMap
     * @param request
     * @param response
     */
    public static void bigExcel(Class<?> aClass, ExportParams params, Map<String, Object> dataParams, IExcelExportServer excelExportServer, ModelMap modelMap, HttpServletRequest request,
                                HttpServletResponse response) {
        modelMap.put(BigExcelConstants.CLASS, aClass);
        modelMap.put(BigExcelConstants.PARAMS, params);
        //就是我们的查询参数,会带到接口中,供接口查询使用
        modelMap.put(BigExcelConstants.DATA_PARAMS, dataParams);
        modelMap.put(BigExcelConstants.DATA_INTER, excelExportServer);
        PoiBaseView.render(modelMap, request, response, BigExcelConstants.EASYPOI_BIG_EXCEL_VIEW);
    }
}

3 新建导出实体类

项目需要引入lombok，编译器需安装lombok插件，不使用@Data标签可忽略。

@Data
public class SwordOutputExcel {
    @Excel(orderNum = "0", name = "id", width = 36)
    private String id;
    @Excel(orderNum = "1", name = "名称", width = 30)
    private String name;
    @Excel(orderNum = "2", name = "属性", width = 30, replace = {"巨剑_0", "太刀_1", "光剑_2", "左轮_3"})
    private String type;
}

4 自定义样式设置

新建样式类EasyExcelStyle继承默认样式类ExcelExportStylerDefaultImpl

public class EasyExcelStyle extends ExcelExportStylerDefaultImpl {
    public EasyExcelStyle(Workbook workbook) {
        super(workbook);
    }

    /**
     * 标题样式
     * @param color
     * @return
     */
    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
//        titleStyle.setFont(getFont(workbook, (short) 11, false));
        // 背景色
        titleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return titleStyle;
    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    /**
     * 列表头样式
     * @param color
     * @return
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return titleStyle;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        // 字体样式
        font.setFontName("宋体");
        // 是否加粗
        font.setBold(isBold);
        // 字体大小
        font.setFontHeightInPoints(size);
        return font;
    }
}

新建样式枚举类

public enum EasyExcelStyleType {
    NONE("默认样式", ExcelExportStylerDefaultImpl.class),
    ONE("自定义样式一", EasyExcelStyle.class);

    EasyExcelStyleType(String str, Class<?> cla) {
        this.str = str;
        this.cla = cla;
    }

    private String str;
    private Class<?> cla;

    public String getStr() {
        return str;
    }

    public void setStr(String str) {
        this.str = str;
    }

    public Class<?> getCla() {
        return cla;
    }

    public void setCla(Class<?> cla) {
        this.cla = cla;
    }
}

5 控制器

	@GetMapping("/exportExcel")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        // 要导出数据
        SwordOutputExcel s1 = new SwordOutputExcel();
        s1.setId(IdUtil.simpleUUID());
        s1.setName("紫芸双影剑");
        s1.setType("0");
        SwordOutputExcel s2 = new SwordOutputExcel();
        s2.setId(IdUtil.simpleUUID());
        s2.setName("细雪之舞");
        s2.setType("1");
        List<SwordOutputExcel> list = new ArrayList<>();
        list.add(s1);
        list.add(s2);
        ModelMap modelMap = new ModelMap();
        ExportParams exportParams = new ExportParams();
        // 自定义样式设置
        exportParams.setStyle(EasyExcelStyleType.ONE.getCla());
        EasyExcelUtil.normalExcel(list, SwordOutputExcel.class, exportParams, "武器" + Instant.now().getEpochSecond(), modelMap, request, response);
    }

6 前端发送请求

<h3>导出Excel</h3>
<a href="/customUser/exportExcel">导出</a>

7 测试结果

二 导入

1 新建导入类

主类

@Data
public class SwordImportEntity {
    @Excel(name = "id")
    private String id;
    @Excel(name = "名称")
    private String name;
    @Excel(name = "类型", replace = {"巨剑_0", "太刀_1", "光剑_2", "左轮_3"})
    private String type;
    @ExcelCollection(name = "拥有玩家")
    private List<SwordUserEntity> userEntityList;
}

子类

@Data
public class SwordUserEntity {
    @Excel(name = "玩家昵称")
    private String userName;
    @Excel(name = "玩家职业", replace = {"剑魂_0", "漫游_1", "散打_2"})
    private String occupation;
    @Excel(name = "创建时间")
    private Date createTime;
}

2 导入Excel表格

表格

3 控制器

	@PostMapping("/importExcel")
    @ResponseBody
    public Map<String, Object> importUser(@RequestParam("uploadFile") MultipartFile multipartFile) {
        Map<String, Object> map = new HashMap<>(2);
        ImportParams params = new ImportParams();
        /**
         * 这里需要注意表头的行数设置一定要正确！否则集合数据将无法读取，
         * 可以通过WPS或者office查看实际表头所占用的行数，
         * 一定要区分表头与标题的区别，表头是列名称，标题是表头上面的文字，
         * 本文示例文件中没有标题，所以setTitleRows为0
         */
        // 设置表头行数
        params.setHeadRows(2);
        // 标题行设置为0行，默认是0，可以不设置
        params.setTitleRows(0);
        try {
            List<SwordImportEntity> result = ExcelImportUtil.importExcel(multipartFile.getInputStream(), SwordImportEntity.class, params);
            for (SwordImportEntity t : result) {
                // 自定义数据校验略
                // 打印数据
                System.out.println(t);
            }
            map.put("code", 200);
            map.put("msg", "导入成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

4 前端发送请求

<h3>导入Excel</h3>
<input id="fileId" type="file" name="uploadFile" value="请选择文件">
<a onclick="uploadFile();" style="cursor: pointer; display: inline-block;background-color: aqua">导入</a>

function uploadFile() {
    let fileobj = $("#fileId")[0].files[0];
    console.log(fileobj);
    let form = new FormData();
    form.append("uploadFile", fileobj);
    $.ajax({
        type: 'POST',
        url: '/customUser/importExcel',
        data: form,
        // 告诉jquery要传输data对象
        processData: false,
        // 告诉jquery不需要增加请求头对于contentType的设置
        contentType: false,
        success: function (arg) {
            console.log(arg)
        }
    })
}

5 测试结果