easyexcel使用实例

https://github.com/alibaba/easyexcel场景：应业务方要求，想通过试卷id，导出试卷对应的题目，支持导出excel和word

github地址：https://github.com/alibaba/easyexcel

除了自己应用，也看到了一篇简单的简单的实例介绍文章，也介绍了easyexcel的使用

史上最全的Excel导入导出【easyexcel版】

一、引入easyexcel包

gradle中引入jar

dependencies {
    compile 'com.alibaba:easyexcel:1.0.1'
}

maven中引入jar

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

2、要上实现代码了

Controller层

@Api(tags = {"试卷Api"})
@RestController
@RequestMapping("api/v1/paper")
@Slf4j
public class PaperController {


    @Autowired
    private ExportPaperStrategyHandler handler;

    /**
     * 注:请不要格式化PaperWord.ftl文件，会格式化后会因为空格，导致图片导出失败
     */
    @ApiOperation("导出试卷excel或者word")
    @GetMapping("/export-paper")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "paperIds", value = "试卷id", dataType = "Integer", required = true),
            @ApiImplicitParam(name = "exportTypeEnum", value = "导出类型", dataType = "String", required = true)})
    public void exportPaperExcelOrWord(@RequestParam("paperIds") List<Integer> paperIds,
                                       @RequestParam("exportTypeEnum") String exportTypeEnum,
                                       HttpServletResponse response) throws IOException {
        String exportType = exportTypeEnum.toUpperCase().trim();
        ExportPaperProcessor processor = handler.getProcessor(ExportTypeEnum.valueOf(exportType));
        processor.exportPaperProcess(paperIds, ExportTypeEnum.valueOf(exportType), response);
    }
}

Handler层

@Service
public class ExportPaperStrategyHandler {

    @Autowired
    private final Map<String, ExportPaperProcessor> orderStrategyMap = new ConcurrentHashMap<>();

    public ExportPaperProcessor getProcessor(ExportTypeEnum typeEnum) {
        return orderStrategyMap.get(typeEnum.name());
    }

}

接口层

public interface ExportPaperProcessor {
    /**
     * 通过试卷id及导出类型，导出excel或word
     *
     * @param paperIds 试卷id集合
     * @param typeEnum 枚举
     * @param response 返回流
     * @throws IOException IO异常
     */
    void exportPaperProcess(List<Integer> paperIds, ExportTypeEnum typeEnum, HttpServletResponse response) throws IOException;

}

 导出Excel实现

@Component("EXCEL")
public class ExportPaperExcelProcessor implements ExportPaperProcessor {

    @Autowired
    private PaperService paperService;

    @Override
    public void exportPaperProcess(List<Integer> paperIds, ExportTypeEnum typeEnum, HttpServletResponse response) throws IOException {
        List<ExcelPropertyPaperModel> data = paperService.exportPaperExcel(paperIds);
        ExcelWriter writer = new ExcelWriter(PaperExcelWrite.getOutputStream("PaperExcel", response), ExcelTypeEnum.XLSX);
        Sheet sheetData = new Sheet(1, 0, ExcelPropertyPaperModel.class);
        sheetData.setSheetName("PaperExcel");
        writer.write(data, sheetData);
        writer.finish();
    }
}

导出Word实现

@Component("WORD")
public class ExportPaperWordProcessor implements ExportPaperProcessor {

    @Autowired
    private PaperService paperService;

