EasyExcel项目实例

1. 批量导出到多个工作铺

@PostMapping("download")
public void download(HttpServletResponse response,
                     @RequestParam(value = "types") String types,
                     @RequestParam(value = "status", required = false) Integer status,
                     @RequestParam(value = "startTime", required = false) String startTime,
                     @RequestParam(value = "endTime", required = false) String endTime,
                     @RequestParam(value = "userId", required = false) Integer userId) throws IOException {
                         //格式化时间
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Date startTime1 = null;
    Date endTime1 = null;
    if (startTime != null && endTime != null) {
        try {
            startTime1 = sdf.parse(startTime);
            endTime1 = sdf.parse(endTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
    }

    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    //设置背景颜色
    headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    //设置头字体
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short) 18);
    headWriteFont.setBold(true);
    headWriteCellStyle.setWriteFont(headWriteFont);
    //设置头居中
    headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    //内容策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short) 14);
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    //设置 水平居中
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("业绩评定", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xls");

    ExcelWriter excelWriter = null;
    try {
        // 这里 指定文件
        excelWriter = EasyExcel.write(response.getOutputStream())
                .excelType(XLS)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .build();
        List<String> typeList;
        if (types.equals("all")) {
            typeList = allList();
        } else {
            typeList = JSONArray.parseArray(types, String.class);
        }
        for (String type : typeList) {
            // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class 实际上可以一直变
            WriteSheet writeSheet = EasyExcel.writerSheet(CommonUtils.excelName(type)).head(demoData(type)).build();
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            excelWriter.write(data(type, status, startTime1, endTime1, userId), writeSheet);
        }
    } finally {
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
    }

}

2. 写入模板导出

@PostMapping("templateWrite")
public void templateWrite(HttpServletResponse response,
                          @RequestParam(value = "userId", required = false) Integer userId) throws IOException {
    //内容策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    //设置 水平居中
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(null, contentWriteCellStyle);

    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String templateFileName = "/static/demo.xls";
    String fileName = URLEncoder.encode("业绩总分一览表", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xls");
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    EasyExcel.write(response.getOutputStream(), AllScoresExcel.class)
            .excelType(XLS)
            .registerWriteHandler(horizontalCellStyleStrategy)
            .withTemplate(new ClassPathResource(templateFileName).getInputStream())
            .needHead(false)
            .sheet()
            .doWrite(allScoresDate(userId));
}

3. 批量导入

public RestResult<Object> tureOrFalse(MultipartFile file){
    FileInputStream inputStream=null;
    try {
        inputStream = (FileInputStream) file.getInputStream();
    } catch (IOException e) {
        e.printStackTrace();
    }
    //easyExcel默认从第二行开始读取
    //如果需要读取表头，headRowNumber(2)设置的值就是读取表头的行数
    EasyExcel.read(inputStream, ModelQuestion.class, new ModelQuestionListener(modelQuestionService)).headRowNumber(2).sheet().doRead();
    return RestResult.ok(1,"aa");
}

4. 监听器

@Slf4j
public class ModelQuestionListener extends AnalysisEventListener<ModelQuestion> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 1000;
    List<ModelQuestion> list = new ArrayList<>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private ModelQuestionService demoDAO;
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public ModelQuestionListener(ModelQuestionService demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(ModelQuestion data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        if (data.getCourseId()!=null && data.getMajorId()!=null && data.getChapter()!=null && data.getQuestion()!=null && data.getAnswer()!=null && data.getDifficulty()!=null){
            if (data.getParsing()==null){
                data.setParsing("无");
            }
            list.add(data);
        }
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.insertList(list);
        log.info("存储数据库成功！");
    }
}

5. 数据格式化

public class JsonConverter implements Converter<String> {
    @Override
    public Class supportJavaTypeKey() {
        return String.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    /**
     * 这里读的时候会调用
     *
     * @param cellData
     *            NotNull
     * @param contentProperty
     *            Nullable
     * @param globalConfiguration
     *            NotNull
     * @return
     */
    @Override
    public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                    GlobalConfiguration globalConfiguration) {
        JSONObject json = new JSONObject();
        json.put("answer",cellData.getStringValue());
        return json.toString();
    }
    /**
     * 这里是写的时候会调用 不用管
     *
     * @param value
     *            NotNull
     * @param contentProperty
     *            Nullable
     * @param globalConfiguration
     *            NotNull
     * @return
     */
    @Override
    public CellData convertToExcelData(String value, ExcelContentProperty contentProperty,
                                       GlobalConfiguration globalConfiguration) {
        return new CellData(value);
    }
}