Spring Boot实现导入导出案例

wen java案例 2026-08-10 101

本文目录导读：

Spring Boot实现导入导出案例

项目依赖配置
实体类
Excel导入导出DTO
Mapper接口
Service层
Controller层
通用响应类
运行配置
前端测试页面
数据库建表SQL
测试用例
使用说明

我将为您提供一个完整的Spring Boot导入导出案例,涵盖Excel文件的导入和导出功能。

项目依赖配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>import-export-demo</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.14</version>
        <relativePath/>
    </parent>
    <properties>
        <java.version>1.8</java.version>
        <easyexcel.version>3.3.2</easyexcel.version>
    </properties>
    <dependencies>
        <!-- Spring Boot Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- MyBatis Plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <!-- MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <!-- EasyExcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>${easyexcel.version}</version>
        </dependency>
        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- Validation -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <!-- Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

实体类

package com.example.demo.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@TableName("user_info")
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private String username;
    private String email;
    private String phone;
    private Integer age;
    private String address;
    private String status;
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
}

Excel导入导出DTO

package com.example.demo.model;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import java.time.LocalDateTime;
@Data
@ContentRowHeight(20)
@HeadRowHeight(25)
@ColumnWidth(20)
public class UserExcelDTO {
    @ExcelProperty(value = "用户ID", index = 0)
    private Long id;
    @ExcelProperty(value = "姓名", index = 1)
    @NotBlank(message = "姓名不能为空")
    private String name;
    @ExcelProperty(value = "用户名", index = 2)
    @NotBlank(message = "用户名不能为空")
    private String username;
    @ExcelProperty(value = "邮箱", index = 3)
    @Email(message = "邮箱格式不正确")
    private String email;
    @ExcelProperty(value = "手机号", index = 4)
    @Pattern(regexp = "^1[3-9]\\d{9}$", message = "手机号格式不正确")
    private String phone;
    @ExcelProperty(value = "年龄", index = 5)
    private Integer age;
    @ExcelProperty(value = "地址", index = 6)
    private String address;
    @ExcelProperty(value = "状态", index = 7)
    private String status;
    @ExcelProperty(value = "创建时间", index = 8)
    private LocalDateTime createTime;
}

Mapper接口

package com.example.demo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

Service层

package com.example.demo.service;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import com.example.demo.model.UserExcelDTO;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
    /**
     * 导出用户数据
     */
    public void exportUsers(HttpServletResponse response, String name) {
        try {
            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("用户数据_" + System.currentTimeMillis(), "UTF-8");
            response.setHeader("Content-disposition", 
                "attachment;filename=" + fileName + ".xlsx");
            // 查询数据
            List<User> users = getUserList(name);
            // 转换为DTO
            List<UserExcelDTO> dataList = new ArrayList<>();
            for (User user : users) {
                UserExcelDTO dto = new UserExcelDTO();
                BeanUtils.copyProperties(user, dto);
                dataList.add(dto);
            }
            // 写入Excel
            EasyExcel.write(response.getOutputStream(), UserExcelDTO.class)
                    .sheet("用户数据")
                    .doWrite(dataList);
        } catch (IOException e) {
            throw new RuntimeException("导出Excel失败", e);
        }
    }
    /**
     * 导入用户数据
     */
    @Transactional(rollbackFor = Exception.class)
    public List<UserExcelDTO> importUsers(MultipartFile file) {
        List<UserExcelDTO> errorList = new ArrayList<>();
        try {
            EasyExcel.read(file.getInputStream(), UserExcelDTO.class, 
                new AnalysisEventListener<UserExcelDTO>() {
                    private List<UserExcelDTO> batchList = new ArrayList<>();
                    private static final int BATCH_SIZE = 100;
                    @Override
                    public void invoke(UserExcelDTO data, AnalysisContext context) {
                        // 数据验证
                        if (validateData(data)) {
                            batchList.add(data);
                            if (batchList.size() >= BATCH_SIZE) {
                                saveBatch(batchList);
                                batchList.clear();
                            }
                        } else {
                            errorList.add(data);
                        }
                    }
                    @Override
                    public void doAfterAllAnalysed(AnalysisContext context) {
                        if (!batchList.isEmpty()) {
                            saveBatch(batchList);
                            batchList.clear();
                        }
                    }
                }).sheet().doRead();
        } catch (IOException e) {
            throw new RuntimeException("导入Excel失败", e);
        }
        return errorList;
    }
    /**
     * 批量保存用户
     */
    private void saveBatch(List<UserExcelDTO> dtoList) {
        List<User> userList = new ArrayList<>();
        LocalDateTime now = LocalDateTime.now();
        for (UserExcelDTO dto : dtoList) {
            User user = new User();
            BeanUtils.copyProperties(dto, user);
            // 检查用户是否存在
            LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(User::getUsername, dto.getUsername());
            User existUser = userMapper.selectOne(wrapper);
            if (existUser != null) {
                user.setId(existUser.getId());
                user.setUpdateTime(now);
                userMapper.updateById(user);
            } else {
                user.setCreateTime(now);
                user.setUpdateTime(now);
                userMapper.insert(user);
            }
        }
    }
    /**
     * 数据验证
     */
    private boolean validateData(UserExcelDTO data) {
        // 姓名和用户名不能为空
        if (data.getName() == null || data.getName().isEmpty()) {
            return false;
        }
        if (data.getUsername() == null || data.getUsername().isEmpty()) {
            return false;
        }
        // 邮箱格式验证
        if (data.getEmail() != null && !data.getEmail().isEmpty()) {
            if (!data.getEmail().matches("^[A-Za-z0-9+_.-]+@(.+)$")) {
                return false;
            }
        }
        // 手机号格式验证
        if (data.getPhone() != null && !data.getPhone().isEmpty()) {
            if (!data.getPhone().matches("^1[3-9]\\d{9}$")) {
                return false;
            }
        }
        return true;
    }
    /**
     * 查询用户列表
     */
    private List<User> getUserList(String name) {
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        if (name != null && !name.isEmpty()) {
            wrapper.like(User::getName, name);
        }
        wrapper.orderByDesc(User::getCreateTime);
        return userMapper.selectList(wrapper);
    }
}

