@RequestMapping(value = "/user/import", method = RequestMethod.POST)
public @ResponseBody String importRecords(MultipartFile file) {
try{
StringBuilder errorSb = new StringBuilder();
List<User> list = new ArrayList<>();
XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
XSSFSheet sheet = workbook.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
Map<String, List<String>> map = new HashMap<>();
Date now = new Date();
for (int i = 1; i < rows; i++) {
XSSFRow row = sheet.getRow(i);
if (row == null) {
log.info("此行数据为空");
continue;
}
String userId = this.getRowStringValue(row.getCell(0));
String name = this.getRowStringValue(row.getCell(1));
String phone = this.getRowStringValue(row.getCell(2));
String address = this.getRowStringValue(row.getCell(3));
if (StringUtils.isEmpty(userId) && StringUtils.isEmpty(name)
&& StringUtils.isEmpty(phone) && StringUtils.isEmpty(address)) {
log.info("此行数据为空");
continue;
}
// 校验一些必填
if(StringUtils.isEmpty(userId) || !StringUtils.isNumeric(userId)){
errorSb.append("第"+i+"行,工号为空或者值不正确</br>");
continue;
}
if(StringUtils.isEmpty(name)){
errorSb.append("第"+i+"行,姓名为空</br>");
continue;
}
if(StringUtils.isEmpty(phone)){
errorSb.append("第"+i+"行,手机号码为空</br>");
continue;
}
User record = new User();
record.setId(Integer.valueOf(userId));
record.setName(name);
record.setPhone(phone);
record.setAddress(address);
list.add(record);
}
if(errorSb.length() > 0){
log.info("导入校验报错", errorSb.toString());
return "导入校验报错";
}
if(list != null && !list.isEmpty()){
userService.batchInsert(list);
}
}catch (Exception e){
log.error("员工导入异常:", e);
return "员工导入异常";
}
return "success";
}
public static String getRowStringValue(XSSFCell cell){
String result;
if(cell != null && cell.getCellType() == Cell.CELL_TYPE_STRING){
result = cell.getStringCellValue();
}else if(cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
DecimalFormat df = new DecimalFormat("0");
result = df.format(cell.getNumericCellValue());
if (result.endsWith(".0")) {
result = result.replace(".0", "");
}
}else {
result = cell == null ? "" : cell.toString();
}
return result;
}
pom文件加上poi依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>