- jar包
- controller层
- QuestionbankDAO
- QuestionbankData
- QuestionExcelListener
- questionbankServiceImpl
官网地址:https://www.yuque.com/easyexcel/doc/read jar包
com.alibaba
easyexcel
3.1.1
controller层
@ApiOperation("新增题目Excel")
@PostMapping("/insertQuestionExcel")
public CommonResult insertQuestionExcel(@RequestParam MultipartFile file){
try {
EasyExcel.read(file.getInputStream(), QuestionbankData.class, new QuestionExcelListener(questionbankService)).sheet().doRead();
} catch (IOException e) {
e.printStackTrace();
}
return CommonResult.success("Excel成功插入数据库!!✿✿ヽ(°▽°)ノ✿撒花");
}
QuestionbankDAO
package com.qx.answer.pojo.dao;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
@EqualsAndHashCode
public class QuestionbankDAO {
private String Content;
private String ChildContent;
private Integer Type;
private String AnswerContent;
}
QuestionbankData
package com.qx.answer.pojo.dto;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import java.util.List;
@Getter
@Setter
@EqualsAndHashCode
public class QuestionbankData {
@ExcelProperty(value = "题目",index = 0)
private String Content;
@ExcelProperty(value = "选择题选项",index = 1)
private String ChildContent;
@ExcelProperty(value = "类型",index = 3)
private Integer Type;
@ExcelProperty(value = "答案",index = 2)
private String AnswerContent;
}
QuestionExcelListener
package com.qx.answer.config;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.qx.answer.pojo.dao.QuestionbankDAO;
import com.qx.answer.pojo.dto.QuestionbankData;
import com.qx.answer.service.IQuestionbankService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
@Scope("prototype")
@Slf4j
public class QuestionExcelListener extends AnalysisEventListener {
@Autowired
private IQuestionbankService questionbankService;
private static final int BATCH_COUNT = 100;
//缓存数据
private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private QuestionbankDAO questionbankDAO;
public QuestionExcelListener() {
questionbankDAO = new QuestionbankDAO();
}
public QuestionExcelListener(IQuestionbankService qbService) {
questionbankService = qbService;
}
@Override
public void invoke(QuestionbankData questionbankData, AnalysisContext analysisContext) {
// 每解析一行数据,该方法会被调用一次
System.out.println("解析到一条数据:"+ JSON.toJSONString(questionbankData));
cachedDataList.add(questionbankData);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//存入到数据库中
saveData();
// 全部解析完成被调用
System.out.println("所有数据解析完成!!");
}
//存储到数据库
private void saveData() {
log.info(": 共计{}条数据,开始存储数据库!", cachedDataList.size());
questionbankService.insertSelective(cachedDataList);
System.out.println("数据插入数据库成功!!");
}
}
questionbankServiceImpl
@Override
public void insertSelective(List list) {
for (QuestionbankData qbD:list) {
Questionbank qb = new Questionbank();
qb.setType(qbD.getType());
qb.setAnswerContent(qbD.getAnswerContent());
qb.setContent(qbD.getContent());
questionbankMapper.insert(qb);
Questionchildcontent qcc = new Questionchildcontent();
if(qbD.getChildContent()!=null){
qcc.setQuerstionBankId(qb.getId());
qcc.setContent(qbD.getChildContent());
questionchildcontentMapper.insert(qcc);
}
System.out.println("数据插入数据库成功!!"+qb);
}
}



