easyExcle 使用用例

入口controller:

@PostMapping("/{examId}/export")
public void export(@PathVariable Long examId, HttpServletResponse response){
    try{
        List<ExamUserAnswerResponse> examUserAnswerResponseList = Lists.newArrayList();
        int page = 1;
        while(true){
            Pageable pageable = PageRequest.of(page - 1, CommConstants.EXPORT_PAGE_SIZE, Sort.by(Sort.Direction.DESC, "updatedAt"));
            Page<ExamUserAnswerResponse> list = examUserAnswerService.list(CurrentUser.getUserId(), examId, null, null, pageable);
            if(list.hasContent()){
                examUserAnswerResponseList.addAll(list.getContent());
                page++;
            }else{
                break;
            }
        }

        List<ReviewListExportRespose> reviewListExportResposeList = Lists.newArrayList();
        for(ExamUserAnswerResponse examUserAnswerResponse : examUserAnswerResponseList){
            ReviewListExportRespose reviewListExportRespose = new ReviewListExportRespose();
            reviewListExportRespose.setTitle(examUserAnswerResponse.getTitle());
            if(examUserAnswerResponse.getIsPass() == null){
                reviewListExportRespose.setIsPass("");
            }else if(examUserAnswerResponse.getIsPass().equals(0)){
                reviewListExportRespose.setIsPass(CommConstants.EXAM_NO_PASS_WORD);
            }else{
                reviewListExportRespose.setIsPass(CommConstants.EXAM_PASS_WORD);
            }
            reviewListExportRespose.setUserName(examUserAnswerResponse.getUserName());
            reviewListExportRespose.setOrgName(examUserAnswerResponse.getOrgName());
            reviewListExportRespose.setAnswerTime(examUserAnswerResponse.getAnswerTime()== null?"":DateUtils.dateToStr(examUserAnswerResponse.getAnswerTime(),null));
            reviewListExportRespose.setExamTime(examUserAnswerResponse.getExamTime()==null?"":String.valueOf(examUserAnswerResponse.getExamTime()));
            reviewListExportRespose.setScore(examUserAnswerResponse.getScore() == null?"":examUserAnswerResponse.getScore().toString());
            reviewListExportRespose.setObjectiveScore(examUserAnswerResponse.getObjectiveScore()== null?"":examUserAnswerResponse.getObjectiveScore().toString());
            reviewListExportRespose.setSubjectiveScore(examUserAnswerResponse.getSubjectiveScore()== null?"":examUserAnswerResponse.getSubjectiveScore().toString());
            reviewListExportRespose.setAnswerQuestionCount(String.valueOf(examUserAnswerResponse.getAnswerQuestionCount()));
            reviewListExportRespose.setAnswerQuestionRate(String.valueOf((int)examUserAnswerResponse.getAnswerQuestionRate())+"%");
            reviewListExportRespose.setCorrectRate(examUserAnswerResponse.getCorrectRate() ==null?"":String.valueOf(examUserAnswerResponse.getCorrectRate().intValue())+"%");
            reviewListExportResposeList.add(reviewListExportRespose);
        }

        log.info("查询到的导出列表数据:{}", reviewListExportResposeList.toString());
        EasyExcelUtils.writeExcel(response,reviewListExportResposeList,new StringBuilder(CommConstants.EXPORT_EXAM_FILE_NAME).append(System.currentTimeMillis()).toString(),
                CommConstants.EXPORT_EXAM_FILE_NAME,
                new ReviewListExportRespose());
    } catch (Exception e) {
        e.printStackTrace();
        log.error(e.toString(), e);
    }
}

工具类：EasyExcelUtils.java

package com.gaodun.mcloud.mtksvc.utils;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;

/**
 * @ClassName EasyExcelUtils
 * @Author laixiaoxing
 * @Date 2019/1/30 下午11:59
 * @Description 封装的EasyExcel导出工具类
 * @Version 1.0
 */
@Slf4j
public class EasyExcelUtils {