    @Override
    public void exportPaperProcess(List<Integer> paperIds, ExportTypeEnum typeEnum, HttpServletResponse response) {
        List<ExcelPropertyPaperModel> excelPropertyPaperModels = paperService.exportPaperExcel(paperIds);
        List<ExcelPropertyPaperModel> list = new ArrayList<>();
        for (ExcelPropertyPaperModel excelPropertyPaperModel : excelPropertyPaperModels) {
            ExcelPropertyPaperModel model = new ExcelPropertyPaperModel();
            model.setPaperId(excelPropertyPaperModel.getPaperId());
            model.setItemId(excelPropertyPaperModel.getItemId());
            model.setType(NullCheck.get(excelPropertyPaperModel.getType()));
            model.setRank(NullCheck.get(excelPropertyPaperModel.getRank()));
            model.setTitle(Jsoup.parse(NullCheck.get(excelPropertyPaperModel.getTitle())).text());
            model.setAnswer(NullCheck.get(excelPropertyPaperModel.getAnswer()));
            model.setSelect(Jsoup.parse(NullCheck.get(excelPropertyPaperModel.getSelect())).text());
            model.setAnalysis(Jsoup.parse(NullCheck.get(excelPropertyPaperModel.getAnalysis())).text());
            model.setKnowledgePoint(NullCheck.get(excelPropertyPaperModel.getKnowledgePoint()));
            model.setImageTitles(getImageSrc(Jsoup.clean(NullCheck.get(excelPropertyPaperModel.getTitle()), Whitelist.relaxed())));
            model.setImageAnalysis(getImageSrc(Jsoup.clean(NullCheck.get(excelPropertyPaperModel.getAnalysis()), Whitelist.relaxed())));
            list.add(model);
        }
        ExportPaperWordUtil ewUtil = new ExportPaperWordUtil();
        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("list", list);
        ewUtil.exportWordBatch(dataMap, "PaperWord", "PaperWord.ftl", response);

    }


    /**
     * 获取获取图片Base64字符串
     *
     * @param clean 题目标签字符串
     * @return 图片Base64字符串
     */
    public List<String> getImageSrc(String clean) {
        List<String> listSrc = new ArrayList<>();
        Document doc = Jsoup.parse(clean);
        // 获取带有src属性的img元素
        Elements imgTags = doc.select("img[src]");
        for (Element element : imgTags) {
            // 获取src的绝对路径
            String src = element.attr("src");
            // 根据src获取图片
            String imageBase64StrFromUrl = WebFileUtils.getImageBase64StrFromUrl(src);
            listSrc.add(imageBase64StrFromUrl != null ? imageBase64StrFromUrl : "");
        }
        return listSrc;
    }
}

wordUtil  压缩包的类

@Slf4j
public class ExportPaperWordUtil {
    private Configuration config;

    public ExportPaperWordUtil() {
        config = new Configuration(DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
        config.setDefaultEncoding("utf-8");
        config.setOutputFormat(XMLOutputFormat.INSTANCE);
    }

    /**
     * 压缩包方式导出多个word
     * 由于一次请求浏览器只能响应一次，想导出多个必须打包，亲测for循环导出只能导一个
     * 如果想做到分别单独下载，那就得用插件啦，这里不提供插件的做法
     * 思路：生成临时目录-在临时目录生成word-将临时目录打zip包-zip文件下载-删除临时目录和zip包，
     * 回收系统资源
     */
    public void exportWordBatch(Map<String, Object> map, String title, String templateName,
                                HttpServletResponse response) {
        File zipFile = null;
        File directory = null;
        InputStream fin = null;
        ServletOutputStream out = null;
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/octet-stream");
        response.addHeader("Content-Disposition", "attachment;filename=" + System.currentTimeMillis() + ".zip");
        //加载模板(路径)数据，也可使用setServletContextForTemplateLoading()方法放入到web文件夹下
        config.setClassForTemplateLoading(this.getClass(), "/templates");
        //设置异常处理器 这样的话 即使没有属性也不会出错 如：${list.name}...不会报错
        config.setTemplateExceptionHandler(TemplateExceptionHandler.IGNORE_HANDLER);
        try {
            if (templateName.endsWith(ConstantConfig.PATH_TYPE)) {
                templateName = templateName.substring(0, templateName.indexOf(ConstantConfig.PATH_TYPE));
            }
            Template freemarkerTemplate = config.getTemplate(templateName + ConstantConfig.PATH_TYPE);
            out = response.getOutputStream();
            // 根据当前时间创建临时目录
            String path = this.getClass().getResource("/").getPath() + "resources/word/" + System.currentTimeMillis();
            directory = new File(path);
            directory = new File(path);
            directory.mkdirs();
            // 调用工具类的createDoc方法在临时目录下生成Word文档
            createDoc(map, freemarkerTemplate, directory.getPath() + "/" + title + ".doc");
            //压缩目录
            ZipUtil.createZip(path, path + "zip.zip");
            // 根据路径获取刚生成的zip包文件
            zipFile = new File(path + "zip.zip");
            fin = new FileInputStream(zipFile);
            // 缓冲区
            byte[] buffer = new byte[1024];
            int bytesToRead = -1;
            // 通过循环将读入的Word文件的内容输出到浏览器中
            while ((bytesToRead = fin.read(buffer)) != -1) {
                out.write(buffer, 0, bytesToRead);
            }
            response.flushBuffer();
        } catch (Exception e) {
            log.error("exportWordBatch", e);
        } finally {
            try {
                if (fin != null) {
                    fin.close();
                }
                if (out != null) {
                    out.close();
                }
                if (zipFile != null) {
                    zipFile.delete();
                }
                if (directory != null) {
                    //递归删除目录及目录下文件
                    ZipUtil.deleteFile(directory);
                }
            } catch (Exception e2) {
                log.error("exportWordBatch", e2);
            }

        }
    }

    /**
     * 生成word文档
     *
     * @param dataMap  参数
     * @param template 模版
     * @param filename 文件名称
     * @return 文件对象
     */
    private File createDoc(Map<?, ?> dataMap, Template template, String filename) {
        File file = new File(filename);
        Template templateData = template;
        Writer writer = null;
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(file);
            writer = new OutputStreamWriter(fos, UTF_8);
            templateData.process(dataMap, writer);
        } catch (Exception ex) {
            log.error("createDoc", ex);
        } finally {
            try {
                fos.close();
                writer.close();
            } catch (Exception e) {
                log.error("createDoc", e);
            }

        }
        return file;
    }

}

