Excel批量导入数据
1. 问题场景
考试系统开发中,类似考题、学生信息等,手动录入太繁杂,在excel当中编辑好再导入相对便捷,因此需要这样一个接口。
2. 实现方法
Dao层省略,只列出接口层和服务层处理逻辑:
接口类ExcelDealControllerjava:
@Autowired
private QuestionService queService;
@RequestMapping(value= "/addques",method=RequestMethod.POST)
public String dealQuestionInfoExcel(MultipartFile file, String subId) {
String result = queService.uploadQuestionInfoExcel(file,subId);
log.info("上传文件的结果:"+result);
return "redirect:../../question.html";
}
处理类:QuestionService.java
@Autowired
private QuestionDao queDao;
@Autowired
private OptionDao optDao;
@Autowired
private ServletContext context;
private JSONObject resultObj;
private String subId = "";
// 处理上传考题的信息的excel
public String uploadQuestionInfoExcel(MultipartFile mf, String sId) {
resultObj = new JSONObject();
subId = sId;
// 获取上传路径baseUrl
String baseUrl = context.getRealPath("") + "\\upload\\excel\\";
if (null != mf) {
String fileName = mf.getOriginalFilename();
if (!"".equalsIgnoreCase(fileName)) {
// 传到服务器文件夹中的文件名称为 ctimeMillis+3个随机整数+原始文件名.suffix
StringBuilder sb = new StringBuilder("que_");
Long currentTimeMillis = System.currentTimeMillis();
String ctm = currentTimeMillis.toString();
// 截取后六位
ctm = ctm.substring(ctm.length() - 6, ctm.length());
sb.append(ctm);
for (int i = 0; i < 3; i++) {
sb.append((int) (Math.random() * 10));
}
// 获取后缀
String suffix = fileName.substring(fileName.indexOf("."),
1. 问题场景
考试系统开发中,类似考题、学生信息等,手动录入太繁杂,在excel当中编辑好再导入相对便捷,因此需要这样一个接口。
2. 实现方法
Dao层省略,只列出接口层和服务层处理逻辑:
接口类ExcelDealControllerjava:
@Autowired
private QuestionService queService;
@RequestMapping(value= "/addques",method=RequestMethod.POST)
public String dealQuestionInfoExcel(MultipartFile file, String subId) {
String result = queService.uploadQuestionInfoExcel(file,subId);
log.info("上传文件的结果:"+result);
return "redirect:../../question.html";
}
处理类:QuestionService.java
@Autowired
private QuestionDao queDao;
@Autowired
private OptionDao optDao;
@Autowired
private ServletContext context;
private JSONObject resultObj;
private String subId = "";
// 处理上传考题的信息的excel
public String uploadQuestionInfoExcel(MultipartFile mf, String sId) {
resultObj = new JSONObject();
subId = sId;
// 获取上传路径baseUrl
String baseUrl = context.getRealPath("") + "\\upload\\excel\\";
if (null != mf) {
String fileName = mf.getOriginalFilename();
if (!"".equalsIgnoreCase(fileName)) {
// 传到服务器文件夹中的文件名称为 ctimeMillis+3个随机整数+原始文件名.suffix
StringBuilder sb = new StringBuilder("que_");
Long currentTimeMillis = System.currentTimeMillis();
String ctm = currentTimeMillis.toString();
// 截取后六位
ctm = ctm.substring(ctm.length() - 6, ctm.length());
sb.append(ctm);
for (int i = 0; i < 3; i++) {
sb.append((int) (Math.random() * 10));
}
// 获取后缀
String suffix = fileName.substring(fileName.indexOf("."),