在java中使用alibaba提供的easyexcel方法导出数据到excel文档

使用alibaba提供的easyexcel方法导出数据到excel文档

---

---

前言

该方式适用于前端页面点击导出然后将数据导出成excel文件的场景
依据导出数据实体类来自动导出我们想要的字段数据

---

一、EasyExcel 是什么？

EasyExcel 是一个基于 Java 的简单、省内存的读写 Excel 的开源项目。
　　在尽可能节约内存的情况下支持读写百 M 的 Excel（没有一次性将数据读取到内存中，从磁盘中一行行读取，从而节约内存）。
　　EasyExcel 采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）。
简单地理解：可以方便、快速的读取、写入 execl 内容。

【官方文档地址：】
https://alibaba-easyexcel.github.io/index.html
https://www.yuque.com/easyexcel

二、使用步骤

1.引入库

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

2.输出的实体类（表头）

    @Data
    public class Resource {
        @ExcelProperty(value = "id", index = 0)
        private Integer id;
        @ExcelProperty(value = "资源名称", index = 1)
        private long name;
        @ExcelProperty(value = "资源时间", index = 2)
        private String displayTime;
        @ExcelProperty(value = "总量", index = 3)
        private Integer count;
        
        /**
         * 打印数据为：1 或 2 需要转换一下。
         * 1-需要
         * 2-不需要
         */
        @ExcelProperty(value = "是否需要打印", index = 4,converter =StatusConverter.class)
        private String collectStatus;
        
        /**
         * 该字段无需导出
         */
        @ExcelIgnore
        private String orgCode;
        
    }

---

3.字段转义方法CollectStatusConverter（按需使用）

public class CollectStatusConverter implements Converter<String> {

    @Override
    public Class<String> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return "1".equals(cellData.getStringValue()) ? "1" : "2";
    }

    @Override
    public WriteCellData<?> convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return new WriteCellData<>(value.equals("1") ? "需要" :"不需要");
    }
}

4.定义通用"导出方法的"ExportService

public interface ExportService {

    String type();

    String fileName();

    void doExport(Map<String, String> params, HttpServletResponse response) throws IOException;
}

5.定义其中导出方法的ResourcesListServiceImpl

@Component
public class ResourcesListServiceImpl implements ExportService {

    @Resource
    private UserService userService;
    @Resource
    private HttpServletRequest request;
    
    @Override
    public String type() {
        return "preType";
    }

    @Override
    public String fileName() {
        try {
            return URLEncoder.encode("用户资源报表.xlsx", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void doExport(Map<String, String> params) {
        //将map转化为实体类
        CollectSearcher searcher = JSON.parseObject(JSON.toJSONString(params), CollectSearcher.class);
        //查询user表中具体的信息
        List<CatalogBasicInfo> users = userService.getUserList(searcher);
        //写出
        ExportUtil.write(response, Resource.class, users);
    }
}

6.导出工具栏ExportUtil

public class ExportUtil {
    public static void write(HttpServletResponse response, Class clazz, List data) {
        try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            //调用alibabab提高的EasyExcel工具类
            EasyExcel.write(baos, clazz).sheet().doWrite(data);
            response.setContentLength(baos.size());
            try (ServletOutputStream outputStream = response.getOutputStream()) {
                baos.writeTo(outputStream);
            } catch (IOException e) {
                // 处理输出流的异常
                throw new BizException(ResultEnum.ERROR.getCode(), "写入响应流失败");
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new BizException(ResultEnum.ERROR.getCode(), "写入文件失败");
        }
    }
}

7.定义初始化加载导出资源ExportFactory

@Component
public class ExportFactory implements ApplicationContextAware {
    private Map<String, ExportService> factory = new HashMap<>();

    private ApplicationContext applicationContext;

    @PostConstruct
    public void init() {
        Map<String, ExportService> beans = applicationContext.getBeansOfType(ExportService.class);
        beans.forEach((k, v) -> {
            factory.put(v.type(), v);
        });
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext = applicationContext;
    }

    public ExportService getInstance(String type){
        return factory.get(type);
    }
}

8.实现导出Controller

@RestController
@RequestMapping("/user")
public class UserController {

/**
     * 数据导出
     * @param params:
     *       exportType:preType(标识字段)
     *       name:小红（若有筛选项，则传）
     * @param response
     */
    @RequestMapping("downloadResources")
    public void downloadResources(@RequestParam Map<String, String> params,  HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //params.get("exportType")为"preType"
        ExportService service = exportFactory.getInstance((String.valueOf(params.get("exportType"))));
        //service.fileName()为'用户资源报表.xlsx'
        response.setHeader("Content-disposition", "attachment;filename=" + service.fileName());
        service.doExport(params, response);
    }
    }

9.导出结果