参考博客:https://blog.youkuaiyun.com/qq_28288835/article/details/109025117
pom.xml
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>
java代码:
@Override
@Transactional(rollbackFor = Exception.class)
public ResultJson importAssess(MultipartFile file, Map<String, Object> params) throws IOException {
if(file == null){
return new ResultJson<>(400, "导入文件不能为空");
}
String fileName=file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return new ResultJson<>(400, "上传文件格式不正确");
}
Map<String, Object> listResult = importDataHandle(file);
Boolean state = (Boolean) listResult.get("state");
if(!state){
return new ResultJson<>(Constant.ERROR_CODE400, listResult.get("msg").toString());
}
List<Assess> assessList = (List<Assess>) listResult.get("data");
List<Assess> insertList = new ArrayList<>();
List<Assess> updateList = new ArrayList<>();
for(Assess assess : assessList){
// 处理数据...
}
try{
if(!insertList.isEmpty()){
int res = assessMapper.insertAssessAll(insertList);
if(res <= 0){
throw new IOException("添加信息失败");
}
}
if(!updateList.isEmpty()){
int res = assessMapper.updateAssessAll(updateList);
if(res <= 0){
throw new IOException("修改信息失败");
}
}
}catch (RuntimeException e){
return new ResultJson<>(Constant.ERROR_CODE600, Constant.ERROR_MSG600);
}
return null;
}
/**
* 导入数据处理
*/
private Map<String, Object> importDataHandle(MultipartFile file){
List<Assess> assessList = new ArrayList<>();
Map<String, Object> result = new HashMap<>();
result.put("state", true);
result.put("msg", "");
InputStream is = null;
Workbook wb = null;
try {
//读取文件流
is = file.getInputStream();
//文件名
String fileName=file.getOriginalFilename();
boolean notNull = false;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
//xlsx格式
wb = new XSSFWorkbook(is);
} else {
//xls格式
wb = new HSSFWorkbook(is);
}
if (wb != null) {
//默认读取第一个sheet
Sheet sheet = wb.getSheetAt(0);
if (sheet != null) {
//最先读取首行
boolean firstRow = true;
boolean isThrow = false;
try {
if (sheet.getLastRowNum() > 0) {
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
//循环行
Assess assess = new Assess();
Row row = sheet.getRow(i);
//首行 提取注解 验证格式是否正确
if (firstRow) {
firstRow = false;
}else{
//忽略空白行
if (row == null) {
continue;
}
int theRow = i + 1;
if (row.getCell(0) != null) {
row.getCell(0).setCellType(CellType.STRING);
String name = row.getCell(0).getStringCellValue();
if (ValidationForm.isNullOrEmpty(name)) {
isThrow = true;
result.put("msg", "导入失败(第" + theRow + "行,评估人不能为空)");
throw new RuntimeException("导入失败(第" + theRow + "行,评估人不能为空)");
} else {
assess.setAssessUsername(name);
}
} else {
isThrow = true;
result.put("msg", "导入失败(第" + theRow + "行,评估人不能为空)");
throw new RuntimeException("导入失败(第" + theRow + "行,评估人不能为空)");
}
// 姓名
if (row.getCell(1) != null) {
row.getCell(1).setCellType(CellType.STRING);
String name = row.getCell(1).getStringCellValue();
if (ValidationForm.isNullOrEmpty(name)) {
isThrow = true;
result.put("msg", "导入失败(第" + theRow + "行,姓名不能为空)");
throw new RuntimeException("导入失败(第" + theRow + "行,姓名不能为空)");
} else {
assess.setName(name);
}
} else {
isThrow = true;
result.put("msg", "导入失败(第" + theRow + "行,姓名不能为空)");
throw new RuntimeException("导入失败(第" + theRow + "行,姓名不能为空)");
}
// 民族
if (row.getCell(2) != null) {
row.getCell(2).setCellType(CellType.STRING);
String value = row.getCell(2).getStringCellValue();
if (!ValidationForm.isNullOrEmpty(value)) {
assess.setNation(value);
}
}
assessList.add(assess);
}
}
}else {
isThrow = true;
throw new RuntimeException("导入失败,数据为空");
}
}catch (Exception e) {
e.printStackTrace();
System.out.println(e.getMessage());
}
if(isThrow){
result.put("state", false);
return result;
}
}
}
} catch (IOException e) {
result.put("state", false);
result.put("msg", "文件读取失败!");
return result;
} finally {
// 关闭资源
try {
if (is != null) {
is.close();
}
if (wb != null) {
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
if(!assessList.isEmpty()){
result.put("data", assessList);
return result;
}else{
result.put("state", false);
result.put("msg", "导入失败,数据为空");
}
return result;
}
批量新增、修改xml
<insert id="insertAssessAll" parameterType="com.example.server_assess.pros.assess.model.Assess">
insert into tm_assess(
idno,
...
name
)
values
<foreach collection="assessList" separator="," index="index" item="item">
(
#{item.idno},
...
#{item.name}
)
</foreach>
</insert>
<update id="updateAssessAll" parameterType="com.example.server_assess.pros.assess.model.Assess">
<foreach collection="assessList" separator=";" item="item">
update tm_assess
<set>
<if test="item.idno != null">
idno = #{item.idno},
</if>
<if test="item.name != null">
name = #{item.name},
</if>
</set>
where id = #{item.id}
</foreach>
</update>