1.读取网络路径的Excel文件(流文件格式)
2.获取Excel的每一行数据,并验证学校班级是否存在,并获取对应的schoolID和classID
3.批量保存学生信息,并返回保存成功的行数
导入关于Excel的Pom文件
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
测试过程中发现bug:
1、表格数据容易出现前后空格,居中显示时,对数据核查出现失误,故需要对表格数据使用trim()去除数据的前后空格。
2、表格数据为纯数字时,获取表格会默认类型为number,此时需要获取String会报“Cannot get a STRING value from a NUMERIC cell”错,需要提前使用 c0.setCellType(CellType.STRING); 详情见下面代码。
int HttpResult;
try {
logger.debug("studentExcelImport Excel fileUrl:{}", fileUrl);
URL url = new URL(fileUrl);
HttpURLConnection conn = (HttpURLConnection)url.openConnection(); // 试图连接并取得返回状态码
HttpResult = conn.getResponseCode();
if(HttpResult != HttpURLConnection.HTTP_OK) {
logger.debug("studentExcelImport Excel HttpResult:{}", HttpResult);
throw new BadRequestException("文件服务器连接失败");
} else {
List<Student> temp = new ArrayList<>();
Boolean bRet = false;
InputStream fileIn = conn.getInputStream();
//根据指定的文件输入流导入Excel从而产生Workbook对象
Workbook wb0 = null;
if (fileUrl.contains(".xlsx")) {
wb0 = new XSSFWorkbook(fileIn);
} else if (fileUrl.contains(".xls")) {
wb0 = new HSSFWorkbook(fileIn);
} else {
throw new BadRequestException("文件类型不正确");
}
//获取Excel文档中的第一个表单
Sheet sheet = wb0.getSheetAt(0);
if(sheet.getPhysicalNumberOfRows()<2) {
wb0.close();
logger.debug("studentExcelImport Excel row:{}", sheet.getPhysicalNumberOfRows());
throw new BadRequestException("表格数据过少,保存失败");
}
//对Sheet中的每一行进行迭代
for (Row r : sheet) {
//如果当前行的行号(从0开始)未达到1(第二行)则重新循环
if(r.getRowNum()<1){
continue;
}
//创建实体类
Student stu=new Student();
Cell c0 = r.getCell(0);
Cell c1 = r.getCell(1);
Cell c2 = r.getCell(2);
if(null == c0 || null == c1 || null == c2) {
wb0.close();
throw new BadRequestException("表格第["+r.getRowNum()+"]行数据不完整");
}
//当表格内数据为数字时,会自动转换成number类型,所以需提前转换表格数据为String类型
c0.setCellType(CellType.STRING);
c1.setCellType(CellType.STRING);
c2.setCellType(CellType.STRING);
stu.setSchoolName(c0.getStringCellValue());
stu.setClassName(c1.getStringCellValue());
stu.setName(c2.getStringCellValue());
//第四列数据选填
stu.setCardID(null == r.getCell(3)?
"" : r.getCell(3).getStringCellValue());
//检查学生的学校和班级是否存在
SchoolClass scinfo = badgeDataRepository.
querySchoolClassByName(stu.getClassName(), stu.getSchoolName());
if(null == scinfo) {
wb0.close();
throw new BadRequestException("表格第["+r.getRowNum()+"]行数据异常,学校或班级不存在");
}
logger.debug("studentExcelImport Excel[{}] name:{},schoolID:{},classID:{}",
r.getRowNum(), stu.getStudentName(), scinfo.getSchoolID(), scinfo.getClassID());
stu.setSchoolID(scinfo.getSchoolID());
stu.setClassID(scinfo.getClassID());
temp.add(stu);
}
wb0.close();
fileIn.close();
//批量保存学生信息,避免频繁开关数据库连接
bRet = badgeDataRepository.addStudentList(temp);
if(!bRet) {
throw new BadRequestException("保存学生信息失败");
}
return temp.size();
}
}catch (IOException e) {
throw new BadRequestException("导入Excel文件失败");
}
对mysql数据库进行批量新增
<insert id="addStudentList" useGeneratedKeys="true" keyProperty="id">
insert into student(
name,
cardID,
SchoolID,
SchoolName,
ClassID,
ClassName
)values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.name},
#{item.cardID},
#{item.schoolID},
#{item.schoolName},
#{item.classID},
#{item.className}
)
</foreach>
</insert>