Java 读取Excel模板中的数据到实体类

一. 前提条件

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 "";
	
	// 1001E=请输入{msgArgs}。
	String message() default "{1001E}";

	Class<?>[] groups() default {};

	Class<? extends Payload>[] payload() default {};
}

标记Excel单元格的行，列，属性信息的注解

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface ExcelCellAnnotation {
	
	// Excel单元格行的index
    int rowIndex() default 0;
	// Excel单元格列的index
    int columnIndex() default 0;
	// Excel单元格列的类型,默认是字符串类型
    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);

        // 将Excel中的文件读取到Entity中
        ExcelEntity excelEntity = new ExcelEntity();
        service.readExcel(multipartFile, excelEntity);

        // 对Excel中的数据进行校验
        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 {

    // set方法
    private final static String methodAction = "set";
    // Excel单元格格式化对象
    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");
    }
	
	// 读取Excel中的数据
    public void readExcel(MultipartFile multipartFile, ExcelEntity excelEntity) throws Exception {

        // 获取sheet页对象
        InputStream inputStream = multipartFile.getInputStream();
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = sheets.getSheetAt(0);

        // 单元格中的值
        String cellValue = "";

        // 获取类上的所有属性(public和private都可以获取到)
        Field[] fields = excelEntity.getClass().getDeclaredFields();
        for (Field field : fields) {

            // 如果该属性中没有ExcelCellAnnotation注解的话,跳过
            ExcelCellAnnotation annotation = field.getAnnotation(ExcelCellAnnotation.class);
            if (ObjectUtils.isEmpty(annotation)) {
                continue;
            }

            // 根据行列的index,获取当前的单元格对象
            XSSFCell cell = sheet
                    // 获取属性上注解标记的单元格的行index
                    .getRow(annotation.rowIndex())
                    // 获取属性上注解标记的单元格的列index
                    .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);
        }
    }
	
	// 对Excel中的数据进行校验
    public void validateExcelData(ExcelEntity excelEntity) {

        // 使用自定义注解对excel数据进行校验并打印
        Set<ConstraintViolation<ExcelEntity>> validateResults = validator.validate(excelEntity);
        for (ConstraintViolation<ExcelEntity> validateResult : validateResults) {
            System.out.println(validateResult.getMessage());
        }

        // 打印excel中获取到的数据
        System.out.println(excelEntity);
    }
}