方法代码如下:
@RequestMapping(value = "/common/importExcel.json")
@ResponseBody
public ResponseResult importExcel(@RequestParam("importFile") MultipartFile file, HttpServletRequest request,
HttpServletResponse response) throws IOException {
String classId = request.getParameter("classId");
if (!file.isEmpty()) {
try {
POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
HSSFWorkbook wb = new HSSFWorkbook(fs);
Sheet sheet = wb.getSheetAt(0);
List<String> errorMessage = importStudent(sheet, classId, getCurrentUserId());
if (errorMessage.isEmpty()) {
return new ResponseResult("导入成功!");
} else {
return new ResponseResult(false, null, StringUtils.join(errorMessage, "<br/>"));
}
} catch (Exception e) {
logger.error(e.getMessage(), e);
if (StringUtils.isNotEmpty(e.getMessage()) && e.getMessage().contains("Your file appears not to be a valid OLE2 document")) {
return new ResponseResult(false, null, "请使用标准导入模版来导入数据!");
} else {
return new ResponseResult(false, null, "导入的模板有误,请校验是否满足以下要求:<br/>" +
"1、\"学号\"不得超出50个字符;<br/>" +
"2、\"姓名\"不得超过32个字符;<br/>" +
"3、填写的单元格中不得包含计算公式;<br/>" +
"4、导入的模板不能删除或添加列;<br/>" +
"5、需要下拉选择的信息,只能是模板中的选择项");
}
}
}
return new ResponseResult(false, null, "未知错误!");
}
@RequestParam("importFile") MultipartFile file
可以拿到请求中名为importFile的文件,
//解析Excel文件
POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
//得到文档对象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//得到excel文档的第一个表格
Sheet sheet = wb.getSheetAt(0);
//调用importStudent方法校验表格导入的数据是否有错误数据
List<String> errorMessage = importStudent(sheet, classId, getCurrentUserId());
importStudent方法代码如下:
private List<String> importStudent(Sheet sheet, String classId, String userId) throws BusinessException {
// 初始化需要校验的数据合集
Set<String> studentNoSet = new HashSet<>();
Set<String> phoneSet = new HashSet<>();
Set<String> isGroupLeaderSet = new HashSet<>();
List<String> errorMessage = new ArrayList<>();
//0行是标题 从第一行开始
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
// 列
int n = 0;
StringBuilder sb = new StringBuilder();
for (int j = 0; j < row.getLastCellNum(); j++) {
sb.append("第").append(j).append("列-值:").append(sheet.getRow(i).getCell(j)).append(" ");
if (PubUtil.isNotEmpty(sheet.getRow(i).getCell(j)) &&
row.getCell(j).getCellType() == Cell.CELL_TYPE_BLANK) {
n++;
}
}
logger.info("共有{}列,值为空!", n);
logger.info("第{}行记录, {}", i, sb.toString());
if (n > 1) {
errorMessage.add("第" + i + "行,空白列超过一列,请重新导入");
break;
}
// 如果名称为空,不继续导入
String column0 = toStr(row.getCell(0));
String column1 = toStr(row.getCell(1));
String column2 = toStr(row.getCell(2));
String column3 = toStr(row.getCell(3));
String column4 = toStr(row.getCell(4));
String column5 = toStr(row.getCell(5));
if (PubUtil.isEmpty(column0)) {
errorMessage.add("第" + i + "行,第1列,数据记录为空,请重新导入");
} else {
if (studentNoSet.contains(column0)) {
errorMessage.add("第" + i + "行,第1列,学号重复");
}
if (classUserRelService.countByClassIdStudentNo(null, classId, column0) > 0) {
errorMessage.add("第" + i + "行,第1列,学号在班级中已存在");
}
}
if (PubUtil.isEmpty(column1)) {
errorMessage.add("第" + i + "行,第2列,数据记录为空,请重新导入");
}
if (PubUtil.isEmpty(column2)) {
errorMessage.add("第" + i + "行,第3列,数据记录为空,请重新导入");
} else {
if (!Pattern.compile("^(1[0-9]\\d{9})$").matcher(column2).matches()) {
errorMessage.add("第" + i + "行,第3列,手机号码格式错误");
}
if (phoneSet.contains(column2)) {
errorMessage.add("第" + i + "行,第3列,手机号码重复");
}
if (classUserRelService.countByPhone(classId, column2) > 0) {
errorMessage.add("第" + i + "行,第3列,该手机号码用户在班级中已存在");
}
}
if (PubUtil.isEmpty(column3)) {
errorMessage.add("第" + i + "行,第4列,数据记录为空,请重新导入");
}
if (PubUtil.isEmpty(column4)) {
errorMessage.add("第" + i + "行,第5列,数据记录为空,请重新导入");
}
if (PubUtil.isEmpty(column5)) {
errorMessage.add("第" + i + "行,第6列,数据记录为空,请重新导入");
}
if (PubUtil.isNotEmpty(column4) && PubUtil.isEmpty(column5) && YesNoEnum.YES.getCode().equals(column5) && isGroupLeaderSet.contains(column4)) {
errorMessage.add("第" + i + "行,第6列,小组组长重复");
}
if (PubUtil.isNotEmpty(column4) && PubUtil.isEmpty(column5) && YesNoEnum.YES.getCode().equals(column5)) {
if (classUserRelService.countIsGroupLeaderByGroupName(classId, column5) > 0) {
errorMessage.add("第" + i + "行,第6列,该小组组长已存在");
}
}
studentNoSet.add(column0);
phoneSet.add(column2);
if (PubUtil.isNotEmpty(column4) && YesNoEnum.YES.getCode().equals(column5)) {
isGroupLeaderSet.add(column4);
}
if (errorMessage.size() > 10) {
break;
}
}
// 各个数据进行校验
if (sheet.getLastRowNum() > 1 && errorMessage.isEmpty()) {
logger.info("导入记录数: {}", sheet.getLastRowNum() - 1);
// 初始化需要保存的数据
List<UserVOExt> userVOExts = new ArrayList<>();
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
String column0 = toStr(row.getCell(0));
if (StringUtils.isEmpty(column0)) {
continue;
}
String column1 = toStr(row.getCell(1));
String column2 = toStr(row.getCell(2));
String column3 = toStr(row.getCell(3));
String column4 = toStr(row.getCell(4));
String column5 = YesNoEnum.getByName(toStr(row.getCell(5))).getCode();
String column6 = toStr(row.getCell(6));
UserVOExt userVOExt = BeanUtils.copyToNewBean(userService.getStudentByPhone(column2).getInfo(), UserVOExt.class);
if (userVOExt == null) {
userVOExt = generatorUserVOExt(column1, column2, column3, column6, column0, column4, column5);
} else {
userVOExt.setStudentNo(column0);
userVOExt.setGroupName(column4);
userVOExt.setIsGroupLeader(column5);
}
userVOExts.add(userVOExt);
}
userService.insertImport(userVOExts, classId, userId);
}
return errorMessage;
}
//创建数据合集用来存放需要校验的数据,set的数据结构表示set只有值没有键,而且每两两个值是not equal的,set集合中最多有一个null值
Set<String> studentNoSet = new HashSet<>();
Set<String> phoneSet = new HashSet<>();
Set<String> isGroupLeaderSet = new HashSet<>();
//List<String>集合是用来存放校验所报的错误信息的,因为错误信息不知一条
List<String> errorMessage = new ArrayList<>();
//A mutable sequence of characters.可变的字符序列:表示这个字符序列可以继续添加字符
StringBuilder sb = new StringBuilder();
//eg:
StringBuilder b = new StringBuilder ("123");
b.append("456");
// b打印结果为:123456
System.out.println(b);