一. 前提条件
1.1 需求
- 从指定的Excel模板中读取数据,将读取到的数据存储到数据库中。
1.2 分析
- 需要用到
poi
读取Excel - 使用自定义注解标记Excel单元格的行,列,数据类型方便读取数据
- 需要使用
hibernate validation
校验数据 - 前台需要使用
FormData()
对象向后台传入文件,需要指定只能上传Excel类型的文件 - 读取到的数据依次的get和set到entity中很麻烦,需要用到
反射
进行封装
二. 准备
2.1 自定义注解
import javax.validation.Constraint;
import javax.validation.constraints.NotEmpty;
import javax.validation.Payload;
import javax.validation.ReportAsSingleViolation;
import java.lang.annotation.*;
@Documented
@Target({ ElementType.FIELD })
@Retention(RetentionPolicy.RUNTIME)
@Constraint(validatedBy = {})
@NotEmpty
@ReportAsSingleViolation
public @interface ValidateNotEmpty {
String msgArgs() default "";
String message() default "{1001E}";
Class<?>[] groups() default {};
Class<? extends Payload>[] payload() default {};
}
import java.lang.annotation.*;
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelCellAnnotation {
int rowIndex() default 0;
int columnIndex() default 0;
java.lang.Class type() default String.class;
}
2.2 封装Excel的实体类
- 以「契約者申請日」这个项目为例说明
- 在Excel模板中的位置为第2行
- 在Excel模板中的位置为第22列
- 在Excel模板中的数据类型为
Date
类型
import lombok.Data;
import java.util.Date;
@Data
public class ExcelEntity {
@ValidateNotEmpty(msgArgs = "契約者申請日")
@ExcelCellAnnotation(rowIndex = 1, columnIndex = 21, type = Date.class)
private String keiyakushaShinseibi;
@ValidateNotEmpty(msgArgs = "フリガナ")
@ExcelCellAnnotation(rowIndex = 4, columnIndex = 5)
private String furikana;
@ValidateNotEmpty(msgArgs = "契約者(氏名)")
@ExcelCellAnnotation(rowIndex = 5, columnIndex = 5)
private String keiyakuShaName;
@ExcelCellAnnotation(rowIndex = 5, columnIndex = 20)
private String sexMan;
@ExcelCellAnnotation(rowIndex = 5, columnIndex = 22)
private String sexWoman;
@ValidateNotEmpty(msgArgs = "契約者住所")
@ExcelCellAnnotation(rowIndex = 8, columnIndex = 5)
private String keiyakushaJyusho;
@ValidateNotEmpty(msgArgs = "契約者連絡先_携帯")
@ExcelCellAnnotation(rowIndex = 10, columnIndex = 8)
private String keiyakushaPhone;
@ValidateNotEmpty(msgArgs = "契約者連絡先_メール")
@ExcelCellAnnotation(rowIndex = 10, columnIndex = 16)
private String keiyakushaMail;
}
三. 前台
- 通过
accept="application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
来实现只能上传Excel类型的数据 - 上传成功或者失败都需要把文件上传input中的value置为空,保证同一个文件可以上传多次
<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<input
type="file"
id="excel"
accept="application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
/>
<button id="btn">上传</button>
</body>
<script src="https://code.jquery.com/jquery-3.6.3.js"></script>
<script>
$(function () {
$("#btn").click(function () {
const formData = new FormData();
formData.append("excelFile", $("#excel").get(0).files[0]);
$.ajax({
url: `/poi/excel`,
type: 'POST',
data: formData,
processData: false,
contentType: false,
success: function (data, status, xhr) {
console.log(data);
},
error(xhr, textStatus, errorMessage) {
console.log(textStatus);
},
complete(jqXHR, textStatus) {
$("#excel").val("");
}
});
})
})
</script>
</html>
四. Controller层
- 通过
MultipartHttpServletRequest
来获取前台上传到后台的文件
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;
import java.util.List;
@Controller
@RequestMapping("/poi")
public class PoiController {
@Autowired
private PoiService service;
@GetMapping("/init")
public ModelAndView init() {
ModelAndView modelAndView = new ModelAndView();
modelAndView.setViewName("poiTest");
return modelAndView;
}
@PostMapping("/excel")
public ResponseEntity<Void> handleExcel(MultipartHttpServletRequest request) throws Exception {
List<MultipartFile> file = request.getMultiFileMap().get("excelFile");
MultipartFile multipartFile = file.get(0);
ExcelEntity excelEntity = new ExcelEntity();
service.readExcel(multipartFile, excelEntity);
service.validateExcelData(excelEntity);
return ResponseEntity.noContent().build();
}
}
五. Service层💪💪💪
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.util.ReflectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.validation.beanvalidation.LocalValidatorFactoryBean;
import org.springframework.web.multipart.MultipartFile;
import javax.validation.ConstraintViolation;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Set;
@Service
public class PoiService implements InitializingBean {
private final static String methodAction = "set";
private final static DataFormatter formatter = new DataFormatter();
private static DateFormat dateformat = null;
@Autowired
private LocalValidatorFactoryBean validator;
@Override
public void afterPropertiesSet() {
dateformat = new SimpleDateFormat("yyyy-MM-dd");
}
public void readExcel(MultipartFile multipartFile, ExcelEntity excelEntity) throws Exception {
InputStream inputStream = multipartFile.getInputStream();
XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
XSSFSheet sheet = sheets.getSheetAt(0);
String cellValue = "";
Field[] fields = excelEntity.getClass().getDeclaredFields();
for (Field field : fields) {
ExcelCellAnnotation annotation = field.getAnnotation(ExcelCellAnnotation.class);
if (ObjectUtils.isEmpty(annotation)) {
continue;
}
XSSFCell cell = sheet
.getRow(annotation.rowIndex())
.getCell(annotation.columnIndex());
Class valueType = annotation.type();
if (Date.class == valueType) {
cellValue = dateformat.format(cell.getDateCellValue());
} else if (String.class == valueType) {
cellValue = formatter.formatCellValue(cell);
}
String methodName = methodAction + StringUtils.capitalize(field.getName());
Method setMethod = ReflectionUtils.findMethod(excelEntity.getClass(), methodName, cellValue.getClass());
ReflectionUtils.invokeMethod(setMethod, excelEntity, cellValue);
}
}
public void validateExcelData(ExcelEntity excelEntity) {
Set<ConstraintViolation<ExcelEntity>> validateResults = validator.validate(excelEntity);
for (ConstraintViolation<ExcelEntity> validateResult : validateResults) {
System.out.println(validateResult.getMessage());
}
System.out.println(excelEntity);
}
}
六. 效果