    /**
     * @Author laixiaoxing
     * @Description 导出excel 支持一张表导出多个sheet
     * @Param OutputStream 输出流
     * Map<String, List>  sheetName和每个sheet的数据
     * ExcelTypeEnum 要导出的excel的类型 有ExcelTypeEnum.xls 和有ExcelTypeEnum.xlsx
     * @Date 上午12:16 2019/1/31
     */
    public static void createExcelStreamMutilByEaysExcel(HttpServletResponse response, Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelTypeEnum type) throws UnsupportedEncodingException {
        if (checkParam(SheetNameAndDateList, type)) return;
        try {
            // response.setContentType("multipart/form-data");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + "default" + type.getValue());
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, type, true);
            setSheet(SheetNameAndDateList, writer);
            writer.finish();
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出 Excel ：一个 sheet，带表头
     *
     * @param response  HttpServletResponse
     * @param list      数据 list，每个元素为一个 BaseRowModel
     * @param sheetName 导入文件的 sheet 名
     * @param object    映射实体类，Excel 模型
     */
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel object) throws Exception {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");//.xlsx格式
        response.setHeader("Content-Disposition", "attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xlsx");
        ExcelWriter writer = new ExcelWriter(getOutputStream(response), ExcelTypeEnum.XLSX);
        Sheet sheet = new Sheet(1, 0, object.getClass());
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();

    }

    /**
     * 导出文件时为Writer生成OutputStream
     */
    private static OutputStream getOutputStream(HttpServletResponse response) {
        try {
            return response.getOutputStream();
        } catch (IOException e) {
            throw new RuntimeException("导出文件时为Writer生成OutputStream失败！");
        }
    }


    /**
     * @Author laixiaoxing
     * @Description //setSheet数据
     * @Date 上午12:39 2019/1/31
     */
    private static void setSheet(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelWriter writer) {
        int sheetNum = 1;
        for (Map.Entry<String, List<? extends BaseRowModel>> stringListEntry : SheetNameAndDateList.entrySet()) {
            Sheet sheet = new Sheet(sheetNum, 0, stringListEntry.getValue().get(0).getClass());
            sheet.setSheetName(stringListEntry.getKey());
            writer.write(stringListEntry.getValue(), sheet);
            sheetNum++;

        }
    }


    /**
     * @Author laixiaoxing
     * @Description 校验参数
     * @Date 上午12:39 2019/1/31
     */
    private static boolean checkParam(Map<String, List<? extends BaseRowModel>> SheetNameAndDateList, ExcelTypeEnum type) {
        if (CollectionUtils.isEmpty(SheetNameAndDateList)) {
            log.error("SheetNameAndDateList不能为空");
            return true;
        } else if (type == null) {
            log.error("导出的excel类型不能为空");
            return true;
        }
        return false;
    }
}

返回Response：ReviewListExportRespose.java

package com.gaodun.mcloud.mtksvc.response;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

import java.io.Serializable;

@Data
public class ReviewListExportRespose extends BaseRowModel implements Serializable {

    private static final long serialVersionUID = -7767136607492109153L;

    @ExcelProperty(value = "考试名称",index = 0)
    private String title;

    @ExcelProperty(value = "考试结果",index = 1)
    private String isPass;

    @ExcelProperty(value ="考试人",index = 2)
    private String userName;

    @ExcelProperty(value ="所在部门",index = 3)
    private String orgName;

    @ExcelProperty(value ="进入试卷时间",index =4)
    private String answerTime;

    @ExcelProperty(value ="考试用时(分钟)",index =5)
    private String examTime;

    @ExcelProperty(value ="考试总分",index =6)
    private String score;

    @ExcelProperty(value = "客观题得分",index =7)
    private String objectiveScore;

    @ExcelProperty(value = "主观题得分",index =8)
    private String subjectiveScore;

    @ExcelProperty(value = "答题数",index =9)
    private String answerQuestionCount;

    @ExcelProperty(value = "答题率",index =10)
    private String answerQuestionRate;

    @ExcelProperty(value = "客观题正确率",index =11)
    private String correctRate;

}