SpringBoot实现Excel导入学生信息

引言
在实际业务开发中，批量导入Excel数据是一个常见需求。本文将基于Spring Boot框架，结合MyBatis-Plus和Apache POI工具，手把手教你实现一个学生信息Excel导入功能。通过本文，你将掌握以下技能：
1.Excel文件解析
2.数据校验与批量插入
3.高效处理大文件导入

一、环境准备
1.1 技术选型
Spring Boot 3.x
MyBatis-Plus 3.5.3
Apache POI 5.2.3（解析Excel）
Hibernate Validator（数据校验）

1.2 添加依赖（pom.xml）

点击查看代码

<!-- MyBatis-Plus -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

<!-- Excel解析 -->
<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>

<!-- 数据校验 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-validation</artifactId>
</dependency>

二、核心代码实现
2.1 学生实体类（StudentEntity.java）

点击查看代码

@Data
@TableName("student")
public class StudentEntity {

    private Long id;

    @NotBlank(message = "姓名不能为空")
    private String name;

    @Min(value = 0, message = "年龄不能小于0")
    @Max(value = 150, message = "年龄不能超过150")
    private Integer age;

    @Pattern(regexp = "^(男|女)$", message = "性别只能是男或女")
    private String gender;
}

2.2 Mapper接口（StudentMapper.java）

点击查看代码

@Mapper
public interface StudentMapper extends BaseMapper<StudentEntity> {
    // 无需定义方法，直接继承基础CRUD方法

    int insertBatchSomeColumn(@Param("students") List<StudentEntity> students);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zuxia.dao.StudentMapper">
    <insert id="insertBatchSomeColumn">
        INSERT INTO `student` (`id`,`name`,`age`,`gender`)
        VALUES
        <foreach collection="students" item="stu" separator=",">
            (#{stu.id}, #{stu.name}, #{stu.age}, #{stu.gender})
        </foreach>
    </insert>
</mapper>

2.3 Service层（StudentService.java）

点击查看代码

@Service
public class StudentServiceImp implements StudentService {

    @Autowired
    private StudentMapper studentMapper;

    @Transactional
    public void batchInsert(List<StudentEntity> students) {
        // MyBatis-Plus 批量插入（需配置批处理参数）
        studentMapper.insertBatchSomeColumn(students);
    }

    public List<StudentEntity> parseExcel(MultipartFile file) throws IOException {
        List<StudentEntity> students = new ArrayList<>();

        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rows = sheet.iterator();

        int rowNumber = 0;
        while (rows.hasNext()) {
            Row currentRow = rows.next();
            if (rowNumber == 0) {  // 跳过标题行
                rowNumber++;
                continue;
            }

            StudentEntity student = new StudentEntity();
            // 注意：根据Excel列顺序解析字段
            student.setId((long) currentRow.getCell(0).getNumericCellValue());
            student.setName(currentRow.getCell(1).getStringCellValue());
            student.setAge((int) currentRow.getCell(2).getNumericCellValue());
            student.setGender(currentRow.getCell(3).getStringCellValue());

            students.add(student);
            rowNumber++;
        }
        workbook.close();
        return students;
    }
}

2.4 Controller层（StudentController.java）

点击查看代码

@RestController
@RequestMapping("/api/students")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @PostMapping("/upload")
    public ResponseEntity<String> uploadExcel(@RequestParam("file") MultipartFile file) {
        try {
            List<StudentEntity> students = studentService.parseExcel(file);
            studentService.batchInsert(students);
            return ResponseEntity.ok("成功导入 " + students.size() + " 条数据");
        } catch (IOException e) {
            return ResponseEntity.status(500).body("文件处理失败: " + e.getMessage());
        } catch (Exception e) {
            return ResponseEntity.badRequest().body("数据格式错误: " + e.getMessage());
        }
    }
}

三、关键配置
3.1 数据库批处理配置（application.yml）

点击查看代码

mybatis-plus:
  configuration:
    default-executor-type: batch  # 启用批处理模式
  global-config:
    db-config:
      id-type: auto  # 主键自增策略

3.2 文件上传限制（application.yml）

点击查看代码

spring:
  servlet:
    multipart:
      max-file-size: 10MB
      max-request-size: 10MB