实体model，定义excel导出的表头

@EqualsAndHashCode(callSuper = true)
@Data
@JsonFilter("excelPropertyPaperModelFilter")
public class ExcelPropertyPaperModel extends BaseRowModel {
    private Paper paper;
    private Item item;
    private PaperItem paperItem;
    private List<ItemSelect> itemSelects;
    private List<String> knowledgePoints;
    @ExcelProperty(value = "试卷id", index = 0)
    private String paperId;
    @ExcelProperty(value = "题目id", index = 1)
    private String itemId;
    @ExcelProperty(value = "类型", index = 2)
    private String type;
    @ExcelProperty(value = "难度", index = 3)
    private String rank;
    @ExcelProperty(value = "题干", index = 4)
    private String title;
    @ExcelProperty(value = "正确答案", index = 5)
    private String answer;
    @ExcelProperty(value = "选项", index = 6)
    private String select;
    @ExcelProperty(value = "解析", index = 7)
    private String analysis;
    @ExcelProperty(value = "知识点标签", index = 8)
    private String knowledgePoint;
    @ExcelProperty(value = "题干图片", index = 9)
    private List<String> imageTitles;
    @ExcelProperty(value = "解析图片", index = 10)
    private List<String> imageAnalysis;

    public void fillInAll() {
        this.paperId = paper.getId().toString();
        this.itemId = item.getId().toString();
        this.type = ItemTypeEnum.getItemType(item.getType());
        this.rank = item.getRank() == 1 ? "难" : (item.getRank() == 2 ? "中" : "易");
        this.title = item.getTitle();
        this.answer = item.getAnswer();
        this.analysis = item.getAnalysis();
        this.select = StringUtils.join(getItemSelect(itemSelects), "/");
        this.knowledgePoint = StringUtils.join(knowledgePoints, "/");
    }

    public List<String> getItemSelect(List<ItemSelect> itemSelects) {
        List<String> list = new ArrayList<>();
        if (itemSelects.isEmpty()) {
            return list;
        }
        for (ItemSelect itemSelect : itemSelects) {
            String itemSelectData = itemSelect.getOption() + "、" + itemSelect.getItemOption();
            list.add(itemSelectData);
        }
        return list;
    }

}