导入题库示例

/**
 * 导入题库
 *
 * @param req
 * @return
 */
 
@SuppressWarnings({"rawtypes", "unchecked"})
@RequestMapping(value = "/insertQuestion", method = {RequestMethod.POST,
        RequestMethod.GET}, produces = MediaType.APPLICATION_JSON_VALUE)
public ResultTmplateMsg insertQuestion(String comCode, HttpServletRequest req,
                                       HttpServletResponse res) throws Exception {
    long startTime = 0;// 导入起始时间
    startTime = System.currentTimeMillis();
    comCode = "2b4e70fd559f4a36af22972a73fcbb4d";
    ResultTmplateMsg resultMsg = null;
    List<String> msg = insertQuestion(comCode, req);
    log.info("用时==={}", System.currentTimeMillis() - startTime);
    log.info("msg=={}", msg);
    if (msg.get(msg.size() - 1).equals("0")) {
        msg.remove((msg.size() - 1));
        String result = String.valueOf(msg);
        return ResultTmplateMsg.commResultMsg(0, result);
    }
    String result = msg.toString();
    return ResultTmplateMsg.commResultMsg(1, result);
}

public List<String> insertQuestion(String comCode, HttpServletRequest req) throws IOException {

    List<String> msg = new ArrayList<String>();
    Map<String, Object> errMap = new HashMap<String, Object>();// 存放错误信息
    errMap.put("action", "导入");
    String path = "D:\\filepath\\" + "题库.xlsx";
    Boolean aBoolean = FileUtil.saveHttpFile(req, path);
    if (!aBoolean) {
        String message = "导入异常";
        msg.add(message);
        errMap.put("errMessage", message);
        return msg;
    }
    int total = 0;
        try {
            InputStream inputStream = new FileInputStream(path);
            ExcelReader build = EasyExcel.read(inputStream)
                    .build();
            List<ReadSheet> readSheets = build.excelExecutor()
                    .sheetList();
            log.info(String.valueOf(readSheets));
        } catch (Exception e) {
            String message = "导入异常";
            msg.add(message);
            errMap.put("errMessage", message);
            return msg;
        }
        // 读取导入数据
        List<TemplateEntity> newList = readerMultilist(new File(path));
        log.info("newList=====>{}>", newList);
        int count = 0;
        for (TemplateEntity templateEntity : newList) {
            if (UtilsHelper.isNotEmpty(templateEntity)) {
                count++;
                if (UtilsHelper.isEmpty(templateEntity.getQuestionType())) {
                    String message = "第" + count + "行试题类型为空,上传失败";
                    msg.add(message);
                    continue;
                }
                if (UtilsHelper.isEmpty(templateEntity.getQuestionClass())) {
                    String message = "第" + count + "行试题类别为空,上传失败";
                    msg.add(message);
                    continue;
                }
                if (UtilsHelper.isEmpty(templateEntity.getQuestionTrouble())) {
                    String message = "第" + count + "行试题难度为空,上传失败";
                    msg.add(message);
                    continue;
                }
                if (UtilsHelper.isEmpty(templateEntity.getQuestionValue())) {
                    String message = "第" + count + "行试题分值为空,上传失败";
                    msg.add(message);
                    continue;
                }
                if (UtilsHelper.isEmpty(templateEntity.getQuestionAnswer())) {
                    String message = "第" + count + "行试题答案为空,上传失败";
                    msg.add(message);
                    continue;
                }
                if (UtilsHelper.isEmpty(templateEntity.getQuestionTitle())) {
                    String message = "第" + count + "行试题题目为空,上传失败";
                    msg.add(message);
                    continue;
                }else {
                    Map<String, Object> titleMap = new HashMap<>();
                    titleMap.put("questionTitle",templateEntity.getQuestionTitle());
                    titleMap.put("comCode",comCode);
                    Map<String, Object> recNoMap = questionService.getRecNoByQuestionTitle(titleMap);
                    //真删
                    questionService.delete(recNoMap);
                }
            }
            questionService.insert(comCode,templateEntity);
            total++;
        }
        String message = "批量成功" + total + "条";
        msg.add(message);

        if (msg.size() > 1) {
            msg.add("0");
        }

    return msg;
}

/**
 * 读取excel的数据(多个Sheet)
 *
 * @param
 * @param
 * @return
 * @throws IOException
 */
public static List<TemplateEntity> readerMultilist(File excelFile) throws IOException {
    InputStream inputStream = new FileInputStream(excelFile);
    TemplateListener listener = new TemplateListener();    // 定义的 listener
    ExcelReader excelReader = EasyExcel.read(inputStream).build();
    // readSheet参数设置读取sheet的序号
    // 读取sheet  限制6页要加在下面加
    ReadSheet readSheet1 = EasyExcel.readSheet(0).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
    ReadSheet readSheet2 = EasyExcel.readSheet(1).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
    ReadSheet readSheet3 = EasyExcel.readSheet(2).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
    ReadSheet readSheet4 = EasyExcel.readSheet(3).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
    ReadSheet readSheet5 = EasyExcel.readSheet(4).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
    ReadSheet readSheet6 = EasyExcel.readSheet(5).headRowNumber(1).head(TemplateEntity.class).registerReadListener(listener).build();
    excelReader.read(readSheet1, readSheet2, readSheet3, readSheet4,readSheet5,readSheet6);
    // 读的时候会创建临时文件，需要关闭
    excelReader.finish();
    // 取出数据放入map中，然后返回
    List<TemplateEntity> list = listener.getData();
    log.info(list.toString());
    inputStream.close();

    return list;
}

 分隔------------------------------------------------------------------------------------------------------------------- 分隔

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.fineone.shelterbelt.entity.TemplateEntity;

import java.util.ArrayList;
import java.util.List;

public class TemplateListener extends AnalysisEventListener<TemplateEntity> {

    private List<TemplateEntity> list = new ArrayList<>();

    // 一条一条读取数据，全部添加到list集合里
    @Override
    public void invoke(TemplateEntity data, AnalysisContext analysisContext) {
        list.add(data);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {}

    public List<TemplateEntity> getData() {
        return list;
    }
}

  分隔------------------------------------------------------------------------------------------------------------------ 分隔

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;

@Data
public class TemplateEntity {

    @ExcelProperty("试题类型")
    private String questionType;
    @ExcelProperty("试题类别")
    private String questionClass;
    @ExcelProperty("试题难度")
    private String questionTrouble;
    @ExcelProperty("试题分值")
    private String questionValue;
    @ExcelProperty("试题题目")
    private String questionTitle;
    @ExcelProperty("试题答案")
    private String questionAnswer;
    @ExcelProperty("选项A")
    private String choiceA;
    @ExcelProperty("选项B")
    private String choiceB;
    @ExcelProperty("选项C")
    private String choiceC;
    @ExcelProperty("选项D")
    private String choiceD;
    @ExcelProperty("选项E")
    private String choiceE;
    @ExcelProperty("选项F")
    private String choiceF;

}