目录
- 一. 前提条件
- 二. 准备
- 三. 前台
- 四. Controller层
- 五. Service层💪💪💪
- 六. 效果
一. 前提条件
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 {@Autowiredprivate 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;@Autowiredprivate LocalValidatorFactoryBean validator;@Overridepublic 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);}
}
六. 效果