Controller层

package com.example.demo.controller;
import com.example.demo.model.UserExcelDTO;
import com.example.demo.service.UserService;
import com.example.demo.util.ResponseResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
@RestController
@RequestMapping("/api/user")
public class UserController {
    @Autowired
    private UserService userService;
    /**
     * 导出用户数据
     */
    @GetMapping("/export")
    public void exportUsers(HttpServletResponse response,
                           @RequestParam(required = false) String name) {
        userService.exportUsers(response, name);
    }
    /**
     * 导入用户数据
     */
    @PostMapping("/import")
    public ResponseResult<List<UserExcelDTO>> importUsers(
            @RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseResult.error("上传文件不能为空");
        }
        // 检查文件格式
        String filename = file.getOriginalFilename();
        if (filename != null && !filename.endsWith(".xlsx") && !filename.endsWith(".xls")) {
            return ResponseResult.error("文件格式不正确，请上传Excel文件");
        }
        try {
            List<UserExcelDTO> errorList = userService.importUsers(file);
            return ResponseResult.success(errorList);
        } catch (Exception e) {
            return ResponseResult.error("导入失败：" + e.getMessage());
        }
    }
    /**
     * 下载导入模板
     */
    @GetMapping("/template")
    public void downloadTemplate(HttpServletResponse response) {
        userService.downloadTemplate(response);
    }
}

通用响应类

package com.example.demo.util;
import lombok.Data;
@Data
public class ResponseResult<T> {
    private Integer code;
    private String message;
    private T data;
    public static <T> ResponseResult<T> success(T data) {
        ResponseResult<T> result = new ResponseResult<>();
        result.setCode(200);
        result.setMessage("操作成功");
        result.setData(data);
        return result;
    }
    public static <T> ResponseResult<T> success(String message, T data) {
        ResponseResult<T> result = new ResponseResult<>();
        result.setCode(200);
        result.setMessage(message);
        result.setData(data);
        return result;
    }
    public static <T> ResponseResult<T> error(String message) {
        ResponseResult<T> result = new ResponseResult<>();
        result.setCode(500);
        result.setMessage(message);
        return result;
    }
    public static <T> ResponseResult<T> error(Integer code, String message) {
        ResponseResult<T> result = new ResponseResult<>();
        result.setCode(code);
        result.setMessage(message);
        return result;
    }
}

运行配置

# application.yml
server:
  port: 8080
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/import_export_demo?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: root
    password: root
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: auto

