关于excel:导入文件

实体

@ColumnWidth(20)
@HeadRowHeight(15) // 题目高度
@ContentRowHeight(15) // 文本行高度
public class BackQuestionImport extends BaseRowModel {@ExcelProperty( ")
@ApiModelProperty(value="id")
private int id;
@ExcelProperty("问题 id")
@ApiModelProperty(value="问题 id")
private int questionId;
@ExcelProperty("专家 id")
private int invitId;
@ExcelProperty("专家名字")
private String invitName;
@ExcelProperty("发问日期")
private String insertTime;
@ExcelProperty("问题题目")
private String question;
@ExcelProperty("标签 id")
private String labelValue;
@ExcelProperty("标签名称")
private String lableName;
@ExcelProperty("问题形容")
private String describe;
@ExcelProperty("图片")
private String filePath;
@ExcelIgnore
@ApiModelProperty(value="是否导出")
private Integer exportFlag;
@ExcelProperty("专家答复")
@ApiModelProperty(value="专家答复")
private String answer;

管制层

@PostMapping("/questionListImport")
@ApiOperation(httpMethod = "POST",value = "",notes ="")
public WGJsonResult questionListImport(@RequestParam("file") MultipartFile multipartFile) throws IOException {return backQuestionService.questionListImport(multipartFile);
}

服务层

@Override
public WGJsonResult questionListImport(MultipartFile multipartFile) throws IOException {Sheet sheet = new Sheet(1, 1, BackQuestionImport.class);
    List<Object> readList = EasyExcelFactory.read(multipartFile.getInputStream(), sheet);
    // 存 BackQuestionExport 实体的 汇合
    List<BackQuestionExport> list = new ArrayList<BackQuestionExport>();
    for (Object obj : readList) {BackQuestionExport backQuestionExport = new BackQuestionExport();
        BeanUtils.copyProperties(obj,backQuestionExport);
        list.add(backQuestionExport);
    }
    // 插入 ask_answer
    backQuestionMapper.addQuestionAnswerData(list);
    // 记录日志 ask_exp_imp_record
    addLog(list,2);
    return  success("导入胜利");
}

sql 语句

 INSERT INTO  ask_answer (
    id,
    result,
    label_value,
    lable_name,
    user_id,
    user_name,
    question_id
    )
    VALUES
    <foreach collection ="list" item="item" index= "index" separator =",">
        (#{item.id},
        #{item.answer},
        #{item.labelValue},
        #{item.lableName},
        #{item.invitId},
        #{item.invitName},
        #{item.questionId}
        )
    </foreach >
    ON DUPLICATE KEY UPDATE
    id = VALUES(id),
    result = VALUES(result),
    label_value = VALUES(label_value),
    lable_name = VALUES(lable_name),
    user_id = VALUES(user_id),
    user_name = VALUES(user_name),
    question_id = VALUES(question_id)
    

记录日志 ask_exp_imp_record

public  void addLog(List<BackQuestionExport> list,Integer type){
    // 获取各个数据的汇合
    List<String> questions = list.stream().map(BackQuestionExport::getQuestion).distinct().collect(Collectors.toList());// 先获取问题汇合
    List<Integer> questionIds =  list.stream().map(BackQuestionExport::getQuestionId).distinct().collect(Collectors.toList());
    List<Integer> invitIds =  list.stream().map(BackQuestionExport::getInvitId).distinct().collect(Collectors.toList());
    List<String> invitNames =  list.stream().map(BackQuestionExport::getInvitName).distinct().collect(Collectors.toList());
    List<String> answers =  list.stream().map(BackQuestionExport::getAnswer).distinct().collect(Collectors.toList());
    List<Integer> ids =  list.stream().map(BackQuestionExport::getId).distinct().collect(Collectors.toList());
    // 保留记录
    BackQuestionLog backQuestionLog = new BackQuestionLog();
    backQuestionLog.setQuestionIds(questionIds.stream().map(String::valueOf).collect(Collectors.joining(",")).trim());
    backQuestionLog.setQuestions(questions.stream().map(String::valueOf).collect(Collectors.joining(",")).trim());
    backQuestionLog.setQuestionNum(questionIds.size());
    backQuestionLog.setAnswerManIds(invitIds.stream().map(String::valueOf).collect(Collectors.joining(",")).trim());
    backQuestionLog.setAnswerManNames(StringUtils.join(invitNames, ",").trim());

    backQuestionLog.setAnswerIds(ids.stream().map(String::valueOf).collect(Collectors.joining(",")).trim());
    backQuestionLog.setAnswers(answers.stream().map(String::valueOf).collect(Collectors.joining(",")).trim());
    backQuestionLog.setAnswerNum(answers.size());

    backQuestionLog.setType(type);
    backQuestionLog.setStatus(0);
    // 插入日志表 ask_exp_imp_record
    backQuestionMapper.addQuestionAnswerLog(backQuestionLog);
}