实体
@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);}
服务层
@Overridepublic 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);}