前端测试页面

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">导入导出测试</title>
    <link href="https://cdn.bootcdn.net/ajax/libs/twitter-bootstrap/4.6.2/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    <div class="container mt-5">
        <h2>用户数据导入导出</h2>
        <div class="mt-4">
            <!-- 导出功能 -->
            <div class="card mb-4">
                <div class="card-header">
                    <h5>导出用户数据</h5>
                </div>
                <div class="card-body">
                    <div class="form-group">
                        <label>按姓名筛选（可选）</label>
                        <input type="text" id="exportName" class="form-control" 
                               style="max-width: 300px;" placeholder="请输入用户姓名">
                    </div>
                    <button class="btn btn-primary" onclick="exportUsers()">导出Excel</button>
                </div>
            </div>
            <!-- 导入功能 -->
            <div class="card">
                <div class="card-header">
                    <h5>导入用户数据</h5>
                </div>
                <div class="card-body">
                    <div class="form-group">
                        <input type="file" id="importFile" class="form-control-file" 
                               accept=".xlsx,.xls" style="max-width: 300px;">
                    </div>
                    <button class="btn btn-success" onclick="importUsers()">导入Excel</button>
                    <button class="btn btn-info" onclick="downloadTemplate()" style="margin-left: 10px;">
                        下载模板
                    </button>
                </div>
            </div>
            <!-- 错误数据显示 -->
            <div id="errorInfo" class="mt-4"></div>
        </div>
    </div>
    <script>
        // 导出用户数据
        function exportUsers() {
            const name = document.getElementById('exportName').value;
            const params = new URLSearchParams();
            if (name) {
                params.append('name', name);
            }
            window.location.href = `/api/user/export?${params.toString()}`;
        }
        // 导入用户数据
        function importUsers() {
            const fileInput = document.getElementById('importFile');
            const file = fileInput.files[0];
            if (!file) {
                alert('请选择要导入的Excel文件');
                return;
            }
            const formData = new FormData();
            formData.append('file', file);
            fetch('/api/user/import', {
                method: 'POST',
                body: formData
            })
            .then(response => response.json())
            .then(data => {
                if (data.code === 200) {
                    alert('导入成功');
                    if (data.data && data.data.length > 0) {
                        displayErrors(data.data);
                    }
                } else {
                    alert(data.message);
                }
            })
            .catch(error => {
                console.error('导入失败:', error);
                alert('导入失败');
            });
        }
        // 下载模板
        function downloadTemplate() {
            window.location.href = '/api/user/template';
        }
        // 显示错误数据
        function displayErrors(errors) {
            let html = '<h5>导入失败的数据：</h5>';
            html += '<table class="table table-bordered">';
            html += '<thead><tr><th>姓名</th><th>用户名</th><th>邮箱</th><th>错误信息</th></tr></thead>';
            html += '<tbody>';
            errors.forEach(error => {
                const errorMsg = validateError(error);
                html += `<tr>
                    <td>${error.name || '-'}</td>
                    <td>${error.username || '-'}</td>
                    <td>${error.email || '-'}</td>
                    <td class="text-danger">${errorMsg}</td>
                </tr>`;
            });
            html += '</tbody></table>';
            document.getElementById('errorInfo').innerHTML = html;
        }
        // 简单验证错误信息
        function validateError(data) {
            if (!data.name) return '姓名不能为空';
            if (!data.username) return '用户名不能为空';
            if (data.email && !isEmail(data.email)) return '邮箱格式不正确';
            if (data.phone && !isPhone(data.phone)) return '手机号格式不正确';
            return '其他错误';
        }
        function isEmail(email) {
            return /^[^@]+@[^@]+\.[^@]+$/.test(email);
        }
        function isPhone(phone) {
            return /^1[3-9]\d{9}$/.test(phone);
        }
    </script>
</body>
</html>

数据库建表SQL

CREATE DATABASE IF NOT EXISTS import_export_demo DEFAULT CHARACTER SET utf8mb4;
USE import_export_demo;
CREATE TABLE IF NOT EXISTS `user_info` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COMMENT '姓名',
    `username` VARCHAR(50) NOT NULL COMMENT '用户名',
    `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
    `age` INT DEFAULT NULL COMMENT '年龄',
    `address` VARCHAR(200) DEFAULT NULL COMMENT '地址',
    `status` VARCHAR(20) DEFAULT 'active' COMMENT '状态',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_username` (`username`),
    KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

测试用例

package com.example.demo;
import com.example.demo.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class ImportExportDemoApplicationTests {
    @Autowired
    private UserService userService;
    @Test
    void contextLoads() {
        // 测试数据导出逻辑
        System.out.println("测试用例执行成功");
    }
}

使用说明

启动项目：运行 ImportExportDemoApplication 主类
API测试：
- 导出：GET /api/user/export
- 导入：POST /api/user/import (上传Excel文件)
- 模板下载：GET /api/user/template
Excel文件格式：
- 第一行为列头（自动识别）
- 支持字段：用户ID、姓名、用户名、邮箱、手机号、年龄、地址、状态、创建时间
功能特点：
- 支持大数据量分批处理
- 导入时自动去重（按用户名）
- 数据验证和错误反馈
- 支持模板下载

这个案例提供了完整的导入导出功能,您可以根据实际需求进行调整和扩展。

本文地址： https://bmjup.com/post/42634.html